Write a SQL query to find all numbers that appear at least three times

Question

I am in the SQL language and wondering:

Write an SQL query to find all the numbers that appear at least three times in a row.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above table of logs, 1 is the only number that appears at least three times.

I got the solution online and tested it. But I really don't get it. The big picture of the solution is clear. The table sqcounts the occurrences. But I did not understand the calculation part sq. I have done a lot of research on MYSQL. @counter := IF(@prev = Num, @counter + 1, 1)means that prev = Num, which makes counter = counter + 1, otherwise counter = 1. (SELECT @counter:=1, @prev:=NULL) varsmeans creating a table varsthat includes two columns counterand pre.

- sq? SELECT? SQL, , . !

SELECT  DISTINCT(Num) AS ConsecutiveNums
FROM (
    SELECT
    Num,
    @counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
    @prev := Num
    FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars
) sq
WHERE how_many_cnt_in_a_row >= 3

Answer 1

-, @counter @prev. .

(SELECT @counter:=1, @prev:=NULL)

, sq , , . @counter variable , , @prev Num, @counter reset 1, .

, sq:

+-----+-----------------------+ | Num | how_many_cnt_in_a_row | +-----+-----------------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 1 | 1 | | 2 | 1 | | 2 | 2 | +-----+-----+

Answer 2

, . .

SELECT... FROM

SELECT...FROM Logs y, (...) vars?

: create table test(field1 int), 3 :

field1
-------
1
2
3

select * from test, (select @counter:=1, @prev:=NULL) vars

field1  @counter:=1  @prev=NULL
------- ------------ -----------
1       1            NULL
2       1            NULL
3       1            NULL

@counter @prev . 1 NULL. , , .

.

SELECT
Num,
@counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
@prev := Num
FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars

ID = 1, Num = 1 Num .

. , @prev = Num. , @prev - NULL, . , @prev = Num . IF IF(condition, what-to-do-if-condition-is-true, what-to-do-if-condition-is-false).

IF(@prev = Num, @counter + 1, 1)
   -----------  ------------  --
   condition    do this       do this if condition
                if true       is false

@prev NULL Num, 1.

@prev Num. . , SELECT .

Num  @prev was  @counter was  @counter calculation      @prev reset to Num
---  ---------  ------------  -----------------------   ------------------
1    NULL       1             is @prev = 1? No. So 1      1
1    1          1             is @prev = 1? Yes! So 2     1
1    1          2             is @prev = 1? Yes! So 3     1
2    1          3             is @prev = 2? No. So 1      2
1    2          1             is @prev = 1? No. So 1      1
2    1          1             is @prev = 2? No. So 1      2
2    2          1             is @prev = 2? Yes! So 2     2

2- 3- .

, , SELECT DISTINCT... : , @counter 3 .

Num   @counter  @prev
----  --------  -----
1     3         1

1 , , 1. DISTINCT (Num) 1. . WHERE WHERE ... = 3 insted >= 3.

, .