180. Consecutive Numbers
Description
Table: Logs
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ In SQL, id is the primary key for this table. id is an autoincrement column starting from 1.
Find all numbers that appear at least three times consecutively.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three times.
Solutions
Solution 1: Two Joins
We can use two joins to solve this problem.
First, we perform a self-join with the condition l1.num = l2.num
and l1.id = l2.id - 1
, so that we can find all numbers that appear at least twice in a row. Then, we perform another self-join with the condition l2.num = l3.num
and l2.id = l3.id - 1
, so that we can find all numbers that appear at least three times in a row. Finally, we only need to select the distinct l2.num
.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
1 2 3 4 5 6 |
|
Solution 2: Window Function
We can use the window functions LAG
and LEAD
to obtain the num
of the previous row and the next row of the current row, and record them in the fields $a$ and $b$, respectively. Finally, we only need to filter out the rows where $a = num$ and $b = num$, which are the numbers that appear at least three times in a row. Note that we need to use the DISTINCT
keyword to remove duplicates from the results.
We can also group the numbers by using the IF
function to determine whether the num
of the current row is equal to the num
of the previous row. If they are equal, we set it to $0$, otherwise we set it to $1$. Then, we use the window function SUM
to calculate the prefix sum, which is the grouping identifier. Finally, we only need to group by the grouping identifier and filter out the numbers with a row count greater than or equal to $3$ in each group. Similarly, we need to use the DISTINCT
keyword to remove duplicates from the results.
1 2 3 4 5 6 7 8 9 10 11 12 |
|
Solution 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|