如何让PostgreSQL在无订单日期时返回0值？

问题核心在于没有订单的日期不会出现在GROUP BY的结果集中，COALESCE只能处理已有行内的NULL值，无法凭空生成缺失的日期行。要实现需求，需要先生成目标区间内的完整日期序列，再通过左连接关联订单统计数据，最后将统计字段的NULL值转为0。

1. 生成目标日期区间的完整日期序列 #

不同数据库生成日期序列的方式不同，以下是几种常见数据库的实现方式：

PostgreSQL #

利用generate_series直接生成日期范围：

SELECT generate_series(
    '2022-07-20'::DATE,
    '2022-07-26'::DATE,
    '1 day'::INTERVAL
)::DATE AS order_date;

MySQL #

通过递归CTE生成日期序列：

WITH RECURSIVE date_range AS (
    SELECT '2022-07-20' AS order_date
    UNION ALL
    SELECT DATE_ADD(order_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE order_date < '2022-07-26'
)
SELECT order_date FROM date_range;

SQL Server #

使用递归CTE生成日期序列：

WITH date_range AS (
    SELECT CAST('2022-07-20' AS DATE) AS order_date
    UNION ALL
    SELECT DATEADD(DAY, 1, order_date)
    FROM date_range
    WHERE order_date < CAST('2022-07-26' AS DATE)
)
SELECT order_date FROM date_range OPTION (MAXRECURSION 0);

2. 左连接订单统计数据 #

将生成的日期序列作为主表，左连接你的订单统计查询结果，再用COALESCE将统计字段的NULL值转为0。以PostgreSQL为例，完整SQL如下：

WITH date_range AS (
    SELECT generate_series(
        '2022-07-20'::DATE,
        '2022-07-26'::DATE,
        '1 day'::INTERVAL
    )::DATE AS order_date
),
order_stats AS (
    SELECT 
        date(created_at) AS order_date,
        COUNT(id) AS total_orders,
        SUM(COALESCE(total_price, 0)) AS total_price,
        SUM(COALESCE(taxes, 0)) AS taxes,
        SUM(COALESCE(shipping, 0)) AS shipping,
        AVG(COALESCE(total_price, 0)) AS average_order_value,
        SUM(COALESCE(total_discount, 0)) AS total_discount,
        SUM(total_price - COALESCE(taxes, 0) - COALESCE(shipping, 0) - COALESCE(total_discount, 0)) AS net_sales
    FROM orders
    WHERE shop_id = 43
      AND orders.active = true
      AND orders.created_at >= '2022-07-20'
      AND orders.created_at <= '2022-07-26'
    GROUP BY date(created_at)
)
SELECT 
    dr.order_date,
    COALESCE(os.total_orders, 0) AS total_orders,
    COALESCE(os.total_price, 0) AS total_price,
    COALESCE(os.taxes, 0) AS taxes,
    COALESCE(os.shipping, 0) AS shipping,
    COALESCE(os.average_order_value, 0) AS average_order_value,
    COALESCE(os.total_discount, 0) AS total_discount,
    COALESCE(os.net_sales, 0) AS net_sales
FROM date_range dr
LEFT JOIN order_stats os ON dr.order_date = os.order_date
ORDER BY dr.order_date DESC;

注意事项 #

• 原查询中的COUNT(COALESCE(id, 0))可简化为COUNT(id)，因为主键id不会为NULL，COUNT仅统计非NULL值，缺失数据的日期通过左连接后用COALESCE转0即可。
• 对于average_order_value，无订单日期的AVG结果为NULL，转0是常规处理，你也可根据需求保留NULL。
• 若需频繁查询日期区间数据，可提前创建包含所有日期的日历表，替代临时生成的日期序列，提升查询效率。

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