mysql - 每日、每月或每年报告列出新老用户的 Access 情况

Question

我有 2 个表，表结构如下 -

用户(UserID int , Name varchar(50), DateCreated datetime)和

UserLogin(Id 整数，UserId 整数，DateLoggedIn 日期时间)

我需要按以下格式生成每天/每月/每年登录应用程序的用户的报告 -

日期、NewUsers、OldUsers、TotalUsers

日期 - 为报告选择的日期

NewUser - 如果用户是第一次在应用程序中登录，他将被视为新用户，否则将被视为老用户。

OldUser - 首次 Access 后的后续用户登录。

比如用户A在15日登录一个月，15日会被视为新用户，但以后 Access 该网站将被视为老用户，需要在报表中报告为老用户。同样，如果用户 B 在 2012 年 4 月 30 日登录过一次，那么他将被视为旧用户。

你们可以给我一个输出报告的查询吗？

我的数据如下——

用户表

| UserID |         Name | DateCreated ||--------|--------------|-------------||      1 |      Rajveer |  2012-01-01 ||      2 |  Manoj Tyagi |  2011-08-10 ||      3 | Raghu Pandey |  2009-05-13 ||      4 |   Bhajan Lal |  2012-05-01 ||      5 | Satbir Singh |  2011-08-03 |

用户登录表

| ID | UserID | DateLoggedIn ||----|--------|--------------||  1 |      1 |   2012-04-27 ||  2 |      1 |   2012-05-25 ||  3 |      1 |   2012-05-28 ||  4 |      2 |   2012-03-21 ||  5 |      2 |   2012-05-30 ||  6 |      3 |   2012-05-20 ||  7 |      4 |   2012-05-29 ||  8 |      5 |   2012-04-22 ||  9 |      3 |   2012-05-15 |

我确实需要一个月的全部数据(所以不需要 distinct)....理想情况下，根据我提供的数据，我应该得到以下结果

| Month | NewUsers | OldUsers | TotalUsers ||-------|----------|----------|------------||     3 |        1 |        0 |          1 ||     4 |        2 |        0 |          2 ||     5 |        2 |        4 |          6 |

由于用户 id 3 在 5 月登录了两次，他的第一次 Access 应被视为新 Access ，第二次 Access 应被视为旧 Access 。

TIA，

帕里托什艾莉亚。

Answer 1

您可以在连接日期在登录表和用户表之间执行外部连接，然后计算每个表中不同的用户 ID:

SELECT   DATE(l.DateLoggedIn)                                AS Date,
         COUNT(DISTINCT u.UserID)                            AS NewUsers,
         COUNT(DISTINCT l.UserId) - COUNT(DISTINCT u.UserID) AS OldUsers,
         COUNT(DISTINCT l.UserId)                            AS TotalUsers
FROM     UserLogin                                           AS l
  LEFT JOIN Users                                            AS u
         ON l.UserId = u.UserID
        AND DATE(l.DateLoggedIn) = DATE(u.DateCreated)
GROUP BY Date