MySQL函数：`CUME_DIST()`计算行在分组中的累积分布。

MySQL 中的 CUME_DIST() 函数：累积分布的计算与应用

大家好，今天我们来深入探讨 MySQL 中的一个非常有用的窗口函数：CUME_DIST()。这个函数主要用于计算行在分组中的累积分布，帮助我们了解数据在一个排序数据集中的相对位置。

什么是累积分布？

在统计学中，累积分布函数（Cumulative Distribution Function，CDF）描述了一个实值随机变量 X 小于或等于某个值 x 的概率。 简单来说，对于排序后的数据集，CUME_DIST() 函数返回的是小于等于当前行值的行数与总行数的比值。

例如，假设我们有一个销售额的数据集：

销售额
100
200
200
300
400
500

对于销售额为 300 的行，CUME_DIST() 的结果将是 4/6 = 0.6667，因为有 4 行的销售额小于等于 300。

CUME_DIST() 函数的语法

CUME_DIST() 函数的语法如下：

CUME_DIST() OVER (
    [PARTITION BY column_list]
    ORDER BY column_list
)

• CUME_DIST(): 函数本身，没有参数。
• OVER(): 指定窗口规范。
• PARTITION BY column_list (可选): 将结果集分成多个分区。 CUME_DIST() 函数在每个分区内独立计算。 如果省略 PARTITION BY 子句，则将整个结果集视为一个分区。
• ORDER BY column_list: 定义每个分区内数据的排序方式。 CUME_DIST() 函数基于此排序计算累积分布。

CUME_DIST() 的使用示例

为了更好地理解 CUME_DIST() 函数，我们来看几个具体的示例。

示例 1：计算员工薪资的累积分布

假设我们有一个名为 employees 的表，包含员工姓名和薪资信息：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 60000),
(4, 'David', 70000),
(5, 'Eve', 80000),
(6, 'Frank', 90000);

我们可以使用 CUME_DIST() 函数计算每个员工薪资的累积分布：

SELECT
    employee_name,
    salary,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution
FROM
    employees;

查询结果如下：

employee_name	salary	cumulative_distribution
Alice	50000.00	0.1667
Bob	60000.00	0.5000
Charlie	60000.00	0.5000
David	70000.00	0.6667
Eve	80000.00	0.8333
Frank	90000.00	1.0000

从结果可以看出，Alice 的薪资累积分布为 0.1667，意味着只有 16.67% 的员工薪资小于等于 Alice 的薪资。 Frank 的薪资累积分布为 1.0000，意味着所有员工的薪资都小于等于 Frank 的薪资。

示例 2：按部门计算员工薪资的累积分布

假设我们在 employees 表中添加一个 department 列：

ALTER TABLE employees ADD COLUMN department VARCHAR(255);

UPDATE employees SET department = 'Sales' WHERE employee_id IN (1, 2);
UPDATE employees SET department = 'Marketing' WHERE employee_id IN (3, 4);
UPDATE employees SET department = 'Engineering' WHERE employee_id IN (5, 6);

我们可以使用 PARTITION BY 子句按部门计算员工薪资的累积分布：

SELECT
    employee_name,
    department,
    salary,
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary) AS cumulative_distribution
FROM
    employees;

查询结果如下：

employee_name	department	salary	cumulative_distribution
Alice	Sales	50000.00	0.5000
Bob	Sales	60000.00	1.0000
Charlie	Marketing	60000.00	0.5000
David	Marketing	70000.00	1.0000
Eve	Engineering	80000.00	0.5000
Frank	Engineering	90000.00	1.0000

从结果可以看出，每个部门内的薪资累积分布是独立计算的。 在 Sales 部门，Alice 的薪资累积分布为 0.5000，意味着 Sales 部门中 50% 的员工薪资小于等于 Alice 的薪资。

示例 3：查找薪资位于前 25% 的员工

我们可以结合 CUME_DIST() 函数和子查询来查找薪资位于前 25% 的员工：

SELECT
    employee_name,
    salary
FROM (
    SELECT
        employee_name,
        salary,
        CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution
    FROM
        employees
) AS subquery
WHERE
    cumulative_distribution <= 0.25;

查询结果如下：

employee_name	salary
Alice	50000.00

结果显示，只有 Alice 的薪资位于前 25%。

示例 4：计算用户购买金额的累积分布，并将其划分为不同的等级

假设我们有一个 orders 表，包含用户 ID 和购买金额：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, user_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 100.00),
(3, 101, 150.00),
(4, 103, 200.00),
(5, 102, 250.00),
(6, 104, 300.00);

我们可以使用 CUME_DIST() 函数计算每个用户购买金额的累积分布，并将其划分为不同的等级：

SELECT
    user_id,
    amount,
    CUME_DIST() OVER (ORDER BY amount) AS cumulative_distribution,
    CASE
        WHEN CUME_DIST() OVER (ORDER BY amount) <= 0.25 THEN 'Bronze'
        WHEN CUME_DIST() OVER (ORDER BY amount) <= 0.50 THEN 'Silver'
        WHEN CUME_DIST() OVER (ORDER BY amount) <= 0.75 THEN 'Gold'
        ELSE 'Platinum'
    END AS customer_level
FROM
    orders;

查询结果如下：

user_id	amount	cumulative_distribution	customer_level
101	50.00	0.1667	Bronze
102	100.00	0.3333	Silver
101	150.00	0.5000	Silver
103	200.00	0.6667	Gold
102	250.00	0.8333	Platinum
104	300.00	1.0000	Platinum

在这个例子中，我们根据用户的购买金额将其划分为 Bronze、Silver、Gold 和 Platinum 四个等级。

示例 5：分析网站访问时长的分布

假设我们有一个 website_visits 表，记录了用户的访问时长：

CREATE TABLE website_visits (
    visit_id INT PRIMARY KEY,
    user_id INT,
    duration INT -- 访问时长，单位：秒
);

INSERT INTO website_visits (visit_id, user_id, duration) VALUES
(1, 1, 30),
(2, 2, 60),
(3, 1, 90),
(4, 3, 120),
(5, 2, 150),
(6, 4, 180),
(7, 1, 210),
(8, 3, 240),
(9, 2, 270),
(10, 4, 300);

我们可以使用 CUME_DIST() 函数分析网站访问时长的分布：

SELECT
    duration,
    CUME_DIST() OVER (ORDER BY duration) AS cumulative_distribution
FROM
    website_visits;

查询结果如下：

duration	cumulative_distribution
30	0.1000
60	0.2000
90	0.3000
120	0.4000
150	0.5000
180	0.6000
210	0.7000
240	0.8000
270	0.9000
300	1.0000

从结果可以看出，10% 的访问时长小于等于 30 秒，20% 的访问时长小于等于 60 秒，以此类推。

CUME_DIST() 与 RANK(), DENSE_RANK(), PERCENT_RANK() 的区别

CUME_DIST() 函数与其他一些窗口函数（如 RANK(), DENSE_RANK(), PERCENT_RANK()）有一些相似之处，但它们之间存在关键区别。

函数名称	功能描述	返回值类型
RANK()	计算每行在其分区中的排名，具有相同值的行排名相同，后续排名跳过。	整数
DENSE_RANK()	计算每行在其分区中的排名，具有相同值的行排名相同，后续排名不跳过。	整数
PERCENT_RANK()	计算每行在其分区中的百分比排名。	浮点数
CUME_DIST()	计算每行在其分区中的累积分布。	浮点数

• RANK() 和 DENSE_RANK(): 返回的是排名，而不是分布。 RANK() 在出现相同值时会跳过排名，而 DENSE_RANK() 不会跳过。
• PERCENT_RANK(): 返回的是百分比排名，计算公式为 (RANK() - 1) / (总行数 - 1)。 它表示的是当前行之前有多少行的值小于当前行。
• CUME_DIST(): 返回的是累积分布，表示当前行以及之前所有行的占比。

总结：

• RANK() 和 DENSE_RANK() 关注的是排名顺序。
• PERCENT_RANK() 关注的是百分比排名。
• CUME_DIST() 关注的是数据的累积分布情况。

CUME_DIST() 的适用场景

CUME_DIST() 函数在以下场景中非常有用：

• 数据分析: 了解数据在一个排序数据集中的相对位置，例如，了解某个用户的消费金额在所有用户中的排名情况。
• 风险评估: 评估某个事件发生的概率，例如，评估某个贷款违约的概率。
• 性能监控: 监控系统的性能指标，例如，监控服务器的 CPU 使用率。
• 用户分层: 根据用户的行为数据将用户划分为不同的等级，例如，将用户划分为 VIP 用户、普通用户和低活跃用户。

注意事项

• CUME_DIST() 函数只能在窗口函数中使用。
• ORDER BY 子句是必需的，因为它定义了计算累积分布的排序方式。
• PARTITION BY 子句是可选的，它可以将结果集分成多个分区，并在每个分区内独立计算累积分布。
• 当存在多个具有相同值的行时，CUME_DIST() 函数会将这些行视为一个整体进行计算。

总结一下CUME_DIST()这个函数

CUME_DIST() 是一个强大的窗口函数，可以帮助我们分析数据的分布情况。 掌握 CUME_DIST() 函数的用法，可以让我们更好地理解数据，并做出更明智的决策。 通过一些示例展示了如何使用这个函数。