题目出处:
苏克1900:腾讯面试官出的 2 道经典数据分析面试题zhuanlan.zhihu.com![66a0b5a3685c78fa0d2d8a0c50f3e253.png](https://i-blog.csdnimg.cn/blog_migrate/64a5d1a0d3089c4d6f638b40ec4280ff.jpeg)
题目描述如下:
- 有一张用户签到表【t_user_attendence】,标记每天用户是否签到(说明:该表包含所有用户所有工作日的出勤记录) ,包含三个字段:日期【fdate】,用户id【fuser_id】,用户当天是否签到【fis_sign_in:0否1是】;
- 问题1:请计算截至当前每个用户已经连续签到的天数(输出表仅包含当天签到的所有用户,计算其连续签到天数)
输出表【t_user_consecutive_days】:用户id【fuser_id】,用户连续签到天数【fconsecutive_days】 - 问题2:请计算每个用户历史以来最大的连续签到天数(输出表为用户签到表中所有出现过的用户,计算其历史最大连续签到天数)
输出表【t_user_max_days】:用户id【fuser_id】,用户最大连续签到天数【fmax_days】
个人抽象且浅显的理解:连续问题类似高中物理两物体同向匀速前行问题,两物体之间的偏移量始终相同。(文科生浅显的理解,你杠你对)
因此关键在于找到匀速前行的“两物体”。
运行环境:MySQL8.0版本、SQLyog
题目没有给出具体数据,为了方便理解,添加虚拟数据。
建表:
CREATE TABLE IF NOT EXISTS t_user_attendence(
fdate DATE NOT NULL,
fuser_id INT PRIMARY KEY,
fis_sign_in INT NOT NULL
);
插入数据:
INSERT INTO t_user_attendence VALUES
('2020-07-06','01','1'),
('2020-07-06','02','1'),
('2020-07-06','03','1'),
('2020-07-07','01','1'),
('2020-07-07','02','1'),
('2020-07-07','03','0'),
('2020-07-08','01','1'),
('2020-07-08','02','0'),
('2020-07-08','03','1'),
('2020-07-09','01','1'),
('2020-07-09','02','0'),
('2020-07-09','03','1'),
('2020-07-10','01','1'),
('2020-07-10','02','1'),
('2020-07-10','03','1'),
('2020-07-13','01','1'),
('2020-07-13','02','1'),
('2020-07-13','03','1'),
('2020-07-14','01','1'),
('2020-07-14','02','0'),
('2020-07-14','03','1'),
('2020-07-15','01','0'),
('2020-07-15','02','1'),
('2020-07-15','03','0'),
('2020-07-16','01','1'),
('2020-07-16','02','0'),
('2020-07-16','03','1'),
('2020-07-17','01','1'),
('2020-07-17','02','1'),
('2020-07-17','03','0');
![ceeb7b1be4e83aa231e5d239d7f41e87.png](https://i-blog.csdnimg.cn/blog_migrate/25d9366c1f143fe8289d550ebfb2a193.jpeg)
先看fuser_id为1的员工签到情况
SELECT *
FROM t_user_attendence
WHERE fuser_id=1
![0dedd6a8df96f4e6cf26a564e8434de6.png](https://i-blog.csdnimg.cn/blog_migrate/8b70d1d993febc26d0f254a78ee8d4da.png)
再根据日期排序作出辅助列(r1),根据签到与否排序作出辅助列(r2)
SELECT *
,row_number() over(ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in ORDER BY fdate) AS r2
FROM (
SELECT fdate,fuser_id,fis_sign_in
FROM t_user_attendence
WHERE fuser_id=1
) t1
ORDER BY fdate
![25594ce9ffb85adfd9c5dc3a156db9e8.png](https://i-blog.csdnimg.cn/blog_migrate/19412b15ca60b5c188e31e68e256d5fc.png)
计算r1、r2的差值
SELECT *,r1-r2 FROM(
SELECT *
,row_number() over(ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in ORDER BY fdate) AS r2
FROM (
SELECT fdate,fuser_id,fis_sign_in
FROM t_user_attendence
WHERE fuser_id=1
) t1
ORDER BY fdate
) t2
![8ac77b38ec68825548a884b46316a3eb.png](https://i-blog.csdnimg.cn/blog_migrate/e814389202476c083c43aceffb83b826.png)
fis_sign_in=1对应的r1-r2列出现的连续相同值,代表连续签到。
2020-07-15未签到,r1-r2列出现异常值。
16和17日连续签到后,再次出现连续相同值。
接下来只需要对fis_sign_in=1且r1-r2列出现的连续相同值进行计数即可,用到count函数。
SELECT *
,COUNT(1) over(PARTITION BY fis_sign_in,r1-r2) AS r3
FROM(
SELECT *,r1-r2 FROM(
SELECT *
,row_number() over(ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in ORDER BY fdate) AS r2
FROM (
SELECT fdate,fuser_id,fis_sign_in
FROM t_user_attendence
WHERE fuser_id=1
) t1
ORDER BY fdate
) t2
) t3
ORDER BY fdate
![d54dd2d32949d7cfb0bd9cf9508f4147.png](https://i-blog.csdnimg.cn/blog_migrate/0b9eec634a9c3ea224c066bf7f71c73b.jpeg)
根据问题1的题意,查看最新日期所在行即可满足要求
SELECT fuser_id
,COUNT(1) over(PARTITION BY fis_sign_in,r1-r2) AS r3
FROM(
SELECT *,r1-r2 FROM(
SELECT *
,row_number() over(ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in ORDER BY fdate) AS r2
FROM (
SELECT fdate,fuser_id,fis_sign_in
FROM t_user_attendence
WHERE fuser_id=1
) t1
ORDER BY fdate
) t2
) t3
ORDER BY fdate DESC
LIMIT 1
![b0ce25ab85af239e362421d805d96d5f.png](https://i-blog.csdnimg.cn/blog_migrate/932895d0bbcefa0d59e86bb2b47ad8c8.png)
注意这里因为sql执行顺序的缘故不能直接加where fdate='2020-07-17'的限制条件
需要再嵌套一层才能使用where作限制
SELECT fuser_id,r3 FROM(
SELECT *
,COUNT(1) over(PARTITION BY fis_sign_in,r1-r2) AS r3
FROM(
SELECT *,r1-r2 FROM(
SELECT *
,row_number() over(ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in ORDER BY fdate) AS r2
FROM (
SELECT fdate,fuser_id,fis_sign_in
FROM t_user_attendence
WHERE fuser_id=1
) t1
ORDER BY fdate
) t2
) t3
ORDER BY fdate
) t4
WHERE fdate='2020-07-17'
输出结果也是一样的喔,fuser_id=2和=3也是一样的做法。
明白方法后我们再回来思考题目1,题目要求是需要一次性输出所有员工的连续签到数,因此需要在用窗口函数进行排序时按照fuser_id进行分组。
CREATE TABLE IF NOT EXISTS t_user_consecutive_days AS
SELECT fuser_id,fconsecutive_days
FROM(
SELECT fdate,fuser_id,fis_sign_in
,COUNT(1) over(PARTITION BY fis_sign_in,fuser_id,r1-r2) AS fconsecutive_days
FROM(
SELECT *,r1-r2
FROM(
SELECT *
,row_number() over(PARTITION BY fuser_id ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in,fuser_id ORDER BY fdate) AS r2
FROM t_user_attendence
ORDER BY fdate,fuser_id
) t
) t2
ORDER BY fdate,fuser_id
) t3
WHERE fdate='2020-07-17'
![0de90ef849cceda1e3a98c63def0be1c.png](https://i-blog.csdnimg.cn/blog_migrate/902a0f8a978f7609c62085dffabd3df4.png)
题目2的解答沿袭了题目1的方法。题目要求计算每个用户历史以来最大的连续签到天数,因此主要解决最大即可,使用max函数。
CREATE TABLE IF NOT EXISTS t_user_max_days AS
SELECT fuser_id,MAX(r3) AS fmax_days
FROM(
SELECT fdate,fuser_id,fis_sign_in
,COUNT(1) over(PARTITION BY fis_sign_in,fuser_id,r1-r2) AS r3
FROM(
SELECT *,r1-r2
FROM(
SELECT *
,row_number() over(PARTITION BY fuser_id ORDER BY fdate) AS r1
,row_number() over(PARTITION BY fis_sign_in,fuser_id ORDER BY fdate) AS r2
FROM t_user_attendence
ORDER BY fdate,fuser_id
) t
) t2
ORDER BY fdate,fuser_id
) t3
GROUP BY fuser_id
![56817171a5cde085ab0438edc1afe07b.png](https://i-blog.csdnimg.cn/blog_migrate/2b2cced0ac24e944e8f6c18129e5bae6.png)
解题方法参考了爱数据学习社的推文“超经典SQL题 | 做完这4道面试题你就过关了”
如有错误欢迎指正喔!有更好的解法也可以在评论区交流探讨!谢谢!