准备测试数据
create table proc(
name varchar(100),
size varchar(30),
color varchar(30),
price money
);
insert into proc values('name_A','L','red',69),('name_A','M','red',86),
('name_A','L','white',106),('name_A','s','',100),('name_A','s','black',100),
('name_B','s','black',100),('name_B','L','black',120),('name_C','M','white',99)
group by
- 按name分组，并查询出每组中price值最大的记录
select name,max(price) from proc group by name;
--查询结果
# name max
1 name_A ￥106.00
2 name_B ￥120.00
3 name_C ￥99.00
- 按name分组，并查询出每组中最大price值是大于等于100的记录
select name,max(price) from proc group by name having max(price)>=money(100);
--查询结果
# name max
1 name_A ￥106.00
2 name_B ￥120.00
- 只查询size是s和M值的记录，并按name分组，查询出每组中最大price值是大于等于100的记录
select name,max(price) from proc where size in ('s','M') group by name having max(price)>=money(100);
--查询结果
# name max
1 name_A ￥100.00
2 name_B ￥100.00
GROUPING SETS
GROUPING SETS 的每一个子列表可以指定一个或者多个列或者表达式，每个分组列用括号()区别
select name,size,sum(price) from proc group by grouping sets((name,size))--按（name,size）分组
--等同select name,size,sum(price) from proc group by name,size
--查询结果
# name size sum
1 name_A L ￥175.00
2 name_A M ￥86.00
3 name_B L ￥120.00
4 name_C M ￥99.00
5 name_B s ￥100.00
6 name_A s ￥200.00
select name,size,count(*) from proc group by grouping sets(name,size,()); --分别按name,size，空列表（null）分组
--查询结果
# name size count
1 [NULL] [NULL] 8
2 name_A [NULL] 5
3 name_B [NULL] 2
4 name_C [NULL] 1
5 [NULL] L 3
6 [NULL] M 2
7 [NULL] s 3
select name,size,count(*) from proc group by grouping sets(name,size);--分别按name,size分组
--查询结果
# name size count
1 name_A [NULL] 5
2 name_B [NULL] 2
3 name_C [NULL] 1
4 [NULL] L 3
5 [NULL] M 2
6 [NULL] s 3
ROLLUP
ROLLUP ( e1, e2, e3, … )表示给定的表达式列表及其所有前缀（包括空列表）
select name,size,sum(price) from proc group by ROLLUP(name,size)
--等同select name,size,sum(price) from proc group by grouping set((),name,(name,size))
--查询结果
# name size sum
1 [NULL] [NULL] ￥780.00
2 name_A L ￥175.00
3 name_A M ￥86.00
4 name_B L ￥120.00
5 name_C M ￥99.00
6 name_B s ￥100.00
7 name_A s ￥200.00
8 name_A [NULL] ￥461.00
9 name_B [NULL] ￥220.00
10 name_C [NULL] ￥99.00
聚集函数grouping
对应的表达式被包括在产生结果行的分组 集合的分组条件中则每一位是 0，否则不为0
select name,size,grouping(name),sum(price) from proc group by grouping sets(name,size)
--查询结果
# name size grouping sum
1 name_A [NULL] 0 ￥461.00
2 name_B [NULL] 0 ￥220.00
3 name_C [NULL] 0 ￥99.00
4 [NULL] L 1 ￥295.00
5 [NULL] M 1 ￥185.00
6 [NULL] s 1 ￥300.00
使用窗口函数
- 语法：function over(partition by filed order by filed2），指定分组字段并对分区中的内容进行排序
function ：窗口函数
filed：进行分组的字段partition可不要
filed2：排序的字段，order by 可不要 - row_number()、rank()、dense_rank()都是对分组中的内容进行排序，区别如下查询结果
select name,price,row_number() over (partition by name order by price) from proc; --序号连续
--查询结果
# name price xh
1 name_A ￥69.00 1
2 name_A ￥86.00 2
3 name_A ￥100.00 3
4 name_A ￥100.00 4
5 name_A ￥106.00 5
6 name_B ￥100.00 1
7 name_B ￥120.00 2
8 name_C ￥99.00 1
select name,price,rank() over (partition by name order by price) from proc;
--排序字段若值相同序号一样，序号不连续
--查询结果
# name price xh
1 name_A ￥69.00 1
2 name_A ￥86.00 2
3 name_A ￥100.00 3
4 name_A ￥100.00 3
5 name_A ￥106.00 5
6 name_B ￥100.00 1
7 name_B ￥120.00 2
8 name_C ￥99.00 1
select name,price,dense_rank() over (partition by name order by price) as xh from proc;
--排序字段若值相同需要一样，序号连续
--查询结果
# name price xh
1 name_A ￥69.00 1
2 name_A ￥86.00 2
3 name_A ￥100.00 3
4 name_A ￥100.00 3
5 name_A ￥106.00 4
6 name_B ￥100.00 1
7 name_B ￥120.00 2
8 name_C ￥99.00 1
学习链接：postgre13.1手册