使用MySQL Lead窗口函数统计连续两天均有消费的不同客户数

需求说明 #

统计销售表sales_t中每个日期t，同时在t日和t+1日都产生消费的不同客户数量。

表结构与测试数据 #

-- 创建表
CREATE TABLE sales_t(
  id INTEGER PRIMARY KEY,
  d_date date NOT NULL,
  sale INT NOT NULL,
  customer_n INT NOT NULL
);
-- 插入测试数据
INSERT INTO sales_t VALUES (1, '2021-06-30', 12,  1);
INSERT INTO sales_t VALUES (2, '2021-06-30', 22,  5);
INSERT INTO sales_t VALUES (3, '2021-06-30', 111,  3);
INSERT INTO sales_t VALUES (4, '2021-07-01', 27, 1);
INSERT INTO sales_t VALUES (5, '2021-07-01', 90, 4);
INSERT INTO sales_t VALUES (6, '2021-07-01', 33, 3);
-- 注：原始测试数据存在重复id=6的插入语句，实际执行可修改id为7避免主键冲突，不影响统计逻辑
INSERT INTO sales_t VALUES (7, '2021-07-01', 332, 3);

实现思路 #

• 先对客户+消费日期维度去重，排除同一客户同一天多笔消费的干扰
• 使用LEAD窗口函数按客户ID分组、按消费日期升序排序，获取每个客户下一次消费的日期
• 筛选出下一次消费日期等于当前日期+1天的客户，即为满足连续两天消费的客户
• 关联全量日期列表，统计每个日期对应的符合条件的客户数，无符合条件客户的日期返回0

完整查询SQL #

WITH customer_unique_dates AS (
    -- 客户消费日期去重
    SELECT DISTINCT customer_n, d_date
    FROM sales_t
),
customer_next_consume AS (
    -- 用LEAD窗口函数取每个客户的下一次消费日期
    SELECT
        customer_n,
        d_date,
        LEAD(d_date) OVER (PARTITION BY customer_n ORDER BY d_date) AS next_date
    FROM customer_unique_dates
),
all_stat_dates AS (
    -- 获取所有需要统计的日期
    SELECT DISTINCT d_date FROM sales_t
)
SELECT
    ad.d_date AS `Date`,
    COUNT(DISTINCT cn.customer_n) AS `sale_t_and_t+1`
FROM all_stat_dates ad
LEFT JOIN customer_next_consume cn
    ON ad.d_date = cn.d_date AND cn.next_date = DATE_ADD(cn.d_date, INTERVAL 1 DAY)
GROUP BY ad.d_date
ORDER BY ad.d_date;

输出结果 #

结果说明：2021-06-30对应的统计值为2，原因是客户1和客户3都在6月30日和7月1日产生了消费记录；2021-07-01没有后续的消费数据，因此统计值为0。

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