PostgreSQL如何补全无设备分配月份的记录并设为0

要实现补全无设备分配月份的记录并将assigned设为0，核心思路是先生成所有客户+所有目标月份的完整组合，再与原统计结果做左连接，最后将空值替换为0。以下是基于PostgreSQL的完整解决方案：

WITH date_range AS (
    -- 生成需要覆盖的月份范围，自动适配数据中的最早/最晚分配月份
    SELECT generate_series(
        (SELECT date_trunc('month', MIN("tsAssignation")) FROM "devicesAssignations"),
        (SELECT date_trunc('month', MAX("tsAssignation")) FROM "devicesAssignations"),
        INTERVAL '1 month'
    ) AS month_date
),
unique_clients AS (
    -- 提取所有有设备分配记录的客户（若有独立客户表，建议直接用客户表）
    SELECT DISTINCT "clientId" FROM "devicesAssignations"
),
client_month_pairs AS (
    -- 生成每个客户与每个月份的全量组合
    SELECT uc."clientId", dr.month_date
    FROM unique_clients uc
    CROSS JOIN date_range dr
)
SELECT 
    cmp."clientId",
    cmp.month_date AS date,
    COALESCE(stat.assigned, 0) AS assigned
FROM client_month_pairs cmp
LEFT JOIN (
    -- 原统计逻辑封装为子查询
    SELECT 
        "clientId", 
        date_trunc('month', "tsAssignation") AS month_date, 
        COUNT("deviceId") AS assigned
    FROM "devicesAssignations"
    GROUP BY "clientId", date_trunc('month', "tsAssignation")
) stat ON cmp."clientId" = stat."clientId" AND cmp.month_date = stat.month_date
-- 可选：过滤特定客户，比如只看clientId=11的记录
WHERE cmp."clientId" = 11
ORDER BY cmp."clientId", cmp.month_date;

关键部分说明： #

• date_range：自动生成数据中最早到最晚分配月份的所有月度节点，也可以手动指定固定范围（比如generate_series('2024-02-01'::date, '2024-06-01'::date, INTERVAL '1 month')）。
• unique_clients：确保覆盖所有有过设备分配的客户；如果系统有独立的clients表，直接用该表能包含从未分配过设备的客户。
• client_month_pairs：通过交叉连接（CROSS JOIN）生成每个客户与每个月份的组合，保证每个客户每个月份都有一条基础记录。
• 左连接+COALESCE：将全量组合与原统计结果关联，无匹配的月份（无设备分配）会返回NULL，用COALESCE将其替换为0。

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