SQL查询实现客户购买数据中无购买记录的缺失周展示

实现思路 #

• 第一步：为每个客户生成从注册日起的完整周序列，覆盖该客户从首次注册到最晚购买记录对应的所有周
• 第二步：将生成的全量周序列和你已有的带购买周标记的用户购买表做左关联，无匹配的购买信息补空为-
• 第三步：按客户ID、周数排序后输出即可得到包含无购买空周的完整列表

参考SQL代码（Hive/Spark SQL版本，其他数据库仅需调整连续周序列生成逻辑即可） #

-- 预处理原表，计算每条购买记录对应的购买周（优化原case when逻辑，无需手动枚举周数）
with customer_purchase_with_week as (
    select 
        cust_id,
        cust_reg_date,
        cust_purchase_date,
        purchase_made,
        -- 按你定义的每10天为1周规则计算周数
        concat('Week ', ceil(datediff(cust_purchase_date, cust_reg_date)/10), ' Purchase') as week_purchase
    from customers_table
),
-- 统计每个客户的最大购买周数，用于确定周序列生成范围
customer_max_week as (
    select 
        cust_id,
        cust_reg_date,
        max(ceil(datediff(cust_purchase_date, cust_reg_date)/10)) as max_week
    from customers_table
    group by cust_id, cust_reg_date
),
-- 生成连续周序列，100为周数上限，可根据业务实际情况调整
all_weeks as (
    select pos+1 as week_num 
    from posexplode(split(space(100), ' '))
),
-- 生成每个客户对应的全量周列表
customer_full_weeks as (
    select 
        a.cust_id,
        a.cust_reg_date,
        concat('Week ', b.week_num, ' Purchase') as week_purchase
    from customer_max_week a
    left join all_weeks b on b.week_num <= a.max_week
)
-- 左关联购买数据，空值补'-'
select 
    a.cust_id,
    a.cust_reg_date,
    nvl(b.cust_purchase_date, '-') as cust_purchase_date,
    nvl(b.purchase_made, '-') as purchase_made,
    a.week_purchase
from customer_full_weeks a
left join customer_purchase_with_week b 
    on a.cust_id = b.cust_id 
    and a.week_purchase = b.week_purchase
order by a.cust_id, a.week_purchase

如果使用MySQL 8.0+、PostgreSQL这类支持递归CTE的数据库，仅需要替换周序列生成部分即可，示例如下：

-- MySQL 8.0+ 连续周序列生成示例
with recursive all_weeks as (
    select 1 as week_num
    union all
    select week_num + 1 from all_weeks where week_num < 100
)

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