活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

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


---

## 一、窗口函数基础语法

窗口函数的通用结构：

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

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

---

## 二、排名函数

### 2.1 ROW_NUMBER / RANK / DENSE_RANK

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

```sql
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 &gt;= '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

```sql
-- 用子查询或 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 &lt;= 3
ORDER BY dept_id, rn;
```

### 2.3 NTILE：分桶排名

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

```sql
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）

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

```sql
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 个月的滑动平均销售额，平滑季节性波动：

```sql
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 窗口帧语法速查

```sql
-- 从分区第一行到当前行（累计）
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：取上一行的值（环比）

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

```sql
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;
```

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

### 4.2 LEAD：取下一行的值

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

```sql
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 个月）：

```sql
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;
```

---

## 五、占比计算

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

```sql
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 的扩展，自动生成多层次的小计行：

```sql
-- 按部门和月份统计，并自动生成部门小计和总计
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：所有维度组合的交叉小计

```sql
-- 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

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

```sql
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 &lt;= 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 (...)` |



运维管理

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

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

评论

热门帖子