Window Function

Reference: Window Functions in SQL - GeeksforGeeks

talk is cheap, example first

Original table

Employees Table

if we want to know the average salary of each department, and keep the origin table structure

we can write like this without join

SELECT Name, Age, Department, Salary, 
       AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
 FROM employee

the result:

Review structure of window function

SELECT column_name1, 
       window_function(column_name2) 
       OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column
FROM table_name;

Key Terms

• window_function: Any aggregate or ranking function (SUM(), AVG(), ROW_NUMBER(), etc.)

• column_name1: Regular column(s) to be selected in the outputand column_name2: Column on which the window function is applied

• column_name3: Column used for dividing rows into groups (PARTITION BY)and column_name4: Column used to define order of rows within each partition (ORDER BY)

• new_column: Alias for calculated result of the window function

• table_name: table from which data is selected

Window function is a table generator using speticular written function including aggregate function to add new column as a new feature based on original table.Compare with group by, Window function allow us to preserve the original row-level detail.

Solution

Reference:180. 连续出现的数字 - 力扣（LeetCode）

firstly, use window function to add a new column on the Logs table basis

select distinct num as ConsecutiveNums
from 
(select *,
    row_number() over (partition by num order by id) as serial
from Logs) as tt

serial is the sequence number that a particular num appears in the table

id measures the frequency numbers appear in the table, we assume id is continuous increasing by 1

So the requirement of a num appear consecutively is its id - serial equals to the id - serial above the number.

Does a consistent id - serial guarantees that numbers appear consecutively? Not necessarily. We must include the condition that the num themselves are identical

It can be observed that when id = 1, id = 2 id - serial is equal

and the id start with 0, id - serial would become -1 in the beginning, which will lead to a overflow exception. Considering that "the specific value of id - serial does not matter", we can modify the indicator to "id + 1 - serial", the +1 could as well be +1000, however, since the minmum id starts from 0, it is not strictly necessary to use large constant.

How to achieve this? Use group by (id + 1 - serial), num

Summary：

1. build a new column serial with window function

2. Based on the analysis above, group by (id + 1 - serial), num, num also tackled group by grammer detail.

group by

As we know, if you use group by , the column after select must fit one of the conditions below

1. column after group by

2. aggregate function

3. Constant or Expression independent of data of the table

4. if you group by primary key, you have no restrictions

but look at the original code

select num as ConsecutiveNums
from 
(select *,
    row_number() over (partition by num order by id) as serial
from Logs) as tt
group by (id + 1 - serial)
having count(*) >= 3

it could run and mysql do not occur exception

this is because mysql will randomly pick one value from each group, although it is illegal in syntax, but we know in some cases the nums in the group are the same ONLY_ FULL_GROUP_BY