DuckDB 直接查询 Web API：HTTP 扩展 + JSON 函数实战

-- 安装并加载 httpfs 扩展
INSTALL httpfs;
LOAD httpfs;

-- 解析多层嵌套的电商 API 响应
WITH api_response AS (
    SELECT read_json_auto('https://jsonplaceholder.typicode.com/posts', auto_detect=true) AS raw_data
),
parsed_json AS (
    SELECT 
        json_extract(raw_data, '$.orders') AS orders_json
    FROM api_response
),
order_items AS (
    SELECT 
        order_val->>'id' AS order_id,
        order_val->>'customer.name' AS customer_name,
        order_val->>'customer.email' AS customer_email,
        order_val->>'status' AS status,
        order_val->>'total' AS total_amount
    FROM parsed_json,
    read_json_auto('https://jsonplaceholder.typicode.com/posts', auto_detect=true) AS t(order_val)
)
SELECT 
    status,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount::DOUBLE), 2) AS total_revenue,
    ROUND(AVG(total_amount::DOUBLE), 2) AS avg_order_value
FROM order_items
GROUP BY status
ORDER BY total_revenue DESC;

-- 直接从 URL 读取 Parquet 文件
SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet');

-- 读取多个文件（glob 模式）
SELECT COUNT(*) FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet');

-- 读取远程 CSV（自动检测分隔符）
SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/datasets.csv');

-- 读取远程 JSON 文件
SELECT * FROM read_json_auto('https://jsonplaceholder.typicode.com/users');

-- 组合：从 API 获取 Parquet 并分析
SELECT 
    region,
    SUM(revenue) AS total_revenue,
    AVG(order_count) AS avg_orders
FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
WHERE date >= '2025-01-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Step 1: 从多个数据源聚合
WITH competitor_data AS (
    -- 来源1: 应用商店评论 API
    SELECT 
        item->>'product_name' AS product,
        item->>'rating' AS rating,
        item->>'review_date' AS review_date,
        item->>'source' AS source
    FROM read_json_auto('https://jsonplaceholder.typicode.com/comments', auto_detect=true) AS item
),
-- 来源2: 社交媒体提及
social_mentions AS (
    SELECT 
        m->>'mention_text' AS text,
        m->>'sentiment' AS sentiment,
        m->>'platform' AS platform,
        m->>'timestamp' AS mentioned_at
    FROM read_json_auto('https://api.social-tracker.com/v1/mentions?q=competitor', auto_detect=true) AS m
),
-- 合并分析
analysis AS (
    SELECT 
        product,
        AVG(rating::DOUBLE) AS avg_rating,
        COUNT(*) AS review_count,
        MIN(review_date) AS first_review,
        MAX(review_date) AS last_review
    FROM competitor_data
    GROUP BY product
)
-- 最终输出：按评分排序的竞品列表
SELECT 
    a.product,
    a.avg_rating,
    a.review_count,
    a.first_review,
    a.last_review,
    CASE 
        WHEN a.avg_rating >= 4.5 THEN '🟢 强势'
        WHEN a.avg_rating >= 4.0 THEN '🟡 稳定'
        ELSE '🔴 预警'
    END AS status
FROM analysis a
ORDER BY a.avg_rating DESC;

-- 缓存 API 响应到临时表
CREATE TEMP TABLE cached_github_repos AS
SELECT * FROM read_json_auto('https://api.github.com/users/duckdb/repos', auto_detect=true) AS value;

-- 后续分析直接查询缓存
SELECT 
    value->>'name' AS repo_name,
    value->>'stargazers_count' AS stars,
    value->>'language' AS language
FROM cached_github_repos
WHERE value->>'stargazers_count'::BIGINT > 1000
ORDER BY stars DESC;

-- 并行读取多个 Parquet 文件（DuckDB 自动并行化）
SELECT 
    file_name,
    COUNT(*) AS row_count,
    SUM(size_bytes) AS total_size
FROM parquet_metadata('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
GROUP BY file_name
ORDER BY total_size DESC;

-- 直接在读取时过滤，减少数据传输
SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
WHERE date >= '2025-06-01' AND category = 'electronics';