6.postgresql--grouping()

CREATE TABLE sales ( brand VARCHAR NOT NULL, segment VARCHAR NOT NULL, quantity
INT NOT NULL, PRIMARY KEY (brand, segment) ); INSERT INTO sales (brand, segment,
quantity) VALUES ('ABC', 'Premium', 100), ('ABC', 'Basic', 200), ('XYZ',
'Premium', 100), ('XYZ', 'Basic', 300);
<>union all的实现
select brand,segment,sum(quantity) from sales group by brand,segment union all
select brand,null,sum(quantity) from sales group by brand union all select null,
segment,sum(quantity) from sales group by segment union all select null,null,sum
(quantity) from sales ;
<>grouping sets实现
select coalesce (brand,'所有品牌') brand,coalesce(segment,'所有类型') segment,sum(
quantity) from sales group by grouping sets((brand,segment),(brand),(segment),()
)
pg既然提供了grouping sets，就提供了对应了对应的grouping()函数，用来判断是否该纬度在该指标统计中是否被用到

用到，返回0
没用到，返回1
select grouping(brand) as brand_used, grouping(segment) as segment_used, brand,
segment, sum(quantity) from sales group by grouping sets((brand,segment),(brand
),(segment),()) having grouping(brand) = 1

• « 上一篇：不使用插件预览pdf等类型文件
• » 下一篇：【动手学深度学习】pytorch-参数管理