group by 和分区函数Partition By的区别

我们一般常用的group by  顶多 在加一个   having 子句 ，现在介绍一个更神奇的

Partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。
partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

常用的函数：
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …) 求分组后的总数
max() over(partition by … order by …) 求分组后的最大值
min() over(partition by … order by …) 求分组后的最小值
sum() over(partition by … order by …) 求分组后的总和
avg() over(partition by … order by …) 求分组后的平均值
first_value() over(partition by … order by …) 求分组后的第一个值
last_value() over(partition by … order by …) 求分组后的最后一个值
lag() over(partition by … order by …) 取出分组后前n行数据
lead() over(partition by … order by …) 取出分组后后n行数据
一、rank()
rank() over(partition by A order by B)
是按照A进行分组，分组里面的数据按照B进行排序，over即在什么之上，rank()即跳跃排序(比如存在两个第一名，接下来就是第三名) 举例：

select 课程, 学生ID, 分数, rank() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表
查询结果：
课程    学生ID    分数    排名
语文    1        99        1
语文    5        99        1
语文    7        89        3
语文    9        79        4

二、row_number()
row_number() over(partition by A order by B)
row_number(): 如果有两个第一名时，只返回一个结果。 举例：

select 课程, 学生ID, 分数, row_number() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表
查询结果：

课程    学生ID    分数    排名
语文    1        99        1
语文    5        99        2
语文    7        89        3
语文    9        79        4

三、dense_rank()
dense_rank() over(partition by A order by B)
dense_rank(): 连续排序(如果有两个第一名时，接下来仍然是第二名) 举例：

select 课程, 学生ID, 分数, rank() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表
查询结果：

课程    学生ID    分数    排名
语文    1        99        1
语文    5        99        1
语文    7        89        2
语文    9        79        3
————————————————

还有介绍一个相关联的用法

     group_concat函数常用于select 语句中，下面我们通过一张表来讲解group_concat函数的用法。

select * from exam;

|id    |subject |student|teacher|score|
---------------------------------------
|1	   |数学    |小红	|王老师	|80   |
|2	   |数学    |小李	|王老师	|80   |
|3	   |数学    |小王	|王老师	|70   |
|4	   |数学    |小张	|王老师	|90   |
|5	   |数学    |小赵	|王老师	|70   |
|6	   |数学    |小孙	|王老师	|80   |
|7	   |数学    |小钱	|王老师	|90   |
|8	   |数学    |小高	|王老师	|70   |
|9	   |数学    |小秦	|王老师	|80   |
|10	   |数学    |小马	|王老师	|90   |
|11	   |数学    |小朱	|王老师	|90   |
|12	   |语文    |小高	|李老师	|70   |
|15	   |语文    |小秦	|李老师	|70   |
|18	   |语文    |小马	|李老师	|80   |
|21	   |语文    |小朱	|李老师	|90   |
|24	   |语文    |小钱	|李老师	|90   |

如果我们希望按分数score进行分组，并将分组后的学生姓名打印下来，就可以用group_concat实现。执行sql：

|score |group_concat(student)        |
-------------------------------------
|70       |小王,小赵,小高,小高,小秦        |
|80       |小红,小李,小孙,小秦,小马        |
|90       |小张,小钱,小马,小朱,小朱,小钱    |

 不难看出，在70分这一行有两条小高的记录，90分这一行有两条小钱和小朱的记录，如果我们需要去重，则需要给函数中加一个distinct参数：

select score,group_concat(distinct student) from exam group by score;

|score |group_concat(student)    |
---------------------------------
|70       |小王,小赵,小高,小秦        |
|80       |小红,小李,小孙,小秦,小马    |
|90       |小张,小钱,小马,小朱        |

     这样group_concat每行数据的结果中就没有了重复值，但是在数据中的分隔符为默认的逗号','，如果想修改默认的分隔符，只需要在上述指令中稍作修改：

select score,group_concat(distinct student separator '%') from exam group by score;

|score |group_concat(student)    |
---------------------------------
|70       |小王%小秦%小赵%小高        |
|80       |小孙%小李%小秦%小红%小马    |
|90       |小张%小朱%小钱%小马        |
 

版权声明：本文为CSDN博主「惊寂123」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/locken123/article/details/127411319