如何让SQL统计无订单日期时返回0而非NULL？

需求：统计指定日期范围内的订单数量，要求所有生成的日期都显示在结果中，无订单的日期对应统计数为0。当前SQL存在两个问题：返回了NULL日期的行，且缺失无订单的日期。

当前SQL语句 #

SELECT           days.date,
                 COALESCE(Count(service_orders.id)::int) AS count
FROM             (
                        SELECT To_char( Date_trunc('day', (CURRENT_DATE + offs)), 'YYYY-MM-DD' )            AS date,
                               trim(both ' ' FROM to_char(date_trunc('day', (CURRENT_DATE + offs)), 'day')) AS day
                        FROM   generate_series(0, 60, 1)                                                    AS offs ) days
RIGHT OUTER JOIN
                 (
                           SELECT    service_order.id,
                                     to_char( date_trunc('day', service_order.date), 'YYYY-MM-DD' ) service_order_day
                           FROM      service_order
                           LEFT JOIN order_
                           ON        service_order.order_id = order_.id) service_orders
ON               days.date = service_orders.service_order_day
GROUP BY         days.date
ORDER BY         days.date ASC;

当前查询结果 #

2023-06-22  6
2023-06-23  5
NULL        15312

修正后的SQL语句 #

SELECT           days.date,
                 COALESCE(Count(service_orders.id)::int, 0) AS count
FROM             (
                        SELECT To_char( Date_trunc('day', (CURRENT_DATE + offs)), 'YYYY-MM-DD' )            AS date,
                               trim(both ' ' FROM to_char(date_trunc('day', (CURRENT_DATE + offs)), 'day')) AS day
                        FROM   generate_series(0, 60, 1)                                                    AS offs ) days
LEFT OUTER JOIN
                 (
                           SELECT    service_order.id,
                                     to_char( date_trunc('day', service_order.date), 'YYYY-MM-DD' ) service_order_day
                           FROM      service_order
                           LEFT JOIN order_
                           ON        service_order.order_id = order_.id) service_orders
ON               days.date = service_orders.service_order_day
GROUP BY         days.date
ORDER BY         days.date ASC;

关键修改说明 #

1. 替换连接类型：将RIGHT OUTER JOIN改为LEFT OUTER JOIN，确保days表生成的所有日期都被保留，只关联匹配的订单记录，避免保留订单表中超出日期范围的记录（这是原SQL出现NULL日期的核心原因）。
2. 明确COALESCE默认值：在COALESCE中添加默认值0，即使出现COUNT返回NULL的极端情况，也能确保显示为0（实际COUNT(service_orders.id)在无匹配时会返回0，此操作是双重保障）。

期望输出 #

2023-06-22  6
2023-06-23  5
2023-06-24  0
2023-06-25  0
...

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