【数据分析面试】37.找出连续出现3次的数字（SQL：LEAD()和 LAG()的使用）

题目: 找出连续出现3次的数字

给定一个日志表 Logs，包含两列：Id 和 Num。请编写一个 SQL 查询，找出在 Num 列中连续出现至少三次的数字。

示例：

输入：

Logs 表格：

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

输出：

ConsecutiveNums
1

答案

解题思路

通过使用窗口函数，我们可以将每个数字的前两个相邻数字与当前数字进行比较，以确定是否存在连续出现三次的情况。

答案代码

SELECT DISTINCT Num as ConsecutiveNums
FROM(
    SELECT Id,Num,
    Lead(Num,1)OVER() as num_1, -- 选择指定行的向下一行的内容
    Lead(Num,2)OVER() as num_2  -- 选择指定行的向下两行的内容
    FROM Logs
) as c
WHERE c.Num = c.num_1 AND c.num_1 = c.num_2 -- 这三个数都要相等。

这段代码中，我们使用了 Lead() 函数来获取当前行的后两行的 Num 值，并通过比较确定是否连续出现了三次相同的数字。

窗口函数LEAD() 和 LAG()的区别

当使用窗口函数时，LEAD() 和 LAG() 函数都用于访问当前行之前或之后的行的值，但它们的行为略有不同。

让我们通过一个简单的示例来说明它们之间的区别：

假设我们有以下数据表 Numbers：

Id	Value
1	10
2	20
3	30
4	40
5	50

现在，我们想要查询每一行的 Value 值，并且获取其前一行和后一行的值。

SELECT
    Id,
    Value,
    LAG(Value) OVER (ORDER BY Id) AS PreviousValue,
    LEAD(Value) OVER (ORDER BY Id) AS NextValue
FROM Numbers;

这会产生以下结果：

Id	Value	PreviousValue	NextValue
1	10	(null)	20
2	20	10	30
3	30	20	40
4	40	30	50
5	50	40	(null)

• LAG(Value) OVER (ORDER BY Id): 返回按照 Id 排序的前一行的 Value 值。对于第一行，因为没有前一行，所以返回 null。
• LEAD(Value) OVER (ORDER BY Id): 返回按照 Id 排序的后一行的 Value 值。对于最后一行，因为没有后一行，所以返回 null。

所以，LAG() 和 LEAD() 分别获取了前一行和后一行的值，并根据指定的排序条件进行操作。

更多详细答案可关注公众号查阅。