Verwenden von „group by' in Sequelize, um eine Gruppenaggregationsabfrage durchzuführen

1. Gruppenabfrage in SQL und Sequelize

1.1 Gruppenabfrage in SQL

In der SQL-Abfrage wird die Gruppenabfrage über den GROUP BY-Sprachnamen implementiert. Die GROUP BY-Klausel muss in Verbindung mit einer Aggregatfunktion verwendet werden, um die Gruppenabfrage abzuschließen. Wenn in den Feldern der SELECT-Abfrage keine Aggregatfunktion verwendet wird, muss diese in der ORDER BY-Klausel erscheinen. Nach dem Gruppieren der Abfrage ist das Abfrageergebnis eine nach einer oder mehreren Spalten gruppierte Ergebnismenge.

GROUP BY-Syntax

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

In der obigen Aussage:

Aggregationsfunktion – Gruppierungsabfragen werden normalerweise mit Aggregatfunktionen verwendet, zu denen Folgendes gehört:

COUNT() – wird verwendet, um die Anzahl der Datensätze zu zählen

SUM() – wird verwendet, um die Summe der Feldwerte zu berechnen

AVG() – wird verwendet, um den Durchschnittswert von Feldern zu berechnen

MAX – wird verwendet, um den maximalen Wert des Abfragefelds zu ermitteln.

MIX – wird verwendet, um den minimalen Wert des Abfragefelds zu ermitteln.

GROUP BY subname – wird verwendet, um den anzugeben Gruppenfeld

HAVING subname – wird zum Filtern von Gruppierungsergebnissen verwendet. Ergebnisse, die dem bedingten Ausdruck entsprechen, werden angezeigt

MIT ROLLUP subname – wird verwendet, um anzugeben, dass ein Datensatz angehängt werden soll. Wird verwendet, um die vorherigen Daten zusammenzufassen

1.2 Gruppenabfrage in Sequelize

Aggregatfunktionen verwenden

Sequelize bietet Aggregatfunktionen, mit denen Aggregatabfragen direkt am Modell durchgeführt werden können :

aggregate(field,aggregatFunction, [options]) – Abfrage nach der angegebenen Aggregatfunktion

sum(field, [options]) – Summe

count( field, [options ])-Anzahl der statistischen Abfrageergebnisse

max(field, [Optionen])-Fragen Sie den Maximalwert ab

min(field, [Optionen])-Fragen Sie den Minimalwert ab

Unter den oben genannten Aggregatfunktionen können Sie gruppierungsbezogene Felder über die Attribute „options.attributes“ und „options.attributes“ angeben, und Sie können Filterbedingungen über „options.having“ angeben, es gibt jedoch keine direkte Spezifikation der Parameter WITH ROLLUP-Klausel.

Verwenden Sie beispielsweise .sum(), um die Bestellmenge von Benutzern abzufragen, deren Bestellmenge größer als 1 ist:

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

Die generierte SQL-Anweisung lautet wie folgt:

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

Aggregatparameter verwenden

Zusätzlich zur direkten Verwendung von Aggregatfunktionen können Sie auch aggregationsabfragebezogene Parameter in Methoden wie findAll() angeben, um Aggregationsabfragen zu implementieren. Bei der Abfrage können Sie auch die Gruppierung verwandter Felder über die Attribute „options.attributes“ und „options.attributes“ angeben und Sie können Filterbedingungen über „options.having“ angeben. Anders als bei der direkten Verwendung von Aggregatfunktionsabfragen müssen beim Erstellen einer Aggregatabfrage über Parameter die Aggregatfelder im Parameter „options.attributes“ in Form eines Arrays oder Objekts festgelegt werden und die Aggregatfunktion muss über sequelize.fn übergeben werden ()-Methode.

Verwenden Sie beispielsweise .findAll(), um die Bestellmenge von Benutzern abzufragen, deren Bestellmenge größer als 1 ist:

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

Die generierte SQL-Anweisung lautet wie folgt:

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

2. Anwendungsbeispiel

Jetzt die Bestelltabelle, die Daten lauten wie folgt:

> 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 Einfache Verwendung

Verwenden Sie die Gruppenabfrage, um die Gesamtbestellmenge jedes Kunden zu zählen.

Mithilfe von SQL-Anweisungen können Sie wie folgt abfragen:

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

In Sequelize können Sie wie folgt abfragen:

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

2.2 Verwenden Sie die HAVING-Klausel

, um die Gesamtbestellmenge von Benutzern zu zählen, deren Bestellmenge größer als 1 ist.

Mithilfe von SQL-Anweisungen kann es wie folgt implementiert werden:

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

Und mit Sequelize kann es wie folgt abgefragt werden:

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 Verwendung der WITH ROLLUP-Klausel

WITH ROLLUP-Klausel ist eine neue Funktion von MySQL 5.5+, die zusammenfassend verwendet wird statistische Ergebnisse. Zum Zeitpunkt der Veröffentlichung dieses Artikels unterstützt Sequelize diese Funktion jedoch noch nicht.

Gesamtstatistikspalte hinzufügen:

> 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 Verbindungsabfrage und Gruppierung

Zur Vereinfachung Management, Wir werden verschiedene Informationen in verschiedenen Tabellen speichern. Beispielsweise würden wir Bestellinformationen in einer Tabelle und Kundeninformationen in einer anderen Tabelle ablegen. Für zwei miteinander verbundene Tabellen verwenden wir Join-Abfragen, um verwandte Daten zu finden. Bei der Durchführung von Join-Abfragen können wir auch Aggregatfunktionen verwenden.

Die Bestelltabelle ist wie folgt:

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

Die Kundentabellenstruktur ist wie folgt:

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

Verwenden Sie die Join-Abfrage und die Gruppenabfrage, um die Gesamtbestellmenge jedes Kunden zu zählen.

Verwenden Sie die SQL-Anweisung zur Abfrage wie folgt:

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

Wenn Sie eine Verbindungsabfrage in Sequelize durchführen, müssen Sie zunächst die Zuordnung herstellen zwischen Modellen:

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

Verbindungsabfrage und Gruppierung:

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

Zusammenfassung

Das Obige ist der gesamte Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels für alle beim Lernen oder bei der Arbeit hilfreich sein kann. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen.

Weitere Artikel zur Verwendung von „group by“ zur Durchführung einer Gruppenaggregationsabfrage in Sequelize finden Sie auf der chinesischen PHP-Website!