Sequelize中用group by进行分组聚合查询
一、SQL与Sequelize中的分组查询
1.1 SQL中的分组查询
SQL查询中,通GROUP BY
语名实现分组查询。GROUP BY
子句要和聚合函数配合使用才能完成分组查询,在SELECT
查询的字段中,如果没有使用聚合函数就必须出现在ORDER BY
子句中。分组查询后,查询结果为一个或多个列分组后的结果集。
GROUP BY
语法
1
2
3
4
5
|
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列名 operator value GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP] |
在以上语句中:
聚合函数 - 分组查询通常要与聚合函数一起使用,聚合函数包括:
- COUNT()-用于统计记录条数
- SUM()-用于计算字段的值的总和
- AVG()-用于计算字段的值的平均值
- MAX-用于查找查询字段的最大值
- MIX-用于查找查询字段的最小值
GROUP BY
子名-用于指定分组的字段
HAVING
子名-用于过滤分组结果,符合条件表达式的结果将会被显示
WITH ROLLUP
子名-用于指定追加一条记录,用于汇总前面的数据
1.2 Sequelize中的分组查询
使用聚合函数
Sequelize提供了聚合函数,可以直接对模型进行聚合查询:
- aggregate(field, aggregateFunction, [options])-通过指定的聚合函数进行查询
- sum(field, [options])-求和
- count(field, [options])-统计查询结果数
- max(field, [options])-查询最大值
- min(field, [options])-查询最小值
以上这些聚合函数中,可以通过options.attributes
、options.attributes
属性指定分组相关字段,并可以通过options.having
指定过滤条件,但没有直接指定WITH ROLLUP
子句的参数。
如,使用.sum()
查询订单数量大于1的用户订单额:
1
2
3
|
Order.sum( 'price' , {attributes:[ 'name' ], group: 'name' , plain: false , having:[ 'COUNT(?)>?' , 'name' , 1]}).then( function (result){ console.log(result); }) |
生成的SQL语句如下:
1
|
SELECT ` name `, sum (`price`) AS ` sum ` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT ( 'name' )>1; |
使用聚合参数
除直接使用聚合函数外,也可以在findAll()
等方法中,指定聚合查询相关参数实现聚合查询。查询时,同样可以通过通过options.attributes
、options.attributes
属性指定分组相关字段,并可以通过options.having
指定过滤条件。与直接使用聚合函数查询不一样,通过参数构建聚合查询时,要以数组或对象形式设置options.attributes
参数中的聚合字段,并需要通过sequelize.fn()
方法传入聚合函数。
如,使用.findAll()
查询订单数量大于1的用户订单额:
1
2
3
|
Order.findAll({attributes:[ 'name' , [sequelize.fn( 'SUM' , sequelize.col( 'price' )), 'sum' ]], group: 'name' , having:[ 'COUNT(?)>?' , 'name' , 1], raw: true }).then( function (result){ console.log(result); }) |
生成的SQL语句如下:
1
|
SELECT ` name `, sum (`price`) AS ` sum ` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT ( 'name' )>1; |
二、使用示例
现在订单表,数据如下:
1
2
3
4
5
6
7
8
9
|
> select * from orders; + ---------+-------------+--------+-----------+---------------------+ | orderId | orderNumber | price | name | createdOn | + ---------+-------------+--------+-----------+---------------------+ | 1 | 00001 | 128.00 | 张小三 | 2016-11-25 10:12:49 | | 2 | 00002 | 102.00 | 张小三 | 2016-11-25 10:12:49 | | 4 | 00004 | 99.00 | 王小五 | 2016-11-25 10:12:49 | | 3 | 00003 | 199.00 | 赵小六 | 2016-11-25 10:12:49 | + ---------+-------------+--------+-----------+---------------------+ |
2.1 简单使用
使用分组查询,统计每个客户的订单总额。
使用SQL语句,可以像下面这样查询:
1
2
3
4
5
6
7
8
|
> select name , SUM (price) from orders GROUP BY name ; + -----------+------------+ | name | SUM (price) | + -----------+------------+ | 张小三 | 230.00 | | 王小五 | 99.00 | | 赵小六 | 199.00 | + -----------+------------+ |
而在Sequelize中可以像下面这样实现:
1
2
3
|
Order.findAll({attributes:[ 'sum' , [sequelize.fn( 'SUM' , sequelize.col( 'name' )), 'sum' ]], group: 'name' , raw: true }).then( function (result){ console.log(result); }) |
2.2 使用HAVING子句
统计订单数量大于1的用户的订单总金额。
使用SQL语句,可以像下面这样实现:
1
2
3
4
5
6
7
|
> select name , SUM (price) from orders GROUP BY name HAVING count (1)>1; + -----------+------------+ | name | SUM (price) | + -----------+------------+ | 张小三 | 230.00 | | 赵小六 | 199.00 | + -----------+------------+ |
而使用Sequelize可以像下面这样查询:
1
2
3
|
Order.findAll({attributes:[ 'sum' , [sequelize.fn( 'SUM' , sequelize.col( 'name' )), 'sum' ]], group: 'name' , having:[ 'COUNT(?)>?' , 'name' , 1], raw: true }).then( function (result){ console.log(result); }) |
2.3 使用WITH ROLLUP子句
WITH ROLLUP
子句是MySQL 5.5+新增的特性,用于汇总统计结果。但本文发布时,Sequelize还不支持该特性。
增加总和统计列:
1
2
3
4
5
6
7
8
9
|
> select name , SUM (price) from orders GROUP BY name WITH ROLLUP ; + -----------+------------+ | name | SUM (price) | + -----------+------------+ | 张小三 | 230.00 | | 王小五 | 99.00 | | 赵小六 | 199.00 | | NULL | 528.00 | + -----------+------------+ |
2.4 连接查询与分组
为了管理方便,我们会将不同的信息保存在不同的表中。如,我们会将订单信息放在一张表中,而将客户信息保存在另一张表中。对于存在关联关系的两张表,我们会使用连接查询来查找关联数据,在进行连接查询时,同样可以以使用聚合函数。
订单表如下:
1
2
3
4
5
6
7
8
9
|
> select * from orders; + ---------+-------------+--------+------------+---------------------+ | orderId | orderNumber | price | customerId | createdOn | + ---------+-------------+--------+------------+---------------------+ | 1 | 00001 | 128.00 | 1 | 2016-11-25 10:12:49 | | 2 | 00002 | 102.00 | 1 | 2016-11-25 10:12:49 | | 3 | 00003 | 199.00 | 4 | 2016-11-25 10:12:49 | | 4 | 00004 | 99.00 | 3 | 2016-11-25 10:12:49 | + ---------+-------------+--------+------------+---------------------+ |
客户表结构如下:
1
2
3
4
5
6
7
8
9
|
> select * from customers; + ----+-----------+-----+---------------------+---------------------+ | id | name | sex | birthday | createdOn | + ----+-----------+-----+---------------------+---------------------+ | 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 | | 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 | | 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 | | 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 | + ----+-----------+-----+---------------------+---------------------+ |
使用连接查询并分组查询,统计每个客户的订单总额。
使用SQL语句查询如下:
1
|
> select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name; |
Sequelize中进行连接查询时,首先需要建立模型间的关联关系:
1
|
Order.belongsTo(Customer, {foreignKey: 'customerId' }); |
连接查询及分组:
1
2
3
4
5
6
7
8
|
var include = [{ model: Customer, required: true , attributes: [ 'name' ], }] Order.findAll({include:include, attributes:[[sequelize.fn( 'SUM' , sequelize.col( 'price' )), 'sum' ]], group: 'Customer.name' , having:[ 'COUNT(?)>?' , 'name' , 1], raw: true , rollup: true }).then( function (result){ console.log(result); }) |
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。
posted on 2022-07-25 18:08 zhangzongshan 阅读(2290) 评论(1) 编辑 收藏 举报