时间序列查询中筛选股票月末值：适配月末非工作日场景

完全可以实现！这个需求是金融数据处理里非常常见的场景，我给你拆解清晰的实现思路，再附上主流数据库的可运行代码：

核心思路 #

要满足“月末为周末则取当月最后工作日”的要求，我们需要分两步走：

• 第一步：为每只股票的每个月份，计算出该月实际最后交易日（如果月末是周六/周日，就往前调整到最近的周五；如果月末是工作日则直接用月末日期）
• 第二步：将原表数据与计算出的“月度最后交易日”关联，筛选出对应日期的观测记录

主流数据库实现代码 #

SQL Server 版本 #

WITH MonthlyLastTradeDate AS (
    SELECT
        Stock,
        -- 计算当月最后交易日：月末是周日减2天，周六减1天，否则用月末
        DATEADD(DAY, 
            CASE DATEPART(WEEKDAY, EOMONTH(DatePrice)) 
                WHEN 1 THEN -2  -- SQL Server中WEEKDAY=1代表周日
                WHEN 7 THEN -1  -- WEEKDAY=7代表周六
                ELSE 0 
            END, 
            EOMONTH(DatePrice)
        ) AS LastTradeDateOfMonth,
        YEAR(DatePrice) AS TradeYear,
        MONTH(DatePrice) AS TradeMonth
    FROM YourStockTable  -- 替换成你的表名
    GROUP BY Stock, YEAR(DatePrice), MONTH(DatePrice), EOMONTH(DatePrice)
)
SELECT t.*
FROM YourStockTable t
JOIN MonthlyLastTradeDate m
    ON t.Stock = m.Stock
    AND t.DatePrice = m.LastTradeDateOfMonth
WHERE YEAR(t.DatePrice) = 2017  -- 替换成你需要的年份
ORDER BY t.Stock, t.DatePrice;

MySQL 版本 #

WITH MonthlyLastTradeDate AS (
    SELECT
        Stock,
        -- MySQL中WEEKDAY=5是周六，=6是周日
        CASE WEEKDAY(LAST_DAY(DatePrice))
            WHEN 5 THEN DATE_SUB(LAST_DAY(DatePrice), INTERVAL 1 DAY)
            WHEN 6 THEN DATE_SUB(LAST_DAY(DatePrice), INTERVAL 2 DAY)
            ELSE LAST_DAY(DatePrice)
        END AS LastTradeDateOfMonth,
        YEAR(DatePrice) AS TradeYear,
        MONTH(DatePrice) AS TradeMonth
    FROM YourStockTable  -- 替换成你的表名
    GROUP BY Stock, YEAR(DatePrice), MONTH(DatePrice), LAST_DAY(DatePrice)
)
SELECT t.*
FROM YourStockTable t
JOIN MonthlyLastTradeDate m
    ON t.Stock = m.Stock
    AND t.DatePrice = m.LastTradeDateOfMonth
WHERE YEAR(t.DatePrice) = 2017
ORDER BY t.Stock, t.DatePrice;

PostgreSQL 版本 #

WITH MonthlyLastTradeDate AS (
    SELECT
        Stock,
        -- PostgreSQL中DOW=0是周日，=6是周六
        CASE EXTRACT(DOW FROM DATE_TRUNC('month', DatePrice) + INTERVAL '1 month - 1 day')
            WHEN 0 THEN DATE_TRUNC('month', DatePrice) + INTERVAL '1 month - 3 days'
            WHEN 6 THEN DATE_TRUNC('month', DatePrice) + INTERVAL '1 month - 2 days'
            ELSE DATE_TRUNC('month', DatePrice) + INTERVAL '1 month - 1 day'
        END::DATE AS LastTradeDateOfMonth,
        EXTRACT(YEAR FROM DatePrice) AS TradeYear,
        EXTRACT(MONTH FROM DatePrice) AS TradeMonth
    FROM YourStockTable  -- 替换成你的表名
    GROUP BY Stock, EXTRACT(YEAR FROM DatePrice), EXTRACT(MONTH FROM DatePrice), DATE_TRUNC('month', DatePrice)
)
SELECT t.*
FROM YourStockTable t
JOIN MonthlyLastTradeDate m
    ON t.Stock = m.Stock
    AND t.DatePrice = m.LastTradeDateOfMonth
WHERE EXTRACT(YEAR FROM t.DatePrice) = 2017
ORDER BY t.Stock, t.DatePrice;

补充说明 #

• 如果是单只股票，查询2017年数据会返回12条结果，完全符合你的预期；如果是多只股票，结果数会是「股票数量×12」
• 要是你的业务需要考虑法定节假日（比如月末是工作日但恰逢节假日休市），可以把代码逻辑扩展，结合节假日表进一步调整最后交易日的计算逻辑
• 记得把代码中的YourStockTable替换成你实际的表名

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