group by、partition by、listagg

group by是分组函数，partition by是分区函数， 有一组测试数据如下

SNAME FRUIT FNUM
张三 苹果 2
张三 苹果 2
张三 香蕉 3
李四 西瓜 2
王五 李子 2
王五 李子 4
李四 梨 4

 

1、group by根据指定的规则对数据进行分组

语法：group by A1,A2,A3,....，将相同的A1,A2,A3,...作为一组，统计数据

sql顺序from>where>group by>having>order by

 

a、以sname,fruit作为一组统计数据，select sname,fruit,count(*) from stest group by sname,fruit

 

b、如果要统计数字用sum函数，select sname,fruit,sum(fnum) from stest group by sname,fruit

得到sname有多少个某种fruit

 

c、注意：使用group by是查询的字段必须是包含在group by的后面，或者是包含在聚合函数如count，sum里

如select sname,fruit,count(fnum) from stest group by sname

fruit不是在group by分组依据里，也不是在聚合函数里，就会出现错误

即group by只保留参与分组的字段和聚合函数的结果

 

d、使用group by分组后对某个未统计的字段值拼接成字符串

 mysql用group_concat()，oracle11g版本以前使用wmsys.wm_concat()，未进行测试

oracle11g版本之后的使用listagg函数

 

语法：listagg(a,b)，第一个参数是需要拼接的字段，第二个参数是字段值之间的分隔符

listagg(a,b) within group (order by )...group by

select sname||'拥有'||listagg(fruit,',') within group (order by sname) ||'共'||sum(fnum)||'个' 说明
from (select sname,fruit,sum(fnum) fnum from stest group by sname,fruit)
group by sname

 

2、partition by

与group by不同，partition by能够在保留全部数据的基础上，只对其中某些字段做分组排序

select sname,fruit,fnum, row_number() over( partition by sname order by fnum desc )排名 from stest

select ...,row_number() over (partition by 分区字段 order by 排序字段) 顺序 from 表名