sql - 在 postgresql 中将相同日期的值相加

Question

下面的查询提供了图像中的结果，但是我想对相同日期的NewOrders和DoneOrders求和。我已经在获取过去 7 天的数据。

SELECT DATE(pickup_date),
COUNT(CASE WHEN order_status ='New' THEN 1 END) AS "NewOrders",
COUNT(CASE WHEN order_status ='Done' THEN 1 END) AS "DoneOrders" 
from requests where DATE(pickup_date) > current_date::date - INTERVAL '7 days' GROUP BY pickup_date 
ORDER BY pickup_date;

标签： sqlpostgresql

Answer 1

您需要修复GROUP BY. 您的聚合键是带有时间的日期。您想将该值转换为日期：

SELECT pickup_date::date, 
       COUNT(CASE WHEN order_status ='New' THEN 1 END) AS "NewOrders",
       COUNT(CASE WHEN order_status ='Done' THEN 1 END) AS "DoneOrders" 
FROM requests 
WHERE pickup_date::date > current_date- INTERVAL '7 days'
GROUP BY pickup_date::date
ORDER BY pickup_date::date;

Postgres 也支持FILTER，这是一种更简洁的逻辑编写方式。另外，current_date 是日期，所以不需要转换，也不需要双引号，所以不要用！

SELECT pickup_date::date, 
       COUNT(*) FILTER (WHERE order_status = 'New') AS NewOrders,
       COUNT(*) FILTER (WHERE order_status = 'Done') AS DoneOrders
FROM requests 
WHERE pickup_date::date > current_date - INTERVAL '7 days'
GROUP BY pickup_date::date
ORDER BY pickup_date::date;