SQL需求：统计指定日期股票连续相同Action的天数

现有UserTable表结构及数据如下：

需求：按ticker分组，统计截至指定日期（如2022-03-03），该日期的Action值连续出现的天数，预期结果如下：

可直接传入指定日期，无需动态获取。已尝试使用CTE与窗口函数（ROW_NUMBER()、DENSE_RANK()）编写SQL，但未得到正确结果，寻求正确的SQL实现方案。

表创建与插入数据SQL #

CREATE TABLE UserTable
    ([Date] DATETIME2, [Ticker] varchar(5), [Action] varchar(5))
;
    
INSERT INTO UserTable
    ([Date], [Ticker], [Action])
VALUES
    ('2022-03-01' , 'AAPL' , 'BUY'),
    ('2022-03-02' , 'AAPL' , 'SELL'),
    ('2022-03-03' , 'AAPL' , 'BUY'),
    ('2022-03-01' , 'CMG' , 'SELL'),
    ('2022-03-02' , 'CMG' , 'HOLD'),
    ('2022-03-03' , 'CMG' , 'HOLD'),
    ('2022-03-01' , 'GPS' , 'SELL'),
    ('2022-03-02' , 'GPS' , 'SELL'),
    ('2022-03-03' , 'GPS' , 'SELL')
;

尝试的SQL代码 #

WITH CTE AS (SELECT Date, Ticker, Action,
                    ROW_NUMBER() OVER (PARTITION BY Ticker, Action ORDER BY Date) as row_num
             FROM UserTable)
SELECT Ticker, COUNT(DISTINCT Date) as count_of_days
FROM CTE
WHERE row_num = 1
GROUP BY Ticker;

WITH CTE AS (SELECT Date, Ticker, Action,
DENSE_RANK() OVER (PARTITION BY Ticker ORDER BY Action,Date) as rank
FROM table)
SELECT Ticker, COUNT(DISTINCT Date) as count_of_days
FROM CTE
WHERE rank = 1
GROUP BY Ticker;

核心思路是先锁定指定日期的操作类型，再向前追溯连续相同操作的天数，通过窗口函数标记连续分组后统计天数。以下是正确实现：

DECLARE @TargetDate DATE = '2022-03-03';

WITH TickerLatestAction AS (
    -- 获取指定日期每个ticker的Action
    SELECT Ticker, Action
    FROM UserTable
    WHERE CAST(Date AS DATE) = @TargetDate
),
OrderedActions AS (
    -- 按ticker分组，日期倒序排列，标记连续相同Action的分组
    SELECT 
        ut.Ticker,
        ut.Action,
        ut.Date,
        -- 当Action与前一条（倒序）不同时，分组ID加1
        SUM(CASE WHEN ut.Action = LAG(ut.Action) OVER (PARTITION BY ut.Ticker ORDER BY ut.Date DESC) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ut.Ticker ORDER BY ut.Date DESC) AS GroupId
    FROM UserTable ut
    JOIN TickerLatestAction tla ON ut.Ticker = tla.Ticker
    -- 只保留指定日期及之前的数据
    WHERE CAST(ut.Date AS DATE) <= @TargetDate
)
SELECT 
    oa.Ticker,
    -- 统计当前分组的天数，减1得到连续出现的天数（当天是第0天）
    COUNT(oa.Date) - 1 AS NumSequentialDaysAction
FROM OrderedActions oa
JOIN TickerLatestAction tla ON oa.Ticker = tla.Ticker
WHERE oa.GroupId = 1 -- 只取最新的连续分组
GROUP BY oa.Ticker;

代码说明 #

1. TickerLatestAction：筛选指定日期各股票的操作类型，作为后续统计的基准。
2. OrderedActions：对每个股票的历史操作按日期倒序排列，用SUM()结合LAG()标记连续相同操作的分组——当前操作与上一条（更早的一天）不同时，分组ID加1，确保连续相同操作被归为同一组。
3. 最后统计最新分组（GroupId=1）的天数，减1后得到连续出现的天数（例如GPS连续3天SELL，3-1=2；AAPL当天操作与前一天不同，分组仅1天，1-1=0）。

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