如何基于IPO日期提取股票上市后30/60/90天收盘价？

你的代码存在三个核心问题：

1. CTE stock_list 仅查询了IPOname和date，未包含Table2的CloseDate和Value字段，导致后续CASE语句无法引用这些必要数据
2. 未使用聚合函数，无法实现单股票一行的汇总结果
3. 精确匹配dateadd('days',30,date)=CloseDate容易因非交易日等情况无匹配结果，返回NULL

以下是完全匹配你需求的可行方案：

方案：取最接近IPO后30/60/90天的收盘价 #

该方案会自动匹配距离目标日期最近的交易日收盘价，完美贴合你的示例结果：

MySQL版本 #

WITH stock_days AS (
    SELECT
        t1.IPOName AS Stock,
        t1.Date AS IPO_Date,
        t2.CloseDate,
        t2.Value,
        -- 计算每个收盘价日期与目标日期的差距
        ABS(DATEDIFF(t2.CloseDate, DATE_ADD(t1.Date, INTERVAL 30 DAY))) AS diff_30,
        ABS(DATEDIFF(t2.CloseDate, DATE_ADD(t1.Date, INTERVAL 60 DAY))) AS diff_60,
        ABS(DATEDIFF(t2.CloseDate, DATE_ADD(t1.Date, INTERVAL 90 DAY))) AS diff_90
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.IPOName = t2.SYMBOL
)
SELECT
    s.Stock,
    -- 取距离IPO后30天最近的收盘价
    (SELECT Value FROM stock_days sd WHERE sd.Stock = s.Stock ORDER BY sd.diff_30 LIMIT 1) AS 30DayPrice,
    -- 取距离IPO后60天最近的收盘价
    (SELECT Value FROM stock_days sd WHERE sd.Stock = s.Stock ORDER BY sd.diff_60 LIMIT 1) AS 60DayPrice,
    -- 取距离IPO后90天最近的收盘价
    (SELECT Value FROM stock_days sd WHERE sd.Stock = s.Stock ORDER BY sd.diff_90 LIMIT 1) AS 90DayPrice
FROM (SELECT DISTINCT Stock FROM stock_days) s;

Oracle版本 #

WITH stock_days AS (
    SELECT
        t1.IPOName AS Stock,
        t1.Date AS IPO_Date,
        t2.CloseDate,
        t2.Value,
        ABS(DATEDIFF(day, t2.CloseDate, DATEADD(day, 30, t1.Date))) AS diff_30,
        ABS(DATEDIFF(day, t2.CloseDate, DATEADD(day, 60, t1.Date))) AS diff_60,
        ABS(DATEDIFF(day, t2.CloseDate, DATEADD(day, 90, t1.Date))) AS diff_90
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.IPOName = t2.SYMBOL
)
SELECT
    Stock,
    MAX(Value) KEEP (DENSE_RANK FIRST ORDER BY diff_30) AS 30DayPrice,
    MAX(Value) KEEP (DENSE_RANK FIRST ORDER BY diff_60) AS 60DayPrice,
    MAX(Value) KEEP (DENSE_RANK FIRST ORDER BY diff_90) AS 90DayPrice
FROM stock_days
GROUP BY Stock;

内容的提问来源于stack exchange，提问作者FlyingPickle