SQL练习:SQL69_较难
题目: 查询每个日期登录新用户个数,并且查询结果按照日期升序排序。
题解:
# 方式1:窗口函数ROW_NUMBER+CASE语句
# 思路:给每个用户分区,选出编号为1与对应日期的记录,CASE函数做判断后SUM
SELECT A.date,SUM(
CASE
WHEN t_rank = 1 THEN 1 # 编号为1的是用户新登录
ELSE 0 # 其他编号的是用户再次登录
END)
FROM (SELECT date,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS t_rank
FROM login) AS A
GROUP BY A.date
## PARTITION BY:把user_id分区,这里类似GROUP BY,但要清楚他们不是等同的。
# 方式2:CASE语句
# 思路:日期分组,SUM用户新登录的记录
SELECT date,SUM(CASE
WHEN (user_id,date) IN (
SELECT user_id,MIN(date)
FROM login
GROUP BY user_id) THEN 1 # 若当前日期是用户新登录时间,赋值为1
ELSE 0 # 否则为0
END)
FROM login
GROUP BY date
ORDER BY date
# 方式3:IFNULL函数,这个方式比较复杂,不建议使用
# 思路:直接统计出每日用户新登录的记录,若无记录则置为0
SELECT A.date,IFNULL(n1.new_num,0) # 配合外连接的特性,无记录则为显示0
FROM login A
LEFT JOIN(SELECT B.date,COUNT(DISTINCT B.user_id) AS new_num # 算出每日用户新登录的记录数
FROM login B
WHERE B.date = (SELECT MIN(date) # 新登录时间
FROM login
WHERE user_id = B.user_id)
GROUP BY B.date) n1 ON A.date = n1.date
GROUP BY A.date
ORDER BY A.date
- 个人整理的笔记,仅供学习使用,有问题麻烦指正。
- 题目来源于:牛客网