当前位置：首页 > 问答 > 正文

DB2里那些OLAP函数怎么用，举几个例子来讲讲分析下

召安青
问答
2025-12-28 08:13:02
2

关于DB2中的OLAP函数，说白了就是一类特殊的窗口函数，它们不像普通的SUM或COUNT那样对整个结果集进行汇总，而是能在保留每一行原始数据的同时，对与当前行相关的某个“窗口”内的数据进行计算，这个“窗口”可以灵活地定义，比如从结果集的开头到当前行，或者当前行前后几行，甚至是某个分组内的所有行，这在做排名、累加、移动平均等分析时特别有用。

下面我直接引用一些常见的OLAP函数,并用例子来分析。

来源参考：IBM官方DB2 SQL文档中关于OLAP函数的部分，以及常见的数据库教程如《DB2 SQL开发指南》。

ROW_NUMBER(), RANK(), DENSE_RANK() - 排名函数

这三个函数都用来给数据排名，但处理“并列”情况的方式不同。

ROW_NUMBER()：单纯地给每一行一个连续的、唯一的序号，即使值相同,序号也不同。
RANK()：排名相同的行会得到相同的序号，但下一个不同的值会“跳号”，比如有两个并列第一,那么下一个就是第三名。
DENSE_RANK()：排名相同的行得到相同的序号，但下一个不同的值会紧接着排名，不会跳号，比如有两个并列第一,下一个就是第二名。

例子： 假设我们有一个销售表 sales，有销售员 (salesperson)、区域 (region)、销售额 (sales_amount) 三个字段,我们想给每个区域内的销售员按销售额排名。

SELECT
  salesperson,
  region,
  sales_amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) as row_num,
  RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) as rank,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) as dense_rank
FROM sales
ORDER BY region, sales_amount DESC;

分析一下：

OVER 子句是定义窗口的关键。
PARTITION BY region 意思是“按区域分区”，也就是说，排名是在每个区域内部独立进行的，华东区的排名从1开始，华北区的排名也从1开始,互不干扰。
ORDER BY sales_amount DESC 意思是“按销售额降序排列”,这是排名的依据。
假设华东区有两个销售员销售额都是10万，并列最高。
- ROW_NUMBER() 可能会随机给其中一个第1名，另一个第2名（因为必须生成唯一序号）。
- RANK() 会给这两个人都是第1名,那么下一个销售额较低的人就是第3名。
- DENSE_RANK() 会给这两个人都是第1名,下一个销售额较低的人就是第2名。

SUM() OVER() - 累计求和

普通的SUM是分组汇总，但SUM() OVER()可以实现类似“累计”的效果。

例子： 我们想看到每个销售员，以及他/她所在区域的累计销售额（按销售额从高到低累计）。

SELECT
  salesperson,
  region,
  sales_amount,
  SUM(sales_amount) OVER (PARTITION BY region ORDER BY sales_amount DESC ROWS UNBOUNDED PRECEDING) as running_total
FROM sales
ORDER BY region, sales_amount DESC;

分析一下：

PARTITION BY region 同样,分区保证计算在每个区域内进行。
ORDER BY sales_amount DESC 这里不仅决定了显示顺序,更重要的是定义了累计的顺序。
ROWS UNBOUNDED PRECEDING 这是窗口框架子句，是这里的精髓，它的意思是“从分区的第一行开始，累计到当前行”。UNBOUNDED PRECEDING 之前的所有行”。
这样，结果中每一行的 running_total 列，显示的就是从该区域销售额最高的人开始，累加到当前行这个人的总销售额,你可以清晰地看到销售额的累积过程。

LAG() 和 LEAD() - 访问“前面”或“后面”的行

这两个函数允许你访问当前行之前或之后某一行的数据，非常适合计算环比、同比增长率。

LAG(column, n)：获取当前行之前第n行的数据。
LEAD(column, n)：获取当前行之后第n行的数据。

例子： 有一个月度销售表 monthly_sales，有月份 (month) 和销售额 (amount) 字段,我们想计算本月销售额与上月的差额。

SELECT
  month,
  amount as current_month_amount,
  LAG(amount, 1) OVER (ORDER BY month) as previous_month_amount,
  amount - LAG(amount, 1) OVER (ORDER BY month) as month_over_month_growth
FROM monthly_sales
ORDER BY month;

分析一下：

这里没有使用 PARTITION BY,因为我们是按时间序列全局比较。
OVER (ORDER BY month) 是必须的，它定义了哪一行是“前”，哪一行是“后”。
LAG(amount, 1) 就是取上一行的 amount 值，对于第一个月，因为没有“上一行”，它会返回 NULL。
这样，我们就能轻松地计算出本月对上月的增长额，如果要算增长率，公式就是 (amount / LAG(amount,1) OVER(...)) - 1。

移动平均线 - 结合 ROWS 指定范围

在金融或时间序列分析中，经常需要计算移动平均，比如5日移动平均线,这也可以通过窗口函数轻松实现。

例子： 在股票日交易表 stock_prices 中，有日期 (trade_date) 和收盘价 (close_price),计算5日移动平均价。

SELECT
  trade_date,
  close_price,
  AVG(close_price) OVER (ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as ma_5
FROM stock_prices
ORDER BY trade_date;

分析一下：

AVG(close_price) OVER ... 表示计算平均值。
ORDER BY trade_date 确保按时间顺序计算。
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 这是核心，它定义了一个窗口，包含当前行以及它前面的4行，总共5行数据,然后对这个小小的5行窗口计算平均值。
随着 trade_date 的推移，这个窗口会不断滑动，始终保持计算最近5天的平均值,从而得到移动平均线。

DB2的OLAP函数功能非常强大，其核心在于OVER()子句的灵活运用，通过PARTITION BY进行分组，ORDER BY定义顺序，再结合ROWS ...或RANGE ...来精确控制窗口的范围，你可以轻松实现各种复杂的分析查询，而无需编写复杂的自连接或子查询，这些功能对于数据报告、业务分析和性能优化都至关重要。

DB2里那些OLAP函数怎么用，举几个例子来讲讲分析下