Oracle CUME_DIST()窗口函数详解

我们来详细讲解一下 CUME_DIST() OVER() 窗口函数。这是一个非常实用且在数据分析中常用的函数。

1. 核心概念

CUME_DIST() 的全称是 Cumulative Distribution（累积分布）。它的作用是计算某个值在一组值中的累积分布，即：小于等于当前行的值的行数占总行数的百分比。

它的返回值范围在 0 到 1 之间。

2. 语法

CUME_DIST() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC | DESC]
)

• OVER(): 定义了一个窗口（一组行），函数将在这个窗口上进行计算。
• PARTITION BY (可选): 将数据分成多个分区（组）。CUME_DIST() 的计算会独立地在每个分区内进行。如果省略，则将所有数据视为一个分区。
• ORDER BY (必需): 指定每个分区内数据的排序方式，决定了“小于等于”的顺序。ASC（升序，默认）或 DESC（降序）都会影响计算结果。

3. 计算公式

对于每一行，CUME_DIST() 的计算公式非常直观：

CUME_DIST() = (当前行的排名序号) / (总行数)

这里的“排名序号”指的是：小于等于当前行的行数（注意，不是RANK()的值）。

更准确地说：
CUME_DIST(value) = (number of rows with value <= current value) / (total number of rows in the partition)

4. 举例说明

假设我们有一个 sales 表，记录了销售员的销售额：

salesperson	sale_amount
Alice	1000
Bob	1500
Charlie	1500
David	2000
Eve	2500

现在我们运行以下 SQL 查询：

SELECT
    salesperson,
    sale_amount,
    CUME_DIST() OVER (ORDER BY sale_amount) AS cume_dist
FROM
    sales;

结果与分析：

salesperson	sale_amount	cume_dist	解释
Alice	1000	0.2	小于等于 1000 的有 1 行，总行数 5。1/5 = 0.2
Bob	1500	0.6	小于等于 1500 的有 3 行 (Alice, Bob, Charlie)。3/5 = 0.6
Charlie	1500	0.6	同上，因为 1500 和 Bob 相等，所以排名序号相同
David	2000	0.8	小于等于 2000 的有 4 行。4/5 = 0.8
Eve	2500	1.0	小于等于 2500 的就是所有 5 行。5/5 = 1.0

关键点：

• 对于相同的值（如 Bob 和 Charlie 的 1500），它们的 CUME_DIST() 值相同。
• 这个值表示“有 60% 的销售员，销售额小于等于 1500”。

---

带 PARTITION BY 的例子

假设数据按地区分区，我们想看看每个销售员在其所在地区的累积分布。

salesperson	region	sale_amount
Alice	East	1000
Bob	East	1500
Charlie	West	1500
David	West	2000
Eve	West	2500

SELECT
    salesperson,
    region,
    sale_amount,
    CUME_DIST() OVER (PARTITION BY region ORDER BY sale_amount) AS cume_dist_in_region
FROM
    sales;

结果与分析：

salesperson	region	sale_amount	cume_dist_in_region	解释
Alice	East	1000	0.5	East 分区只有 2 行。小于等于 1000 的有 1 行。1/2 = 0.5
Bob	East	1500	1.0	East 分区小于等于 1500 的有 2 行。2/2 = 1.0
Charlie	West	1500	0.333…	West 分区有 3 行。小于等于 1500 的有 1 行。1/3 ≈ 0.333
David	West	2000	0.666…	West 分区小于等于 2000 的有 2 行。2/3 ≈ 0.666
Eve	West	2500	1.0	West 分区小于等于 2500 的有 3 行。3/3 = 1.0

可以看到，CUME_DIST() 的计算完全在 East 和 West 两个分区内独立进行。

5. 常见使用场景

1. 性能分析：快速了解一个员工的业绩在团队中的位置（例如，“你的销售额超过了 80% 的同事”）。
2. 数据分箱（Binning）：根据累积分布将数据分成不同的组（例如，前20%，20%-60%，后40%）。
3. 异常值检测：累积分布接近 0 或 1 的值可能是需要关注的异常值。
4. 计算百分位数：CUME_DIST() 是计算百分位数的基础。你可以通过寻找 CUME_DIST 最接近 0.5 的值来找到中位数。

6. 与其他窗口函数的区别

函数	目的	返回值特点
CUME_DIST()	计算累积分布百分比	返回 0 到 1 之间的小数，相同值的行结果相同
RANK()	计算排名	返回整数，相同值的行排名相同，但会跳过后续排名（如 1,2,2,4）
DENSE_RANK()	计算密集排名	返回整数，相同值的行排名相同，但不会跳过后续排名（如 1,2,2,3）
PERCENT_RANK()	计算百分比排名	返回 0 到 1 之间的小数，计算公式为 (rank - 1) / (total rows - 1)，表示行的相对排名

简单总结：
CUME_DIST() 回答的问题是：“有多少比例的数据小于等于当前值？”。
而 PERCENT_RANK() 回答的问题是：“当前值超过了多少比例的数据？”（更侧重于排名本身的比例）。

希望这个详细的解释能帮助你彻底理解 CUME_DIST() OVER()！