SQL 进阶

本节目标

读完本篇，你应能：

用 INNER JOIN 与 LEFT JOIN 关联多张表，并理解「保留哪一侧行」的差异
用 WITH（CTE，Common Table Expression）拆分复杂查询、提高可读性
编写窗口函数：按分区排序编号（ROW_NUMBER）与累计求和（SUM() OVER）
各写一条 PIVOT 与 UNPIVOT 示例，理解宽表与长表互转
用 CREATE VIEW 封装常用查询，用 CREATE MACRO 定义可复用的 SQL 表达式
区分窗口函数与聚合函数，并知道 DuckDB 的 QUALIFY 如何过滤窗口结果

若还不熟悉 SELECT / GROUP BY，请先阅读 SQL 基础。

概念：从单表到多表与「不折叠行」的汇总

SQL 基础里主要在一张 employees 表上做单表查询与 GROUP BY 聚合。进阶查询常见两类需求：

多表关联：例如部门详情在另一张表，需要把员工与部门信息拼在同一结果里。
保留明细行的统计：聚合会把多行压成一行；窗口函数 (window functions) 在「当前结果集的每一行」上再算一列（如组内排名、累计和），行数不变。

下面仍从 employees 出发，并增加 departments 与一张用于透视的 sales 表。建议在 CLI 中按块复制执行。

准备示例数据

CREATE OR REPLACE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    department_id INTEGER,
    salary DOUBLE
);

CREATE OR REPLACE TABLE departments (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    location VARCHAR
);

INSERT INTO employees VALUES
    (1, 'Alice', 10, 120000),
    (2, 'Bob', 10, 95000),
    (3, 'Carol', 20, 88000),
    (4, 'Dave', 20, 91000),
    (5, 'Eve', 10, 105000),
    (6, 'Frank', NULL, 72000);

INSERT INTO departments VALUES
    (10, 'Engineering', 'Building A'),
    (20, 'Sales', 'Building B');

CREATE OR REPLACE TABLE sales (
    region VARCHAR,
    product VARCHAR,
    amount DOUBLE
);

INSERT INTO sales VALUES
    ('East', 'Widget', 100),
    ('East', 'Gadget', 80),
    ('West', 'Widget', 120),
    ('West', 'Gadget', 90);

Frank 的 department_id 为 NULL，便于演示 LEFT JOIN 与内连接结果差异。

JOIN：内连接与左连接

内连接 (INNER JOIN) 只保留两表连接条件都能匹配上的行：

SELECT
    e.name AS employee,
    d.name AS department,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY e.name;

Frank 没有有效部门，不会出现在结果中。

左连接 (LEFT JOIN) 保留左表（此处 employees）全部行；右表无匹配时，右表列为 NULL：

SELECT
    e.name AS employee,
    d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
ORDER BY e.name;

写 JOIN 时建议给表起别名 (alias)，并在 ON 里写清等值条件，避免把过滤条件误写在 WHERE 里导致外连接语义被「提前过滤」掉。更多 JOIN 变体见 FROM and JOIN。

WITH：公共表表达式 (CTE)

WITH 把子查询命名成临时结果，供后续 SELECT 引用，逻辑上等价于可读的子查询链：

WITH dept_avg AS (
    SELECT
        department_id,
        avg(salary) AS avg_salary
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
)
SELECT
    e.name,
    e.salary,
    round(da.avg_salary, 2) AS dept_avg_salary,
    round(e.salary - da.avg_salary, 2) AS diff_from_avg
FROM employees e
INNER JOIN dept_avg da ON e.department_id = da.department_id
ORDER BY diff_from_avg DESC;

DuckDB 支持多个 CTE 逗号分隔，也支持 WITH 递归（本系列不展开）。语法见 WITH 子句。

窗口函数：ROW_NUMBER 与 SUM() OVER

窗口函数语法一般为：函数(...) OVER (PARTITION BY ... ORDER BY ...)。同一 PARTITION BY 组内独立计算。

组内按薪资排名（薪资相同则名次可能并列，取决于函数；ROW_NUMBER 会强制连续编号）：

SELECT
    name,
    department_id,
    salary,
    row_number() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank_in_dept
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, salary_rank_in_dept;

按部门分区、按员工 id 排序的累计薪资（演示 SUM() OVER 与帧默认行为）：

SELECT
    name,
    department_id,
    salary,
    sum(salary) OVER (
        PARTITION BY department_id
        ORDER BY id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_salary_sum
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, id;

多个窗口可共用定义：DuckDB 支持 WINDOW w AS (...) 再在 OVER w 中引用，避免重复写长 OVER 子句（WINDOW 子句）。

PIVOT 与 UNPIVOT

PIVOT 把某一列的不同取值展开成多列（宽表），常用聚合填充单元格。DuckDB 提供简化语法：

PIVOT sales
ON product
USING sum(amount)
GROUP BY region;

上例按 region 为行、product 各取值为一列，对 amount 求和。

UNPIVOT 是逆操作：把多列「指标列」转回「名称 + 值」的长表。先建一张小宽表再演示：

CREATE OR REPLACE TABLE sales_wide (
    region VARCHAR,
    widget DOUBLE,
    gadget DOUBLE
);

INSERT INTO sales_wide VALUES
    ('East', 100, 80),
    ('West', 120, 90);

UNPIVOT sales_wide
ON widget, gadget
INTO
    NAME product
    VALUE amount;

-- 等价于 SELECT region, product, amount FROM 上述 UNPIVOT 结果

详细语法与 SQL 标准形式见 PIVOT 与 UNPIVOT。

CREATE VIEW

视图 (VIEW) 保存一条查询定义，查询时像表一样引用，不复制数据：

CREATE OR REPLACE VIEW v_employee_dept AS
SELECT
    e.id,
    e.name,
    coalesce(d.name, '(未分配)') AS department,
    e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

FROM v_employee_dept
WHERE salary >= 90000
ORDER BY salary DESC;

持久化数据库文件中，视图会写入 catalog；脚本里常用 CREATE OR REPLACE VIEW 便于重复执行。见 CREATE VIEW。

CREATE MACRO

宏 (MACRO) 把 SQL 表达式（或表表达式）注册成可调用对象，适合重复使用的公式或参数化子查询：

-- 标量宏：按税率计算税后薪资
CREATE OR REPLACE MACRO after_tax(gross, rate := 0.25) AS (gross * (1 - rate));

SELECT
    name,
    salary,
    round(after_tax(salary), 2) AS net_approx
FROM employees
WHERE department_id = 10;

-- 表宏：返回某部门员工（参数化 FROM 源）
CREATE OR REPLACE MACRO employees_in_dept(dept_id) AS TABLE
    SELECT * FROM employees WHERE department_id = dept_id;

FROM employees_in_dept(20) ORDER BY salary DESC;

CREATE FUNCTION 是 CREATE MACRO 的别名。参数可带默认值（:=）。见 CREATE MACRO。

常见坑

窗口函数 vs 聚合函数：GROUP BY + avg(salary) 会把每组压成一行；avg(salary) OVER (PARTITION BY department_id) 在每一行上给出该组的平均值，行数不变。不要把 OVER 写进 GROUP BY 期望替代分组——语义不同。需要「先聚合再对聚合结果开窗」时，用 CTE 分两步写最清晰。
WHERE 不能过滤窗口列：标准 SQL 里，WHERE 阶段窗口函数尚未计算，不能写 WHERE row_number() OVER (...) = 1。DuckDB 提供 QUALIFY，专门过滤窗口结果，例如「每个部门只保留薪资最高的一人」：
```
SELECT
    name,
    department_id,
    salary
FROM employees
WHERE department_id IS NOT NULL
QUALIFY row_number() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
) = 1
ORDER BY department_id;
```
没有 QUALIFY 时，常见写法是外层再包一层子查询或 CTE，在 WHERE 里过滤别名。
JOIN 条件与 NULL：NULL = NULL 在 SQL 中为未知，不会匹配；外连接后「本应匹配却因 NULL 键失配」的行，右表列为 NULL，与「右表无此行」在结果上需结合业务区分。
PIVOT 列名与类型：透视后新列名来自原列 distinct 值，非法标识符可能被改写；聚合列类型由 USING 表达式决定，宽表过宽时注意列数与内存。
VIEW 与 MACRO 持久化：在内存库中创建的 VIEW/MACRO 随进程结束消失；需要长期保留请使用文件库（见简介与安装）。

SQL 进阶#

本节目标#

概念：从单表到多表与「不折叠行」的汇总#

准备示例数据#

JOIN：内连接与左连接#

WITH：公共表表达式 (CTE)#

窗口函数：ROW_NUMBER 与 SUM() OVER#

PIVOT 与 UNPIVOT#

CREATE VIEW#

CREATE MACRO#

常见坑#

延伸阅读#