Snowflake中如何按连续日期判断客户FLAG_1连续3天为1并生成FLAG_2

问题说明 #

现有订单表TMP_TEST，字段含义如下：

• CUSTOMER_ID：客户ID
• ORDER_DATE：订单日期
• FLAG_1：订单是否包含特定商品，1为包含，0为不包含
  需要按客户维度校验是否存在至少连续3天FLAG_1=1的记录，最终输出新表，包含CUSTOMER_ID和FLAG_2字段（满足连续条件为1，不满足为0）。

样例基础数据 #

CREATE TABLE TMP_TEST
(
CUSTOMER_ID INT,
ORDER_DATE DATE,
FLAG_1 INT
);

INSERT INTO TMP_TEST (CUSTOMER_ID, ORDER_DATE, FLAG_1)
VALUES
  (001, '2020-04-01', 0),
  (001, '2020-04-02', 1),
  (001, '2020-04-03', 1),
  (001, '2020-04-04', 1),
  (001, '2020-04-05', 1),
  (001, '2020-04-06', 0),
  (001, '2020-04-07', 0),
  (001, '2020-04-08', 0),
  (001, '2020-04-09', 1),
  (002, '2020-04-10', 1),
  (002, '2020-04-11', 0),
  (002, '2020-04-12', 0),
  (002, '2020-04-13', 1),
  (002, '2020-04-14', 1),
  (002, '2020-04-15', 0),
  (002, '2020-04-16', 1),
  (002, '2020-04-17', 1);

实现思路 #

采用连续日期孤岛的标准窗口函数解法，执行效率远高于自连接方案，适配Snowflake语法：

• 先按客户ID、FLAG_1值分区，按订单日期排序生成行号，用订单日期减去对应行号的天数，同一段连续相同FLAG值的记录会得到相同的分组标记
• 筛选FLAG_1=1的分组，统计每个分组的连续天数
• 按客户聚合，只要存在任意一个分组连续天数≥3，就将该客户的FLAG_2标记为1，否则为0

可直接运行的代码 #

-- 直接创建包含结果的新表
CREATE TABLE TMP_CUSTOMER_FLAG AS
WITH ordered_records AS (
    SELECT
        CUSTOMER_ID,
        ORDER_DATE,
        FLAG_1,
        DATEADD(
            day,
            -ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID, FLAG_1 ORDER BY ORDER_DATE),
            ORDER_DATE
        ) AS continuous_group_id
    FROM TMP_TEST
),
flag1_consecutive_stats AS (
    SELECT
        CUSTOMER_ID,
        COUNT(*) AS continuous_days
    FROM ordered_records
    WHERE FLAG_1 = 1
    GROUP BY CUSTOMER_ID, continuous_group_id
)
SELECT
    t.CUSTOMER_ID,
    CASE WHEN MAX(COALESCE(s.continuous_days, 0)) >= 3 THEN 1 ELSE 0 END AS FLAG_2
FROM TMP_TEST t
LEFT JOIN flag1_consecutive_stats s
    ON t.CUSTOMER_ID = s.CUSTOMER_ID
GROUP BY t.CUSTOMER_ID;

结果验证 #

执行后查询结果表，和预期完全一致：

客户1在2020-04-02至2020-04-05连续4天FLAG_1=1，满足条件标记为1；客户2最长连续FLAG_1=1的天数为2，不满足条件标记为0。

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