在GROUP BY子句中返回空行 [英] Returning empty rows in GROUP BY clause

问题描述

我有一个查询来检索两个日期之间每天的事件总数.

I have a query to retrieve a total number of events per day between two dates.

SELECT
  DATE_FORMAT(startTime, '%Y%m%d') as day,
  COUNT(0) as numEvents
FROM events
WHERE
  (startTime BETWEEN 20140105000000 AND 20140112235900)
GROUP BY
  day;

这将返回类似列表

day      | numEvents
---------+----------
20140105 | 45
20140107 | 79
20140108 | 12
20140109 | 56

请注意，结果中缺少日期，因为事件表中没有针对这些日期的事件.有什么办法可以返回那几天的0值，导致:

Notice that there are missing days in the result since there are no events in the events table for those days. Is there any way to return 0 values for those days, resulting in:

day      | numEvents
---------+----------
20140105 | 45
20140106 | 0
20140107 | 79
20140108 | 12
20140109 | 56
20140110 | 0
20140111 | 0
20140112 | 0

推荐答案

如果startTime是另一个具有每个日期值的表的外键，则可以执行此操作.这在数据仓库的多维模型上非常常见，在该模型中，您具有日期/时间维度，并且其中包括所有日期.

You will able to do it if startTime is a foreign_key to another table which have values for every date. This is tipical on multidimensional models of data-warehouses, where you have a date/time dimension and them include all days.

在那种情况下，我认为 Vigens Kumar 提供的解决方案会起作用:

In that case, I think the solution provided by Vigens Kumar would work:

SELECT  DATE_FORMAT(startTime, '%Y%m%d') as day, COUNT(DATE_FORMAT(startTime, '%Y%m%d'))
FROM  events 
WHERE
(startTime BETWEEN 20140105000000 AND 20140112235900)
GROUP BY day;

这篇关于在GROUP BY子句中返回空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！