Postgres查询如何为无记录的日期和用户ID填充0购买量

可以实现，核心逻辑是先构造全量的「目标用户+目标日期」笛卡尔积组合，再左关联购买统计结果，将空值填充为0即可。

实现前提 #

你需要先明确两个查询范围：

• 需要覆盖的查询日期区间
• 需要统计的目标用户列表（如果是统计全量已有用户，可直接从原表去重取user_id）

示例代码（以MySQL 8.0+为例） #

WITH RECURSIVE date_range AS (
    -- 定义查询起始日期，按需要修改
    SELECT '2024-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) 
    FROM date_range 
    -- 定义查询结束日期，按需要修改
    WHERE dt < '2024-01-31'
),
user_list AS (
    -- 如果是指定用户，直接写 SELECT 1001 AS user_id UNION ALL SELECT 1002 AS user_id 即可
    SELECT DISTINCT user_id FROM user_purchase_metrics
),
purchase_stats AS (
    -- 原有统计逻辑
    SELECT 
        user_id, 
        DATE(purchase_time) AS purchase_dt, 
        SUM(items_purchased) AS total_items
    FROM user_purchase_metrics
    GROUP BY DATE(purchase_time), user_id
)
-- 全量组合左联统计结果，空值补0
SELECT 
    ul.user_id,
    dr.dt AS record_date,
    COALESCE(ps.total_items, 0) AS total_purchased_items
FROM user_list ul
CROSS JOIN date_range dr
LEFT JOIN purchase_stats ps 
    ON ul.user_id = ps.user_id 
    AND dr.dt = ps.purchase_dt
ORDER BY ul.user_id, dr.dt;

其他方言适配说明 #

• 如果你使用PostgreSQL，日期序列可直接用generate_series生成，无需递归CTE：

  SELECT dt::DATE FROM generate_series('2024-01-01'::DATE, '2024-01-31'::DATE, '1 day'::INTERVAL) dt
  

• 如果你使用低版本不支持CTE的MySQL，可以用临时表或者子查询替代CTE部分实现相同逻辑。

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