DuckDB 高级 SQL 技巧大全：PIVOT、Macro、窗口函数、递归CTE、JSON处理

-- 原始数据：每行一个季度
SELECT * FROM sales;
-- product  | quarter | amount
-- Apple    | Q1      | 100
-- Apple    | Q2      | 150
-- Banana   | Q1      | 200
-- Banana   | Q2      | 120

-- PIVOT：把季度变成列
SELECT * FROM sales
PIVOT(sum(amount) FOR quarter IN ("Q1", "Q2"));
-- product  | Q1   | Q2
-- Apple    | 100  | 150
-- Banana   | 200  | 120

SELECT * FROM pivot_sales
UNPIVOT(amount FOR quarter IN ("Q1", "Q2"));
-- product  | quarter | amount
-- Apple    | Q1      | 100
-- Apple    | Q2      | 150
-- Banana   | Q1      | 200
-- Banana   | Q2      | 120

SELECT 
    category,
    "January" AS jan, "February" AS feb, "March" AS mar
FROM monthly_sales
PIVOT(
    SUM(revenue) 
    FOR month_name 
    IN ("January", "February", "March")
);

-- 定义一个计算平方差的宏
CREATE MACRO square(x) AS x * x;

SELECT square(5) AS result;
-- 25

SELECT square(col_a - col_b) AS diff_square
FROM transactions;

CREATE MACRO mom_growth(curr, prev)
  AS ROUND((curr - prev) * 1.0 / prev, 4);

SELECT 
    month,
    revenue,
    mom_growth(revenue, LAG(revenue) OVER(ORDER BY month)) AS growth
FROM monthly_sales;
-- month   | revenue | growth
-- January | 10000   | NULL
-- February| 12000   | 0.2
-- March   | 11000   | -0.0833

CREATE MACRO round_to(x, digits DEFAULT 2)
  AS ROUND(x, digits);

SELECT round_to(3.14159);              -- 3.14
SELECT round_to(3.14159, 3);           -- 3.142
SELECT round_to(100.12345, 1);         -- 100.1

SELECT name, subject, score,
       LAG(score) OVER(PARTITION BY name ORDER BY score) AS prev_score
FROM scores;

-- name  | subject | score | prev_score
-- Bob   | Science | 88    | NULL
-- Bob   | Math    | 92    | 88
-- Alice | Math    | 85    | NULL
-- Alice | Science | 90    | 85

SELECT DISTINCT name,
       FIRST_VALUE(score) OVER(
         PARTITION BY name ORDER BY score DESC
       ) AS best_score,
       LAST_VALUE(score) OVER(
         PARTITION BY name ORDER BY score ASC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS worst_score
FROM scores;

WITH ranked AS (
    SELECT 
        product_name,
        revenue,
        SUM(revenue) OVER(ORDER BY revenue DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
        SUM(revenue) OVER() AS total
    FROM products
)
SELECT 
    product_name,
    revenue,
    ROUND(cumulative * 100.0 / total, 2) AS cumulative_pct
FROM ranked
ORDER BY revenue DESC;
-- 这就是著名的帕累托分析（二八法则）

WITH base AS (
    -- 第一步：筛选有效订单
    SELECT id, amount, customer_id 
    FROM orders 
    WHERE status = 'completed'
),
ranked AS (
    -- 第二步：计算客户排名
    SELECT 
        customer_id,
        SUM(amount) AS total_spend,
        ROW_NUMBER() OVER(ORDER BY SUM(amount) DESC) AS rn
    FROM base
    GROUP BY customer_id
),
top_customers AS (
    -- 第三步：取 Top 10
    SELECT * FROM ranked WHERE rn <= 10
)
SELECT * FROM top_customers;

WITH RECURSIVE org_tree AS (
    -- 锚点：最高层管理者
    SELECT id, name, manager_id, 1 AS level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下级员工
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT 
    LPAD('', (level-1)*4, ' ') || name AS org_chart,
    level
FROM org_tree
ORDER BY level, name;
-- 结果示例：
-- CEO
--     Engineering
--         Alice
--         Bob
--     Marketing
--         Charlie

-- 提取 JSON 字段
SELECT json_extract_string(
    '{"user": "alice", "age": 30, "tags": ["dev", "gopher"]}',
    '$.user'
) AS username;
-- alice

SELECT * FROM json_each('{
    "items": ["Apple", "Banana", "Cherry"]
}'::JSON -> 'items') AS j;
-- [{"value":"Apple"},{"value":"Banana"},{"value":"Cherry"}]

SELECT
    list_sort([3, 1, 2])              AS sorted,      -- [1, 2, 3]
    list_reverse([1, 2, 3])           AS reversed,    -- [3, 2, 1]
    list_distinct([1, 1, 2, 3])       AS uniq,        -- [1, 2, 3]
    list_concat([1, 2], [3, 4])       AS joined,      -- [1, 2, 3, 4]
    list_slice([1,2,3,4,5], 2, 4)     AS sliced,      -- [2, 3, 4]
    list_avg([10, 20, 30, 40])        AS average,      -- 25.0
    list_generate(1, 10, 2)           AS odds;         -- [1, 3, 5, 7, 9]

-- 假设有一个 API 返回的 JSON 数组
WITH api_data AS (
    SELECT * FROM json_each('[
        {"id": 1, "name": "Alice", "scores": [90, 85, 92]},
        {"id": 2, "name": "Bob", "scores": [78, 88, 95]}
    ]')
)
SELECT 
    value::JSON ->> 'name' AS name,
    list_avg((value::JSON -> 'scores')::LIST) AS avg_score
FROM api_data;
-- Alice | 89.0
-- Bob   | 87.0