如何用SQL Server查询NTN对应Channel连续为Green的次数及时间区间

这个需求属于SQL经典的连续区间分组（岛屿问题），通过窗口函数即可实现，具体方案如下：

实现逻辑 #

• 第一步：筛选所有Channel为Green的记录，排除其他通道数据
• 第二步：对相同NTN的记录按Date_Time升序排序，生成行号
• 第三步：用Date_Time减去和行号等量的时间间隔，得到分组标识：连续的记录减去对应行号后得到的时间值完全相同，可作为同一连续组的标记
• 第四步：按NTN和分组标识聚合，统计每组的记录数、最大/最小时间

完整SQL代码 #

WITH filtered_green AS (
    -- 筛选Green通道的记录
    SELECT NTN, Date_Time
    FROM 你的实际表名 -- 请替换为你自己的数据表名称
    WHERE Channel = 'Green'
),
ranked_data AS (
    -- 按NTN分区、时间升序生成行号
    SELECT 
        NTN,
        Date_Time,
        ROW_NUMBER() OVER(PARTITION BY NTN ORDER BY Date_Time ASC) AS row_num
    FROM filtered_green
),
group_data AS (
    -- 生成连续组的分组标识
    SELECT 
        NTN,
        Date_Time,
        DATEADD(MINUTE, -row_num, Date_Time) AS group_flag
    FROM ranked_data
)
-- 聚合得到最终结果
SELECT 
    NTN,
    COUNT(*) AS Greens,
    MAX(Date_Time) AS [Max(Date_Time)],
    MIN(Date_Time) AS [Min(Date_Time)]
FROM group_data
GROUP BY NTN, group_flag
-- 若需要保留仅1条的连续Green组，删除下面的HAVING条件即可
HAVING COUNT(*) >= 2
ORDER BY NTN

说明 #

如果你的数据时间粒度小于分钟（比如示例里到毫秒），可以把DATEADD的第一个参数替换为SECOND或者MILLISECOND，不影响分组结果。

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