qcc 7회차
2번문제
WITH o_max AS (
SELECT ticker
,date_format(date, '%Y%m') AS highest_mth
,OPEN AS highest_open
,rank() OVER (PARTITION BY ticker ORDER BY OPEN desc) max_rnk
FROM stock_prices
WHERE OPEN IS NOT NULL
),
o_min as(
SELECT ticker
,date_format(date, '%Y%m') AS lowest_mth
,OPEN AS lowest_open
,rank() OVER (PARTITION BY ticker ORDER BY OPEN asc) min_rnk
FROM stock_prices
WHERE OPEN IS NOT NULL
)
SELECT omax.ticker
,omax.highest_mth
,omax.highest_open
,omin.lowest_mth
,omin.lowest_open
FROM o_max omax
JOIN o_min omin
ON omax.ticker = omin.ticker
AND max_rnk =1
AND min_rnk =1
ORDER BY 1;
2번문제 정답
WITH monthly_data as(
SELECT ticker
, date_format(date,'%Y%m') AS month_year
,open
FROM stock_prices sp
WHERE OPEN IS NOT NULL
), ranked_data AS (
SELECT ticker, month_year, open,
row_number() OVER (PARTITION BY ticker ORDER BY OPEN desc) rank_high,
row_number() OVER (PARTITION BY ticker ORDER BY OPEN ) rank_low
FROM monthly_data
)SELECT *
,max(CASE WHEN rank_low = 1 THEN OPEN END) AS lowest_open
,max(CASE WHEN rank_low = 1 THEN month_year END) AS lowest_mth
,max(CASE WHEN rank_high = 1 THEN OPEN END) AS highest_open
,max(CASE WHEN rank_high = 1 THEN month_year END) AS month_year
FROM ranked_data
GROUP BY 1
ORDER BY 1;
이 SQL 쿼리는 주식 데이터를 기반으로 각 종목(ticker)의 월별 최고가와 최저가를 계산하고, 이를 정리하여 반환합니다. 단계별로 쿼리를 분석해 보겠습니다.
1. WITH monthly_data CTE (Common Table Expression)
WITH monthly_data as(
SELECT ticker,
date_format(date,'%Y%m') AS month_year,
open
FROM stock_prices sp
WHERE OPEN IS NOT NULL
)
• 기능:
• stock_prices 테이블에서 데이터 추출.
• date 열을 %Y%m 형식(연도와 월)으로 변환하여 month_year 열 생성.
• open 값이 NULL이 아닌 데이터만 필터링.
• 결과: 각 종목(ticker)의 월별(month_year) 시작가(open) 데이터가 준비됩니다.
2. ranked_data CTE
ranked_data AS (
SELECT ticker, month_year, open,
row_number() OVER (PARTITION BY ticker ORDER BY OPEN DESC) rank_high,
row_number() OVER (PARTITION BY ticker ORDER BY OPEN) rank_low
FROM monthly_data
)
• 기능:
• 각 종목(ticker)에 대해 open 값 기준으로 순위를 매김.
• row_number() 함수로 다음 순위 생성:
• rank_high: 높은 가격 순서로 정렬한 순위.
• rank_low: 낮은 가격 순서로 정렬한 순위.
• 결과: 각 종목-월 조합의 open 값에 대해 최고가와 최저가 순위가 계산됩니다.
3. 최종 SELECT
SELECT ticker,
max(CASE WHEN rank_low = 1 THEN OPEN END) AS lowest_open,
max(CASE WHEN rank_low = 1 THEN month_year END) AS lowest_mth,
max(CASE WHEN rank_high = 1 THEN OPEN END) AS highest_open,
max(CASE WHEN rank_high = 1 THEN month_year END) AS month_year
FROM ranked_data
GROUP BY 1
ORDER BY 1;
• 주요 작업:
• rank_low = 1인 경우:
• 최저가 (lowest_open)와 해당 월 (lowest_mth)을 가져옴.
• rank_high = 1인 경우:
• 최고가 (highest_open)와 해당 월 (month_year)을 가져옴.
• MAX 함수는 조건에 맞는 값 중 가장 큰 값(실제로는 유일 값)을 반환합니다.
• 그룹화:
• 각 종목(ticker)별로 데이터를 그룹화.
• 정렬:
• 결과를 ticker 기준으로 오름차순 정렬.
최종 출력
• 열:
• ticker: 종목 이름.
• lowest_open: 해당 종목의 월별 최저 시작가.
• lowest_mth: 최저 시작가를 기록한 연도-월.
• highest_open: 해당 종목의 월별 최고 시작가.
• month_year: 최고 시작가를 기록한 연도-월.
활용 예시
• 주식 종목별로 월별 데이터에서 최고가와 최저가 및 해당 월을 효율적으로 추출할 때 유용한 쿼리입니다.
• 금융 분석, 투자 전략 수립, 월별 변동성 분석 등에 사용할 수 있습니다.