ClickHouse 窗口函数高级实战：排名、聚合、偏移与进阶玩法

GROUP BY：
□ 把多行「折叠」成一行（聚合后行数变少）
□ 丢失「组内逐行」的视角——看不到每条明细
□ 例：SELECT user_id, count() FROM t GROUP BY user_id

窗口函数：
□ 不折叠行——每条明细仍在，额外算出「组内统计」
□ 在每一行上看到「所属分区的聚合/排名/偏移」
□ 例：SELECT user_id, sum(x) OVER (PARTITION BY user_id) FROM t

分工：
□ 要「一行一组的汇总」→ GROUP BY
□ 要「每条明细带上组内统计/排名」→ 窗口函数
□ 两者常配合：先窗口算出指标，再 GROUP BY 汇总

直观示例：
订单表按 user 分区、按 time 排序 →
每行新增列：
  row_number（用户第几单）
  running_total（累计金额）
  prev_amount（上一单金额）
→ 明细不丢，统计信息加进来

<函数> OVER (
  PARTITION BY 分区列     -- 分组（可选）
  ORDER BY 排序列          -- 组内排序（可选但常需）
  ROWS/RANGE 窗口框       -- 滑动范围（可选）
)

三件套含义：
□ PARTITION BY：分区——函数在「每个分区内」独立计算
  （不写 = 全表一个分区）
□ ORDER BY：组内排序——排名/累计/偏移都依赖它
  （不写 = 无固定顺序，聚合型可用）
□ 窗口框（frame）：「当前行」往前后看多少行
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 前 2 行到当前
  RANGE ...（按值范围）
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 累计到当前

窗口框的粒度：
□ ROWS：按「物理行数」定界（前 N 行）
□ RANGE：按「排序键值差」定界（值范围内所有行）

-- 累计金额（从分区头到当前行）
sum(amount) OVER (
  PARTITION BY user_id
  ORDER BY event_time
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
-- 前 2 行的移动平均
avg(amount) OVER (
  PARTITION BY user_id
  ORDER BY event_time
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

排名函数对比（值: 10, 20, 20, 30）：
□ ROW_NUMBER：物理序号，并列也分先后 → 1, 2, 3, 4
□ RANK：并列同号，下一个跳过 → 1, 2, 2, 4
□ DENSE_RANK：并列同号，下一个不跳 → 1, 2, 2, 3

选择：
□ 只要唯一序号（分页/去重）→ ROW_NUMBER
□ 竞赛排名（并列占位）→ RANK
□ 要「连续的名次数字」→ DENSE_RANK

典型场景：
□ 取每组 TopN（配合 ROW_NUMBER 过滤）
□ 去重（ROW_NUMBER = 1 保留每组最新）
□ 排行榜（RANK/DENSE_RANK）

-- 每组最新一条（ROW_NUMBER 去重）
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY event_time DESC
  ) AS rn
  FROM events
) WHERE rn = 1

-- 每日金额排名（DENSE_RANK）
SELECT day, user_id, amount,
  DENSE_RANK() OVER (PARTITION BY day ORDER BY amount DESC) AS dr
FROM daily_stats

滑动聚合的三种形态：
□ 累计（Running）：从分区头到当前行累加
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
□ 滑动（Sliding）：固定窗口随行滚动（前 N 行到当前行）
  ROWS BETWEEN N PRECEDING AND CURRENT ROW
□ 分区统计：整个分区一个值（每行相同）
  （不写窗口框时 ClickHouse 默认整分区）

典型场景：
□ 累计销售额 / 余额流水（累计）
□ 移动平均（MA5/MA10 股票、销量平滑）（滑动）
□ 同比环比差值（配合 LAG，见第 5 节）
□ 每行占比（amount / sum(amount) OVER (PARTITION BY ...)）

-- 移动平均 MA3（近 3 点平滑）
avg(price) OVER (
  PARTITION BY symbol
  ORDER BY ts
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma3

-- 每行占分区百分比
amount / sum(amount) OVER (PARTITION BY day) * 100 AS pct

偏移函数：
□ LAG(col, n)：取「往前 n 行」的值（n 默认 1）
□ LEAD(col, n)：取「往后 n 行」的值
□ 配合 ORDER BY：按排序后的行序偏移
□ 可选 default：越界时返回（默认 NULL）

典型场景：
□ 环比：当前值 - 上一行值（LAG 差值）
□ 同比：今年 - 去年同窗口（跨分区偏移）
□ 会话时长：当前事件时间 - 上一事件时间
□ 涨跌标记：price - LAG(price) 正负

-- 每行对比上一行
event_time - LAG(event_time) OVER (
  PARTITION BY user_id ORDER BY event_time
) AS gap_seconds

-- 日环比增量
amount - LAG(amount) OVER (
  PARTITION BY store_id ORDER BY day
) AS day_over_day

-- 越界给默认值
LAG(price, 2, 0) OVER (ORDER BY day) AS price_2_ago

分布函数：
□ PERCENT_RANK：当前行排名百分比
  (RANK-1) / (行数-1)，范围 [0,1]
□ CUME_DIST：累计分布（当前行<=值的比例）
  值小于等于当前的行数 / 总行数
□ 分位数近似：ClickHouse 用 quantile* 聚合做分位
  （窗口内分位：quantile(0.95)(x) OVER (...)）

典型场景：
□ 用户分层：按金额排百分位 → 高/中/低频用户
□ 异常检测：值落在分位分布的两端 → 可疑
□ 性能监控：P95/P99 延迟（窗口内 quantile）

配合使用：
□ PERCENT_RANK → 「这行排前百分之几」
□ CUME_DIST → 「多少比例的行 <= 这行」
□ 两者常与排名函数搭配看「分布形状」

-- 用户金额百分位分层
SELECT user_id, amount,
  PERCENT_RANK() OVER (ORDER BY amount DESC) AS pct_rank,
  CASE WHEN PERCENT_RANK() OVER (ORDER BY amount DESC) < 0.2
       THEN 'high' ELSE 'low' END AS tier
FROM user_stats

-- 窗口内 P95 延迟
quantile(0.95)(latency) OVER (
  PARTITION BY service ORDER BY ts
  ROWS BETWEEN 100 PRECEDING AND CURRENT ROW
) AS p95

ClickHouse 默认窗口框：
□ 不写窗口框时 = 整个分区（UNBOUNDED PRECEDING 到 UNBOUNDED FOLLOWING）
  → 和 PostgreSQL/MySQL 默认（到当前行）不同！
□ 影响：不写框的 sum(x) OVER (PARTITION BY ...) = 分区总合计
  → 想要「累计到当前行」必须显式写 ROWS 框

性能注意：
□ 窗口函数需要「分区内排序」→ 消耗内存与排序
□ 大数据集：PARTITION BY 列应尽量「低基数」
  （高基数分区 → 大量小分区 → 排序碎片化）
□ 内存：极端情况下用 external 排序（disk）保稳定
□ 尽量在「已按主键排序」的列上 ORDER BY → 少一次排序

版本支持：
□ ClickHouse 窗口函数从 21.x 起功能完善
□ 早期版本缺某些框语法 → 确认版本能力
□ 与物化视图/预聚合结合：能下推则少算

-- 正确区分两种"合计"
sum(amount) OVER (PARTITION BY user_id)              -- 分区总合计
sum(amount) OVER (PARTITION BY user_id
  ORDER BY event_time
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- 累计到当前

进阶玩法：
□ 去重取最新：ROW_NUMBER + 子查询过滤 rn=1
  → 每组保留最新/最新状态（比 GROUP BY max 更灵活）
□ TopN 每组：ROW_NUMBER 排序 + 过滤 n 内
  → 每组 Top 3 商品 / 每部门 Top 员工
□ 行转列（透视）：先窗口算每组的列值，再交叉表
  → 配合 CASE + GROUP BY 或数据透视
□ 保留明细同时聚合：窗口聚合 + 明细字段并存的报表
□ 复杂比例：分区内占比、与平均的差、距极值的偏移

组合技巧：
□ 窗口函数结果做「二次过滤」→ 必须子查询包一层
□ 多个窗口函数共享 OVER → 写一次复用
□ 与数组函数/字典结合 → 更复杂的分组逻辑

-- 每商品 Top3 的日期（TopN）
SELECT * FROM (
  SELECT product, day, sales,
    ROW_NUMBER() OVER (PARTITION BY product ORDER BY sales DESC) AS rn
  FROM daily_sales
) WHERE rn <= 3

-- 行转列：每天各渠道占比透视（示意）
SELECT day,
  max(CASE WHEN channel='web' THEN amount ELSE 0 END) AS web,
  max(CASE WHEN channel='app' THEN amount ELSE 0 END) AS app
FROM (
  SELECT day, channel, sum(amount) OVER (PARTITION BY day, channel) AS amount
  FROM events
) GROUP BY day

方案对比：
□ 窗口函数：查询时动态算，灵活，但每次查询重算排序
□ 物化视图：预聚合存结果，查询快，但改查询要重建
  → 固定口径的高频报表 → 物化视图
  → 灵活/临时分析 → 窗口函数
□ GROUP BY + 子查询：先聚合再 join 回明细
  → 内存省，但 SQL 复杂、多表 join
□ 标量子查询：简单单值，性能差（逐行执行）
  → 别在大表用

选型决策：
□ 一次性/临时分析 → 窗口函数（灵活）
□ 固定报表高频查 → 物化视图（预计算）
□ 只要聚合不要明细 → GROUP BY（最省）
□ 明细 + 聚合并存 → 窗口函数（或聚合后 join）

性能考量：
□ 窗口函数 = 每次查询「分区 + 排序」
□ 数据大、查询频繁 → 预聚合（物化视图）更划算
□ 两者结合：物化视图存「分区预聚合」，窗口函数做「组内精细」

决策流：
需要明细行？
  是 → 高频固定？→ 物化视图 + 窗口
             → 临时分析？→ 窗口函数
  否 → GROUP BY 聚合（最省）