DuckDB Advanced SQL Techniques: PIVOT, Macro, Window Functions, Recursive CTE & JSON Processing
Many data analysts use DuckDB for a year and still only know SELECT * FROM table. Today, let’s cover 6 advanced techniques with runnable code. Bookmark this for quick reference.
1. PIVOT / UNPIVOT: Say Goodbye to Manual CASE WHEN
Want to transform quarterly sales data from “long format” to “wide format”? PIVOT does it in one line, far more elegant than a bunch of CASE WHEN statements.
PIVOT: Row-to-Column
-- Raw data: one row per quarter
SELECT * FROM sales;
-- product | quarter | amount
-- Apple | Q1 | 100
-- Apple | Q2 | 150
-- Banana | Q1 | 200
-- Banana | Q2 | 120
-- PIVOT: Turn quarters into columns
SELECT * FROM sales
PIVOT(sum(amount) FOR quarter IN ("Q1", "Q2"));
-- product | Q1 | Q2
-- Apple | 100 | 150
-- Banana | 200 | 120
UNPIVOT: Column-to-Row
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
Real-World: E-commerce Monthly Report
Your boss wants a “sales comparison table by category and month” every month. PIVOT does it in three lines:
SELECT
category,
"January" AS jan, "February" AS feb, "March" AS mar
FROM monthly_sales
PIVOT(
SUM(revenue)
FOR month_name
IN ("January", "February", "March")
);
UNPIVOT is equally useful in reverse——when your data is stored in “wide format” (like EAV model), UNPIVOT can restore it to standard long format, saving you from tedious UNION ALL operations.
2. SQL Macro: Encapsulate Common Logic
DuckDB’s Macros aren’t just scalar functions——they can reuse entire expressions, making logic centralized and SQL highly readable.
Basic Macro
-- Define a macro to calculate squared difference
CREATE MACRO square(x) AS x * x;
SELECT square(5) AS result;
-- 25
SELECT square(col_a - col_b) AS diff_square
FROM transactions;
Practical Macro: Month-over-Month Growth
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
Parameterized Macro: With Default Values
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
The core value of Macros: change once, apply everywhere. When you define a judgment logic for “active user”, all your SQL can directly call is_active(user) instead of rewriting the condition every time.
3. LAG / FIRST_VALUE: Window Functions in Practice
Window functions are the dividing line between SQL beginners and intermediates. Master them, and 80% of data analysis needs can be solved.
LAG: Get the Previous Row
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
FIRST_VALUE / LAST_VALUE
Find each user’s highest and lowest scores:
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;
Advanced: Cumulative Percentage
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;
-- This is the famous Pareto analysis (80/20 rule)
4. CTE Chains: Break Complex Logic into Steps
Multi-layer nested subqueries look like hieroglyphs? Break them into steps with CTE, validate each one independently.
Basic CTE Chain
WITH base AS (
-- Step 1: Filter valid orders
SELECT id, amount, customer_id
FROM orders
WHERE status = 'completed'
),
ranked AS (
-- Step 2: Calculate customer ranking
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 (
-- Step 3: Get Top 10
SELECT * FROM ranked WHERE rn <= 10
)
SELECT * FROM top_customers;
CTE + Recursive: Organizational Hierarchy
WITH RECURSIVE org_tree AS (
-- Anchor: top-level manager
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: subordinate employees
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;
-- Result example:
-- CEO
-- Engineering
-- Alice
-- Bob
-- Marketing
-- Charlie
Common use cases for recursive CTE:
- Organizational hierarchy
- Product category trees
- Social network friend chains
- Shortest path calculations (with Dijkstra’s algorithm)
5. JSON Processing + LIST Operations: The Power for Unstructured Data
DuckDB can process JSON without converting to tables first——operate directly with SQL. This is one reason it’s more flexible than PostgreSQL in analytics scenarios.
JSON Extraction
-- Extract JSON fields
SELECT json_extract_string(
'{"user": "alice", "age": 30, "tags": ["dev", "gopher"]}',
'$.user'
) AS username;
-- alice
Expand JSON Arrays
SELECT * FROM json_each('{
"items": ["Apple", "Banana", "Cherry"]
}'::JSON -> 'items') AS j;
-- [{"value":"Apple"},{"value":"Banana"},{"value":"Cherry"}]
LIST Common Operations
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]
Real-World: Processing Nested API Responses
-- Suppose an API returns a JSON array
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
DuckDB’s JSON processing advantages:
- No need to define schema upfront
- Seamless integration with SQL
- Can directly read/write JSON columns in Parquet
6. Six Techniques Comparison: Traditional vs DuckDB
| Need | Traditional Way | DuckDB |
|---|---|---|
| Row-to-column | Manual CASE WHEN | PIVOT(... FOR col IN (...)) |
| Logic reuse | Copy-paste / stored procedures | CREATE MACRO |
| Get previous row | Self-join | LAG() OVER(...) |
| Hierarchical data | Recursive stored procedures | WITH RECURSIVE |
| JSON | Must parse then JOIN | Direct json_extract_string |
| Array ops | Manual loops | list_sort, list_concat, etc. |
7. Monetization Advice
After mastering these advanced techniques, you can:
- Take data analysis freelance projects: Starting at ¥2,000/engagement,熟练后 ¥5,000+/单
- Build data products: Use PIVOT + CTE to quickly generate reports, completing in 1 hour what takes others 3 days
- SaaS-ify: Encapsulate common Macros as services, charge per API call
- Teach: Package these techniques into courses, priced at ¥99-¥299
🔍 The complete code (with 15 production-grade query templates) is published at duckdblab.org. Bookmark for quick reference. 学习更多 DuckDB 实战经验 → duckdblab.org