如何编写一个按日期接收数据组的查询? [英] How write a query which receives data group by date?
问题描述
我写了一个简单的查询
SELECT date, count(user) as count FROM sessions GROUP BY date;
这样的回答
但是,我想查看每个日期,如果日期不存在,则行必须是这样的
But, I want to see every date, if date is not exist rows must be like this
2018-02-01 | 0
2018-02-02 | 0
2018-02-03 | 0
2018-02-04 | 0
2018-02-05 | 1503582
2018-02-06 | 0
2018-02-06 | 0
2018-02-08 | 5612270
...
...
...
2018-02-31 | 0
我应该如何重写查询以获取飞蛾每天的数据?
How can I should rewrite my query for getting data for every day in the moths ?
推荐答案
您需要先生成一个日历,然后将其与表session
的行连接到date
列.下面的子查询将根据@START_DATE
变量中设置的值生成全部1个月的数据.
You need to generate a calendar first and join it's rows with table session
through date
column. The subquery below will generate all 1 month based from the value set in the @START_DATE
variable.
SET @START_DATE = '2018-01-01'; -- set your starting date here
SELECT a.`DATE`,
COUNT(b.user) AS `COUNT`
FROM
(
SELECT DATE(cal.date) `DATE`
FROM (
SELECT @START_DATE + INTERVAL xc DAY AS date
FROM (
SELECT @xi:=@xi+1 as xc from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
(SELECT @xi:=-1) xc0
) xxc1
) cal
WHERE cal.date <= DATE_ADD(DATE_ADD(@START_DATE, INTERVAL 1 MONTH), INTERVAL -1 DAY)
) a
LEFT JOIN sessions b
ON a.`DATE` = b.`DATE`
GROUP BY a.`DATE`
ORDER BY a.`DATE`
这是一个演示.
从该文章:在MySQL中生成一系列日期,并进行了一些修改以允许用户输入开始日期.
The subquery that generates date was borrowed from this article: Generating a Series of Dates in MySQL and modified a little bit to allow user to input the starting date.
这篇关于如何编写一个按日期接收数据组的查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!