【MySQL】巧用 Dense_Rank 窗口函数

力扣题

1、题目地址

2084. 为订单类型为 0 的客户删除类型为 1 的订单

2、模拟表

活动表：Orders

Column Name	Type
order_id	int
customer_id	int
order_type	int

• order_id是此表的主键列。
• 此表的每一行都表示订单的ID、订购该订单的客户的ID以及订单类型。
• 订单可以是类型0或类型1。

3、要求

编写SQL查询以根据以下条件报告所有订单：

如果客户至少有一个类型为0的订单，则不要报告该客户的任何类型为1的订单。
否则，报告客户的所有订单。
按任意顺序返回结果表。

查询结果格式如下例所示。

示例 1：

输入：
Orders 表：

order_id	customer_id	order_type
1	1	0
2	1	0
11	2	0
12	2	1
21	3	1
22	3	0
31	4	1
32	4	1

输出：

order_id	customer_id	order_type
31	4	1
32	4	1
1	1	0
2	1	0
11	2	0
22	3	0

解释：
客户1有两个类型为0的订单。我们两个都返回。
客户2的订单类型为0，订单类型为1。我们只返回类型为0的订单。
客户3的订单类型为0，订单类型为1。我们只返回类型为0的订单。
客户4有两个类型1的订单。我们两个都返回。

4、代码编写

代码思路

1、客户订单类型不存在为 0 则返回全部（包括 0 和 1）
2、客户订单类型存在为 0 返回 0

我的代码

SELECT *
FROM Orders a
WHERE NOT EXISTS(SELECT * FROM Orders WHERE customer_id = a.customer_id AND order_type = '0')
UNION ALL
SELECT *
FROM Orders a
WHERE EXISTS(SELECT * FROM Orders WHERE customer_id = a.customer_id AND order_type = '0')
AND order_type = '0'

网友代码（巧用 Dense_Rank 窗口函数）

专用窗口函数：rank，dense_rank，row_number

Rank：有相同名次，名次按实际个数走，会跳数字
Dense_rank: 有相同名次，名次不跳数
Row_number：相同分数按行数排序

分数	Rank	Dense_Rank	Row_number
100	1	1	1
100	1	1	2
90	3	2	3

SELECT order_id, customer_id, order_type
FROM (
    SELECT *,
           Dense_Rank() over (partition by customer_id order by order_type) rk
    FROM Orders
) AS a
WHERE rk = 1

逻辑：对不同 customer_id 进行分组处理，每个组里面根据 order_type 进行排序，所以有 0 的话肯定是排在前面的，使用的是 Dense_Rank 窗口函数，这意味着如果 order_type 相同的话，数字是不会增加的，所以如果有 order_type 有存在等于 0 的话，就返回对应 rk = 1 （返回 0）就行，如果 order_type 不存在等于 0 的话，也就是返回全部 rk = 1（返回 1，因为没有 0 的），只能说用得很巧妙

Rank 窗口函数也是可以的

SELECT order_id, customer_id, order_type
FROM (
    SELECT *,
           Rank() over (partition by customer_id order by order_type) rk
    FROM Orders
) AS a
WHERE rk = 1

Dense_Rank 和 Rank 窗口函数可参考

MYSQL窗口函数（Rows & Range）—— 滑动窗口函数用法