Introduction

Have you ever faced these pain points?

• You receive data where one column contains comma-separated tags like phone,case,cable, and you need to split them into separate rows for analysis?
• You want to pivot monthly data from rows to columns for横向 comparison, but end up writing 20 CASE WHEN statements?
• You need to calculate cosine similarity between user interest vectors, forcing you to export to Python and use NumPy?

Before DuckDB v1.5, these scenarios typically required Python workarounds or complex SQL tricks. Now, DuckDB provides a complete LIST function family and native PIVOT/UNPIVOT syntax that handles all of this in a single SQL query.

This guide will systematically cover these features—from basic usage to production scenarios—helping you eliminate 80% of the boilerplate code.

1. The LIST Function Family: DuckDB v1.5’s New Weapons

1.1 Core LIST Functions Overview

DuckDB v1.5 introduced a batch of LIST functions specifically designed for nested array data:

1.2 list_reverse — Reverse Arrays

Simple but incredibly useful. For example, getting each user’s most recent 3 purchases:

CREATE TABLE purchases AS
SELECT * FROM VALUES
 (1, ['sneakers', 't-shirt', 'hat', 'socks']),
 (2, ['laptop', 'mouse', 'keyboard']),
 (3, ['coffee', 'cookies', 'bread', 'milk', 'eggs'])
AS t(user_id, recent_items);

-- Get the latest 3 items (reverse then slice)
SELECT 
 user_id,
 list_slice(list_reverse(recent_items), 1, 3) AS latest_3
FROM purchases;

Result:

user_id | latest_3
--------|------------------
1 | [socks, hat, t-shirt]
2 | [keyboard, mouse, laptop]
3 | [eggs, milk, bread]

1.3 list_cosine_similarity — Vector Similarity

This is the core function for recommendation systems. Imagine you have user interest vectors:

CREATE TABLE user_interests AS
SELECT * FROM VALUES
 ('Alice', [0.9, 0.1, 0.3]),
 ('Bob', [0.85, 0.15, 0.2]),
 ('Carol', [0.2, 0.8, 0.5]),
 ('Dave', [0.88, 0.12, 0.22])
AS t(name, interests);

-- Calculate similarity between all user pairs
SELECT 
 a.name AS user_a,
 b.name AS user_b,
 ROUND(list_cosine_similarity(a.interests, b.interests), 3) AS similarity
FROM user_interests a
CROSS JOIN user_interests b
WHERE a.name < b.name
ORDER BY similarity DESC;

Result:

user_a | user_b | similarity
-------|--------|-----------
Alice | Bob | 0.999 -- Extremely similar!
Alice | Dave | 0.998
Bob | Dave | 0.999
Alice | Carol | 0.632 -- Different interests
Bob | Carol | 0.608
Dave | Carol | 0.615

Practical application: Recommendation engine—recommend Carol’s items to Alice since their interest vectors have 0.632 similarity.

1.4 list_distance — Euclidean Distance

-- Calculate Euclidean distance between two vectors
SELECT list_distance([1, 2, 3], [4, 6, 8]);
-- Result: 7.071 (= √(9+16+25))

-- Find the closest pair of users
SELECT 
 a.name, b.name,
 list_distance(a.interests, b.interests) AS distance
FROM user_interests a
CROSS JOIN user_interests b
WHERE a.name < b.name
ORDER BY distance ASC
LIMIT 3;

1.5 list_sort and list_unique

CREATE TABLE shopping_cart AS
SELECT * FROM VALUES
 (1, ['apple', 'banana', 'apple', 'orange', 'banana', 'apple']),
 (2, ['milk', 'bread', 'milk', 'eggs']),
 (3, ['chips', 'soda', 'chips', 'burger'])
AS t(user_id, items);

-- Deduplicate and sort
SELECT 
 user_id,
 list_unique(items) AS unique_items,
 list_sort(list_unique(items)) AS sorted_unique
FROM shopping_cart;

Result:

user_id | unique_items | sorted_unique
--------|----------------------|---------------------
1 | [apple, banana, orange] | [apple, banana, orange]
2 | [milk, bread, eggs] | [bread, eggs, milk]
3 | [chips, soda, burger] | [burger, chips, soda]

2. PIVOT: Say Goodbye to Manual CASE WHEN

2.1 Basic PIVOT Syntax

The traditional approach uses CASE WHEN manually:

-- ❌ Traditional: verbose and hard to maintain
SELECT 
 quarter,
 SUM(CASE WHEN product = 'phone' THEN amount END) AS phone_sales,
 SUM(CASE WHEN product = 'laptop' THEN amount END) AS laptop_sales,
 SUM(CASE WHEN product = 'tablet' THEN amount END) AS tablet_sales
FROM sales
GROUP BY quarter;

DuckDB’s PIVOT syntax does it in one line:

-- ✅ PIVOT: concise and clear
PIVOT sales
ON product
USING SUM(amount);

2.2 Complete Example: Sales Data Pivot

-- Create sample data
CREATE TABLE quarterly_sales AS
SELECT * FROM VALUES
 ('Q1', 'phone', 1200),
 ('Q1', 'laptop', 800),
 ('Q1', 'tablet', 500),
 ('Q2', 'phone', 1500),
 ('Q2', 'laptop', 950),
 ('Q2', 'tablet', 600),
 ('Q3', 'phone', 1100),
 ('Q3', 'laptop', 1100),
 ('Q3', 'tablet', 450),
 ('Q4', 'phone', 1800),
 ('Q4', 'laptop', 1200),
 ('Q4', 'tablet', 700)
AS t(quarter, product, amount);

-- PIVOT: convert product rows to columns
PIVOT quarterly_sales
ON product
USING SUM(amount);

Result:

quarter | phone | laptop | tablet
--------|-------|--------|-------
Q1 | 1200 | 800 | 500
Q2 | 1500 | 950 | 600
Q3 | 1100 | 1100 | 450
Q4 | 1800 | 1200 | 700

2.3 Multiple Aggregation Functions

-- Calculate sum, average, and count simultaneously
PIVOT quarterly_sales
ON product
USING SUM(amount) AS total, AVG(amount) AS avg_amount, COUNT(*) AS cnt;

Result:

quarter | phone_total | phone_avg | laptop_total | laptop_avg | ...
--------|-------------|-----------|--------------|------------|----
Q1 | 1200 | 1200.0 | 800 | 800.0 | ...

2.4 UNPIVOT: Column-to-Row Conversion

The reverse operation is equally simple:

CREATE TABLE monthly_budget AS
SELECT * FROM VALUES
 (1, 5000, 4800, 5200, 4900),
 (2, 3000, 3200, 2800, 3100)
AS t(month_id, jan, feb, mar, apr);

-- UNPIVOT: convert month columns to rows
UNPIVOT monthly_budget INTO name value FOR month IN (jan, feb, mar, apr);

Result:

month_id | name | value
---------|-------|------
1 | jan | 5000
1 | feb | 4800
1 | mar | 5200
1 | apr | 4900
2 | jan | 3000
...

3. Production Scenarios: Combining LIST + PIVOT

Scenario 1: User Tag Analysis

Assume you have user tag data where each user has multiple tags:

CREATE TABLE user_tags AS
SELECT * FROM VALUES
 ('Alice', ['VIP', 'high-spender', 'active']),
 ('Bob', ['new-user', 'electronics']),
 ('Carol', ['VIP', 'frequent-returner', 'high-spender']),
 ('Dave', ['active', 'electronics', 'high-spender'])
AS t(name, tags);

-- Count users per tag
SELECT 
 tag,
 COUNT(*) AS user_count
FROM user_tags, UNNEST(tags) AS tag
GROUP BY tag
ORDER BY user_count DESC;

Result:

tag | user_count
------------------|-----------
high-spender | 3
active | 2
electronics | 2
VIP | 2
new-user | 1
frequent-returner | 1

Scenario 2: Sales Trend Pivot + Anomaly Detection

-- 1. First pivot the data
CREATE TABLE monthly_sales_pivot AS
PIVOT (
 SELECT * FROM VALUES
 ('A', 'Jan', 100), ('A', 'Feb', 150), ('A', 'Mar', 130),
 ('B', 'Jan', 200), ('B', 'Feb', 180), ('B', 'Mar', 220),
 ('C', 'Jan', 80), ('C', 'Feb', 90), ('C', 'Mar', 85)
 AS t(region, month, sales)
) ON month USING SUM(sales);

-- 2. Calculate month-over-month change using LIST functions
SELECT 
 region,
 list_reverse([jan, feb, mar]) AS monthly_sales,
 ROUND(
 (feb - jan) * 100.0 / NULLIF(jan, 0), 1
 ) AS jan_to_feb_change_pct,
 ROUND(
 (mar - feb) * 100.0 / NULLIF(feb, 0), 1
 ) AS feb_to_mar_change_pct
FROM monthly_sales_pivot;

Scenario 3: Similarity-based Recommendations

-- User interest matrix
CREATE TABLE user_items AS
SELECT * FROM VALUES
 ('Alice', ['phone', 'earbuds', 'powerbank']),
 ('Bob', ['phone', 'keyboard', 'mouse']),
 ('Carol', ['tablet', 'case', 'stylus']),
 ('Dave', ['phone', 'case', 'screen-protector'])
AS t(user, items);

-- Calculate user similarity based on item co-occurrence
WITH item_pairs AS (
 SELECT 
 a.user AS user_a,
 b.user AS user_b,
 list_inner_product(
 list_sort(a.items),
 list_sort(b.items)
 ) AS similarity_score
 FROM user_items a
 CROSS JOIN user_items b
 WHERE a.user < b.user
)
SELECT * FROM item_pairs
ORDER BY similarity_score DESC;

4. Comparison with Traditional Tools

Key advantage: DuckDB processes this data in-memory on a single machine, typically 2-5x faster than Pandas, with 60%+ less code.

5. Complete Comparison with Pandas

Assume you have sales data and need to: 1) pivot by product 2) calculate monthly change rates 3) identify anomalous months.

Pandas Approach (15 lines):

import pandas as pd
import numpy as np

# Pivot
pivot = df.pivot_table(index='region', columns='month', values='sales', aggfunc='sum')
# Calculate change rate
pivot['change'] = pivot.pct_change(axis=1) * 100
# Anomaly detection
anomalies = pivot[np.abs(pivot['change']) > 30]

DuckDB Approach (3 lines of SQL):

-- Pivot + change rate + anomaly detection in one go
WITH pivoted AS (
 PIVOT sales_data ON month USING SUM(sales)
),
with_change AS (
 SELECT *, 
 ROUND((feb - jan) * 100.0 / NULLIF(jan, 0), 1) AS mo_change
 FROM pivoted
)
SELECT * FROM with_change
WHERE ABS(mo_change) > 30;

6. Performance Considerations

1. CROSS JOIN Scalability: When calculating similarity between all user pairs, CROSS JOIN produces O(n²) results. When users exceed 10,000, consider clustering first to reduce comparison pairs.

2. LIST Functions in WHERE: Using WHERE list_cosine_similarity(a, b) > 0.8 computes similarity for every row. For large datasets, filter first with other conditions, then calculate similarity.

3. PIVOT Column Limits: DuckDB has no hard limit on PIVOT columns, but readability degrades beyond 100 columns. Consider splitting into multiple PIVOTs.

4. UNNEST Performance: Expanding large arrays (1000+ elements per row) significantly increases downstream computation. Deduplicate with list_unique first.

Summary

DuckDB v1.5’s LIST functions and PIVOT/UNPIVOT syntax transform data processing from “write loops” to “write SQL.” Key takeaways:

• LIST functions: The go-to solution for nested array data, with built-in similarity calculations
• PIVOT: One-line row-to-column transformation, replacing verbose CASE WHEN
• UNPIVOT: Symmetric column-to-row operation
• Combined usage: PIVOT for perspective + LIST for vector operations—complex analysis without leaving SQL

Next time you face nested data or pivoting requirements, try these functions in DuckDB first—you might discover that 90% of cases can be solved with a single SQL query.

📖 Want to systematically learn more DuckDB实战技巧? Visit duckdblab.org for the complete tutorial series.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。