如何基于连续N天is_virtual=1生成flag列？SQL技术问询

需求说明 #

现有一张包含日期、店铺、is_virtual字段的表，需要生成flag列：仅当某行属于连续N天（示例中N=3）is_virtual=1的区间时，flag值为1，其余情况为0。

示例数据 #

可直接用于测试的SQL CTE：

WITH CTE(date,shop,is_virtual) AS
(
  SELECT  '2024-02-25','shop1',0 UNION ALL
  SELECT'2024-02-24','shop1',1 UNION ALL
  SELECT'2024-02-23','shop1',1 UNION ALL
  SELECT'2024-02-22','shop1',1 UNION ALL
  SELECT'2024-02-21','shop1',0 UNION ALL
  SELECT'2024-02-20','shop1',0 UNION ALL
  SELECT'2024-02-19','shop1',1 UNION ALL
  SELECT'2024-02-18','shop1',1 UNION ALL
  SELECT'2024-02-17','shop1',0 UNION ALL
  SELECT'2024-02-16','shop1',1 UNION ALL
  SELECT'2024-02-15','shop1',1 UNION ALL
  SELECT'2024-02-14','shop1',1 UNION ALL
  SELECT'2024-02-13','shop1',1 UNION ALL
  SELECT'2024-02-12','shop1',0 UNION ALL
  SELECT'2024-02-11','shop1',1 
)
 SELECT C.* FROM CTE AS C

现有解法 #

已实现需求但写法较繁琐的SQL：

SELECT date, [is_virtual], flag, flag1
    , flag_result = CASE WHEN flag1 >= 3 THEN 1 ELSE 0 END
FROM (
    SELECT date, [is_virtual], flag
        , flag1 = SUM(CAST([is_virtual] AS int) ) OVER (PARTITION BY grp ORDER BY [is_virtual])
    FROM (
        SELECT date, [is_virtual], flag
            , grp = SUM(CASE WHEN [is_virtual] = prev THEN 0 ELSE 1 END) OVER (ORDER BY date)
        FROM (
            SELECT *
                , prev = LAG([is_virtual]) OVER (ORDER BY date)
            FROM [doexercises].[dbo].[osa1]
        ) s
    ) s1
) s2

更简洁的实现思路与代码 #

思路1：分组标记法（推荐） #

核心逻辑是先给连续相同is_virtual的记录分配组ID，再计算每组的长度，最后根据组长度和is_virtual值生成flag：

1. 用行号差值生成连续段的组ID；
2. 计算每个组的总记录数；
3. 判断当前行所在组的长度是否≥3且is_virtual=1，是则flag=1。

代码实现：

WITH CTE(date,shop,is_virtual) AS
(
  SELECT  '2024-02-25','shop1',0 UNION ALL
  SELECT'2024-02-24','shop1',1 UNION ALL
  SELECT'2024-02-23','shop1',1 UNION ALL
  SELECT'2024-02-22','shop1',1 UNION ALL
  SELECT'2024-02-21','shop1',0 UNION ALL
  SELECT'2024-02-20','shop1',0 UNION ALL
  SELECT'2024-02-19','shop1',1 UNION ALL
  SELECT'2024-02-18','shop1',1 UNION ALL
  SELECT'2024-02-17','shop1',0 UNION ALL
  SELECT'2024-02-16','shop1',1 UNION ALL
  SELECT'2024-02-15','shop1',1 UNION ALL
  SELECT'2024-02-14','shop1',1 UNION ALL
  SELECT'2024-02-13','shop1',1 UNION ALL
  SELECT'2024-02-12','shop1',0 UNION ALL
  SELECT'2024-02-11','shop1',1 
),
Grouped AS (
    SELECT 
        *,
        -- 生成连续段组ID：全局行号 - 同店铺同is_virtual分组内的行号
        grp = ROW_NUMBER() OVER (PARTITION BY shop ORDER BY date DESC) 
             - ROW_NUMBER() OVER (PARTITION BY shop, is_virtual ORDER BY date DESC)
    FROM CTE
),
GroupCount AS (
    SELECT 
        *,
        -- 计算当前组的总记录数
        cnt = COUNT(*) OVER (PARTITION BY shop, grp)
    FROM Grouped
)
SELECT 
    date,
    shop,
    is_virtual,
    flag = CASE 
              WHEN is_virtual = 1 AND cnt >= 3 THEN 1 
              ELSE 0 
           END
FROM GroupCount
ORDER BY date DESC;

思路2：滑动窗口法 #

通过滑动窗口计算当前行及前后N-1行的is_virtual总和，判断是否存在连续3天为1的情况：

WITH CTE(date,shop,is_virtual) AS
(
  SELECT  '2024-02-25','shop1',0 UNION ALL
  SELECT'2024-02-24','shop1',1 UNION ALL
  SELECT'2024-02-23','shop1',1 UNION ALL
  SELECT'2024-02-22','shop1',1 UNION ALL
  SELECT'2024-02-21','shop1',0 UNION ALL
  SELECT'2024-02-20','shop1',0 UNION ALL
  SELECT'2024-02-19','shop1',1 UNION ALL
  SELECT'2024-02-18','shop1',1 UNION ALL
  SELECT'2024-02-17','shop1',0 UNION ALL
  SELECT'2024-02-16','shop1',1 UNION ALL
  SELECT'2024-02-15','shop1',1 UNION ALL
  SELECT'2024-02-14','shop1',1 UNION ALL
  SELECT'2024-02-13','shop1',1 UNION ALL
  SELECT'2024-02-12','shop1',0 UNION ALL
  SELECT'2024-02-11','shop1',1 
),
SlidingWindow AS (
    SELECT 
        *,
        -- 当前行及后2行的is_virtual总和
        forward_sum = SUM(is_virtual) OVER (PARTITION BY shop ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING),
        -- 当前行及前2行的is_virtual总和
        backward_sum = SUM(is_virtual) OVER (PARTITION BY shop ORDER BY date DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    FROM CTE
)
SELECT 
    date,
    shop,
    is_virtual,
    flag = CASE 
              WHEN is_virtual = 1 AND (forward_sum >=3 OR backward_sum >=3) THEN 1
              ELSE 0
           END
FROM SlidingWindow
ORDER BY date DESC;

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