获取并非每天发生的事件的每日计数

我有一个customer当客户注册时会在其中插入新行的表。

Problem

我想知道给定日期范围内每天的注册总数。

例如，查找2015-07-01到2015-07-10每天的注册总数

顾客表样本数据[显示相关列]

customerid    username    created
1             mrbean      2015-06-01
2             tom         2015-07-01
3             jerry       2015-07-01
4             bond        2015-07-02
5             superman    2015-07-10
6             tintin      2015-08-01
7             batman      2015-08-01
8             joker       2015-08-01

所需输出

created      signup
2015-07-01   2
2015-07-02   1
2015-07-03   0
2015-07-04   0
2015-07-05   0
2015-07-06   0
2015-07-07   0
2015-07-08   0
2015-07-09   0
2015-07-10   1

使用的查询

SELECT 
    DATE(created) AS created, COUNT(1) AS signup
FROM
    customer
WHERE
    DATE(created) BETWEEN '2015-07-01' AND '2015-07-10'
GROUP BY DATE(created)
ORDER BY DATE(created)

我得到以下输出：

created      signup
2015-07-01   2
2015-07-02   1
2015-07-10   1

我应该在查询中进行哪些修改才能获得所需的输出？

您正在寻找一种方法来获取列出的所有日期，甚至是那些未在您的列表中表示的日期customer桌子。这是 SQL 中臭名昭著的难题。这是因为 SQL 的纯粹形式缺乏任何连续序列的概念……基数、天数等​​等。

因此，您需要引入一个包含连续基数、日期或其他内容的源的表，然后将现有数据左连接到该表。

有几种方法可以做到这一点。一是为自己创造一个calendar表中的每一行代表当前十年或本世纪或其他时间的每一天，然后加入其中。 （与现代数据库的功能相比，该表不会很大。

假设您有该表，并且它有一个名为date。那么你就会这样做。

 SELECT calendar.date AS created,
        ISNULL(a.customer_count, 0) AS customer_count
   FROM calendar
   LEFT JOIN ( 
            SELECT COUNT(*) AS customer_count,
                   DATE(created) AS created
              FROM customer
             GROUP BY DATE(created)
        ) a ON calendar.date = a.created
   WHERE calendar.date BETWEEN start AND finish 
   ORDER BY calendar.date

请注意几件事。首先，LEFT JOIN从日历表到您的数据集。如果你用的是普通的JOIN数据集中缺失的数据将抑制日历中的行。

其次，ISNULL在顶层SELECT将数据集中缺失的 null 值转换为零值。

现在，您会问，我在哪里可以获得该日历表？我恭敬地建议您查一下，如果您无法弄清楚，请再问一个问题。

我就此写了一篇小文章，您可以在这里找到。http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/