每天不发生事件的日常计数 [英] Getting daily counts for events that don't happen every day

问题描述

我有一个客户表，当客户注册发生时，插入一行。

I have a customer table in which a new row is inserted when a customer signup occurs.

问题

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

I want to know the total number of signup per day for a given date range.

例如，从2015-07-01到2015-07-10发现每天的注册总数。

For example, find the total number of signup each day from 2015-07-01 to 2015-07-10

客户表
示例数据[显示相关列]

customer table sample data [relevant columns shown]

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

在查询中应该进行什么修改以获取所需的输出？

What modification should I make in the query to get the required output?

推荐答案

您正在寻找一种方法来列出所有的日子，即使那些在客户表。这是SQL中的一个臭名昭着的痛苦。这是因为在其纯粹的形式中，SQL缺少连续序列的任何东西的基本数字，天数，无论如何。

You're looking for a way to get all the days listed, even those days that aren't represented in your customer table. This is a notorious pain in the neck in SQL. That's because in its pure form SQL lacks the concept of a contiguous sequence of anything ... cardinal numbers, days, whatever.

所以，您需要引入一个包含连续基数，或日期或某事的来源的表，然后LEFT将您现有的数据加入该表。

So, you need to introduce a table containing a source of contiguous cardinal numbers, or dates, or something, and then LEFT JOIN your existing data to that table.

有几种方法可以做到这一点。一个是在现在的十年或者一个世纪中为每一天创建一个日历表，然后加入它。 （与现代数据库的能力相比，该表格不会很大。

There are a few ways of doing that. One is to create yourself a calendar table with a row for every day in the present decade or century or whatever, then join to it. (That table won't be very big compared to the capability of a modern database.

假设你有这个表，它有一个名为 date 然后你会这样做。

Let's say you have that table, and it has a column named date. Then you'd do this.

 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 ，数据集中丢失的数据将会压制日历中的行。

Notice a couple of things. First, the LEFT JOIN from the calendar table to your data set. If you use an ordinary JOIN the missing data in your data set will suppress the rows from the calendar.

其次，在 SEL中的 ISNULL ECT 将您的数据集中的缺失，空值转换为零值。

Second, the ISNULL in the toplevel SELECT to turn the missing, null, values from your dataset into zero values.

现在，您问我在哪里可以获得该日历表？我恭敬地建议你看看，并提出另一个问题，如果你不能弄清楚。

Now, you ask, where can I get that calendar table? I respectfully suggest you look that up, and ask another question if you can't figure it out.

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

I wrote a little essay on this, which you can find here.http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/

这篇关于每天不发生事件的日常计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！