WITH target AS (
|
SELECT '002747.SZ' symbol UNION ALL SELECT '600889.SH'
|
), p AS (
|
SELECT d.symbol,d.trade_date,d.open_price,d.high_price,d.low_price,d.close_price,d.amount,
|
ROW_NUMBER() OVER (PARTITION BY d.symbol ORDER BY d.trade_date DESC) rn_desc
|
FROM a_share_daily_price d JOIN target t ON d.symbol=t.symbol
|
WHERE d.trade_date >= '2026-03-01'
|
), keydates AS (
|
SELECT symbol,
|
MAX(CASE WHEN trade_date <= '2026-03-31' THEN trade_date END) d_q1,
|
MAX(trade_date) d_latest
|
FROM p GROUP BY symbol
|
), prices AS (
|
SELECT k.symbol,k.d_q1,k.d_latest,
|
MAX(CASE WHEN p.trade_date=k.d_q1 THEN p.close_price END) close_q1,
|
MAX(CASE WHEN p.trade_date=k.d_latest THEN p.close_price END) close_latest,
|
MAX(CASE WHEN p.rn_desc=5 THEN p.close_price END) close_5d,
|
MAX(CASE WHEN p.rn_desc=10 THEN p.close_price END) close_10d,
|
MAX(CASE WHEN p.rn_desc=20 THEN p.close_price END) close_20d,
|
MAX(CASE WHEN p.rn_desc=60 THEN p.close_price END) close_60d,
|
AVG(CASE WHEN p.rn_desc<=20 THEN p.amount END) amount_20d,
|
AVG(CASE WHEN p.rn_desc<=60 THEN p.amount END) amount_60d,
|
MAX(CASE WHEN p.rn_desc<=20 THEN p.high_price END) high_20d,
|
MAX(CASE WHEN p.rn_desc<=60 THEN p.high_price END) high_60d
|
FROM keydates k JOIN p ON p.symbol=k.symbol GROUP BY k.symbol,k.d_q1,k.d_latest
|
)
|
SELECT symbol,d_q1,d_latest,
|
ROUND(close_q1,4) close_q1,
|
ROUND(close_latest,4) close_latest,
|
ROUND((close_latest/close_q1-1)*100,2) ret_since_q1_pct,
|
ROUND((close_latest/close_5d-1)*100,2) ret_5d_pct,
|
ROUND((close_latest/close_10d-1)*100,2) ret_10d_pct,
|
ROUND((close_latest/close_20d-1)*100,2) ret_20d_pct,
|
ROUND((close_latest/close_60d-1)*100,2) ret_60d_pct,
|
ROUND((close_latest/high_20d-1)*100,2) dist_high_20d_pct,
|
ROUND((close_latest/high_60d-1)*100,2) dist_high_60d_pct,
|
ROUND(amount_20d/100000000,2) amount_20d_yi,
|
ROUND(amount_60d/100000000,2) amount_60d_yi,
|
ROUND((amount_20d/amount_60d-1)*100,2) amount_20d_vs_60d_pct
|
FROM prices
|
