MySQL 8.0 `CUME_DIST`、`NTILE` 等排名函数在数据分析中的实践

MySQL 8.0 排名函数：让数据分析像开挂一样简单！🚀

各位观众老爷们，大家好！今天咱们聊点高大上的，但保证接地气！咱们今天要聊的是MySQL 8.0中那些让人眼前一亮的排名函数，比如CUME_DIST、NTILE等等。这些小家伙们，用好了，能让你的数据分析工作效率瞬间提升N个档次，简直就像开了外挂一样！

为什么我们需要排名函数？🤔

在数据分析的世界里，我们经常需要对数据进行排序、分组、排名。比如：

• “找出销售额排名前10的客户”
• “将客户按照消费能力分成五等份”
• “计算每个产品的销售额占总销售额的比例”

以前，实现这些需求，要么写一大堆复杂的SQL，看得人头昏眼花，要么就得把数据拉到程序里，用代码吭哧吭哧地算。现在有了排名函数，这些复杂的问题，一行SQL搞定！是不是很激动？

今天咱们就来好好扒一扒这些排名函数，看看它们到底有什么神奇之处。

一、排名函数家族大阅兵 👨‍👩‍👧‍👦

MySQL 8.0 引入了不少排名函数，今天我们重点关注几个：

• ROW_NUMBER(): 简单粗暴，就是给每一行数据分配一个唯一的序号。
• RANK(): 排名，但允许并列排名，比如如果有两个第二名，那就会跳过第三名，直接到第四名。
• DENSE_RANK(): 也是排名，但更紧凑，并列排名后不会跳过名次，如果有两个第二名，那下一个就是第三名。
• NTILE(n): 将数据分成 n 等份，并为每一份分配一个组号。
• CUME_DIST(): 计算累积分布，也就是小于等于当前值的行数占总行数的比例。
• PERCENT_RANK(): 计算相对排名，也就是 (RANK – 1) / (总行数 – 1)。

是不是感觉有点晕？没关系，咱们一个个来，保证让你彻底理解！

二、ROW_NUMBER()：简单粗暴的序号生成器 🔢

ROW_NUMBER() 函数是最简单的排名函数，它会为结果集中的每一行分配一个唯一的序号，从1开始。

语法：

ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

• OVER(): 指定窗口函数的作用范围。
• PARTITION BY: 将数据分成多个分区，每个分区独立进行排名。
• ORDER BY: 指定排序规则，决定序号的生成顺序。

例子：

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

Salesperson	SalesAmount
Alice	1000
Bob	1500
Charlie	1200
David	800
Eve	1500

我们想给每个销售员按照销售额降序分配一个序号：

SELECT
    Salesperson,
    SalesAmount,
    ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS RowNum
FROM
    sales;

结果：

Salesperson	SalesAmount	RowNum
Bob	1500	1
Eve	1500	2
Charlie	1200	3
Alice	1000	4
David	800	5

看到没？ ROW_NUMBER() 完美地完成了任务，即使Bob和Eve的销售额一样，它们也分别获得了不同的序号。

应用场景：

• 分页查询：可以结合 ROW_NUMBER() 和 LIMIT 实现分页功能。
• 数据去重：可以根据某个字段分组，然后取每个分组的 ROW_NUMBER() = 1 的数据，实现数据去重。

三、RANK() 和 DENSE_RANK()：排名界的双胞胎兄弟 👯‍♂️

RANK() 和 DENSE_RANK() 都是用来排名的，它们最大的区别在于如何处理并列排名。

• RANK(): 并列排名会占用名次，导致跳号。
• DENSE_RANK(): 并列排名不占用名次，名次是连续的。

语法：

RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

DENSE_RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

例子：

还是上面的 sales 表，我们分别使用 RANK() 和 DENSE_RANK() 进行排名：

SELECT
    Salesperson,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS DenseRank
FROM
    sales;

结果：

Salesperson	SalesAmount	Rank	DenseRank
Bob	1500	1	1
Eve	1500	1	1
Charlie	1200	3	2
Alice	1000	4	3
David	800	5	4

可以看到，Bob和Eve的销售额并列第一，RANK() 跳过了第二名，直接到了第三名，而 DENSE_RANK() 仍然是第二名。

应用场景：

• RANK(): 适用于需要知道具体排名，并且允许跳号的场景。
• DENSE_RANK(): 适用于需要紧凑的排名，不允许跳号的场景。

举个栗子：

假设你们公司要评选优秀员工，前三名可以获得奖励。如果使用 RANK()，可能会出现并列第一的情况，导致只有两个人获奖。如果使用 DENSE_RANK()，即使有并列第一，也能保证有三个人获奖。

四、NTILE(n)：分组小能手 🧑‍🤝‍🧑

NTILE(n) 函数可以将数据分成 n 等份，并为每一份分配一个组号，组号从1开始。

语法：

NTILE(n) OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

例子：

还是上面的 sales 表，我们想将销售员按照销售额分成三组：

SELECT
    Salesperson,
    SalesAmount,
    NTILE(3) OVER (ORDER BY SalesAmount DESC) AS GroupNum
FROM
    sales;

结果：

Salesperson	SalesAmount	GroupNum
Bob	1500	1
Eve	1500	1
Charlie	1200	2
Alice	1000	3
David	800	3

可以看到，NTILE(3) 将销售员分成了三组，组号分别为1、2、3。

应用场景：

• 客户分层：可以将客户按照消费金额分成不同的等级，进行差异化营销。
• 性能测试：可以将请求按照时间分成不同的批次，分析每个批次的性能指标。

注意事项：

• 如果数据不能被 n 整除，那么前面的组会比后面的组多一个数据。
• NTILE(n) 的结果是整数，范围是 1 到 n。

五、CUME_DIST()：统计利器，掌握全局 📊

CUME_DIST() 函数计算累积分布，也就是小于等于当前值的行数占总行数的比例。

语法：

CUME_DIST() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

例子：

还是上面的 sales 表，我们计算每个销售员的销售额的累积分布：

SELECT
    Salesperson,
    SalesAmount,
    CUME_DIST() OVER (ORDER BY SalesAmount DESC) AS CumulativeDistribution
FROM
    sales;

结果：

Salesperson	SalesAmount	CumulativeDistribution
Bob	1500	0.4
Eve	1500	0.4
Charlie	1200	0.6
Alice	1000	0.8
David	800	1.0

可以看到，Bob和Eve的销售额都是1500，小于等于1500的行数占总行数的比例是40%。David的销售额是800，小于等于800的行数占总行数的比例是100%。

应用场景：

• 风险评估：可以计算某个指标的累积分布，评估风险等级。
• 市场分析：可以计算某个产品的市场份额的累积分布，了解市场占有率。

举个栗子：

假设你们公司想了解客户的消费能力，你可以使用 CUME_DIST() 计算每个客户的消费金额的累积分布。如果一个客户的消费金额的累积分布是0.9，说明他的消费能力超过了90%的客户。

六、PERCENT_RANK()：相对排名，一览众山小 🏔️

PERCENT_RANK() 函数计算相对排名，也就是 (RANK – 1) / (总行数 – 1)。

语法：

PERCENT_RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3 [ASC | DESC] )

例子：

还是上面的 sales 表，我们计算每个销售员的销售额的相对排名：

SELECT
    Salesperson,
    SalesAmount,
    PERCENT_RANK() OVER (ORDER BY SalesAmount DESC) AS PercentRank
FROM
    sales;

结果：

Salesperson	SalesAmount	PercentRank
Bob	1500	0.0
Eve	1500	0.0
Charlie	1200	0.5
Alice	1000	0.75
David	800	1.0

可以看到，Bob和Eve的销售额都是最高的，相对排名都是0%。David的销售额是最低的，相对排名是100%。

应用场景：

• 绩效评估：可以计算员工的绩效指标的相对排名，了解员工在团队中的表现。
• 教育评估：可以计算学生的考试成绩的相对排名，了解学生在班级中的水平。

总结一下：

PERCENT_RANK() 的结果是一个介于 0 和 1 之间的值，表示当前值在排序后的数据集中所处的位置。

七、实战演练：用排名函数解决实际问题 💪

光说不练假把式！咱们来几个实际的例子，看看如何使用排名函数解决实际问题。

1. 找出每个部门销售额排名前三的员工：

假设我们有一个 employee 表，记录了员工的部门和销售额：

EmployeeName	Department	SalesAmount
Alice	Sales	1000
Bob	Sales	1500
Charlie	Sales	1200
David	Marketing	800
Eve	Marketing	1500
Frank	Marketing	1000

我们想找出每个部门销售额排名前三的员工：

SELECT
    EmployeeName,
    Department,
    SalesAmount
FROM
    (
        SELECT
            EmployeeName,
            Department,
            SalesAmount,
            DENSE_RANK() OVER (PARTITION BY Department ORDER BY SalesAmount DESC) AS Rank
        FROM
            employee
    ) AS RankedEmployees
WHERE
    Rank <= 3;

解释：

• 首先，我们使用 DENSE_RANK() 函数对每个部门的员工按照销售额降序排名。
• 然后，我们筛选出排名小于等于3的员工。

2. 将客户按照消费金额分成五个等级：

假设我们有一个 customer 表，记录了客户的消费金额：

CustomerName	PurchaseAmount
Alice	100
Bob	200
Charlie	300
David	400
Eve	500
Frank	600
Grace	700
Henry	800
Ivy	900
Jack	1000

我们想将客户按照消费金额分成五个等级：

SELECT
    CustomerName,
    PurchaseAmount,
    NTILE(5) OVER (ORDER BY PurchaseAmount) AS CustomerLevel
FROM
    customer;

解释：

• 我们使用 NTILE(5) 函数将客户按照消费金额分成五个等级。

3. 计算每个产品的销售额占总销售额的比例：

假设我们有一个 product_sales 表，记录了每个产品的销售额：

ProductName	SalesAmount
ProductA	1000
ProductB	2000
ProductC	3000

我们想计算每个产品的销售额占总销售额的比例：

SELECT
    ProductName,
    SalesAmount,
    SalesAmount / SUM(SalesAmount) OVER () AS SalesPercentage
FROM
    product_sales;

解释：

• 我们使用 SUM(SalesAmount) OVER () 计算总销售额。
• 然后，我们用每个产品的销售额除以总销售额，得到销售额占比。

注意： 这里 SUM(SalesAmount) OVER () 也使用了窗口函数，它计算的是所有行的销售额总和。

八、总结：排名函数，数据分析的瑞士军刀 🧰

总而言之，MySQL 8.0 的排名函数就像数据分析的瑞士军刀，功能强大，使用灵活。掌握了这些函数，你可以轻松解决各种数据分析问题，提高工作效率，简直不要太爽！

最后，送给大家一句话：

“工欲善其事，必先利其器。掌握排名函数，让你的数据分析工作如虎添翼！” 🐅

希望今天的分享对大家有所帮助！咱们下期再见！ 👋