2.SQL连续查询问题拓展—记上海拼多多非技术岗面试真题2024-08-01

真巧，昨天刚写了关于数据库连续问题的解决方案，没想到今天下午两点就有朋友在上海拼多多面试非技术岗位中就遇到了相似的问题。下面是原题：

一个最大连续支付失败的次数有一张支付流水表pay;字段如下

id	uid	time	status
pay_01	1	2024-01-15 10:00:00	fail
pay_02	1	2024-01-15 10:00:20	success

求每个用户的最大连续支付失败次数
输出字段:uid,cnt（失败次数）

如果我朋友看过我昨天写的数据库查找连续数据的文章，那么至少会有一些思路，而不是写的一塌糊涂了哈哈哈。

思路

就像我昨天写的一样，连续问题，创造一个连续列来和目标判断是否连续的列相比较是比较简单的一种办法。
比如题目中，需要时间连续的某人最大的支付失败数量，那么就需要按照时间排序了，那么如何判断时间连续，题目中并没有规定时间的出现频次，所以直接和时间来做比较判断连续并不是一个好的解决办法。

这里其实最简单的就是生成两次行号了。
步骤
1.按照用户编号开窗，按照时间排序，增加行号，这样就得到了每个用户按照时间排序的订单序号。
2.筛选掉支付订单成功的，再根据用户编号开窗，按照时间排序，增加行号，这样就得到了每个用户按照时间排序的失败的订单的序号。
3.两个序号相减，相等的数字则表示连续。
4.按照用户和得到的数字分组，或者最大的组，就是最大的连续支付失败的订单数量。

WITH ContinueCustomer AS(
	SELECT p1.*, p1.rowNumber - RANK() over(PARTITION by p1.uid ORDER BY p1.time) as diff FROM (
	SELECT *,RANK() over(PARTITION by uid ORDER BY time) as rowNumber FROM pay
	) p1 WHERE p1.status = 'fail'
),
EveryContinueCustomerCounts AS(
	SELECT uid,count(1) as times FROM ContinueCustomer GROUP BY uid,diff
) 
SELECT uid,max(times) as cnt FROM EveryContinueCustomerCounts GROUP BY uid