sql - 有关(另一个)SQL 查询的帮助

Question

继我之前的 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

Answer 1

假设SET SESSION sql_mode = 'ANSI_QUOTES':

SELECT first AS yyyymmdd, 
       COUNT('x') AS new_user_count
FROM (SELECT guid,
             MIN("when") AS first
      FROM activity
      GROUP BY 1) first_appearance
WHERE first BETWEEN @since_date AND @till_date
GROUP BY 1

如果您愿意，当然可以用 DATE_SUB() 替换 @since_date。