如何使用generate_series为缺失的iso_week记录补count为0

现有业务表存储员工客户维度的周度计数数据，包含字段：First（名）、Last（姓）、Client（客户名）、iso_week（ISO周，格式为年_周数）、count（计数），现有样例数据如下：

First   Last    Client          iso_week  count
Aaron   Cook    AVALON          2018_01   1
Aaron   Cook    AVALON          2018_02   1
Aaron   Cook    AVALON          2018_04   2
Angela  Myers   New Western     2018_03   3

需求说明 #

为每个「员工+客户」唯一组合补全所有连续的iso_week记录，缺失周的count字段统一填充为0，预期输出效果如下：

First   Last    Client          iso_week  count
Aaron   Cook    AVALON          2018_01   1
Aaron   Cook    AVALON          2018_02   1
Aaron   Cook    AVALON          2018_03   0
Aaron   Cook    AVALON          2018_04   2
Angela  Myers   New Western     2018_01   0
Angela  Myers   New Western     2018_02   0
Angela  Myers   New Western     2018_03   3
Angela  Myers   New Western     2018_04   0

实现方案（基于PostgreSQL的generate_series函数） #

实现逻辑分为三步：

• 首先从原表提取ISO周的最小、最大值，生成连续的周序列，统一转换为年_周数格式
• 提取原表所有去重的「员工+客户」组合，和全量周序列做笛卡尔积，得到所有需要的维度组合行
• 左关联原业务表的计数数据，空值补0即可

对应SQL代码如下：

WITH 
-- 1、获取所有去重的员工+客户组合
distinct_user_client AS (
    SELECT DISTINCT First, Last, Client
    FROM your_business_table
),
-- 2、计算全局周范围，生成连续的ISO周序列
week_range AS (
    SELECT 
        MIN(to_date(iso_week, 'IYYY_IW')) AS min_week,
        MAX(to_date(iso_week, 'IYYY_IW')) AS max_week
    FROM your_business_table
),
all_weeks AS (
    SELECT 
        to_char(generate_series(min_week, max_week, '1 week'::interval), 'IYYY_IW') AS iso_week
    FROM week_range
)
-- 3、笛卡尔积关联后左匹配原始计数，空值补0
SELECT 
    duc.First,
    duc.Last,
    duc.Client,
    aw.iso_week,
    COALESCE(yt.count, 0) AS count
FROM distinct_user_client duc
CROSS JOIN all_weeks aw
LEFT JOIN your_business_table yt
    ON duc.First = yt.First
    AND duc.Last = yt.Last
    AND duc.Client = yt.Client
    AND aw.iso_week = yt.iso_week
ORDER BY duc.First, duc.Last, duc.Client, aw.iso_week;

注意：将代码中的your_business_table替换为你的实际业务表名即可。如果你的周范围需要自定义而非从原表取，可直接修改week_range中的min_week和max_week赋值。

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