理解连接和窗口函数

Source: Dev.to

连接（Joins）

连接允许你基于相关列将两个或多个表的行组合在一起。

INNER JOIN（内连接）

仅返回两个表中具有匹配值的行。

-- Syntax
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.name,
    o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

LEFT JOIN（左连接）

返回左表的所有行以及右表中匹配的行。右表中不匹配的行会包含 NULL。

-- Syntax
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN（右连接）

返回右表的所有行以及左表中匹配的行。左表中不匹配的行会包含 NULL。

-- Syntax
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

FULL JOIN（全连接）

当左表或右表任一侧有匹配时返回所有行。没有匹配的行在另一侧会包含 NULL。

-- Syntax
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN（笛卡尔积）

创建两个表的笛卡尔积；第一个表的每一行都会与第二个表的每一行配对。

-- Syntax
SELECT columns
FROM table1
CROSS JOIN table2;

-- Example
SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM customers c
CROSS JOIN orders o;

窗口函数（Window Functions）

窗口函数在一组与当前行某种方式相关的表行上执行计算。与聚合函数不同，窗口函数 不会 合并行；原始行数保持不变。

通用语法

window_function([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
    [frame_clause]
)

分类

聚合窗口函数（Aggregate Window Functions）

在定义的窗口上执行聚合计算。

-- Example: total quantity per customer
SELECT
    o.customer_id,
    o.order_date,
    c.first_name,
    c.last_name,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY o.customer_id) AS customer_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

常用函数：SUM()、AVG()、COUNT()、MIN()、MAX()。

排名窗口函数（Ranking Window Functions）

根据排序为行分配排名或位置。

-- ROW_NUMBER()
SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

-- RANK()
SELECT
    employee_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK()
SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- NTILE()
SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;

值（导航）窗口函数（Value (Navigation) Window Functions）

在同一窗口内访问其他行的值。

-- LAG(): value from the previous row
SELECT
    order_id,
    quantity,
    LAG(quantity) OVER (ORDER BY order_id) AS previous_quantity
FROM orders;

-- LEAD(): value from the next row
SELECT
    order_id,
    quantity,
    LEAD(quantity) OVER (ORDER BY order_id) AS next_quantity
FROM orders;

这些函数在分析查询中尤为有用，因为它们可以在不合并结果集的情况下，将一行与其同伴进行比较。