力扣1107 每日新用户统计

力扣SQL查询案例——在过去90天内，每个日期首次登录的用户数

目录

题目描述

解题思路

完整代码

---

题目描述

Traffic 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
该表可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Traffic 表：
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1       | login    | 2019-05-01    |
| 1       | homepage | 2019-05-01    |
| 1       | logout   | 2019-05-01    |
| 2       | login    | 2019-06-21    |
| 2       | logout   | 2019-06-21    |
| 3       | login    | 2019-01-01    |
| 3       | jobs     | 2019-01-01    |
| 3       | logout   | 2019-01-01    |
| 4       | login    | 2019-06-21    |
| 4       | groups   | 2019-06-21    |
| 4       | logout   | 2019-06-21    |
| 5       | login    | 2019-03-01    |
| 5       | logout   | 2019-03-01    |
| 5       | login    | 2019-06-21    |
| 5       | logout   | 2019-06-21    |
+---------+----------+---------------+
输出：
+------------+-------------+
| login_date | user_count  |
+------------+-------------+
| 2019-05-01 | 1           |
| 2019-06-21 | 2           |
+------------+-------------+
解释：
请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。

解题思路

要解决这个问题，我们需要找出在过去90天内，每个日期首次登录的用户数。解题思路如下：

1. 筛选登录活动：首先，我们需要从Traffic表中筛选出所有login活动的记录。

2. 确定每个用户的首次登录日期：接下来，对于每个用户，我们需要找到他们的首次登录日期。这可以通过对每个用户的登录记录按日期排序，然后选择最早的一条记录来实现。

3. 限定时间范围：由于我们只关心过去90天内的数据，因此需要从今天（2019-06-30）往回数90天，即2019-04-01起的数据。

4. 计算每个日期的首次登录用户数：最后，我们需要按照首次登录日期对用户进行分组，并计算每个组的用户数。

完整代码

WITH FirstLogin AS (
    SELECT user_id, MIN(activity_date) AS first_login_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
    HAVING first_login_date BETWEEN DATE_SUB('2019-06-30', INTERVAL 90 DAY) AND '2019-06-30'
)

SELECT first_login_date AS login_date, COUNT(user_id) AS user_count
FROM FirstLogin
GROUP BY first_login_date
ORDER BY first_login_date;

• FirstLogin子查询：这个CTE（公用表表达式）首先筛选出所有login活动的记录，然后对每个user_id按activity_date进行分组并使用MIN(activity_date)来找到每个用户的首次登录日期。HAVING子句确保我们只考虑过去90天内的首次登录。

• 最终的SELECT语句：从FirstLogin中选择first_login_date和对应的user_id数量（作为user_count），并按first_login_date进行分组，以计算每个日期的首次登录用户数。结果按first_login_date排序，以符合题目要求的任意顺序。

通过

代码优化

贴近业务，刷题考虑这些比较少了，先掌握再提升

1. 索引使用：确保Traffic表中的user_id、activity和activity_date列上有适当的索引。特别是，对于此查询，一个覆盖索引(activity, activity_date, user_id)可能会非常有用，因为它可以加速login活动的筛选和按日期的最小值查找。

2. 减少数据扫描量：在WHERE子句中通过指定activity = 'login'来缩小查询范围，这样可以减少需要处理的数据量。此外，通过在HAVING子句中进一步限制日期范围，只考虑过去90天内的数据，也有助于减少计算量。

3. 使用更有效的日期计算：如果数据库支持，使用数据库特定的日期函数来计算90天前的日期，可能比硬编码更有效，也更易于维护。例如，MySQL中可以使用DATE_SUB(NOW(), INTERVAL 90 DAY)来动态计算过去90天的日期，这样查询就不会因为固定日期而过时。

4. 分析执行计划：运行查询的执行计划，查看是否有可能的性能瓶颈。数据库的执行计划可以帮助你了解查询是如何执行的，包括使用的索引、数据扫描量和各个操作的成本。