砍材农夫

公用表表达式

CTE生成一个命名临时表,并且只在查询期间有效
CTE临时表在一个查询中可以多次引用

WITH cte_name AS (
    SELECT ...
)
SELECT ...  FROM cte_name

CTE的递归生成序列

WITH RECURSIVE cte_name (column_name) AS (
    SELECT ...
)
SELECT ... FROM cte_name
UNION ALL

窗口函数

窗口函数是对一组行进行聚合运算，并返回一个结果。

function_name ([exp]) OVER (
    [PARTITION BY expression] 
    [ORDER BY expression [ASC|DESC]] [ROWS | RANGE frame_unit])

聚合函数

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS row_num

返回窗口分区内数据的行号

RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS rank

类似row_number()函数，但是会跳过相同值的行