PostgreSQL如何将generate_series生成的日期序列关联至现有数据集？

现有如下工时记录数据集：

'item': 'test', 'assignee_id': 1, 'date': datetime.date(2023, 3, 1), 'hours_worked': 1
'item': 'test', 'assignee_id': 1, 'date': datetime.date(2023, 3, 4), 'hours_worked': 2
'item': 'test', 'assignee_id': 2, 'date': datetime.date(2023, 3, 2), 'hours_worked': 2

该数据记录了人员在指定日期的工时，但缺少部分维度组合的空记录（例如经办人1的2023/03/02、2023/03/03），需要基于日期、经办人、任务三个维度补全所有缺失的记录。

已通过generate_series生成日期序列，但不知道如何关联原数据集完成补全。

要补全多维度的缺失记录，核心是先构建出所有可能的维度组合，再通过左连接关联原数据填充工时：

1. 修正日期序列范围 #

你提供的generate_series存在日期范围错误（结束日期2004-02-01早于开始日期2023-01-01），先调整为符合需求的范围（比如覆盖原数据的2023年3月1日至3月4日）：

SELECT t.day::date 
FROM generate_series(
    timestamp '2023-03-01',
    timestamp '2023-03-04',
    interval '1 day'
) AS t(day);

2. 构建完整维度组合 #

通过**交叉连接（CROSS JOIN）**生成日期、经办人、任务的所有可能组合：

• 从日期序列获取所有日期
• 从原数据中提取唯一的assignee_id和item（如果有固定的维度列表也可以直接用）

示例SQL：

WITH all_dates AS (
    -- 生成目标日期范围的所有日期
    SELECT t.day::date AS work_date
    FROM generate_series(
        timestamp '2023-03-01',
        timestamp '2023-03-04',
        interval '1 day'
    ) AS t(day)
),
all_assignees_items AS (
    -- 提取原数据中所有唯一的经办人和任务组合
    SELECT DISTINCT assignee_id, item
    FROM time_records -- 假设原数据表名为time_records
)
-- 交叉连接得到所有维度组合
SELECT 
    adi.item,
    adi.assignee_id,
    ad.work_date
FROM all_dates ad
CROSS JOIN all_assignees_items adi;

3. 左连接原数据补全工时 #

将上述完整维度组合与原数据集左连接，缺失的hours_worked可以设为0（或保留NULL，根据业务需求）：

WITH all_dates AS (
    SELECT t.day::date AS work_date
    FROM generate_series(
        timestamp '2023-03-01',
        timestamp '2023-03-04',
        interval '1 day'
    ) AS t(day)
),
all_assignees_items AS (
    SELECT DISTINCT assignee_id, item
    FROM time_records
)
SELECT 
    adi.item,
    adi.assignee_id,
    ad.work_date,
    COALESCE(tr.hours_worked, 0) AS hours_worked -- 用COALESCE将NULL转为0
FROM all_dates ad
CROSS JOIN all_assignees_items adi
LEFT JOIN time_records tr 
    ON tr.date = ad.work_date 
    AND tr.assignee_id = adi.assignee_id 
    AND tr.item = adi.item
ORDER BY adi.assignee_id, ad.work_date;

执行后会得到所有维度组合的记录，缺失工时的记录会显示hours_worked=0。

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