Sequelize で group by を使用してグループ集計クエリを実行する

1. SQL のグループ クエリと Sequelize

1.1 SQL のグループ クエリ

SQL クエリでは、グループ クエリは GROUP BY キーワードによって実装されます。グループ クエリを完了するには、GROUP BY 句を集計関数と組み合わせて使用​​する必要があります。SELECT クエリのフィールドで集計関数を使用しない場合は、ORDER BY 句にその関数を指定する必要があります。クエリをグループ化すると、クエリ結果は 1 つ以上の列でグループ化された結果セットになります。

GROUP BY 構文

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

上記のステートメント:

集計関数 - グループ化クエリは通常、集計関数と一緒に使用されます:

COUNT() - レコード数をカウントするために使用されます

SUM() - フィールドの値の合計を計算するために使用されます

AVG() - フィールドの値の平均を計算するために使用されます

MAX - クエリの最大値を見つけるために使用されますfield

MIX - クエリフィールドの最小値を見つけるために使用されます

GROUP BY subname - グループ化するフィールドを指定するために使用されます

HAVING subname - グループ結果をフィルタリングするために使用され、条件式に一致する結果は表示されます

WITH ROLLUP サブ名 - レコードの追加を指定するために使用されます。前のデータを要約するには、使用します

1.2 Sequelize のグループ クエリ

集計関数を使用します

Sequelize は、集計クエリを直接実行できる集計関数を提供しますモデル:

aggregate(field,ggregateFunction, [options])-クエリの集計関数を指定する

sum(field, [options])-summation

count(field, [options])-クエリ結果の統計的な数

max(field, [options]) - 最大値のクエリ

min (field, [options]) - 最小値のクエリ

上記の集計関数のうち、options.attributes と options を通じてグループ化関連フィールドを指定できます。 .attributes 属性を使用し、options.having を通じてフィルター条件を指定できますが、ROLLUP 句のパラメーターを直接指定することはできません。

たとえば、注文数量が 1 より大きいユーザーの注文金額をクエリするには、.sum() を使用します。

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. 使用例

注文テーブルが完成しました。 、データは次のとおりです:

> 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 ステートメントを使用すると、次のようなクエリを実行できます:

> select name, SUM(price) from orders GROUP BY name;
+-----------+------------+
| 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 ステートメントを使用すると、次のように実装できます:

> 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 句は統計結果を要約するための SQL 5.5 以降の新機能。ただし、この記事の公開時点では、Sequelize はこの機能をまだサポートしていません。

合計統計列を追加:

> 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 つのテーブルの場合、結合クエリを使用して関連データを検索します。結合クエリを実行するときは、集計関数も使用できます。

注文テーブルは次のとおりです:

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

顧客テーブルの構造は次のとおりです:

> 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 ステートメントを使用して次のようにクエリを実行します:

> 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 で接続クエリを実行するときは、まずモデル間の関係を確立する必要があります:

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

概要

以上がこの記事の内容です。この記事の内容が皆さんの勉強や仕事に少しでもお役に立てれば幸いです。ご質問がございましたら、メッセージを残してご連絡ください。

Sequelize でグループ集計クエリを実行するための group by の使用に関連するその他の記事については、PHP 中国語 Web サイトに注目してください。