DuckDB 聚合函数进阶：STRING_AGG、ARRAY_AGG、MAP_AGG 一行搞定复杂聚合

-- 基础用法：把每个客户的购买商品合并成逗号分隔字符串
SELECT 
    customer_id,
    STRING_AGG(product_name, ', ') AS purchased_products
FROM order_items
GROUP BY customer_id;

-- 按价格降序排列，先买的最贵商品排在前面
SELECT 
    customer_id,
    STRING_AGG(product_name, ', ' ORDER BY price DESC) AS top_purchases
FROM order_items
GROUP BY customer_id;

-- 合并成数组，方便后续处理
SELECT 
    customer_id,
    ARRAY_AGG(product_name) AS products,
    ARRAY_LENGTH(ARRAY_AGG(product_name)) AS purchase_count
FROM order_items
GROUP BY customer_id;

-- 获取每个客户的购买次数和商品列表
SELECT 
    customer_id,
    ARRAY_AGG(product_name) AS products,
    ARRAY_AGG(price) AS prices,
    ARRAY_LENGTH(ARRAY_AGG(product_name)) AS count,
    -- 计算总金额
    SUM(price) AS total_spent
FROM order_items
GROUP BY customer_id
HAVING ARRAY_LENGTH(ARRAY_AGG(product_name)) >= 3;  -- 只保留购买3次以上的客户

-- 把商品名称和价格映射成键值对
SELECT 
    customer_id,
    MAP_AGG(product_name, price) AS product_prices
FROM order_items
GROUP BY customer_id;

-- 查询 MAP 中某个商品的价格
SELECT 
    customer_id,
    product_prices['iPhone'] AS iphone_price,
    product_prices['MacBook'] AS macbook_price
FROM (
    SELECT 
        customer_id,
        MAP_AGG(product_name, price) AS product_prices
    FROM order_items
    GROUP BY customer_id
) t;

-- 生成完整的客户购买报告
SELECT 
    customer_id,
    -- 字符串：购买的商品列表
    STRING_AGG(product_name, ', ' ORDER BY order_date DESC) AS recent_purchases,
    -- 数组：所有购买记录
    ARRAY_AGG(product_name) AS all_products,
    -- 键值对：商品-价格映射
    MAP_AGG(product_name, price) AS price_map,
    -- 统计指标
    COUNT(*) AS total_orders,
    SUM(price) AS total_spent,
    AVG(price) AS avg_order_value,
    MAX(order_date) AS last_purchase_date
FROM order_items
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

SELECT 
    customer_id,
    CAST(STRING_AGG(product_name, ', ') AS VARCHAR(10000)) AS all_products
FROM order_items
GROUP BY customer_id;

SELECT 
    customer_id,
    STRING_AGG(COALESCE(product_name, '未知商品'), ', ') AS products
FROM order_items
GROUP BY customer_id;

SELECT 
    customer_id,
    STRING_AGG(product_name, ', ' ORDER BY price DESC, product_name) AS products
FROM order_items
GROUP BY customer_id;

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/customer/{customer_id}/profile")
def get_customer_profile(customer_id: int):
    con = duckdb.connect("orders.duckdb")
    result = con.execute("""
        SELECT 
            STRING_AGG(product_name, ', ') AS products,
            MAP_AGG(product_name, price) AS prices,
            SUM(price) AS total_spent
        FROM order_items
        WHERE customer_id = ?
        GROUP BY customer_id
    """, [customer_id]).fetchone()
    return {"customer_id": customer_id, "profile": result}