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