跟在我以前的后面
question
.
我有一张叫的桌子
activity
有2列:
`when` as datetime // last time i saw a user
guid as varchar // a unique identifier for each user
我有一个查询,返回给定日期和时间跨度内我有多少新用户:
SET @till_date='2009-11-01'; #for today, give CURDATE()+1
SET @duration=1; # this can be changed to 7, 31, etc
SELECT COUNT(guid) AS total_new_users, DATE_SUB(@till_date, INTERVAL @duration DAY) AS since_date, @till_date AS till_date
FROM `activity` WHERE 1
AND `when` BETWEEN DATE_SUB(@till_date, INTERVAL @duration DAY) AND @till_date
AND guid NOT IN
(
SELECT guid
FROM `activity`
WHERE `when` < DATE_SUB(@till_date, INTERVAL @duration DAY)
)
我希望能够构建一个查询,在给定的时间跨度内,该查询将返回每个日期的新用户列表。它将用于构建一个月、一周等每天新用户的图表。
像这样:
total_new_users | since_date | till_date
----------------------------------------------
10 | 2009-11-20 | 2009-11-21
10 | 2009-11-21 | 2009-11-22
10 | 2009-11-22 | 2009-11-23
我想用
UNION
具有
LOOP
但是我的SQL知识非常基础,有帮助吗?
结果
(感谢@pilcrow):
SET @till_date='2009-11-15';
SET @since_date='2009-11-01';
SELECT first AS yyyymmdd,
COUNT('x') AS new_user_count
FROM (SELECT guid,
DATE(MIN(`when`)) AS first
FROM activity
GROUP BY 1) first_appearance
WHERE first BETWEEN @since_date AND @till_date
GROUP BY 1
