SQL Server 2016按交易周期聚合时序数据的窗口函数实现求助

嘿，我来帮你搞定这个分区聚合的难题！你的核心需求是把每个以SampleAndTrade开头、后续跟着TradeOnly的连续记录划分为一个交易周期，然后聚合出每个周期的开始时间、结束时间和间隔分钟数。咱们一步步来实现：

核心思路：给每个交易周期打分组标识 #

因为每个交易周期都是以SampleAndTrade开启的，所以我们可以用窗口累计求和的方式，给每条记录分配一个分组ID：每遇到一条SampleAndTrade类型的记录，分组ID就加1，这样同一周期内的所有记录都会拥有相同的分组ID。

具体SQL实现 #

假设你的原表名为TradeData，字段分别是Name、Time、Type，可以用以下代码来实现转换：

WITH GroupedData AS (
    SELECT 
        Name,
        Time,
        Type,
        -- 给每个交易周期分配唯一分组ID：按Name分区，遇到SampleAndTrade就累加1
        SUM(CASE WHEN Type = 'SampleAndTrade' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Name ORDER BY Time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CycleGroupID
    FROM TradeData
)
SELECT 
    Name,
    -- 提取开始时间的时分部分
    FORMAT(MIN(Time), 'HH:mm') AS StartTime,
    -- 提取结束时间的时分部分
    FORMAT(MAX(Time), 'HH:mm') AS EndTime,
    -- 计算间隔分钟数：总时长/(记录数-1)，因为间隔数=记录数-1
    DATEDIFF(MINUTE, MIN(Time), MAX(Time)) / (COUNT(*) - 1) AS IntervalMin
FROM GroupedData
GROUP BY Name, CycleGroupID
ORDER BY Name, StartTime;

代码解释 #

1. CTE部分（GroupedData）：
   • 用SUM() OVER()窗口函数，按Name分区（不同的Name分开处理），按Time排序，从第一条到当前行累计SampleAndTrade的出现次数，得到CycleGroupID。这样每个新的SampleAndTrade都会开启一个新的分组。
2. 聚合部分：
   • 按Name和CycleGroupID分组，取每组的最早时间作为StartTime，最晚时间作为EndTime。
   • 间隔分钟数的计算：用总时长（结束时间-开始时间的分钟数）除以间隔数（记录数-1），比如4条记录有3个间隔，总时长30分钟的话，30/3=10分钟，正好匹配你的需求。

测试验证 #

用你提供的测试数据运行这段代码，就能得到你想要的输出结果：

Name StartTime EndTime IntervalMin
CL1  10:50     11:20   10
CL1  14:10     14:50   10
HO1  10:50     11:20   10
HO1  14:10     14:50   10

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