# 1) 개별 연도·종목 단위 수익률 및 5분위 관측치 테이블
size_obs_df = con.execute(f"""
WITH YearlyBounds AS (
SELECT
EXTRACT(YEAR FROM Date) AS yr,
Market,
Code,
ARG_MIN(Name, Date) AS Name,
ARG_MIN(Marcap, Date) AS start_marcap,
ARG_MAX(Marcap, Date) AS end_marcap,
ARG_MIN(Close, Date) AS start_close,
ARG_MAX(Close, Date) AS end_close,
EXP(SUM(LN(GREATEST(1.0 + COALESCE(ChangesRatio, 0) / 100.0, 0.0001)))) - 1.0 AS compound_return,
MIN(Date) AS first_trade_date,
MAX(Date) AS last_trade_date,
COUNT(*) AS trading_days
FROM '{PARQUET_GLOB}'
WHERE Market IN ('KOSPI', 'KOSDAQ')
AND Volume > 0
GROUP BY EXTRACT(YEAR FROM Date), Market, Code
)
SELECT
yr,
Market,
Code,
Name,
CASE
WHEN yr < 2000 THEN '1990년대'
WHEN yr < 2010 THEN '2000년대'
WHEN yr < 2020 THEN '2010년대'
ELSE '2020년대'
END AS decade,
NTILE(5) OVER (PARTITION BY yr, Market ORDER BY start_marcap DESC) AS size_quintile,
compound_return,
(end_close - start_close) / NULLIF(start_close, 0) AS close_return,
(end_marcap - start_marcap) / NULLIF(start_marcap, 0) AS marcap_return
FROM YearlyBounds
WHERE trading_days >= 200 -- 연중 정상 거래 종목 필터링
""").df()
quintile_labels = {
1: 'Q1 (대형주)',
2: 'Q2 (중대형)',
3: 'Q3 (중형주)',
4: 'Q4 (중소형)',
5: 'Q5 (소형주)'
}
size_obs_df['quintile_name'] = size_obs_df['size_quintile'].map(quintile_labels)
# 2) 시장별·5분위별 요약 통계 테이블
size_summary_df = con.execute("""
SELECT
Market,
size_quintile,
COUNT(*) AS stock_year_obs,
ROUND(MEDIAN(compound_return), 4) AS med_compound_ret,
ROUND(QUANTILE_CONT(compound_return, 0.25), 4) AS q25_compound_ret,
ROUND(QUANTILE_CONT(compound_return, 0.75), 4) AS q75_compound_ret,
ROUND(AVG(compound_return), 4) AS mean_compound_ret,
ROUND(MEDIAN(marcap_return), 4) AS med_marcap_ret,
ROUND(AVG(marcap_return), 4) AS mean_marcap_ret
FROM size_obs_df
GROUP BY Market, size_quintile
ORDER BY CASE WHEN Market = 'KOSPI' THEN 1 ELSE 2 END, size_quintile
""").df()
size_summary_df['quintile_name'] = size_summary_df['size_quintile'].map(quintile_labels)
