力扣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天内,每个日期首次登录的用户数。解题思路如下:
-
筛选登录活动:首先,我们需要从
Traffic
表中筛选出所有login
活动的记录。 -
确定每个用户的首次登录日期:接下来,对于每个用户,我们需要找到他们的首次登录日期。这可以通过对每个用户的登录记录按日期排序,然后选择最早的一条记录来实现。
-
限定时间范围:由于我们只关心过去90天内的数据,因此需要从今天(2019-06-30)往回数90天,即2019-04-01起的数据。
-
计算每个日期的首次登录用户数:最后,我们需要按照首次登录日期对用户进行分组,并计算每个组的用户数。
完整代码
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
排序,以符合题目要求的任意顺序。
通过
代码优化
贴近业务,刷题考虑这些比较少了,先掌握再提升
-
索引使用:确保
Traffic
表中的user_id
、activity
和activity_date
列上有适当的索引。特别是,对于此查询,一个覆盖索引(activity, activity_date, user_id)
可能会非常有用,因为它可以加速login
活动的筛选和按日期的最小值查找。 -
减少数据扫描量:在
WHERE
子句中通过指定activity = 'login'
来缩小查询范围,这样可以减少需要处理的数据量。此外,通过在HAVING
子句中进一步限制日期范围,只考虑过去90天内的数据,也有助于减少计算量。 -
使用更有效的日期计算:如果数据库支持,使用数据库特定的日期函数来计算90天前的日期,可能比硬编码更有效,也更易于维护。例如,MySQL中可以使用
DATE_SUB(NOW(), INTERVAL 90 DAY)
来动态计算过去90天的日期,这样查询就不会因为固定日期而过时。 -
分析执行计划:运行查询的执行计划,查看是否有可能的性能瓶颈。数据库的执行计划可以帮助你了解查询是如何执行的,包括使用的索引、数据扫描量和各个操作的成本。