sql 差值_鹅厂2道经典sql面试题的解法思考

题目出处：

苏克1900：腾讯面试官出的 2 道经典数据分析面试题​zhuanlan.zhihu.com

题目描述如下：

• 有一张用户签到表【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');

部分数据

先看fuser_id为1的员工签到情况

SELECT *
FROM t_user_attendence
WHERE fuser_id=1

fis_sign_in列 签到为1，未签到为2

再根据日期排序作出辅助列(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

2020-07-15未签到，r2值为1。r1、r2偏移量发生改变

计算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

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

根据问题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

完成！

注意这里因为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'

完成！

---

题目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

完成！

---

解题方法参考了爱数据学习社的推文“超经典SQL题 | 做完这4道面试题你就过关了”

如有错误欢迎指正喔！有更好的解法也可以在评论区交流探讨！谢谢！