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

Question

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

问题

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

例如求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

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

Answer 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，数据集中缺失的数据将抑制日历中的行。

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

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

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