Common tools for analysts SQL (common syntax and common window functions)

Time:2020-2-15

1 – execution sequence
from-on-join-where-group by-having-select-distinct-order by-limit

From (the Cartesian product is calculated for the tables on the left and right sides of from to get the virtual table V1)
On (filter V1 table to get V2)
Join (carry out left join, right join and other processing on V2 table to get V3)
Where (where filter V3 to get V4)
Group by (group by conditional operation on V4 to get V5)
Having (to filter the condition of having in V5 to get V6)
Select (select the specified field)
Distinct (de duplication)
Order by
Limit (take out the specified line)

2-window function
2.1 use scenario: de duplication
row_number() over (partition by … order by )
rank() over (partition by … order by )