Utilisation de group by dans Sequelize pour effectuer une requête d'agrégation de groupe

1. Requête de groupe en SQL et Sequelize

1.1 Requête de groupe en SQL

Dans la requête SQL, la requête de groupe est implémentée via le nom de langage GROUP BY. La clause GROUP BY doit être utilisée conjointement avec une fonction d'agrégation pour compléter la requête de groupe. Dans les champs de la requête SELECT, si une fonction d'agrégation n'est pas utilisée, elle doit apparaître dans la clause ORDER BY. Après avoir regroupé la requête, le résultat de la requête est un ensemble de résultats regroupés par une ou plusieurs colonnes.

Syntaxe GROUP BY

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

Dans l'instruction ci-dessus :

Fonction d'agrégation - Les requêtes de regroupement sont généralement utilisées avec des fonctions d'agrégation, qui incluent :

COUNT()-utilisé pour compter le nombre d'enregistrements

SUM()-utilisé pour calculer la somme des valeurs des champs

AVG()-utilisé pour calculer la valeur moyenne des champs

MAX - utilisé pour trouver la valeur maximale du champ de requête

MIX - utilisé pour trouver la valeur minimale du champ de requête

Sous-nom GROUP BY - utilisé pour spécifier le Champ de groupe

HAVING sous-nom - utilisé pour filtrer les résultats de regroupement, les résultats qui correspondent à l'expression conditionnelle seront affichés

AVEC le sous-nom ROLLUP - utilisé pour spécifier l'ajout d'un enregistrement, utilisé pour résumer les données précédentes

1.2 Requête de groupe dans Sequelize

Utilisation de fonctions d'agrégation

Sequelize fournit des fonctions d'agrégation qui peuvent directement effectuer des requêtes d'agrégation sur le modèle :

aggregate(field, AggregateFunction, [options]) - Requête par la fonction d'agrégation spécifiée

sum(field, [options]) - Somme

count( field, [options ])-Nombre de résultats de requêtes statistiques

max(field, [options])-Requête de la valeur maximale

min(field, [options])-Requête de la valeur minimale

Parmi les fonctions d'agrégation ci-dessus, vous pouvez spécifier des champs liés au regroupement via les attributs options.attributes et options.attributes, et vous pouvez spécifier des conditions de filtre via options.having, mais il n'y a pas de spécification directe des paramètres du AVECclause ROLLUP.

Par exemple, utilisez .sum() pour interroger le montant de la commande des utilisateurs dont la quantité de commande est supérieure à 1 :

Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){
 console.log(result);
})

L'instruction SQL générée est la suivante :

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

Utiliser les paramètres d'agrégation

En plus d'utiliser directement les fonctions d'agrégation, vous pouvez également spécifier des paramètres liés aux requêtes d'agrégation dans des méthodes telles que findAll() pour implémenter une requête d'agrégation. Lors de l'interrogation, vous pouvez également spécifier le regroupement des champs associés via les attributs options.attributes et options.attributes, et vous pouvez spécifier les conditions de filtre via options.having. Contrairement à l'utilisation directe de requêtes de fonction d'agrégation, lors de la création d'une requête d'agrégation via des paramètres, les champs d'agrégation du paramètre options.attributes doivent être définis sous la forme d'un tableau ou d'un objet, et la fonction d'agrégation doit être transmise via sequelize.fn. () méthode.

Par exemple, utilisez .findAll() pour interroger le montant de la commande des utilisateurs dont la quantité de commande est supérieure à 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);
})

L'instruction SQL générée est la suivante :

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

2. Exemple d'utilisation

Maintenant le tableau des commandes, les données sont les suivantes :

> 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 Utilisation simple

Utilisez la requête de groupe pour compter le montant total de la commande de chaque client.

À l'aide des instructions SQL, vous pouvez interroger comme ceci :

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

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

2.2 Utiliser la clause HAVING

pour compter le montant total de la commande des utilisateurs dont la quantité de commande est supérieure à 1.

À l'aide d'instructions SQL, il peut être implémenté comme suit :

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

Et en utilisant Sequelize, il peut être interrogé comme suit :

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 Utilisation de la clause WITH ROLLUP

La clause WITH ROLLUP est une nouvelle fonctionnalité de MySQL 5.5 et est utilisée pour résumer les résultats statistiques. Cependant, au moment de la publication de cet article, Sequelize ne prend pas encore en charge cette fonctionnalité.

Ajouter une colonne de statistiques totales :

> 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 Requête et regroupement de connexion

Pour la commodité de gestion, nous enregistrerons différentes informations dans différents tableaux. Par exemple, nous placerions les informations de commande dans un tableau et les informations client dans un autre tableau. Pour deux tables liées, nous utiliserons des requêtes de jointure pour rechercher des données associées. Lors de l'exécution de requêtes de jointure, nous pouvons également utiliser des fonctions d'agrégation.

Le tableau des commandes est le suivant :

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

La structure du tableau des clients est la suivante :

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

Utilisez la requête de jointure et la requête de groupe pour compter le montant total de la commande de chaque client.

Utilisez l'instruction SQL pour interroger comme suit :

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

Lorsque vous effectuez une requête de connexion dans Sequelize, vous devez d'abord établir l'association entre modèles :

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

Requête et regroupement de connexion :

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

Résumé

Ce qui précède est l'intégralité du contenu de cet article. J'espère que le contenu de cet article pourra être utile aux études ou au travail de chacun. Si vous avez des questions, vous pouvez laisser un message pour communiquer.

Pour plus d'articles sur l'utilisation de group by pour effectuer une requête d'agrégation de groupe dans Sequelize, veuillez faire attention au site Web PHP chinois !