-- 單行命令查詢文件：列投影+謂詞下推
SELECT user_id, SUM(amount) AS total_spend
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date BETWEENDATE'2025-01-01'ANDDATE'2025-03-31'
AND country = 'CN'
GROUPBY user_id
ORDERBY total_spend DESC
LIMIT20;

import duckdb
import pandas as pd

q = """
SELECT user_id, SUM(amount) AS total_spend
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date >= DATE '2025-01-01' AND tx_date < DATE '2025-04-01'
GROUP BY user_id ORDER BY total_spend DESC LIMIT 20
"""
df = duckdb.query(q).to_df()  # 精簡(jiǎn)、整潔、可直接繪圖的數(shù)據(jù)

-- 目錄結(jié)構(gòu): data/country=CN/yyyymm=202501/part-*.parquet
SELECT country, yyyymm, COUNT(*) AS n, SUM(amount) AS total_amount
FROM read_parquet('data/country=*/yyyymm=*/part-*.parquet', hive_partitioning=1)
WHERE yyyymm BETWEEN '202501' AND '202503'
GROUP BY country, yyyymm
ORDER BY yyyymm, country;

-- 根據(jù)updated_at字段保留每個(gè)user_id的最新檔案
WITHprofilesAS (
SELECT *
FROM read_parquet('data/user_profiles/*.parquet')
)
SELECT *
FROMprofiles
QUALIFY ROW_NUMBER() OVER (
PARTITIONBY user_id ORDERBY updated_at DESC
) = 1;

-- 7日滾動(dòng)營(yíng)收和周同比變化
WITH s AS (
SELECT tx_date::DATEAS d, SUM(amount) AS daily_rev
FROM read_parquet('data/transactions/*.parquet')
GROUPBY1
)
SELECT
  d,
  daily_rev,
SUM(daily_rev) OVER (
    ORDERBY d
    RANGEBETWEENINTERVAL6DAYPRECEDINGANDCURRENTROW
  ) AS rev_7d,
  (daily_rev - LAG(daily_rev, 7) OVER (ORDERBY d)) AS week_delta
FROM s
ORDERBY d;

-- 將長(zhǎng)格式轉(zhuǎn)換為寬格式（分類作為列）
WITH daily AS (
SELECT
    DATE_TRUNC('day', ts) AS d,
    category,
    COUNT(*) ASevents
FROM read_parquet('data/events/*.parquet')
GROUPBY1,2
)
PIVOT daily
ONcategory
USINGSUM(events)
GROUPBY d
ORDERBY d;

-- 反向操作：寬格式轉(zhuǎn)長(zhǎng)格式，便于整潔繪圖
UNPIVOT read_parquet('data/agg/daily_by_category.parquet')
ON COLUMNS(* EXCLUDE d)
INTO NAME category VALUE events;

-- 展開訂單行中的JSON商品數(shù)組
WITH orders AS (
SELECT *
FROM read_json_auto('data/orders_2025.json')  -- 每行包含items[]
),
items AS (
SELECT
    o.order_id,
    i->>'sku'      AS sku,
    CAST(i->>'qty'ASINTEGER) AS qty,
    CAST(i->>'price'ASDOUBLE) AS price
FROM orders o, UNNEST(o.items) AS t(i)
)
SELECT sku, SUM(qty) AS units, SUM(qty*price) AS revenue
FROM items
GROUPBY sku
ORDERBY revenue DESC;

-- 在需要時(shí)重新構(gòu)建整潔的JSON
SELECT order_id,
       to_json( struct_pack(
         total_items := SUM(qty),
         total_price := SUM(qty*price)
       )) AS order_summary_json
FROM items
GROUPBY order_id;

-- 為團(tuán)隊(duì)成員和未來(lái)的你保存一個(gè)"黃金"數(shù)據(jù)切片
COPY (
SELECT user_id,
         SUM(amount) AS total_spend,
         COUNT(*)    AS tx_count
FROM read_parquet('data/transactions/*.parquet')
WHERE tx_date >= DATE'2025-01-01'
GROUPBY user_id
) TO'out/spend_2025_q1.parquet'
  (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 128000);

import duckdb
import pandas as pd

users = pd.read_csv("data/users.csv")  # 小型維度表

q = """
SELECT u.user_id, u.segment, t.total_spend
FROM users AS u
JOIN (
  SELECT user_id, SUM(amount) AS total_spend
  FROM read_parquet('data/transactions/*.parquet')
  GROUP BY 1
) AS t
USING (user_id)
ORDER BY total_spend DESC
LIMIT 50
"""
df = duckdb.query(q).to_df()  # 數(shù)據(jù)分析師的理想工作流