我正试图返回每小时一次的搜索次数报告。我的结果不包括搜索次数为零的时间，我认为我使用COALESCE的语法是正确的。有人知道我做错了什么吗？谢谢
SELECT CAST(startdatetime as Date),extract(hour from startdatetime) as hr, COALESCE(count(distinct id),0) as average_per_hour
FROM search WHERE CAST(startdatetime As Date) = '2014/07/05'
GROUP BY CAST(startdatetime as Date),extract(hour from startdatetime)
ORDER BY CAST(startdatetime as Date),extract(hour from startdatetime)
最佳答案
有些精致，但基本上与“没有名字的马”一样：
SELECT DATE '2014-07-05', hr, COUNT(DISTINCT id) AS average_per_hour
FROM generate_series(0, 23) hr
LEFT JOIN search ON EXTRACT(HOUR FROM startdatetime) = hr AND CAST(startdatetime AS DATE) = '2014-07-05'
GROUP BY hr
ORDER BY hr
在
CAST(startdatetime AS DATE)和ORDER BY中使用GROUP BY是不相关的，因为您只搜索一天。如果不是一般情况，您也需要调整generate_series()。编辑：
这可以跨多天工作：
SELECT CAST(hr AS DATE), EXTRACT(HOUR FROM hr), COUNT(DISTINCT id) AS average_per_hour
FROM generate_series('2014-07-05 00:00:00', '2014-07-06 23:00:00', INTERVAL '1' HOUR) hr
LEFT JOIN search ON date_trunc('hour', startdatetime) = hr
GROUP BY hr
ORDER BY hr
关于postgresql - 在postgres中正确使用COALESCE/COALESCE无法正常工作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24651109/