SQL如何统计时间序列中股票价格未发生变动的持续天数

前提说明 #

假设你存储股票日终价格的表名为stock_daily_price，核心字段如下：

• stock_code：股票代码/股票名称，示例中对应值为XYZ
• trade_date：交易日期，DATE类型
• close_price：当日收盘价，数值类型

核心逻辑 #

计算价格维持天数只需要三步：

1. 提取每只股票的最新交易日期和对应收盘价
2. 匹配到该股票上一次收盘价不等于最新价的最晚交易日期
3. 两个日期的差值即为当前价格维持的天数，若该股票上市后从未发生价格变动，则从首次记录的交易日期开始计算

可直接运行的SQL代码 #

WITH latest_price AS (
    -- 取每只股票最新交易日的价格信息
    SELECT 
        stock_code,
        trade_date AS latest_trade_date,
        close_price AS latest_price
    FROM (
        SELECT 
            stock_code,
            trade_date,
            close_price,
            ROW_NUMBER() OVER (PARTITION BY stock_code ORDER BY trade_date DESC) AS rn
        FROM stock_daily_price
        -- 仅查询单只股票时可打开下方注释
        -- WHERE stock_code = 'XYZ'
    ) t
    WHERE rn = 1
),
last_change_date AS (
    -- 取每只股票上一次价格变动的最后日期
    SELECT 
        s.stock_code,
        MAX(s.trade_date) AS change_date
    FROM stock_daily_price s
    INNER JOIN latest_price lp 
        ON s.stock_code = lp.stock_code 
        AND s.close_price != lp.latest_price
    GROUP BY s.stock_code
)
-- 最终计算维持天数
SELECT 
    lp.stock_code,
    lp.latest_price,
    lp.latest_trade_date - COALESCE(lcd.change_date, (SELECT MIN(trade_date) FROM stock_daily_price WHERE stock_code = lp.stock_code)) AS price_keep_days
FROM latest_price lp
LEFT JOIN last_change_date lcd ON lp.stock_code = lcd.stock_code;

示例场景验证 #

你提到的股票XYZ场景：最新价103，2021年10月23日价格从104变为103，假设最新交易日为2021年10月30日：

• 代码会匹配到上一次价格不等于103的最晚日期为2021年10月22日
• 计算得到价格维持天数为2021-10-30 - 2021-10-22 = 8，结果和预期一致

简化提示 #

如果仅需要查询单只股票的结果，在子查询中添加对应股票的过滤条件即可，执行效率更高。

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