CREATE TABLE purchases AS
SELECT * FROM VALUES
    (1, ['运动鞋', 'T恤', '帽子', '袜子']),
    (2, ['笔记本电脑', '鼠标', '键盘']),
    (3, ['咖啡', '饼干', '面包', '牛奶', '鸡蛋'])
AS t(user_id, recent_items);

-- 获取最近3件商品（反转后取前3个）
SELECT 
    user_id,
    list_slice(list_reverse(recent_items), 1, 3) AS latest_3
FROM purchases;

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);

-- 计算所有用户对之间的相似度
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;

-- 计算两个向量的欧氏距离
SELECT list_distance([1, 2, 3], [4, 6, 8]);
-- 结果: 7.071（= √(9+16+25)）

-- 找出距离最近的两个用户
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;

CREATE TABLE shopping cart AS
SELECT * FROM VALUES
    (1, ['苹果', '香蕉', '苹果', '橙子', '香蕉', '苹果']),
    (2, ['牛奶', '面包', '牛奶', '鸡蛋']),
    (3, ['薯片', '可乐', '薯片', '汉堡'])
AS t(user_id, items);

-- 去重 + 排序
SELECT 
    user_id,
    list_unique(items) AS unique_items,
    list_sort(list_unique(items)) AS sorted_unique
FROM shopping_cart;

-- ❌ 传统写法：繁琐且难以维护
SELECT 
    quarter,
    SUM(CASE WHEN product = '手机' THEN amount END) AS phone_sales,
    SUM(CASE WHEN product = '电脑' THEN amount END) AS computer_sales,
    SUM(CASE WHEN product = '平板' THEN amount END) AS tablet_sales
FROM sales
GROUP BY quarter;

-- ✅ PIVOT 写法：简洁清晰
PIVOT sales
ON product
USING SUM(amount);

-- 创建示例数据
CREATE TABLE quarterly_sales AS
SELECT * FROM VALUES
    ('Q1', '手机', 1200),
    ('Q1', '电脑', 800),
    ('Q1', '平板', 500),
    ('Q2', '手机', 1500),
    ('Q2', '电脑', 950),
    ('Q2', '平板', 600),
    ('Q3', '手机', 1100),
    ('Q3', '电脑', 1100),
    ('Q3', '平板', 450),
    ('Q4', '手机', 1800),
    ('Q4', '电脑', 1200),
    ('Q4', '平板', 700)
AS t(quarter, product, amount);

-- PIVOT：把 product 行转成列
PIVOT quarterly_sales
ON product
USING SUM(amount);

-- 同时计算总和、平均值、数量
PIVOT quarterly_sales
ON product
USING SUM(amount) AS total, AVG(amount) AS avg_amount, COUNT(*) AS cnt;

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：把月份列转成行
UNPIVOT monthly_budget INTO name value FOR month IN (jan, feb, mar, apr);

CREATE TABLE user_tags AS
SELECT * FROM VALUES
    ('Alice', ['VIP', '高消费', '活跃用户']),
    ('Bob',   ['新用户', '电子产品']),
    ('Carol', ['VIP', '退换货频繁', '高消费']),
    ('Dave',  ['活跃用户', '电子产品', '高消费'])
AS t(name, tags);

-- 统计每个标签覆盖的用户数
SELECT 
    tag,
    COUNT(*) AS user_count
FROM user_tags, UNNEST(tags) AS tag
GROUP BY tag
ORDER BY user_count DESC;

-- 1. 先用 PIVOT 做趋势分析
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. 用 LIST 函数计算月度变化率
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;

-- 用户兴趣矩阵
CREATE TABLE user_items AS
SELECT * FROM VALUES
    ('Alice', ['手机', '耳机', '充电宝']),
    ('Bob',   ['手机', '键盘', '鼠标']),
    ('Carol', ['平板', '保护套', '触控笔']),
    ('Dave',  ['手机', '手机壳', '屏幕膜'])
AS t(user, items);

-- 基于共现商品计算用户相似度
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;

import pandas as pd
import numpy as np

# 行转列
pivot = df.pivot_table(index='region', columns='month', values='sales', aggfunc='sum')
# 计算变化率
pivot['change'] = pivot.pct_change(axis=1) * 100
# 异常检测
anomalies = pivot[np.abs(pivot['change']) > 30]

-- 透视 + 变化率 + 异常检测一气呵成
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;