一、SQL66 牛客每个人最近的登录日期(一)
牛客每天有很多人登录,请你统计一下牛客每个用户最近登录是哪一天。
有一个登录(login)记录表,简况如下:
请你写出一个sql语句查询每个用户最近一天登录的日子,并且按照user_id升序排序,上面的例子查询结果如下:
二、FIRST_VALUE,LAST_VALUE
1.FIRST_VALUE
原始表中信息
id|userid|clientid|date
1|2 |1 |2020-10-12
2|3 |2 |2020-10-12
3|2 |2 |2020-10-13
4|3 |2 |2020-10-13
SELECT user_id,
FIRST_VALUE(date) over(partition by user_id order by date DESC) d
FROM login;
user_id|max_date
2|2020-10-13
2|2020-10-13
3|2020-10-13
3|2020-10-13
2.last_value
SELECT user_id,
LAST_VALUE(date) over(partition by user_id order by date) d
FROM login;
last_value()默认统计范围是 rows between unbounded preceding and current row,也就是取当前行数据与当前行之前的数据的比较。
(详见https://blog.csdn.net/huozhicheng/article/details/5843782#part1
)
2|2020-10-12
2|2020-10-13
3|2020-10-12
3|2020-10-13
SELECT user_id,
LAST_VALUE(date) over(partition by user_id order by date
rows between unbounded preceding and unbounded following) d
FROM login;
2|2020-10-13
2|2020-10-13
3|2020-10-13
3|2020-10-13
(SUM函数,跟last_value()相同)
取消重复值
SELECT distinct user_id,
具体代码如下
SELECT distinct user_id,
LAST_VALUE(date) over(partition by user_id order by date
rows between unbounded preceding and unbounded following) d
FROM login;
3.查询每个日期登录新用户个数
牛客每天有很多人登录,请你统计一下牛客每个日期登录新用户个数,
有一个登录(login)记录表,简况如下:
请你写出一个sql语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序,上面的例子查询结果如下:
具体代码如下:
SELECT t.date,sum(t.num) as new
FROM
( SELECT date,
(case when
date=min(date) over(partition by user_id order by date)
then 1
ELSE 0
END) as num
FROM login
) as t
GROUP BY date
ORDER BY date;