分组查询与分组条件过滤

​专栏内容:

postgresql内核源码分析手写数据库toadb并发编程

个人主页:我的主页 管理社区:开源数据库 座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

文章目录

分组查询与分组条件过滤一、前言 二、概述 三、分组group by 介绍 基本分组使用分组中使用聚合函数多表join中使用分组多列的分组

四、分组条件having介绍 五、总结 六、结尾

一、前言

本文主要分享在postgresql 数据库中对查询结果进行分组group by,以及对分组进行条件过滤having,同时对它们的使用场景进行案例分享。

二、概述

在数据查询中,我们经常对数据进行分类,往往不止一种分类,分类的好处是将大的数据集能划分成更小的数据集,方便我们进行递归的分析,更精确的查找想要的数据。 那么如何进行分组,以及分组中的过滤查找呢?

对结果集进行分组,是在前面介绍的基本查询 select ... from ... where ... 后面再使用 group by 关键字; 在分组上进行按条件筛选过滤就不能使用where子句中的条件了,而是使用having关键字来指定条件;

下面我们来详细介绍。

三、分组group by 介绍

对查询结果数据进行按某个字段或某几个字段进行分组,使用关键字group by ,它的SQL语法如下:

SELECT column1, column2, ..., aggregate_function(column3)

FROM tablename1 group by column1,column2,...;

一般分组是配合聚合函数使用的,常用的聚合函数如sum求合、max/min求最大/小值、avg求平均值。

select 子句中要列出来要分组的列,同时可以增加聚合函数,计算分组中某个值;在group by子句中指定分组的列名,这样结果就会按指定列进行分组,并分别在每个分组中应用聚合函数计算;当然还可以增加where子句,对结果集进行过滤;而group by 子句是在where子句之后进行,也就是它应用于where过滤之后的结果集;

下面我们来看几个例子吧。

基本分组使用

在这里我们还是使用产品表与订单表,表的定义与数据准备如下:

-- 创建产品表

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(255) NOT NULL,

price DECIMAL(10, 2) NOT NULL,

category VARCHAR(255)

);

-- 创建订单表

CREATE TABLE orders (

order_id INT PRIMARY KEY,

product_id INT,

quantity INT NOT NULL,

region VARCHAR(255) NOT NULL,

order_date DATE NOT NULL,

FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

表中也准备了一些数据,查询如下:

postgres=# select * from products ;

product_id | product_name | price | category

------------+--------------+---------+----------

2 | shirt | 202.40 | type2

3 | cake | 37.80 | type4

5 | hat | 88.40 | type2

6 | milk | 19.80 | type4

1 | iphone | 8999.01 | type5

7 | keyboard | 92.01 | type5

4 | pencil | 8.20 | type1

(7 rows)

postgres=# select * from orders ;

order_id | product_id | quantity | region | order_date

----------+------------+----------+---------+------------

1 | 1 | 8 | region1 | 2022-04-01

2 | 1 | 102 | region2 | 2022-06-01

3 | 2 | 19 | region1 | 2022-05-01

4 | 3 | 3 | region1 | 2022-04-01

5 | 4 | 58 | region2 | 2022-06-01

6 | 5 | 1 | region1 | 2022-05-01

7 | 6 | 106 | region1 | 2022-04-01

8 | 6 | 99 | region2 | 2022-06-01

9 | 4 | 32 | region1 | 2022-05-01

(9 rows)

下面我们执行一条最简单的分组查询

postgres=# select category from products group by category;

category

----------

type1

type2

type5

type4

(4 rows)

按产品类型进行分组,可以看到结果中列出了分组类型,等价于查询产品类型,并且使用distinct进行去重的结果。

分组中使用聚合函数

分组的常见用法,都是配合函合函数来进行统计分析。

postgres=# select min(quantity),max(quantity),avg(quantity), region as b from orders group by b;

min | max | avg | b

-----+-----+---------------------+---------

1 | 106 | 28.1666666666666667 | region1

58 | 102 | 86.3333333333333333 | region2

(2 rows)

统计出各区域的订单中商品数量的最高水平、最低水平、还有平均水平,这里按区域进行分组,然后使用聚合函数进行统计;

在SQL中我们给区域字段region使用别名b,在group by中是可以引用别名。

多表join中使用分组

还可以在多表join时使用分组统计。

postgres=# select category, sum(price*quantity) from products p inner join orders o using(product_id) group by category;

category | sum

----------+-----------

type1 | 738.00

type2 | 3934.00

type5 | 989891.10

type4 | 4172.40

(4 rows)

统计商品大类的销售额,这里需要将products表与orders表进行内联接,才能将价格与销售数量结合起来。

多列的分组

当然也可以按几个列进行依次分组,在现实生活中也常常用到。

postgres=# select region, category, sum(price*quantity) amount from products p inner join orders o using(product_id) group by region,category order by region, amount;

region | category | amount

---------+----------+-----------

region1 | type1 | 262.40

region1 | type4 | 2212.20

region1 | type2 | 3934.00

region1 | type5 | 71992.08

region2 | type1 | 475.60

region2 | type4 | 1960.20

region2 | type5 | 917899.02

(7 rows)

按区域进行统计每种大类商品的销售额,这是在上个案例基础上再增加区域region列的分组,然后按区域进行排序,再按销售额升序排列。

四、分组条件having介绍

使用where子句对结果集进行条件过滤,那么对于按分组统计的结果可以使用条件进行过滤吗?

答案是肯定的,可以使用having子句对分组的统计结果进行条件过滤。

带有having子句的SQL语法格式如下:

SELECT column1, column2, ..., aggregate_function(column3)

FROM tablename1 group by column1,column2,... HAVING conditions;

在group by 后面追加having关键字来指定条件;

这里不要搞混了,where 与 having的作用,虽然它们俩个都是带有条件的,where是对from子句中的原始表数据时行条件筛选,得到结果集,它是先于group by来执行;而having是对group by的分组结果进行条件过滤,是在group by之后执行。

下面我们来看一下如何使用。

postgres=# select min(quantity) as num, region from orders group by region having min(quantity) > 10;

num | region

-----+---------

58 | region2

(1 row)

对上面的分组聚合结果再过滤一下,只显示大于销售数量大于10的分组。

注意, having子句中不能使用select子句中的别名,也就是不能用num > 10这样的写法。

五、总结

本文分享了一个很有用的SQL语法,对结果集按类分组group by子句,它一般与聚合函数一起使用,分组之后对各个分组再进行统计分析,当然也可以对分组统计的结果再进行有条件的筛选,这就用到了having 子句,它是一个条件表达式,只是针对分组进行条件过滤。

同时在编写SQL时要注意,有了group by子句后,在select子句中要包括分组的列和对应的聚合函数。

六、结尾

非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com 如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

相关文章

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: