Verwenden von „group by' in Sequelize, um eine Gruppenaggregationsabfrage durchzuführen
- 高洛峰Original
- 2016-12-28 11:30:463156Durchsuche
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!