获取并非每天都发生的事件的每日计数 [英] Getting daily counts for events that don't happen every day

问题描述

我有一个 customer 表，其中在发生客户注册时插入一个新行.

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?

推荐答案

您正在寻找一种方法来列出所有日期，即使是那些没有出现在您的 customer 表中的日期.这是 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.

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

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.

有几种方法可以做到这一点.一种是为自己创建一个 calendar 表，其中包含当前十年或世纪或其他任何日期的每一天，然后加入它.(与现代数据库的功能相比，该表不会很大.

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.

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

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屋！