GBase 8a OLAP 窗口函数实战：排名、累计、环比、占比全解析

GBase 8a 对 OLAP 窗口函数有完整支持，是数据仓库分析场景的核心能力。本文用真实的业务场景驱动，逐一讲解 ROW_NUMBER/RANK、滑动聚合、LAG/LEAD 环比、ROLLUP 小计，以及它们在 MPP 环境下的执行原理。

---

一、窗口函数基础语法

窗口函数的通用结构：

函数名() OVER (
    [PARTITION BY 分组列]   -- 窗口分组（可选）
    [ORDER BY 排序列]       -- 窗口内排序（可选）
    [ROWS|RANGE BETWEEN ... AND ...]  -- 窗口帧范围（可选）
)

与普通聚合函数（GROUP BY）的核心区别：窗口函数不折叠行。每一行都保留在结果集中，同时能"看到"窗口内其他行的数据。

---

二、排名函数

2.1 ROW_NUMBER / RANK / DENSE_RANK

以销售数据为例，按部门对销售额排名：

SELECT
    dept_id,
    salesperson,
    sale_amount,

    -- 连续行号，相同值也不并列
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS row_num,

    -- 并列排名，跳号（1,1,3,4...）
    RANK()       OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS rnk,

    -- 并列排名，不跳号（1,1,2,3...）
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS dense_rnk

FROM sales
WHERE sale_date >= '2024-01-01';

输出示例：

dept_id | salesperson | sale_amount | row_num | rnk | dense_rnk
--------+-------------+-------------+---------+-----+----------
101     | 张三        | 95000       | 1       | 1   | 1
101     | 李四        | 95000       | 2       | 1   | 1   ← 并列第 1
101     | 王五        | 82000       | 3       | 3   | 2   ← RANK 跳到 3，DENSE_RANK 是 2
101     | 赵六        | 71000       | 4       | 4   | 3

2.2 实用场景：取每个部门销售额 Top 3

-- 用子查询或 CTE 过滤排名
WITH ranked AS (
    SELECT
        dept_id,
        salesperson,
        sale_amount,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS rn
    FROM sales
    WHERE YEAR(sale_date) = 2024
)
SELECT dept_id, salesperson, sale_amount
FROM ranked
WHERE rn <= 3
ORDER BY dept_id, rn;

2.3 NTILE：分桶排名

将每个部门的销售员按业绩分成 4 个等级（四分位）：

SELECT
    dept_id,
    salesperson,
    sale_amount,
    NTILE(4) OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS quartile
    -- 1 = 顶部 25%，4 = 底部 25%
FROM sales
WHERE YEAR(sale_date) = 2024;

---

三、累计与滑动聚合

3.1 月累计（Year-to-Date）

统计每个月的销售额，以及截至该月的累计销售额：

SELECT
    dept_id,
    sale_month,
    monthly_amount,

    -- 累计到当月（从第一行到当前行）
    SUM(monthly_amount) OVER (
        PARTITION BY dept_id
        ORDER BY sale_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_amount,

    -- 简写（效果相同，ORDER BY 默认到 CURRENT ROW）
    SUM(monthly_amount) OVER (
        PARTITION BY dept_id
        ORDER BY sale_month
    ) AS ytd_amount_v2

FROM (
    SELECT
        dept_id,
        DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
        SUM(sale_amount)                AS monthly_amount
    FROM sales
    GROUP BY dept_id, DATE_FORMAT(sale_date, '%Y-%m')
) t
ORDER BY dept_id, sale_month;

3.2 滑动平均（Moving Average）

计算近 3 个月的滑动平均销售额，平滑季节性波动：

SELECT
    dept_id,
    sale_month,
    monthly_amount,
    ROUND(
        AVG(monthly_amount) OVER (
            PARTITION BY dept_id
            ORDER BY sale_month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 前 2 行 + 当前行 = 3 行窗口
        ), 2
    ) AS moving_avg_3m
FROM monthly_sales
ORDER BY dept_id, sale_month;

3.3 窗口帧语法速查

-- 从分区第一行到当前行（累计）
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 固定行数滑动窗口（前 N 行到当前行）
ROWS BETWEEN N PRECEDING AND CURRENT ROW

-- 前后各 N 行（居中滑动窗口）
ROWS BETWEEN N PRECEDING AND N FOLLOWING

-- 整个分区（等同于没有 ORDER BY 时的默认行为）
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

---

四、环比与同比：LAG / LEAD

4.1 LAG：取上一行的值（环比）

计算每月销售额与上月的环比增长率：

SELECT
    dept_id,
    sale_month,
    monthly_amount,
    LAG(monthly_amount, 1) OVER (
        PARTITION BY dept_id
        ORDER BY sale_month
    ) AS prev_month_amount,

    ROUND(
        (monthly_amount - LAG(monthly_amount, 1) OVER (
            PARTITION BY dept_id ORDER BY sale_month
        )) / NULLIF(LAG(monthly_amount, 1) OVER (
            PARTITION BY dept_id ORDER BY sale_month
        ), 0) * 100, 2
    ) AS mom_growth_pct   -- Month-over-Month

FROM monthly_sales
ORDER BY dept_id, sale_month;

NULLIF(..., 0) 防止除以 0 报错，当上月销售额为 0 时返回 NULL。

4.2 LEAD：取下一行的值

预算完成率预测（用已知月份的数据预测趋势）：

SELECT
    sale_month,
    monthly_amount,
    LEAD(monthly_amount, 1) OVER (ORDER BY sale_month) AS next_month_amount,
    LEAD(monthly_amount, 3) OVER (ORDER BY sale_month) AS next_quarter_amount
FROM monthly_sales;

4.3 同比（Year-over-Year）

同比需要跨年份比较，LAG 指定偏移 12 行（12 个月）：

SELECT
    sale_month,
    monthly_amount,
    LAG(monthly_amount, 12) OVER (
        PARTITION BY dept_id
        ORDER BY sale_month
    ) AS same_month_last_year,

    ROUND(
        (monthly_amount - LAG(monthly_amount, 12) OVER (
            PARTITION BY dept_id ORDER BY sale_month
        )) / NULLIF(LAG(monthly_amount, 12) OVER (
            PARTITION BY dept_id ORDER BY sale_month
        ), 0) * 100, 2
    ) AS yoy_growth_pct

FROM monthly_sales
ORDER BY dept_id, sale_month;

---

五、占比计算

计算每个部门的销售额占公司总销售额的比例：

SELECT
    dept_id,
    sale_month,
    monthly_amount,

    -- 占全公司当月总额的比例
    ROUND(
        monthly_amount * 100.0 /
        SUM(monthly_amount) OVER (PARTITION BY sale_month),
        2
    ) AS pct_of_total,

    -- 占该部门全年总额的比例（当月在全年的贡献）
    ROUND(
        monthly_amount * 100.0 /
        SUM(monthly_amount) OVER (PARTITION BY dept_id),
        2
    ) AS pct_of_dept_annual

FROM monthly_sales
ORDER BY sale_month, dept_id;

---

六、ROLLUP：小计与合计

ROLLUP 是 GROUP BY 的扩展，自动生成多层次的小计行：

-- 按部门和月份统计，并自动生成部门小计和总计
SELECT
    COALESCE(dept_id, '【全部】')   AS dept,
    COALESCE(sale_month, '【汇总】') AS month,
    SUM(sale_amount)                 AS total_amount
FROM sales
WHERE YEAR(sale_date) = 2024
GROUP BY ROLLUP(dept_id, sale_month)
ORDER BY dept_id, sale_month;

输出结构：

dept   | month   | total_amount
-------+---------+-------------
101    | 2024-01 | 320000
101    | 2024-02 | 285000
...
101    | 【汇总】 | 3540000   ← 部门 101 全年小计
102    | 2024-01 | 410000
...
102    | 【汇总】 | 4820000   ← 部门 102 全年小计
【全部】| 【汇总】 | 8360000   ← 全公司总计

CUBE：所有维度组合的交叉小计

-- CUBE 生成所有可能的维度组合小计
SELECT
    COALESCE(CAST(dept_id AS CHAR), '全部') AS dept,
    COALESCE(region, '全部')                AS region,
    SUM(sale_amount)                         AS total
FROM sales
GROUP BY CUBE(dept_id, region)
ORDER BY dept_id, region;

CUBE 会生成：(dept, region)、(dept, 全部)、(全部, region)、(全部, 全部) 共 4 种组合的小计，适合多维分析报表。

---

七、首尾值函数

FIRST_VALUE / LAST_VALUE

取窗口内第一行或最后一行的值，常用于计算"距最低/最高点的差值"：

SELECT
    dept_id,
    sale_month,
    monthly_amount,

    -- 该部门历史最低月销售额（窗口内第一行，按升序排列）
    FIRST_VALUE(monthly_amount) OVER (
        PARTITION BY dept_id
        ORDER BY monthly_amount ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS hist_min,

    -- 该部门历史最高月销售额
    FIRST_VALUE(monthly_amount) OVER (
        PARTITION BY dept_id
        ORDER BY monthly_amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS hist_max

FROM monthly_sales;

---

八、MPP 环境下的执行原理

窗口函数在 GBase 8a 中的执行流程：

各 gnode 本地扫描 + 预聚合
        ↓
按 PARTITION BY 列 Hash Redistribute（数据 Shuffle）
  → 确保同一 PARTITION 的所有行都在同一个 gnode
        ↓
各 gnode 在本地执行窗口计算（排序 + 滑动窗口）
        ↓
gcluster 汇总结果

性能关键点：

1. PARTITION BY 列如果恰好是表的分布键，则无需 Hash Redistribute，窗口计算完全本地化，性能最优。
2. 如果 PARTITION BY 列不是分布键（常见情况），会触发一次 Hash Redistribute，将同 PARTITION 的数据集中到同一节点。
3. ORDER BY 只影响窗口内的排序，不影响分布策略。
4. 同一 SELECT 中多个窗口函数，如果 PARTITION BY 和 ORDER BY 相同，优化器会复用同一次 Shuffle，无额外开销。

实践建议：如果业务报表有大量按 dept_id 做窗口函数的需求，而表的分布键是 customer_id，可以考虑建一张按 dept_id 分布的汇总中间表，专门服务这类 OLAP 查询。

---

九、常用场景速查

需求	使用函数
分组内排名（唯一）	ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
分组内排名（并列）	RANK() 或 DENSE_RANK()
取分组内 Top N	ROW_NUMBER() + 外层 WHERE rn <= N
累计求和（YTD）	SUM() OVER (ORDER BY ...)
N 期滑动平均	AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)
上期值（环比）	LAG(col, 1) OVER (ORDER BY ...)
去年同期值（同比）	LAG(col, 12) OVER (ORDER BY ...)
占比	col / SUM(col) OVER (PARTITION BY ...)
分组小计 + 合计	GROUP BY ROLLUP(...)
多维交叉小计	GROUP BY CUBE(...)
分区内首尾值	FIRST_VALUE() / LAST_VALUE() OVER (...)