Sequelize中用group by進行分組聚合查詢
一、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)容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流。
相關文章
Node.js中MongoDB插入數(shù)據(jù)的實現(xiàn)方法
在Node.js中,可以使用MongoDB原生驅(qū)動或Mongoose庫來連接和操作MongoDB數(shù)據(jù)庫,本文就來介紹一下Node.js中MongoDB插入數(shù)據(jù)的實現(xiàn)方法,感興趣的可以了解一下2023-12-12node靜態(tài)服務器實現(xiàn)靜態(tài)讀取文件或文件夾
這篇文章主要介紹了node靜態(tài)服務器實現(xiàn)靜態(tài)讀取文件或文件夾,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2019-12-12Node.js自定義對象事件的監(jiān)聽與發(fā)射
這篇文章介紹了Node.js自定義對象事件監(jiān)聽與發(fā)射的方法,文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-07-07node.js中的http.response.addTrailers方法使用說明
這篇文章主要介紹了node.js中的http.response.addTrailers方法使用說明,本文介紹了http.response.addTrailers的方法說明、語法、接收參數(shù)、使用實例和實現(xiàn)源碼,需要的朋友可以參考下2014-12-12