腳本之家服務器常用軟件

快捷導航

Sequelize中用group by進行分組聚合查詢

更新時間：2016年12月12日 08:34:16 投稿：daisy

大家都知道在SQL查詢中，分組查詢是較常用的一種查詢方式。分組查詢是指通過GROUP BY關鍵字，將查詢結(jié)果按照一個或多個字段進行分組，分組時字段值相同的會被分為一組。在Node.js基于Sequelize的ORM框架中，同樣支持分組查詢，使用非常簡單方便。下面來看看詳細的介紹。

一、SQL與Sequelize中的分組查詢

1.1 SQL中的分組查詢

SQL查詢中，通GROUP BY語名實現(xiàn)分組查詢。GROUP BY子句要和聚合函數(shù)配合使用才能完成分組查詢，在SELECT查詢的字段中，如果沒有使用聚合函數(shù)就必須出現(xiàn)在ORDER BY子句中。分組查詢后，查詢結(jié)果為一個或多個列分組后的結(jié)果集。

GROUP BY語法

SELECT 列名, 聚合函數(shù)(列名)
FROM 表名
WHERE 列名 operator value
GROUP BY 列名 
[HAVING 條件表達式] [WITH ROLLUP]

在以上語句中：

聚合函數(shù) - 分組查詢通常要與聚合函數(shù)一起使用，聚合函數(shù)包括：

COUNT()-用于統(tǒng)計記錄條數(shù)
SUM()-用于計算字段的值的總和
AVG()-用于計算字段的值的平均值
MAX-用于查找查詢字段的最大值
MIX-用于查找查詢字段的最小值

GROUP BY子名-用于指定分組的字段

HAVING子名-用于過濾分組結(jié)果，符合條件表達式的結(jié)果將會被顯示

WITH ROLLUP子名-用于指定追加一條記錄，用于匯總前面的數(shù)據(jù)

1.2 Sequelize中的分組查詢

使用聚合函數(shù)

Sequelize提供了聚合函數(shù)，可以直接對模型進行聚合查詢：

aggregate(field, aggregateFunction, [options])-通過指定的聚合函數(shù)進行查詢
sum(field, [options])-求和
count(field, [options])-統(tǒng)計查詢結(jié)果數(shù)
max(field, [options])-查詢最大值
min(field, [options])-查詢最小值

以上這些聚合函數(shù)中，可以通過options.attributes、options.attributes屬性指定分組相關字段，并可以通過options.having指定過濾條件，但沒有直接指定WITH ROLLUP子句的參數(shù)。

如，使用.sum()查詢訂單數(shù)量大于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` GROUP BY name HAVING COUNT('name')>1;

使用聚合參數(shù)

除直接使用聚合函數(shù)外，也可以在findAll()等方法中，指定聚合查詢相關參數(shù)實現(xiàn)聚合查詢。查詢時，同樣可以通過通過options.attributes、options.attributes屬性指定分組相關字段，并可以通過options.having指定過濾條件。與直接使用聚合函數(shù)查詢不一樣，通過參數(shù)構建聚合查詢時，要以數(shù)組或?qū)ο笮问皆O置options.attributes參數(shù)中的聚合字段，并需要通過sequelize.fn()方法傳入聚合函數(shù)。

如，使用.findAll()查詢訂單數(shù)量大于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` GROUP BY name HAVING COUNT('name')>1;

二、使用示例

現(xiàn)在訂單表，數(shù)據(jù)如下：

> 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 簡單使用

使用分組查詢，統(tǒng)計每個客戶的訂單總額。

使用SQL語句，可以像下面這樣查詢：

> select name, SUM(price) from orders GROUP BY name;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 張小三 |  230.00 |
| 王小五 |  99.00 |
| 趙小六 |  199.00 |
+-----------+------------+

而在Sequelize中可以像下面這樣實現(xiàn)：

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){
 console.log(result);
})

2.2 使用HAVING子句

統(tǒng)計訂單數(shù)量大于1的用戶的訂單總金額。

使用SQL語句，可以像下面這樣實現(xiàn)：

> select name, SUM(price) from orders GROUP BY name HAVING count(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 使用WITH ROLLUP子句

WITH ROLLUP子句是MySQL 5.5+新增的特性，用于匯總統(tǒng)計結(jié)果。但本文發(fā)布時，Sequelize還不支持該特性。

增加總和統(tǒng)計列：

> 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 連接查詢與分組

為了管理方便，我們會將不同的信息保存在不同的表中。如，我們會將訂單信息放在一張表中，而將客戶信息保存在另一張表中。對于存在關聯(lián)關系的兩張表，我們會使用連接查詢來查找關聯(lián)數(shù)據(jù)，在進行連接查詢時，同樣可以以使用聚合函數(shù)。

訂單表如下：

> 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 |
+---------+-------------+--------+------------+---------------------+

客戶表結(jié)構如下：

> 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 |
+----+-----------+-----+---------------------+---------------------+

使用連接查詢并分組查詢，統(tǒng)計每個客戶的訂單總額。

使用SQL語句查詢?nèi)缦拢?/p>

> 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中進行連接查詢時，首先需要建立模型間的關聯(lián)關系：

Order.belongsTo(Customer, {foreignKey: 'customerId'});

連接查詢及分組：

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);
})

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。

您可能感興趣的文章: