统计同一客户两种场景下的同产品复购次数需求

需求说明 #

需统计同一客户在两种场景下的同产品复购次数：

• 场景一：特定天数内复购
• 场景二：特定间隔期内复购（间隔期定义为同一客户购买同一产品时，上一次订单的EndDay与下一次订单的StartDay的差值）

样本数据 #

create table tbl
(
  Customer  varchar(5),
  StartDay date,
  EndDay date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      '1/4/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      '2/7/2021',      'Hat',          10.00),
('A',       '1/7/2019',      '1/8/2019',      'Shoe',        10.00),
('B',       '5/8/2018',      '5/9/2018',      'Shoe',         10.00),
('A',       '2/1/2019',      '2/3/2019',      'Shoe',        10.00),
('C',       '6/6/2020',      '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',     '12/9/2021',     'Cloth',       10.00),
('A',       '3/3/2019',      '3/17/2019',     'Cloth',        10.00),
('C',       '7/8/2020',      '7/12/2020',      'Hat',         10.00),
('E',       '7/2/2020',      '9/1/2020',      'Hat',        10.00),
('A',       '3/3/2019',      '3/7/2019',      'Shoe',        10.00),
('A',       '7/5/2022',      '7/9/2022',      'Hat',       10.00),
('C',       '6/6/2020',      '6/8/2020',      'Shoe',        10.00),
('B',       '8/2/2018',      '8/9/2018',      'Shoe',         10.00),
('A',       '1/1/2019',      '1/11/2019',     'Cloth',        10.00),
('E',       '9/3/2020',      '10/1/2020',      'Hat',        10.00),
('E',       '7/2/2020',      '7/8/2020',      'Shoe',       10.00);

示例说明 #

• 客户A首次购买Shoe的EndDay为'1/4/2019'，第二次购买Shoe的StartDay为'1/7/2019'，间隔期在30天内；
• 客户B首次购买Shoe的EndDay为'5/8/2018'，第二次购买Shoe的StartDay为'8/2/2018'，间隔期在60-90天内。

预期结果 #

• 场景一：统计各客户-产品组合在指定天数内的复购次数，结果包含客户、产品、对应天数的复购次数字段
• 场景二：按间隔期区间（如0-30天、31-60天、61-90天等）统计各客户-产品组合的复购次数分布

实现方案 #

场景一：特定天数内复购统计（以30天为例） #

通过LAG()函数获取同一客户同产品的上一次订单结束日期，计算当前订单开始日期与上一次结束日期的间隔天数，统计符合特定天数的复购次数：

WITH ordered_orders AS (
    SELECT 
        Customer,
        Product,
        StartDay,
        LAG(EndDay) OVER (PARTITION BY Customer, Product ORDER BY StartDay) AS prev_end_day
    FROM tbl
)
SELECT 
    Customer,
    Product,
    COUNT(CASE WHEN DATEDIFF(StartDay, prev_end_day) <= 30 THEN 1 END) AS repurchase_count_30d
FROM ordered_orders
WHERE prev_end_day IS NOT NULL
GROUP BY Customer, Product
ORDER BY Customer, Product;

注：若需统计其他天数，修改<= 30中的数值即可。

场景二：特定间隔期内复购统计 #

先计算间隔天数，再按预设区间分组统计各区间的复购次数：

WITH ordered_orders AS (
    SELECT 
        Customer,
        Product,
        DATEDIFF(StartDay, LAG(EndDay) OVER (PARTITION BY Customer, Product ORDER BY StartDay)) AS interval_days
    FROM tbl
),
interval_groups AS (
    SELECT 
        Customer,
        Product,
        CASE 
            WHEN interval_days <= 30 THEN '0-30天'
            WHEN interval_days <= 60 THEN '31-60天'
            WHEN interval_days <= 90 THEN '61-90天'
            ELSE '90天以上'
        END AS interval_range
    FROM ordered_orders
    WHERE interval_days IS NOT NULL
)
SELECT 
    Customer,
    Product,
    interval_range,
    COUNT(*) AS repurchase_count
FROM interval_groups
GROUP BY Customer, Product, interval_range
ORDER BY Customer, Product, interval_range;

注：可根据需求调整CASE语句中的区间划分规则。

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