从表格中获取所有月份日期以及数据 [英] Get all Date of month along with data from table
问题描述
我有两个表user_profile
和tracked_search
. user_profile
表具有用户详细信息,并且tracked_search
跟踪每个用户进行的搜索.
I have two tables user_profile
and tracked_search
. The user_profile
table has user details and tracked_search
tracks searches made by each user.
每当用户进行搜索时,该搜索条目就会出现在tracked_search
表中.如果没有搜索特定日期,则不会在tracked_search
中添加任何内容.
Whenever a user makes a search this search entry goes in the tracked_search
table. If nothing is searched for a particular date nothing is added in tracked_search
.
我需要开发一个报告,在其中我需要显示一个月的所有天中有多少用户进行了搜索.
I need to develop a report where in I need to show on all days of month how many users made searches.
例如:
CREATE TABLE tracked_search (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATE,
user_id int NOT NULL
);
INSERT INTO tracked_search(created, user_id) VALUES
('2017-10-01', 1000),
('2017-10-01', 1000),
('2017-10-01', 2000),
('2017-10-01', 3000),
('2017-10-01', 4000),
('2017-10-04', 1000),
('2017-10-04', 2000),
('2017-10-04', 2000),
('2017-10-04', 2000),
('2017-10-04', 2000),
('2017-10-04', 3000),
('2017-10-31', 1000),
('2017-10-31', 2000),
('2017-10-31', 3000),
('2017-10-31', 4000),
('2017-10-31', 5000);
所需的输出:
Date user_count
2017-10-01 4
2017-10-02 0
2017-10-03 0
2017-10-04 3
2017-10-05 0
...
2017-10-30 0
2017-10-31 5
我写了以下查询
SELECT ts.created , count( distinct ts.user_id) FROM tracked_search ts, user_profile u
WHERE ts.created>=(CURDATE()-INTERVAL 1 MONTH) AND u.id = ts.user_id
group by ts.created;
但我明白了
Date user_count
2017-10-01 4
2017-10-04 3
2017-10-31 5
如果在特定日期没有条目应打印为零,我需要打印所有天数.
I need to print all days values if no entry is there for a particular date it should be zero.
我正在使用MySQL.
I am using MySQL.
推荐答案
顺便说一句,您不需要user_profile上的联接.
By the way, you don't need the join on user_profile.
如果您有一个带有相关日期的dates
表,这很简单:
If you have a dates
table with the relevant dates, this is pretty easy:
SELECT dates.day AS `Date`, COUNT(DISTINCT ts.user_id) AS user_count
FROM dates
LEFT OUTER JOIN tracked_search AS ts
ON ts.created = dates.day
GROUP BY dates.day;
由于您可能没有dates
表,并且可能不想创建和维护一个表,因此可以使用其中一种解决方案动态生成日期列表.例如获取两个日期之间的日期列表或如何在mysql选择查询中获取两个日期之间的日期列表
Since you probably don't have a dates
table and might not want to create and maintain one, you could use one of the solutions for generating the list of dates on the fly. e.g. Get a list of dates between two dates or How to get list of dates between two dates in mysql select query
SELECT dates.day AS `Date`, COUNT(DISTINCT ts.user_id) AS user_count
FROM (
SELECT ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) AS day
FROM (SELECT 0 AS i 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 t0,
(SELECT 0 AS i 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 t1,
(SELECT 0 AS i 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 t2,
(SELECT 0 AS i 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 t3,
(SELECT 0 AS i 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 t4
) AS dates
LEFT OUTER JOIN tracked_search AS ts
ON ts.created = dates.day
WHERE dates.day >= '2017-10-01'
AND dates.day < '2017-11-01'
GROUP BY dates.day;
这篇关于从表格中获取所有月份日期以及数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!