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

一、SQL與Sequelize中的分組查詢

1.1 SQL中的分組查詢

SQL查詢中，通GROUP BY語名實現分組查詢。 GROUP BY子句要和聚合函數搭配使用才能完成分組查詢，在SELECT查詢的欄位中，如果沒有使用聚合函數就必須出現在ORDER BY子句中。分組查詢後，查詢結果為一個或多個列分組後的結果集。

GROUP BY語法

SELECT 列名, 聚合函数(列名)
FROM 表名
WHERE 列名 operator value
GROUP BY 列名 
[HAVING 条件表达式] [WITH ROLLUP]

在上述語句中：

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

     COUNT()-用於統計記錄條數🎠

     COUNT()-用於統計記錄條數🠎用於計算欄位的值的總和

     AVG()-用於計算欄位的值的平均值

     MAX-用於尋找查詢欄位的最大值

    欄位 MIX-用於尋找查詢欄位的最大值

   GROUP BY子名-用於指定分組的欄位

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

WITH ROLLUP子名-用於指定追加一條記錄，用於匯總前面的​​資料

1.2 Sequelize中的分組查詢

使用聚合函數

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

aggregate(field, aggregateFunction, [octions的聚合函數進行查詢

sum(field, [options])-求和

count(field, [options])-統計查詢結果數

max(field, [options])-查詢最大值

min (field, [options])-查詢最小值

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

如，使用.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` GROUP BY name HAVING COUNT('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` GROUP BY name HAVING COUNT('name')>1;

二、使用範例

2.1 簡單使用

使用分組查詢，統計每位客戶的訂單總額。

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

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

   

而在Sequelize中可以像下面這樣實現：

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

   

2.2 使用HAVING子句

統計訂單數量大於1的用戶的訂單總金額。

使用SQL語句，可以像下面這樣實作：

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

   

而使用Sequelize可以像下面這樣查詢：

> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 赵小六 |  199.00 |
+-----------+------------+

RO LLUP子句是MySQL 5.5+新增的特性，用於總結統計結果。但本文發佈時，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.4 連接查詢與分組

為了管理方便，我們會將不同的資訊保存在不同的資料表中。如，我們會將訂單資訊放在一張表中，而將客戶資訊保存在另一張表中。對於存在關聯關係的兩張表，我們會使用連接查詢來尋找關聯數據，在進行連接查詢時，同樣可以使用聚合函數。

訂單表如下：

> select name, SUM(price) from orders GROUP BY name WITH ROLLUP;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 王小五 |  99.00 |
| 赵小六 |  199.00 |
| NULL  |  528.00 |
+-----------+------------+

   

客戶表結構如下：

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

   

使用每個客戶並分配訂單，使用每個客戶的訂單統計數據。

使用SQL語句查詢如下：

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

   

Sequelize中連接查詢時，首先需要建立模型間的關聯關係：

> 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中用group by進行分組聚合查詢相關文章請關注PHP中文網！