PIVOT table_name
ON pivot_column
USING aggregate_function(value_column)
GROUP BY group_columns;

PIVOT sales
ON channel
USING SUM(amount)
GROUP BY month;

import duckdb

con = duckdb.connect("ecommerce.db")

# 创建测试数据
con.execute("""
CREATE TABLE sales (
    month VARCHAR,
    channel VARCHAR,
    amount DECIMAL(12,2)
)
""")

con.execute("""
INSERT INTO sales VALUES
('2026-01', '天猫', 150000), ('2026-01', '京东', 98000), ('2026-01', '抖音', 220000),
('2026-02', '天猫', 180000), ('2026-02', '京东', 110000), ('2026-02', '抖音', 195000),
('2026-03', '天猫', 205000), ('2026-03', '京东', 125000), ('2026-03', '抖音', 240000)
""")

# 长表转宽表
result = con.execute("""
PIVOT sales
ON channel
USING SUM(amount)
GROUP BY month
""").fetchdf()

print(result)

PIVOT sales
ON channel
USING SUM(amount) AS total, COUNT(*) AS orders, AVG(amount) AS avg_order
GROUP BY month;

PIVOT sales
ON channel
USING SUM(amount)
GROUP BY month
WHERE amount > 100000;

SELECT 
    month,
    COALESCE(tianmao, 0) AS tianmao,
    COALESCE(jingdong, 0) AS jingdong,
    COALESCE(douyin, 0) AS douyin
FROM (
    PIVOT sales ON channel USING SUM(amount) GROUP BY month
);

UNPIVOT table_name
ON channel_columns
INTO NAME channel_column VALUE amount_column;

UNPIVOT wide_sales
ON 天猫, 京东, 抖音
INTO NAME channel VALUE amount;

import duckdb

con = duckdb.connect("ecommerce.db")

# 创建宽表
con.execute("""
CREATE TABLE wide_sales (
    month VARCHAR,
    tianmao DECIMAL(12,2),
    jingdong DECIMAL(12,2),
    douyin DECIMAL(12,2)
)
""")

con.execute("""
INSERT INTO wide_sales VALUES
('2026-01', 150000, 98000, 220000),
('2026-02', 180000, 110000, 195000),
('2026-03', 205000, 125000, 240000)
""")

# 宽表转长表
result = con.execute("""
UNPIVOT wide_sales
ON tianmao, jingdong, douyin
INTO NAME channel VALUE amount
""").fetchdf()

print(result)

import duckdb
from datetime import datetime

con = duckdb.connect("shop.db")

# 创建每日销售明细表
con.execute("""
CREATE TABLE daily_sales (
    date DATE,
    channel VARCHAR,
    amount DECIMAL(12,2),
    orders INTEGER
)
""")

# 插入测试数据（模拟 4 周数据）
for week in range(4):
    for day in range(7):
        date = datetime(2026, 6, 1) + __import__('datetime').timedelta(weeks=week, days=day)
        for channel, base_amount in [('天猫', 50000), ('京东', 30000), ('抖音', 40000)]:
            amount = base_amount + (week * 5000) + (day * 1000)
            orders = int(amount / 100)
            con.execute("""
                INSERT INTO daily_sales VALUES (?, ?, ?, ?)
            """, (str(date), channel, amount, orders))

# 生成周报：PIVOT + 环比增长率
weekly_report = con.execute("""
WITH weekly_data AS (
    SELECT 
        strftime('%Y-W%W', date) AS week,
        channel,
        SUM(amount) AS total_amount,
        SUM(orders) AS total_orders
    FROM daily_sales
    GROUP BY week, channel
),
pivoted AS (
    PIVOT weekly_data
    ON channel
    USING SUM(total_amount) AS amount, SUM(total_orders) AS orders
    GROUP BY week
),
with_growth AS (
    SELECT 
        week,
        tianmao AS tianmao_amount,
        jingdong AS jingdong_amount,
        douyin AS douyin_amount,
        LAG(tianmao) OVER (ORDER BY week) AS prev_tianmao,
        LAG(jingdong) OVER (ORDER BY week) AS prev_jingdong,
        LAG(douyin) OVER (ORDER BY week) AS prev_douyin
    FROM pivoted
)
SELECT 
    week,
    tianmao_amount,
    jingdong_amount,
    douyin_amount,
    ROUND((tianmao_amount - prev_tianmao) / NULLIF(prev_tianmao, 0) * 100, 2) AS tianmao_growth,
    ROUND((jingdong_amount - prev_jingdong) / NULLIF(prev_jingdong, 0) * 100, 2) AS jingdong_growth,
    ROUND((douyin_amount - prev_douyin) / NULLIF(prev_douyin, 0) * 100, 2) AS douyin_growth
FROM with_growth
ORDER BY week DESC
LIMIT 4
""").fetchdf()

print("📊 近 4 周各渠道销售趋势")
print(weekly_report.to_string())

# 1. 先过滤再 PIVOT，减少处理数据量
PIVOT (SELECT * FROM sales WHERE date >= '2026-01-01')
ON channel
USING SUM(amount)
GROUP BY month

# 2. 使用物化表缓存 PIVOT 结果
CREATE TABLE IF NOT EXISTS mv_weekly_sales AS
PIVOT weekly_sales
ON channel
USING SUM(amount)
GROUP BY week;

# 3. 结合分区表，进一步提升性能
CREATE TABLE sales (
    month VARCHAR,
    channel VARCHAR,
    amount DECIMAL(12,2)
)
PARTITION BY (month);