您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- mysql- pandas、MySQL、Oracle的分组top n问题研究

pandas、MySQL、Oracle的分组top n问题研究

bubuko 2022/1/25 20:09:26 mysql 字数 37674 阅读 1179 来源 http://www.bubuko.com/infolist-5-1.html

分组top n（分组排序）问题是数据库中的一个经典问题，实际上，根据对并列记录的处理方式不同，该问题可细分为“保留并列”和“不保留并列”两个子问题。在本文的第一部分，笔者使用Python的pandas模块，对 "pandas官方文档" 中的分组top n问题的解决方法进行了拓展，开发了基于DataF ...

分组top n（分组排序）问题是数据库中的一个经典问题，实际上，根据对并列记录的处理方式不同，该问题可细分为“保留并列”和“不保留并列”两个子问题。在本文的第一部分，笔者使用Python的pandas模块，对pandas官方文档中的分组top n问题的解决方法进行了拓展，开发了基于DataFrame数据类型的可以解决是否保留并列、多字段分组、多字段排序功能的topn()函数。

此外，MySQL、Oracle对分组top n问题的解决方法也是多种多样，常见的有基于开窗函数、基于自身左连接、基于用户定义变量等方法。通过对各种方法进行比较，本文第二部分首先选取了功能强大且代码简便的基于开窗函数的方法，该方法适用于MySQL（8.0及以上版本）和Oracle，且可以解决是否保留并列、多字段分组、多字段排序的问题。之后，讲解了基于自身左连接的方法的基本思路，并给出了该方法的适用范围。

一、pandas.DataFrame的分组top n问题

pandas模块是Python数据分析的利器，在pandas官方文档的Comparison with SQL一章，pandas官方给出了分组top n时“不保留并列”这种查询方式的解决方法（多字段分组、多字段排序均可用），还给出了“保留并列”这种查询方式的解决方法（多字段分组可用、但是多字段排序不可用）。针对此问题，笔者进行了相关的拓展并将整个分组排序过程封装为一个函数，通过使用keep_tie变量来控制是否保留并列的记录，可完美支持多字段分组、多字段排序的需求

1. pandas官方文档分组top n代码解析（不保留并列）：

In [36]: (tips.assign(rn=tips.sort_values(['total_bill'], ascending=False)
   ....:                     .groupby(['day'])
   ....:                     .cumcount() + 1)
   ....:      .query('rn < 3')
   ....:      .sort_values(['day', 'rn']))
   ....: 
Out[36]: 
     total_bill    tip     sex smoker   day    time  size  rn
95        40.17   4.73    Male    Yes   Fri  Dinner     4   1
90        28.97   3.00    Male    Yes   Fri  Dinner     2   2
170       50.81  10.00    Male    Yes   Sat  Dinner     3   1
212       48.33   9.00    Male     No   Sat  Dinner     4   2
156       48.17   5.00    Male     No   Sun  Dinner     6   1
182       45.35   3.50    Male    Yes   Sun  Dinner     3   2
197       43.11   5.00  Female    Yes  Thur   Lunch     4   1
142       41.19   5.00    Male     No  Thur   Lunch     5   2

pandas官方文档中使用的数据集名称为tips，里面记录了每笔小费支出的信息，具体包括total_bill, tip, sex, smoker, day, time, size这七个字段

上面的代码查询了（一周七天七个分组下）每组中账单总金额最大的两条记录（不保留并列）。tips是一个DataFrame类型数据，通过assign()方法可向DataFrame中增加一列，等号左边的rn是列标签，等号右边的部分则是该列的值，其中cumcount()表示从0开始的累加计数，后面的+1则是DataFrame特有的向量化操作，表示新增的rn列上的所有数值均+1，使之成为从1开始的排名。当我们想进行多字段分组时，比如对day和time两列的组合进行分组，可将上面代码中的两处‘day‘替换为‘day‘,‘time‘；同理，当我们想进行多字段排序时，比如当total_bill相同时继续比较tip的大小，可将上面代码中的sort_values([‘total_bill‘], ascending=False)修改为sort_values([‘total_bill‘,‘tip‘], ascending=False)

2. pandas官方文档代码解析（保留并列）：

In [38]: (tips[tips['tip'] < 2]
   ....:     .assign(rnk_min=tips.groupby(['sex'])['tip']
   ....:                         .rank(method='min'))
   ....:     .query('rnk_min < 3')
   ....:     .sort_values(['sex', 'rnk_min']))
   ....: 
Out[38]: 
     total_bill   tip     sex smoker  day    time  size  rnk_min
67         3.07  1.00  Female    Yes  Sat  Dinner     1      1.0
92         5.75  1.00  Female    Yes  Fri  Dinner     2      1.0
111        7.25  1.00  Female     No  Sat  Dinner     1      1.0
236       12.60  1.00    Male    Yes  Sat  Dinner     2      1.0
237       32.83  1.17    Male    Yes  Sat  Dinner     2      2.0

上面的代码查询了每个性别分组下的小费支出金额最小且小于2.00的两条记录（保留并列），由于Female分组下有3条记录小费都是1.00，因此它们同为该分组下的第1名。其中tips[tips[‘tip‘] < 2]表示通过布尔值索引的方式选取所有tip<2的行，在此基础上添加一个名为rnk_min的列，该列的值就是按照sex分组时tip的排名（默认升序）。rank()中的参数method表示排名的方法：

  • method=‘min‘：最小排名，当存在并列时，取这些并列项的顺序排名的最小值，对应SQL中的RANK()
  • method=‘max‘：最大排名，当存在并列时，取这些并列项的顺序排名的最大值
  • method=‘first‘：顺序排名，当存在并列时，谁在DataFrame中的顺序靠前，谁的顺序排名也靠前，对应SQL中的ROW_NUMBER()
  • method=‘dense‘：密集排名，后一项的排名总是与前一项相同或加一，不跳跃，对应SQL中的DENSE_RANK()
  • method=‘average‘：平均排名，当存在并列时，取这些并列项的顺序排名的平均值

假设只有四笔小费，金额分别是10元、15元、15元、20元，在升序排序的前提下，下面的表格形象地说明了每种排名的区别：

小费 最小排名
min		 最大排名
max		 顺序排名
first		 密集排名
dense		 平均排名
average
10元 1 1 1 1 1
15元 2 3 2 2 2.5
15元 2 3 3 2 2.5
20元 4 4 4 3 4

可见，最小排名（method=‘min‘）便是我们从一开始提到的“保留并列”的情况，而顺序排名（method=‘first‘）则是“不保留并列”的情况，因此上一节的代码也可以类似地使用本节代码来实现，只需要将参数改为method=‘first‘即可。

说到这里，您可能觉得问题已经圆满解决了，且慢！事实上，本节代码有个非常严重的问题，那就是虽然它可以支持多字段分组，但是却无法支持多字段排序！造成这种情况的根本原因在于pandas中的rank()方法本身是一种基于向量化的操作，因此如果执行一个含有多列的DataFrame的rank()方法，得到的将是基于每列的分别的排名，这与我们想要“先比较第一个字段，当第一个字段的值相等时再比较第二个字段”的初衷是相悖的！

3. 笔者拓展完善的topn()函数

为了解决第二节中pandas官方文档无法进行多字段排序的问题，笔者对pandas官方文档的解决方法进行了的拓展并将整个分组排序过程封装为一个函数，该函数可完美支持是否保留并列、多字段分组、多字段排序的需求。用户在使用时，只需给定下面的参数，便可以得到经过分组top n排序后的、带排名列的DataFrame：

  • df: 包含全部数据的DataFrame
  • n: top n的n（int类型）
  • group_field: 分组字段（单字段分组时为str，多字段分组时为str组成的list）
  • sort_field: 排序字段（单字段排序时为str，多字段排序时为str组成的list）
  • rank_label: 排名结果字段名称（str），默认值为‘ranking‘
  • ascending: 是否升序（布尔值），默认为False（降序）
  • keep_tie: 是否保留并列的记录（布尔值），默认为False（不保留）

此外，由于pandas提供的GITHUB上的数据集下载链接已失效，同时也为方便演示及读者理解，本文接下来的部分将使用自建数据集，该数据集是一张有编号（id）、姓名（name）、性别（gender）、课程（course）、考试得分（score）、作业评分（homework）共6个字段、30条记录的成绩表。在本文接下来的示例中，当使用单字段分组时，默认使用course进行分组，而使用多字段分组时，默认使用course和gender分组；当使用单字段排序时，默认使用score排序，而使用多字段排序时，默认使用score和homework排序。全部数据如下所示：

id name gender course score homework
1 甲 男 语文 70 5
2 乙 男 语文 85 5
3 丙 男 语文 85 4
4 丁 男 语文 70 5
5 戊 男 语文 95 4
6 己 女 语文 60 4
7 庚 女 语文 70 5
8 辛 女 语文 95 5
9 壬 女 语文 55 4
10 癸 女 语文 80 5
11 甲 男 数学 55 5
12 乙 男 数学 90 5
13 丙 男 数学 80 4
14 丁 男 数学 90 5
15 戊 男 数学 90 5
16 己 女 数学 75 4
17 庚 女 数学 60 4
18 辛 女 数学 60 4
19 壬 女 数学 85 5
20 癸 女 数学 85 4
21 甲 男 英语 50 4
22 乙 男 英语 90 5
23 丙 男 英语 90 4
24 丁 男 英语 70 4
25 戊 男 英语 70 5
26 己 女 英语 85 4
27 庚 女 英语 90 5
28 辛 女 英语 95 5
29 壬 女 英语 60 4
30 癸 女 英语 90 4

笔者拓展完善的topn()函数：

import numpy as np
import pandas as pd

# 创建DataFrame
transcript = [(1, '甲', '男', '语文', 70, 5),
              (2, '乙', '男', '语文', 85, 5),
              (3, '丙', '男', '语文', 85, 4),
              (4, '丁', '男', '语文', 70, 5),
              (5, '戊', '男', '语文', 95, 4),
              (6, '己', '女', '语文', 60, 4),
              (7, '庚', '女', '语文', 70, 5),
              (8, '辛', '女', '语文', 95, 5),
              (9, '壬', '女', '语文', 55, 4),
              (10, '癸', '女', '语文', 80, 5),
              (11, '甲', '男', '数学', 55, 5),
              (12, '乙', '男', '数学', 90, 5),
              (13, '丙', '男', '数学', 80, 4),
              (14, '丁', '男', '数学', 90, 5),
              (15, '戊', '男', '数学', 90, 5),
              (16, '己', '女', '数学', 75, 4),
              (17, '庚', '女', '数学', 60, 4),
              (18, '辛', '女', '数学', 60, 4),
              (19, '壬', '女', '数学', 85, 5),
              (20, '癸', '女', '数学', 85, 4),
              (21, '甲', '男', '英语', 50, 4),
              (22, '乙', '男', '英语', 90, 5),
              (23, '丙', '男', '英语', 90, 4),
              (24, '丁', '男', '英语', 70, 4),
              (25, '戊', '男', '英语', 70, 5),
              (26, '己', '女', '英语', 85, 4),
              (27, '庚', '女', '英语', 90, 5),
              (28, '辛', '女', '英语', 95, 5),
              (29, '壬', '女', '英语', 60, 4),
              (30, '癸', '女', '英语', 90, 4)]
df = pd.DataFrame(transcript, columns=['id', 'name', 'gender', 'course', 'score', 'homework'])
df = df.set_index(['id'])  # 设定行标签索引

# 主函数
def topn(df, n, group_field, sort_field, rank_label='ranking', ascending=False, keep_tie=False):
    """
    :param df: 包含全部数据的DataFrame
    :param n: top n的n(int类型)
    :param group_field: 分组字段(str或str组成的list)
    :param sort_field: 排序字段(str或str组成的list)
    :param rank_label: 排名结果字段名称(str)
    :param ascending: 是否升序，默认为False(降序)
    :param keep_tie: 是否保留并列的记录，默认为False(不保留)
    :return: 按照top n分组排序后的DataFrame
    """

    # 如果不保留并列的记录
    if not keep_tie:

        # 由于关键字参数的名称本身是个变量，因此这里采用**kwargs的方式传参，下同
        df = df.assign(**{
            rank_label: df.sort_values(sort_field, ascending=ascending).groupby(group_field).cumcount() + 1
        })

    # 如果保留并列的记录
    else:

        # 产生辅助列标签：先校验'__ngroup'，若df已有此列标签，则依次校验'__ngroup1'、'__ngroup2'...直到找到一个df不存在的列标签
        ngroup_col = '__ngroup'
        num = 1
        while ngroup_col in df.columns:
            ngroup_col = '__ngroup' + str(num)
            num += 1

        # 将多字段排序通过ngroup()方法等价转化为辅助列ngroup_col上的单字段排序，由于转化的过程会导致排序反转，所以应对ascending取反，即ascending=~ascending
        df = df.assign(**{
            ngroup_col: df.groupby(sort_field).ngroup(ascending=~ascending)
        })

        # 添加排名结果列rank_label之后，删除辅助列ngroup_col
        df = df.assign(**{
            rank_label: df.groupby(group_field)[ngroup_col].rank(method='min', ascending=ascending)
        }).drop(ngroup_col, axis=1)

        # 将rank_label列的数据类型转化为int，若不转化将是1.0、2.0这样的float型，不美观
        df[rank_label] = df[rank_label].astype('int')

    if isinstance(group_field, str):
        return df[df[rank_label] <= n].sort_values([group_field] + [rank_label])
    elif isinstance(group_field, list):
        return df[df[rank_label] <= n].sort_values(group_field + [rank_label])
    else:
        raise ValueError('The type of group_field must be str or list.')

执行结果演示：

'''
n=3、单字段分组、单字段排序、不保留并列
ret = topn(df, n=3, group_field='course', sort_field='score', rank_label='ranking', ascending=False, keep_tie=False)
print(ret)
'''
   name gender course  score  homework  ranking
id                                             
15    戊      男     数学     90         5        1
14    丁      男     数学     90         5        2
12    乙      男     数学     90         5        3
28    辛      女     英语     95         5        1
30    癸      女     英语     90         4        2
22    乙      男     英语     90         5        3
5     戊      男     语文     95         4        1
8     辛      女     语文     95         5        2
3     丙      男     语文     85         4        3
'''
n=2、多字段分组、多字段排序、保留并列
ret = topn(df, n=2, group_field=['course', 'gender'], sort_field=['score','homework'], rank_label='排名', ascending=False, keep_tie=True)
print(ret)
'''
   name gender course  score  homework  排名
id                                        
19    壬      女     数学     85         5   1
20    癸      女     数学     85         4   2
12    乙      男     数学     90         5   1
14    丁      男     数学     90         5   1
15    戊      男     数学     90         5   1
28    辛      女     英语     95         5   1
27    庚      女     英语     90         5   2
22    乙      男     英语     90         5   1
23    丙      男     英语     90         4   2
8     辛      女     语文     95         5   1
10    癸      女     语文     80         5   2
5     戊      男     语文     95         4   1
2     乙      男     语文     85         5   2

二、MySQL和Oracle的分组top n问题

1. 建表

MySQL建表语句：

CREATE TABLE transcript(
    id INT(11) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    gender ENUM('男','女') NOT NULL,
    course VARCHAR(20) NOT NULL,
    score INT(11) NOT NULL,
    homework INT(11) NOT NULL
);

Oracle建表语句：

CREATE TABLE transcript(
    id NUMBER(11) PRIMARY KEY,
    name VARCHAR2(20) NOT NULL,
    gender VARCHAR2(3) CHECK (gender IN ('男','女')),
    course VARCHAR2(20) NOT NULL,
    score NUMBER(11) NOT NULL,
    homework NUMBER(11) NOT NULL
);

2. 添加数据

MySQL添加数据语句：

INSERT INTO transcript VALUES
(1, '甲', '男', '语文', 70, 5),
(2, '乙', '男', '语文', 85, 5),
(3, '丙', '男', '语文', 85, 4),
(4, '丁', '男', '语文', 70, 5),
(5, '戊', '男', '语文', 95, 4),
(6, '己', '女', '语文', 60, 4),
(7, '庚', '女', '语文', 70, 5),
(8, '辛', '女', '语文', 95, 5),
(9, '壬', '女', '语文', 55, 4),
(10, '癸', '女', '语文', 80, 5),
(11, '甲', '男', '数学', 55, 5),
(12, '乙', '男', '数学', 90, 5),
(13, '丙', '男', '数学', 80, 4),
(14, '丁', '男', '数学', 90, 5),
(15, '戊', '男', '数学', 90, 5),
(16, '己', '女', '数学', 75, 4),
(17, '庚', '女', '数学', 60, 4),
(18, '辛', '女', '数学', 60, 4),
(19, '壬', '女', '数学', 85, 5),
(20, '癸', '女', '数学', 85, 4),
(21, '甲', '男', '英语', 50, 4),
(22, '乙', '男', '英语', 90, 5),
(23, '丙', '男', '英语', 90, 4),
(24, '丁', '男', '英语', 70, 4),
(25, '戊', '男', '英语', 70, 5),
(26, '己', '女', '英语', 85, 4),
(27, '庚', '女', '英语', 90, 5),
(28, '辛', '女', '英语', 95, 5),
(29, '壬', '女', '英语', 60, 4),
(30, '癸', '女', '英语', 90, 4);

Oracle添加数据语句：和MySQL不同，Oracle需要逐条插入数据，并且手动使用COMMIT提交事务

INSERT INTO transcript VALUES (1, '甲', '男', '语文', 70, 5);
INSERT INTO transcript VALUES (2, '乙', '男', '语文', 85, 5);
INSERT INTO transcript VALUES (3, '丙', '男', '语文', 85, 4);
INSERT INTO transcript VALUES (4, '丁', '男', '语文', 70, 5);
INSERT INTO transcript VALUES (5, '戊', '男', '语文', 95, 4);
INSERT INTO transcript VALUES (6, '己', '女', '语文', 60, 4);
INSERT INTO transcript VALUES (7, '庚', '女', '语文', 70, 5);
INSERT INTO transcript VALUES (8, '辛', '女', '语文', 95, 5);
INSERT INTO transcript VALUES (9, '壬', '女', '语文', 55, 4);
INSERT INTO transcript VALUES (10, '癸', '女', '语文', 80, 5);
INSERT INTO transcript VALUES (11, '甲', '男', '数学', 55, 5);
INSERT INTO transcript VALUES (12, '乙', '男', '数学', 90, 5);
INSERT INTO transcript VALUES (13, '丙', '男', '数学', 80, 4);
INSERT INTO transcript VALUES (14, '丁', '男', '数学', 90, 5);
INSERT INTO transcript VALUES (15, '戊', '男', '数学', 90, 5);
INSERT INTO transcript VALUES (16, '己', '女', '数学', 75, 4);
INSERT INTO transcript VALUES (17, '庚', '女', '数学', 60, 4);
INSERT INTO transcript VALUES (18, '辛', '女', '数学', 60, 4);
INSERT INTO transcript VALUES (19, '壬', '女', '数学', 85, 5);
INSERT INTO transcript VALUES (20, '癸', '女', '数学', 85, 4);
INSERT INTO transcript VALUES (21, '甲', '男', '英语', 50, 4);
INSERT INTO transcript VALUES (22, '乙', '男', '英语', 90, 5);
INSERT INTO transcript VALUES (23, '丙', '男', '英语', 90, 4);
INSERT INTO transcript VALUES (24, '丁', '男', '英语', 70, 4);
INSERT INTO transcript VALUES (25, '戊', '男', '英语', 70, 5);
INSERT INTO transcript VALUES (26, '己', '女', '英语', 85, 4);
INSERT INTO transcript VALUES (27, '庚', '女', '英语', 90, 5);
INSERT INTO transcript VALUES (28, '辛', '女', '英语', 95, 5);
INSERT INTO transcript VALUES (29, '壬', '女', '英语', 60, 4);
INSERT INTO transcript VALUES (30, '癸', '女', '英语', 90, 4);
COMMIT;

3. 预览数据

SELECT * FROM transcript;

+----+------+--------+--------+-------+----------+
| id | name | gender | course | score | homework |
+----+------+--------+--------+-------+----------+
|  1 | 甲   | 男     | 语文   |    70 |        5 |
|  2 | 乙   | 男     | 语文   |    85 |        5 |
|  3 | 丙   | 男     | 语文   |    85 |        4 |
|  4 | 丁   | 男     | 语文   |    70 |        5 |
|  5 | 戊   | 男     | 语文   |    95 |        4 |
|  6 | 己   | 女     | 语文   |    60 |        4 |
|  7 | 庚   | 女     | 语文   |    70 |        5 |
|  8 | 辛   | 女     | 语文   |    95 |        5 |
|  9 | 壬   | 女     | 语文   |    55 |        4 |
| 10 | 癸   | 女     | 语文   |    80 |        5 |
| 11 | 甲   | 男     | 数学   |    55 |        5 |
| 12 | 乙   | 男     | 数学   |    90 |        5 |
| 13 | 丙   | 男     | 数学   |    80 |        4 |
| 14 | 丁   | 男     | 数学   |    90 |        5 |
| 15 | 戊   | 男     | 数学   |    90 |        5 |
| 16 | 己   | 女     | 数学   |    75 |        4 |
| 17 | 庚   | 女     | 数学   |    60 |        4 |
| 18 | 辛   | 女     | 数学   |    60 |        4 |
| 19 | 壬   | 女     | 数学   |    85 |        5 |
| 20 | 癸   | 女     | 数学   |    85 |        4 |
| 21 | 甲   | 男     | 英语   |    50 |        4 |
| 22 | 乙   | 男     | 英语   |    90 |        5 |
| 23 | 丙   | 男     | 英语   |    90 |        4 |
| 24 | 丁   | 男     | 英语   |    70 |        4 |
| 25 | 戊   | 男     | 英语   |    70 |        5 |
| 26 | 己   | 女     | 英语   |    85 |        4 |
| 27 | 庚   | 女     | 英语   |    90 |        5 |
| 28 | 辛   | 女     | 英语   |    95 |        5 |
| 29 | 壬   | 女     | 英语   |    60 |        4 |
| 30 | 癸   | 女     | 英语   |    90 |        4 |
+----+------+--------+--------+-------+----------+
30 rows in set (0.00 sec)

4. 方法一：基于开窗函数（简便）

OVER(PARTITION BY...)是SQL中分析性函数的一部分（MySQL 8.0以上版本及Oracle均支持），用于给结果集进行开窗分区。它和聚合函数GROUP BY不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而GROUP BY是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。

分组top n问题的SQL通解形式为：

SELECT * FROM (
    SELECT t.*, 排序函数() OVER (
        PARTITION BY 分组字段 ORDER BY 排序字段及排序方式
    ) AS ranking
    FROM 表名 t
)
WHERE ranking <= n
ORDER BY 分组字段, ranking;

其中，子查询的排序函数()可以选取ROW_NUMBER()、RANK()、DENSE_RANK()三种形式，分别对应了不同的排序方式。

注意：使用排序函数() OVER()的时候，空值null是最大的，因此如果排序字段有null，可能造成null字段排在最前面，影响排序结果，对此可以在OVER()子句的结尾加入NULLS LAST，即OVER(PARTITION BY ... ORDER BY ... NULLS LAST)。

下面，分别对三种不同的排序函数进行演示：

（1）ROW_NUMBER()

连续自增（如1、2、3、4、5），适用于不保留并列的情况

-- 注意：此例仅展示了子查询选取ROW_NUMBER()排序函数的查询结果
SELECT t.*, ROW_NUMBER() OVER (
    PARTITION BY course,gender ORDER BY score DESC, homework DESC
) AS ranking
FROM transcript t;

执行结果：
id name gender course score homework ranking    
19  壬   女   数学  85  5   1
20  癸   女   数学  85  4   2
16  己   女   数学  75  4   3
18  辛   女   数学  60  4   4
17  庚   女   数学  60  4   5
15  戊   男   数学  90  5   1
12  乙   男   数学  90  5   2
14  丁   男   数学  90  5   3
13  丙   男   数学  80  4   4
11  甲   男   数学  55  5   5
28  辛   女   英语  95  5   1
27  庚   女   英语  90  5   2
30  癸   女   英语  90  4   3
26  己   女   英语  85  4   4
29  壬   女   英语  60  4   5
22  乙   男   英语  90  5   1
23  丙   男   英语  90  4   2
25  戊   男   英语  70  5   3
24  丁   男   英语  70  4   4
21  甲   男   英语  50  4   5
8   辛   女   语文  95  5   1
10  癸   女   语文  80  5   2
7   庚   女   语文  70  5   3
6   己   女   语文  60  4   4
9   壬   女   语文  55  4   5
5   戊   男   语文  95  4   1
2   乙   男   语文  85  5   2
3   丙   男   语文  85  4   3
1   甲   男   语文  70  5   4
4   丁   男   语文  70  5   5

（2）RANK()

跳跃排序（如1、1、1、4、5），适用于保留并列的情况

-- 注意：此例仅展示了子查询选取RANK()排序函数的查询结果
SELECT t.*, RANK() OVER (
    PARTITION BY course,gender ORDER BY score DESC, homework DESC
) AS ranking
FROM transcript t;

执行结果：
id name gender course score homework ranking
19  壬   女   数学  85  5   1
20  癸   女   数学  85  4   2
16  己   女   数学  75  4   3
18  辛   女   数学  60  4   4
17  庚   女   数学  60  4   4
15  戊   男   数学  90  5   1
12  乙   男   数学  90  5   1
14  丁   男   数学  90  5   1
13  丙   男   数学  80  4   4
11  甲   男   数学  55  5   5
28  辛   女   英语  95  5   1
27  庚   女   英语  90  5   2
30  癸   女   英语  90  4   3
26  己   女   英语  85  4   4
29  壬   女   英语  60  4   5
22  乙   男   英语  90  5   1
23  丙   男   英语  90  4   2
25  戊   男   英语  70  5   3
24  丁   男   英语  70  4   4
21  甲   男   英语  50  4   5
8   辛   女   语文  95  5   1
10  癸   女   语文  80  5   2
7   庚   女   语文  70  5   3
6   己   女   语文  60  4   4
9   壬   女   语文  55  4   5
5   戊   男   语文  95  4   1
2   乙   男   语文  85  5   2
3   丙   男   语文  85  4   3
1   甲   男   语文  70  5   4
4   丁   男   语文  70  5   4

（3）DENSE_RANK()

不跳跃排序（如1、1、1、2、3），用得较少

-- 注意：此例仅展示了子查询选取DENSE_RANK()排序函数的查询结果
SELECT t.*, DENSE_RANK() OVER (
    PARTITION BY course,gender ORDER BY score DESC, homework DESC
) AS ranking
FROM transcript t;

执行结果：
id name gender course score homework ranking
19  壬   女   数学  85  5   1
20  癸   女   数学  85  4   2
16  己   女   数学  75  4   3
18  辛   女   数学  60  4   4
17  庚   女   数学  60  4   4
15  戊   男   数学  90  5   1
12  乙   男   数学  90  5   1
14  丁   男   数学  90  5   1
13  丙   男   数学  80  4   2
11  甲   男   数学  55  5   3
28  辛   女   英语  95  5   1
27  庚   女   英语  90  5   2
30  癸   女   英语  90  4   3
26  己   女   英语  85  4   4
29  壬   女   英语  60  4   5
22  乙   男   英语  90  5   1
23  丙   男   英语  90  4   2
25  戊   男   英语  70  5   3
24  丁   男   英语  70  4   4
21  甲   男   英语  50  4   5
8   辛   女   语文  95  5   1
10  癸   女   语文  80  5   2
7   庚   女   语文  70  5   3
6   己   女   语文  60  4   4
9   壬   女   语文  55  4   5
5   戊   男   语文  95  4   1
2   乙   男   语文  85  5   2
3   丙   男   语文  85  4   3
1   甲   男   语文  70  5   4
4   丁   男   语文  70  5   4

5. 方法二：基于自身左连接（复杂，适用范围受限）

适用范围：该方法适用于单字段排序且保留并列的情况

（1）准备工作：基于自身左连接构造辅助表，观察其结构

通过观察可知，t1中第1名所在行的count(t2.id)为0（因为没有比它大的）、t1中第2名所在行的count(t2.id)为1（因为只有1个比它大的）……t1中第n名所在行的count(t2.id)为n-1

SELECT t1.id, t1.name, t1.course, t1.score, t2.id, t2.name, t2.course, t2.score
FROM transcript t1 LEFT JOIN transcript t2 on t1.course = t2.course AND t1.score < t2.score
ORDER BY t1.course, t1.score DESC;

+----+------+--------+-------+------+------+--------+-------+
| id | name | course | score | id   | name | course | score |
+----+------+--------+-------+------+------+--------+-------+
| 12 | 乙   | 数学   |    90 | NULL | NULL | NULL   |  NULL |
| 14 | 丁   | 数学   |    90 | NULL | NULL | NULL   |  NULL |
| 15 | 戊   | 数学   |    90 | NULL | NULL | NULL   |  NULL |
| 19 | 壬   | 数学   |    85 |   12 | 乙   | 数学   |    90 |
| 20 | 癸   | 数学   |    85 |   12 | 乙   | 数学   |    90 |
| 19 | 壬   | 数学   |    85 |   14 | 丁   | 数学   |    90 |
| 20 | 癸   | 数学   |    85 |   14 | 丁   | 数学   |    90 |
| 19 | 壬   | 数学   |    85 |   15 | 戊   | 数学   |    90 |
| 20 | 癸   | 数学   |    85 |   15 | 戊   | 数学   |    90 |
| 13 | 丙   | 数学   |    80 |   20 | 癸   | 数学   |    85 |
| 13 | 丙   | 数学   |    80 |   14 | 丁   | 数学   |    90 |
| 13 | 丙   | 数学   |    80 |   15 | 戊   | 数学   |    90 |
| 13 | 丙   | 数学   |    80 |   19 | 壬   | 数学   |    85 |
| 13 | 丙   | 数学   |    80 |   12 | 乙   | 数学   |    90 |
| 16 | 己   | 数学   |    75 |   12 | 乙   | 数学   |    90 |
| 16 | 己   | 数学   |    75 |   20 | 癸   | 数学   |    85 |
| 16 | 己   | 数学   |    75 |   13 | 丙   | 数学   |    80 |
| 16 | 己   | 数学   |    75 |   14 | 丁   | 数学   |    90 |
| 16 | 己   | 数学   |    75 |   15 | 戊   | 数学   |    90 |
| 16 | 己   | 数学   |    75 |   19 | 壬   | 数学   |    85 |
| 18 | 辛   | 数学   |    60 |   19 | 壬   | 数学   |    85 |
| 17 | 庚   | 数学   |    60 |   12 | 乙   | 数学   |    90 |
| 18 | 辛   | 数学   |    60 |   12 | 乙   | 数学   |    90 |
| 17 | 庚   | 数学   |    60 |   20 | 癸   | 数学   |    85 |
| 18 | 辛   | 数学   |    60 |   20 | 癸   | 数学   |    85 |
| 17 | 庚   | 数学   |    60 |   13 | 丙   | 数学   |    80 |
| 18 | 辛   | 数学   |    60 |   13 | 丙   | 数学   |    80 |
| 17 | 庚   | 数学   |    60 |   14 | 丁   | 数学   |    90 |
| 18 | 辛   | 数学   |    60 |   14 | 丁   | 数学   |    90 |
| 17 | 庚   | 数学   |    60 |   15 | 戊   | 数学   |    90 |
| 18 | 辛   | 数学   |    60 |   15 | 戊   | 数学   |    90 |
| 17 | 庚   | 数学   |    60 |   16 | 己   | 数学   |    75 |
| 18 | 辛   | 数学   |    60 |   16 | 己   | 数学   |    75 |
| 17 | 庚   | 数学   |    60 |   19 | 壬   | 数学   |    85 |
| 11 | 甲   | 数学   |    55 |   20 | 癸   | 数学   |    85 |
| 11 | 甲   | 数学   |    55 |   13 | 丙   | 数学   |    80 |
| 11 | 甲   | 数学   |    55 |   14 | 丁   | 数学   |    90 |
| 11 | 甲   | 数学   |    55 |   15 | 戊   | 数学   |    90 |
| 11 | 甲   | 数学   |    55 |   16 | 己   | 数学   |    75 |
| 11 | 甲   | 数学   |    55 |   17 | 庚   | 数学   |    60 |
| 11 | 甲   | 数学   |    55 |   18 | 辛   | 数学   |    60 |
| 11 | 甲   | 数学   |    55 |   19 | 壬   | 数学   |    85 |
| 11 | 甲   | 数学   |    55 |   12 | 乙   | 数学   |    90 |
| 28 | 辛   | 英语   |    95 | NULL | NULL | NULL   |  NULL |
| 27 | 庚   | 英语   |    90 |   28 | 辛   | 英语   |    95 |
| 30 | 癸   | 英语   |    90 |   28 | 辛   | 英语   |    95 |
| 22 | 乙   | 英语   |    90 |   28 | 辛   | 英语   |    95 |
| 23 | 丙   | 英语   |    90 |   28 | 辛   | 英语   |    95 |
| 26 | 己   | 英语   |    85 |   28 | 辛   | 英语   |    95 |
| 26 | 己   | 英语   |    85 |   22 | 乙   | 英语   |    90 |
| 26 | 己   | 英语   |    85 |   30 | 癸   | 英语   |    90 |
| 26 | 己   | 英语   |    85 |   23 | 丙   | 英语   |    90 |
| 26 | 己   | 英语   |    85 |   27 | 庚   | 英语   |    90 |
| 24 | 丁   | 英语   |    70 |   28 | 辛   | 英语   |    95 |
| 25 | 戊   | 英语   |    70 |   28 | 辛   | 英语   |    95 |
| 24 | 丁   | 英语   |    70 |   22 | 乙   | 英语   |    90 |
| 25 | 戊   | 英语   |    70 |   22 | 乙   | 英语   |    90 |
| 24 | 丁   | 英语   |    70 |   30 | 癸   | 英语   |    90 |
| 25 | 戊   | 英语   |    70 |   30 | 癸   | 英语   |    90 |
| 24 | 丁   | 英语   |    70 |   23 | 丙   | 英语   |    90 |
| 25 | 戊   | 英语   |    70 |   23 | 丙   | 英语   |    90 |
| 24 | 丁   | 英语   |    70 |   26 | 己   | 英语   |    85 |
| 25 | 戊   | 英语   |    70 |   26 | 己   | 英语   |    85 |
| 24 | 丁   | 英语   |    70 |   27 | 庚   | 英语   |    90 |
| 25 | 戊   | 英语   |    70 |   27 | 庚   | 英语   |    90 |
| 29 | 壬   | 英语   |    60 |   28 | 辛   | 英语   |    95 |
| 29 | 壬   | 英语   |    60 |   22 | 乙   | 英语   |    90 |
| 29 | 壬   | 英语   |    60 |   30 | 癸   | 英语   |    90 |
| 29 | 壬   | 英语   |    60 |   23 | 丙   | 英语   |    90 |
| 29 | 壬   | 英语   |    60 |   24 | 丁   | 英语   |    70 |
| 29 | 壬   | 英语   |    60 |   25 | 戊   | 英语   |    70 |
| 29 | 壬   | 英语   |    60 |   26 | 己   | 英语   |    85 |
| 29 | 壬   | 英语   |    60 |   27 | 庚   | 英语   |    90 |
| 21 | 甲   | 英语   |    50 |   22 | 乙   | 英语   |    90 |
| 21 | 甲   | 英语   |    50 |   29 | 壬   | 英语   |    60 |
| 21 | 甲   | 英语   |    50 |   30 | 癸   | 英语   |    90 |
| 21 | 甲   | 英语   |    50 |   23 | 丙   | 英语   |    90 |
| 21 | 甲   | 英语   |    50 |   24 | 丁   | 英语   |    70 |
| 21 | 甲   | 英语   |    50 |   25 | 戊   | 英语   |    70 |
| 21 | 甲   | 英语   |    50 |   26 | 己   | 英语   |    85 |
| 21 | 甲   | 英语   |    50 |   27 | 庚   | 英语   |    90 |
| 21 | 甲   | 英语   |    50 |   28 | 辛   | 英语   |    95 |
|  5 | 戊   | 语文   |    95 | NULL | NULL | NULL   |  NULL |
|  8 | 辛   | 语文   |    95 | NULL | NULL | NULL   |  NULL |
|  2 | 乙   | 语文   |    85 |    5 | 戊   | 语文   |    95 |
|  3 | 丙   | 语文   |    85 |    5 | 戊   | 语文   |    95 |
|  2 | 乙   | 语文   |    85 |    8 | 辛   | 语文   |    95 |
|  3 | 丙   | 语文   |    85 |    8 | 辛   | 语文   |    95 |
| 10 | 癸   | 语文   |    80 |    3 | 丙   | 语文   |    85 |
| 10 | 癸   | 语文   |    80 |    5 | 戊   | 语文   |    95 |
| 10 | 癸   | 语文   |    80 |    8 | 辛   | 语文   |    95 |
| 10 | 癸   | 语文   |    80 |    2 | 乙   | 语文   |    85 |
|  7 | 庚   | 语文   |    70 |    3 | 丙   | 语文   |    85 |
|  1 | 甲   | 语文   |    70 |    5 | 戊   | 语文   |    95 |
|  4 | 丁   | 语文   |    70 |    5 | 戊   | 语文   |    95 |
|  7 | 庚   | 语文   |    70 |    5 | 戊   | 语文   |    95 |
|  1 | 甲   | 语文   |    70 |    8 | 辛   | 语文   |    95 |
|  4 | 丁   | 语文   |    70 |    8 | 辛   | 语文   |    95 |
|  7 | 庚   | 语文   |    70 |    8 | 辛   | 语文   |    95 |
|  1 | 甲   | 语文   |    70 |    2 | 乙   | 语文   |    85 |
|  1 | 甲   | 语文   |    70 |   10 | 癸   | 语文   |    80 |
|  4 | 丁   | 语文   |    70 |    2 | 乙   | 语文   |    85 |
|  4 | 丁   | 语文   |    70 |   10 | 癸   | 语文   |    80 |
|  7 | 庚   | 语文   |    70 |    2 | 乙   | 语文   |    85 |
|  7 | 庚   | 语文   |    70 |   10 | 癸   | 语文   |    80 |
|  1 | 甲   | 语文   |    70 |    3 | 丙   | 语文   |    85 |
|  4 | 丁   | 语文   |    70 |    3 | 丙   | 语文   |    85 |
|  6 | 己   | 语文   |    60 |    3 | 丙   | 语文   |    85 |
|  6 | 己   | 语文   |    60 |    4 | 丁   | 语文   |    70 |
|  6 | 己   | 语文   |    60 |    5 | 戊   | 语文   |    95 |
|  6 | 己   | 语文   |    60 |    7 | 庚   | 语文   |    70 |
|  6 | 己   | 语文   |    60 |    8 | 辛   | 语文   |    95 |
|  6 | 己   | 语文   |    60 |    1 | 甲   | 语文   |    70 |
|  6 | 己   | 语文   |    60 |    2 | 乙   | 语文   |    85 |
|  6 | 己   | 语文   |    60 |   10 | 癸   | 语文   |    80 |
|  9 | 壬   | 语文   |    55 |    3 | 丙   | 语文   |    85 |
|  9 | 壬   | 语文   |    55 |    4 | 丁   | 语文   |    70 |
|  9 | 壬   | 语文   |    55 |    5 | 戊   | 语文   |    95 |
|  9 | 壬   | 语文   |    55 |    6 | 己   | 语文   |    60 |
|  9 | 壬   | 语文   |    55 |    7 | 庚   | 语文   |    70 |
|  9 | 壬   | 语文   |    55 |    8 | 辛   | 语文   |    95 |
|  9 | 壬   | 语文   |    55 |    1 | 甲   | 语文   |    70 |
|  9 | 壬   | 语文   |    55 |    2 | 乙   | 语文   |    85 |
|  9 | 壬   | 语文   |    55 |   10 | 癸   | 语文   |    80 |
+----+------+--------+-------+------+------+--------+-------+
124 rows in set (0.00 sec)

（2）单字段分组、单字段排序、保留并列的SQL查询语句

在上面辅助表的基础上稍加修改，便得到了下面的SQL查询语句：

-- 查询每科score的前n名（分组：course，排序：score）
SELECT t1.name, t1.course, t1.score, COUNT(t2.id)+1 AS rank
FROM transcript t1 LEFT JOIN transcript t2 on t1.course = t2.course AND t1.score < t2.score
GROUP BY t1.name, t1.course, t1.score
HAVING COUNT(t2.id) < 【n】
ORDER BY t1.course, t1.score DESC;

对不同的n，修改【n】值即可，查询top1则将其改为1，查询top2则将其改为2……以此类推

top1查询结果：

+------+--------+-------+------+
| name | course | score | rank |
+------+--------+-------+------+
| 乙   | 数学   |    90 |    1 |
| 戊   | 数学   |    90 |    1 |
| 丁   | 数学   |    90 |    1 |
| 辛   | 英语   |    95 |    1 |
| 戊   | 语文   |    95 |    1 |
| 辛   | 语文   |    95 |    1 |
+------+--------+-------+------+
6 rows in set (0.00 sec)

top2查询结果：

+------+--------+-------+------+
| name | course | score | rank |
+------+--------+-------+------+
| 乙   | 数学   |    90 |    1 |
| 戊   | 数学   |    90 |    1 |
| 丁   | 数学   |    90 |    1 |
| 辛   | 英语   |    95 |    1 |
| 庚   | 英语   |    90 |    2 |
| 丙   | 英语   |    90 |    2 |
| 癸   | 英语   |    90 |    2 |
| 乙   | 英语   |    90 |    2 |
| 戊   | 语文   |    95 |    1 |
| 辛   | 语文   |    95 |    1 |
+------+--------+-------+------+
10 rows in set (0.00 sec)

如果我们想查询的不是每科的前n名，而是后n名（“前n名”指是分组后每科成绩降序排序的前n名，“后n名”则指分组后每科成绩升序排序的前n名），那么和前面的SQL查询语句相比，需要做如下两处改动：

  • 将FROM一行的t1.score < t2.score改为t1.score > t2.score
  • 将ORDER BY一行的t1.score DESC改为t1.score ASC

需要特别注意的是，HAVING一行的<应保持不变

-- 查询每科score的前n名（分组：course，排序：score）
SELECT t1.name, t1.course, t1.score, COUNT(t2.id)+1 AS rank
FROM transcript t1 LEFT JOIN transcript t2 on t1.course = t2.course AND t1.score > t2.score
GROUP BY t1.name, t1.course, t1.score
HAVING COUNT(t2.id) < 【n】
ORDER BY t1.course, t1.score ASC;

top1查询结果：

+------+--------+-------+------+
| name | course | score | rank |
+------+--------+-------+------+
| 甲   | 数学   |    55 |    1 |
| 甲   | 英语   |    50 |    1 |
| 壬   | 语文   |    55 |    1 |
+------+--------+-------+------+
3 rows in set (0.00 sec)

top2查询结果：

+------+--------+-------+------+
| name | course | score | rank |
+------+--------+-------+------+
| 甲   | 数学   |    55 |    1 |
| 辛   | 数学   |    60 |    2 |
| 庚   | 数学   |    60 |    2 |
| 甲   | 英语   |    50 |    1 |
| 壬   | 英语   |    60 |    2 |
| 壬   | 语文   |    55 |    1 |
| 己   | 语文   |    60 |    2 |
+------+--------+-------+------+
7 rows in set (0.00 sec)

（3）多字段分组、单字段排序、保留并列的SQL查询语句

由单自段分组变为多字段分组时，需要做下面的改动：

  • 在FROM一行以AND形式增加条件t1.gender = t2.gender
  • 在SELECT、GROUP BY、ORDER BY的对应位置新增字段t1.gender
-- 查询每种“course,gender”组合下的score前n名（分组：course和gender，排序：score）
SELECT t1.name, t1.gender, t1.course, t1.score, COUNT(t2.id)+1 AS rank
FROM transcript t1 LEFT JOIN transcript t2 on t1.course = t2.course AND t1.gender = t2.gender AND t1.score < t2.score
GROUP BY t1.name, t1.gender, t1.course, t1.score
HAVING COUNT(t2.id) < 【n】
ORDER BY t1.course, t1.gender, t1.score DESC;

top1查询结果：

+------+--------+--------+-------+------+
| name | gender | course | score | rank |
+------+--------+--------+-------+------+
| 丁   | 男     | 数学   |    90 |    1 |
| 乙   | 男     | 数学   |    90 |    1 |
| 戊   | 男     | 数学   |    90 |    1 |
| 癸   | 女     | 数学   |    85 |    1 |
| 壬   | 女     | 数学   |    85 |    1 |
| 丙   | 男     | 英语   |    90 |    1 |
| 乙   | 男     | 英语   |    90 |    1 |
| 辛   | 女     | 英语   |    95 |    1 |
| 戊   | 男     | 语文   |    95 |    1 |
| 辛   | 女     | 语文   |    95 |    1 |
+------+--------+--------+-------+------+
10 rows in set (0.00 sec)

top2查询结果：

+------+--------+--------+-------+------+
| name | gender | course | score | rank |
+------+--------+--------+-------+------+
| 丁   | 男     | 数学   |    90 |    1 |
| 乙   | 男     | 数学   |    90 |    1 |
| 戊   | 男     | 数学   |    90 |    1 |
| 癸   | 女     | 数学   |    85 |    1 |
| 壬   | 女     | 数学   |    85 |    1 |
| 丙   | 男     | 英语   |    90 |    1 |
| 乙   | 男     | 英语   |    90 |    1 |
| 辛   | 女     | 英语   |    95 |    1 |
| 庚   | 女     | 英语   |    90 |    2 |
| 癸   | 女     | 英语   |    90 |    2 |
| 戊   | 男     | 语文   |    95 |    1 |
| 丙   | 男     | 语文   |    85 |    2 |
| 乙   | 男     | 语文   |    85 |    2 |
| 辛   | 女     | 语文   |    95 |    1 |
| 癸   | 女     | 语文   |    80 |    2 |
+------+--------+--------+-------+------+
15 rows in set (0.00 sec)

pandas、MySQL、Oracle的分组top n问题研究

原文：https://www.cnblogs.com/oddgod/p/12354765.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶