PostgreSQL按日期分组统计总订单及指定操作员订单量的实现问题

你可以通过条件聚合实现需求，修改后的查询语句如下：

SELECT 
    TO_DATE(o.order_end_date, 'DD/MM/YYYY') as orderDate,
    COUNT(o.order_index) AS totalOrders,
    COUNT(CASE WHEN o.op_id = 6 THEN 1 END) AS operator6Orders
FROM "Order" o
WHERE TO_DATE(o.order_end_date, 'DD/MM/YYYY') BETWEEN '2021-10-01' AND '2021-10-22'
GROUP BY orderDate
ORDER BY orderDate ASC

逻辑说明 #

• 新增的第三列使用条件统计逻辑：仅当订单关联的操作员ID为6时返回有效值，其余情况返回NULL，COUNT函数会自动忽略NULL值，最终得到指定操作员当日的订单数，无对应订单时返回0，完全匹配你期望的输出格式。
• 原查询中的LEFT JOIN Operator属于多余关联，订单表已经存储了op_id字段，无需关联操作员表即可完成统计，去掉后可以降低查询开销。
• 原查询中把日期过滤条件写在JOIN的ON子句中会导致日期范围外的订单也被统计，这里移到WHERE子句中做过滤，同时BETWEEN后的日期统一使用YYYY-MM-DD的ISO标准格式，避免PostgreSQL日期解析异常。

如果你的业务逻辑需要校验op_id必须是合法存在的操作员ID，可以保留关联，修改为如下版本：

SELECT 
    TO_DATE(o.order_end_date, 'DD/MM/YYYY') as orderDate,
    COUNT(o.order_index) AS totalOrders,
    COUNT(CASE WHEN o.op_id = 6 THEN 1 END) AS operator6Orders
FROM "Order" o
INNER JOIN Operator op ON o.op_id = op.operator_id
WHERE TO_DATE(o.order_end_date, 'DD/MM/YYYY') BETWEEN '2021-10-01' AND '2021-10-22'
GROUP BY orderDate
ORDER BY orderDate ASC

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