代码之家 › 专栏 › 技术社区 › swapab

MySQL按日期和计数分组,包括缺少的日期

mysql5 date mysql

5

swapab · 技术社区 · 12 年前

之前,我在做以下工作,以从报告表中获取每日计数。

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;

显然,这不会让我错过约会。

然后我终于找到了 optimum solution 生成给定日期范围之间的日期序列。但我面临的下一个挑战是将其与我的报告表合并,并按日期分组计数。

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 ;

我得到的结果是

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 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 ;

结果:

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

具有以上查询的最小数据: http://sqlfiddle.com/#!2/dee3e/6

2 回复 | 直到 9 年前

1

4

Adrian Maxwell 12 年前

你需要一个 OUTER JOIN 从开始到结束的每一天,因为如果你使用 INNER JOIN 它会将输出限制为仅连接的日期(即报告表中的那些日期)。

此外,当您使用 外部连接 你必须注意 where clause 不要引起 implicit inner join ; 例如 AND域_id=1 如果在where子句中使用将禁止任何不满足该条件的行,但当用作联接条件时,它仅限制报表表的行。

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;

我还通过使用 DATE_ADD() 将起点推向未来,我已经缩小了它的尺寸。这两个都是选项,可以根据需要进行调整。

Demo at SQLfiddle

要获得每一行的domain_id(如问题中所示),您需要使用以下内容:;注意,您可以使用 IFNULL() 这是MySQL特有的,但我使用过 COALESCE() 这是更通用的SQL。然而,这里显示的@参数的使用是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 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

2

1

D'Arcy Rittich 12 年前

这个 all_dates 子查询仅从当前日期向后查看( curdate() ). 如果要包含将来的日期,请将子查询的第一行更改为如下内容:

select '2015-01-01' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date