如何获取股票每月最后交易日的报价数据？SQL语句修正

原SQL尝试获取股票代码'2GO'的每月最后一天报价，但仅当最后交易日恰好是自然月月末时才会返回数据，若当月最后交易日为非月末日期（如7月29日），则会遗漏该月数据。原SQL如下：

select symbol, trade_date, close_price from soa_pse_stock_quotes_history where symbol = '2GO'
group by symbol, trade_date, close_price
having max(trade_date::date) = (date_trunc('month', trade_date::date) + interval '1 month - 1 day')::date
order by trade_date desc limit 100;

正确SQL语句 #

要获取每月实际最后交易日的报价，可使用窗口函数按月份分组，筛选出每个月中交易日期最晚的记录：

WITH monthly_last_trade AS (
    SELECT 
        symbol,
        trade_date,
        close_price,
        ROW_NUMBER() OVER (
            PARTITION BY date_trunc('month', trade_date::date) 
            ORDER BY trade_date DESC
        ) AS rn
    FROM soa_pse_stock_quotes_history
    WHERE symbol = '2GO'
)
SELECT symbol, trade_date, close_price
FROM monthly_last_trade
WHERE rn = 1
ORDER BY trade_date DESC
LIMIT 100;

说明 #

• 用date_trunc('month', trade_date::date)将交易日期按月分组，确保每个月为独立分组单元
• ROW_NUMBER()窗口函数按每个月内的交易日期倒序排序，给当月最后交易日的记录标记为rn=1
• 筛选rn=1的记录，即为每个月实际最后交易日的报价

期望结果示例：

2GO 2022-08-31 00:00:00 7.28
2GO 2022-06-30 00:00:00 6.82
2GO 2022-05-31 00:00:00 7.1
2GO 2022-03-31 00:00:00 7.31
2GO 2022-02-28 00:00:00 7.5
2GO 2021-12-31 00:00:00 7.61
2GO 2021-09-30 00:00:00 8.14
2GO 2021-08-31 00:00:00 8.06
2GO 2021-06-30 00:00:00 8.48
2GO 2021-05-31 00:00:00 8.34
2GO 2021-04-30 00:00:00 8.4
2GO 2021-03-31 00:00:00 8.5
2GO 2020-09-30 00:00:00 8.42
2GO 2020-06-30 00:00:00 9.63

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