SQL 语法与 DuckDB 扩展

3.1 标准 SQL 兼容性

DuckDB 支持 SQL:2011 标准的绝大多数特性，包括 CTE（WITH 子句）、窗口函数、LATERAL JOIN 等。在此基础上，DuckDB 为分析场景扩展了许多便利语法。

SQL-- 标准 CTE
WITH monthly_sales AS (
    SELECT
        date_trunc('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY month
)
SELECT * FROM monthly_sales ORDER BY month;

-- DuckDB 允许在 SELECT 中使用别名（标准 SQL 中不允许）
SELECT
    amount * 1.13 AS amount_with_tax,
    amount_with_tax * 0.1 AS commission  -- 引用上面定义的别名！
FROM orders;

-- FROM 子句可以省略（对于常量查询）
SELECT 42 AS answer, current_date() AS today;

3.2 GROUPING SETS / CUBE / ROLLUP 多维聚合

这三个语法允许在一次查询中生成多个聚合维度的结果，避免 UNION ALL 多次查询。

GROUPING SETS：手动指定维度组合

SQL-- 同时按（year, quarter）、（year）、（）三个维度聚合
SELECT
    year,
    quarter,
    SUM(sales) AS total
FROM sales_data
GROUP BY GROUPING SETS (
    (year, quarter),  -- 按年季聚合
    (year),           -- 按年聚合
    ()                -- 全局总计
)
ORDER BY year, quarter;

ROLLUP：层次聚合（从细到粗）

SQL-- ROLLUP(country, region, city) 自动生成：
-- (country, region, city) → (country, region) → (country) → ()

SELECT
    country,
    region,
    city,
    SUM(sales) AS total
FROM geo_sales
GROUP BY ROLLUP(country, region, city)
ORDER BY country, region, city;

CUBE：所有可能的维度组合

SQL-- CUBE(year, product, region) 生成 2^3=8 种组合的聚合
SELECT
    year,
    product,
    region,
    SUM(sales) AS total,
    GROUPING(year, product, region) AS grouping_id  -- 标识当前行属于哪个聚合级别
FROM sales_cube
GROUP BY CUBE(year, product, region);

3.3 PIVOT / UNPIVOT 行列转换

PIVOT：将行数据转为列

SQL-- 原始数据：每行一个（产品, 月份, 销量）
-- PIVOT 后：每行一个产品，月份变成列名

PIVOT sales
ON month           -- month 列的值变为列名
USING SUM(amount)  -- 聚合函数
GROUP BY product;  -- 行标识

-- 输出示例：
-- product | Jan   | Feb   | Mar
-- 手机    | 10000 | 12000 | 9000
-- 笔记本  | 8000  | 7500  | 9500

-- 手动指定转换的值列表
PIVOT sales
ON month IN ('Jan', 'Feb', 'Mar')
USING SUM(amount)
GROUP BY product;

UNPIVOT：将列数据转为行

SQL-- 原始宽表：product | jan_sales | feb_sales | mar_sales
-- UNPIVOT 后：product | month | sales

UNPIVOT wide_sales
ON jan_sales, feb_sales, mar_sales  -- 要转换的列
INTO
    NAME  month    -- 新的 "名称" 列
    VALUE sales;   -- 新的 "值" 列

3.4 LIST 和 STRUCT 嵌套类型

DuckDB 原生支持嵌套数据类型，不需要 JSON 字符串的转换开销。

LIST（数组）

SQL-- 创建包含 LIST 列的表
CREATE TABLE users (
    id INTEGER,
    name VARCHAR,
    tags VARCHAR[]       -- 字符串数组
);

INSERT INTO users VALUES
    (1, 'Alice', ['admin', 'user']),
    (2, 'Bob',   ['user']);

-- 访问数组元素（1-indexed）
SELECT name, tags[1] AS first_tag FROM users;

-- 检查数组是否包含某元素
SELECT * FROM users WHERE list_contains(tags, 'admin');

-- 展开数组为多行
SELECT name, unnest(tags) AS tag FROM users;

-- 聚合为数组
SELECT list(name) AS all_names FROM users;

STRUCT（结构体）

SQLCREATE TABLE orders (
    id INTEGER,
    customer STRUCT(name VARCHAR, city VARCHAR, vip BOOLEAN)
);

INSERT INTO orders VALUES
    (1, {'name': 'Alice', 'city': 'Beijing', 'vip': true});

-- 用点语法访问字段
SELECT customer.name, customer.city FROM orders;

-- MAP 类型：动态键值对
SELECT MAP(['key1', 'key2'], [1, 2]) AS m;

3.5 Lambda 函数

DuckDB 支持在列表操作函数中使用 Lambda 表达式，语法为 x -> 表达式。

SQL-- list_transform：对列表中每个元素应用转换
SELECT list_transform([1, 2, 3, 4], x -> x * x) AS squares;
-- 结果：[1, 4, 9, 16]

-- list_filter：过滤列表
SELECT list_filter([1, 2, 3, 4, 5], x -> x % 2 = 0) AS evens;
-- 结果：[2, 4]

-- list_reduce：归约列表
SELECT list_reduce([1, 2, 3, 4], (acc, x) -> acc + x) AS sum;
-- 结果：10

-- 对表的数组列应用 Lambda
SELECT
    name,
    list_transform(scores, x -> round(x * 1.1, 2)) AS boosted_scores
FROM students;

3.6 字符串操作增强

SQL-- || 字符串连接（比 CONCAT 更简洁）
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- printf 格式化字符串
SELECT printf('Hello %s, your score is %.2f', name, score) FROM users;

-- string_split：分割字符串为数组
SELECT string_split('a,b,c', ',');  -- ['a', 'b', 'c']

-- 正则表达式
SELECT regexp_extract('2024-01-15', '(\d{4})-(\d{2})-(\d{2})', 1) AS year;

3.7 ASOF JOIN 时序连接

ASOF JOIN 是 DuckDB 为时序数据专门设计的 JOIN 类型：对于左表的每一行，找到右表中时间戳小于等于它的最近一条记录。常用于把价格/汇率快照表和交易记录关联。

SQL-- 场景：把每笔交易和当时的汇率关联
-- trades 表：trade_id, trade_time, amount_usd
-- rates  表：rate_time, usd_to_cny

SELECT
    t.trade_id,
    t.trade_time,
    t.amount_usd,
    r.usd_to_cny,
    t.amount_usd * r.usd_to_cny AS amount_cny
FROM trades t
ASOF JOIN rates r
    ON t.trade_time >= r.rate_time  -- 找到 rate_time <= trade_time 的最近记录
ORDER BY t.trade_time;

3.8 EXCLUDE 和 REPLACE 列操作

这两个 DuckDB 专有语法让 SELECT 更简洁，避免列出所有列名。

SQL-- EXCLUDE：选择除指定列之外的所有列
SELECT * EXCLUDE (password, secret_key) FROM users;

-- REPLACE：替换某列的值，其他列保持不变
SELECT * REPLACE (
    round(amount, 2) AS amount,
    upper(name) AS name
) FROM orders;

-- 两者组合使用
SELECT * EXCLUDE (raw_data) REPLACE (amount * 1.13 AS amount) FROM orders;