MySQL组按日期计数，包括缺少日期 [英] MySQL group by date and count including missing dates

查看：137
本文介绍了MySQL组按日期计数，包括缺少日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  SELECT COUNT（*）AS count_all，tracked_on 
 FROM`reports` 
 WHERE（domain_id = 939 AND tracked_on> ='2014-01-01'AND tracked_on< ='2014-12-31'）
 GROUP BY tracked_on 
 ORDER BY tracked_on ASC;

显然，这不会给我0个错误日期的计数。



然后，我终于找到了一个最佳解决方案，以在给定的日期范围之间生成日期序列。
但是面临的下一个挑战是加入我的报表，并按日期分组计数。

 选择count（*），all_dates.Date as the_date，domain_id 
 from（
 select curdate（） -  INTERVAL（aa +（10 * ba）+（100 * ca））DAY as Date 
从（选择0作为联合全部选择1联合所有选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为
交叉连接（选择0作为联合全部选择1联合所有选择2联合全部选择3联合全部选择4联合所有选择5联合全部选择6联合全部选择7联合全部选择8联合全部选择9）作为b 
交叉连接（选择0作为联合全部选择1联合全部选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合全部选择7联合全部选择8联合全部选择9）作为c 
 ）all_dates 
内部JOIN报告r 
在all_dates.Date> ='2014-0 1-01'
和all_dates.Date< ='2014-12-31'
其中all_dates.Date在'2014-01-01'和'2014-12-31'和domain_id = 939 GROUP BY by_date命令由the_date ASC;

得到的结果是

  count（*）the_date domain_id 
 46 2014-01-01 939 
 46 2014-01-02 939 
 46 2014-01-03 939 
 46 2014-01-04 939 
 46 2014-01-05 939 
 46 2014-01-06 939 
 46 2014-01-07 939 
 46 2014-01- 08 939 
 46 2014-01-09 939 
 46 2014-01-10 939 
 46 2014-01-11 939 
 46 2014-01-12 939 
 46 2014-01-13 939 
 46 2014-01-14 939 
 ...


而我正在寻找填写缺少的日期0



像 

  count（*）the_date domain_id 
 12 2014-01-01 939 
 23 2014-01-02 939 
 46 2014-01-03 939 
 0 2014-01-04 939 
 0 2014-01-05 939 
 99 2014-01-06 939 
 1 2014- 01-07 939 
 5 2014-01-08 939 
 ...


我给的另一个尝试是：

  select c ount（*），all_dates.Date as the_date，domain_id 
 from（
 select curdate（） -  INTERVAL（aa +（10 * ba）+（100 * ca））DAY as Date 
从（选择0作为联合全部选择1联合所有选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为
交叉加入（选择0作为联合全部选择1联合所有选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合全部选择7联合全部选择8联合全部选择9）作为b 
交叉加入（选择0作为联合全部选择1联合所有选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为c 
） all_dates 
内部JOIN报告r 
 on all_dates.Date = r.tracked_on 
其中all_dates.Date在'2014-01-01'和'2014-12-31'和domain_id = 939 GROUP BY_date命令由the_date ASC;

结果：

 code> count（*）the_date domain_id 
 38 2014-09-03 939 
 8 2014-09-04 939

具有以上查询的最小数据： http：// sqlfiddle .com /＃！2 / dee3e / 6

解决方案

您需要一个 OUTER JOIN 在开始和结束之间的每一天到达，因为如果您使用 INNER JOIN ，则会将输出限制为仅连接的日期（即报表中的那些日期）。



此外，当您使用 OUTER JOIN 时，您必须请注意 where子句中的条件不会导致隐含内部连接;例如 AND domain_id = 1 ，如果在where子句中使用会抑制任何没有满足条件的行，但是当用作连接条件时，它只会限制报表的行。 p> 

  SELECT 
 COUNT（r.domain_id）
，all_dates.Date AS the_date 
，domain_id 
 FROM（
 SELECT DATE_ADD（curdate（），INTERVAL 2 MONTH） -  INTERVAL（aa +（10 * ba））DAY as Date 
 FROM（select 0 as a union all select 1 union all选择2联合所有选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为
 CROSS JOIN（选择0作为联合全部选择1联合全部选择2联合所有选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为b 
）all_dates 
 LEFT OUTER JOIN报告r 
 ON all_dates.Date = r.tracked_on 
 AND domain_id = 1 
 WHERE all_dates.Date BETWEEN'2014-09-01'AND'2014-09-30'
 GROUP BY 
 the_date 
 ORDER BY 
 the_date ASC;

我还更改了all_dates派生表，使用 DATE_ADD（）将起点推到未来，我已经减少了它的大小。这两个都是选项，可以根据您的需要进行调整。



在SQLfiddle演示





到达每个行的domain_id（如您的问题所示），您需要使用以下内容：请注意，您可以使用特定于MySQL的 IFNULL（），但我使用了更为通用的SQL的 COALESCE（）。但是使用@parameter这里显示的是MySQL具体的。

  SET @domain：= 1; 
 
 SELECT 
 COUNT（r.domain_id）
，all_dates.Date AS the_date 
，coalesce（domain_id，@ domain）AS domain_id 
 FROM（
 SELECT DATE_ADD（curdate（），INTERVAL 2个月） -  INTERVAL（aa +（10 * ba））DAY作为日期
 FROM（选择0作为联合全部选择1联合全部选择2联合全部选择3联合所有选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）作为
 CROSS JOIN（选择0作为联合全部选择1联合全部选择2联合全部选择3联合所有选择4联合所有选择5联合所有选择6联合所有选择7联合全部选择8联合全部选择9）作为b 
）all_dates 
 LEFT JOIN报告r 
 ON all_dates.Date = r.tracked_on 
 AND domain_id = @domain 
 WHERE all_dates.Date BETWEEN'2014-09-01'和'2014-09-30'
 GROUP BY 
 the_date 
 ORDER BY 
 the_date ASC;

请参阅SQLfiddle


Previously I was doing following to get per day count from reports table.

SELECT COUNT(*) AS count_all, tracked_on
 FROM `reports`
 WHERE (domain_id = 939 AND tracked_on >= '2014-01-01' AND tracked_on <= '2014-12-31')
 GROUP BY tracked_on
 ORDER BY tracked_on ASC;

Obviously this wont give me 0 count for missing dates.

Then I finally found a optimum solution to generate date-series between given date range. But the next challenge am facing is to join it with my reports table and get the count grouped by date.

select count(*), all_dates.Date as the_date, domain_id
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
    on all_dates.Date >= '2014-01-01'
  and all_dates.Date <= '2014-12-31'
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;

The result am getting is

count(*)    the_date    domain_id
46  2014-01-01  939
46  2014-01-02  939
46  2014-01-03  939
46  2014-01-04  939
46  2014-01-05  939
46  2014-01-06  939
46  2014-01-07  939
46  2014-01-08  939
46  2014-01-09  939
46  2014-01-10  939
46  2014-01-11  939
46  2014-01-12  939
46  2014-01-13  939
46  2014-01-14  939
...

Whereas am looking to fill in the missing dates with 0

something like

count(*)    the_date    domain_id
12  2014-01-01  939
23  2014-01-02  939
46  2014-01-03  939
0   2014-01-04  939
0   2014-01-05  939
99  2014-01-06  939
1   2014-01-07  939
5   2014-01-08  939
...

Another try that I gave was:

select count(*), all_dates.Date as the_date, domain_id
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
    on all_dates.Date = r.tracked_on
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;

Results:

count(*)    the_date    domain_id
38        2014-09-03     939
8         2014-09-04     939

Minimal data with above queries: http://sqlfiddle.com/#!2/dee3e/6

解决方案

You need an OUTER JOIN to arrive at every day between a start and an end because if you use an INNER JOIN it will restrict the output to just the dates that are joined (i.e. just those dates in the report table).

In addition, when you use an OUTER JOIN you must take care that conditions in the where clause don't cause an implicit inner join; for example AND domain_id = 1 if use in the where clause would suppress any row that did not have that condition met, but when used as a join condition it only restricts the rows of the report table.

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT OUTER JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = 1
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

I have also changed the all_dates derived table, by using DATE_ADD() to push the starting point into the future, and I have reduced the it's size. Both of these are options and can be tweaked as you see fit.

Demo at SQLfiddle

to arrive at a domain_id for every row (as shown in your question) you would need to use someting like the following; Note you could use IFNULL() which is MySQL specific but I have used COALESCE() which is more generic SQL. However use of an @parameter as shown here is MySQL specific anyway.

SET @domain := 1;

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , coalesce(domain_id,@domain) AS domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 month) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = @domain
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

See this at SQLfiddle

这篇关于MySQL组按日期计数，包括缺少日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆