group-by datensätze - Abrufen des letzten Datensatzes in jeder Gruppe - MySQL





hochzählen für (17)


Verwenden Sie Ihre subquery , um die richtige Gruppierung zurückzugeben, da Sie auf halbem Weg sind.

Versuche dies:

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

Wenn es keine id , willst du das Maximum von:

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

Auf diese Weise vermeiden Sie korrelierte Unterabfragen und / oder Sortierungen in Ihren Unterabfragen, die dazu neigen, sehr langsam / ineffizient zu sein.

Es gibt eine Tabelle messages , die Daten wie folgt enthält:

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

Wenn ich eine Abfrage ausführen select * from messages group by name , ich bekomme das Ergebnis als:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Welche Abfrage liefert das folgende Ergebnis?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Das heißt, der letzte Datensatz in jeder Gruppe sollte zurückgegeben werden.

Derzeit ist dies die Abfrage, die ich verwende:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

Aber das sieht sehr ineffizient aus. Gibt es noch andere Möglichkeiten, um das gleiche Ergebnis zu erzielen?




Ein Ansatz mit beträchtlicher Geschwindigkeit ist wie folgt.

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

Ergebnis

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1



Wie wäre es damit:

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

Ich hatte ähnliches Problem (auf PostgreSQL Tough) und auf einer 1M Datensätze Tabelle. Diese Lösung benötigt 1.7s vs 44s, die von demjenigen mit LEFT JOIN produziert wurden. In meinem Fall musste ich die Übereinstimmung Ihres Namensfelds mit NULL-Werten filtern, was zu einer noch besseren Leistung von 0,2 Sekunden führte




Hier ist meine Lösung:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;



Hier sind zwei Vorschläge. Erstens, wenn mysql ROW_NUMBER () unterstützt, ist es sehr einfach:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

Ich nehme an, mit "last" meinen Sie zuletzt in Id-Reihenfolge. Wenn nicht, ändern Sie die ORDER BY-Klausel des ROW_NUMBER () - Fensters entsprechend. Wenn ROW_NUMBER () nicht verfügbar ist, ist dies eine andere Lösung:

Zweitens, wenn dies nicht der Fall ist, ist dies oft ein guter Weg, um fortzufahren:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

Mit anderen Worten, wählen Sie Nachrichten aus, bei denen keine Nachricht mit späterer ID mit demselben Namen vorhanden ist.




MySQL 8.0 unterstützt nun Fensterfunktionen, wie fast alle gängigen SQL-Implementierungen. Mit dieser Standardsyntax können wir Abfragen mit der größten Anzahl an Gruppen pro Gruppe schreiben:

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

Unten ist die ursprüngliche Antwort, die ich für diese Frage im Jahr 2009 geschrieben habe:

Ich schreibe die Lösung so:

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

In Bezug auf die Leistung kann je nach Art Ihrer Daten die eine oder die andere Lösung besser sein. Daher sollten Sie beide Abfragen testen und die Datenbank verwenden, die die Leistung am besten erfüllt.

Zum Beispiel habe ich eine Kopie des -August-Daten-Dumps . Ich werde das für das Benchmarking verwenden. Es gibt 1.114.357 Zeilen in der Posts Tabelle. Dies läuft auf MySQL 5.0.75 auf meinem MacBook Pro 2.40GHz.

Ich schreibe eine Abfrage, um den neuesten Post für eine bestimmte Benutzer-ID (meins) zu finden.

Verwenden Sie zunächst die von @Eric shown Methode mit GROUP BY in einer Unterabfrage:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Selbst die EXPLAIN Analyse dauert 16 Sekunden:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

Produziere jetzt das gleiche Abfrageergebnis mit meiner Technik mit LEFT JOIN :

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

Die EXPLAIN Analyse zeigt, dass beide Tabellen ihre Indizes verwenden können:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

Hier ist die DDL für meine Posts Tabelle:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;



Ich habe noch nicht mit großen DB getestet, aber ich denke, das könnte schneller sein als Tabellen beitreten:

SELECT *, Max(Id) FROM messages GROUP BY Name



Hier ist eine weitere Möglichkeit, den letzten zugehörigen Datensatz mit GROUP_CONCAT mit der Reihenfolge nach und SUBSTRING_INDEX zu erhalten, um einen Datensatz aus der Liste auszuwählen

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

Die Other_Columns Abfrage gruppiert alle Other_Columns , die sich in der gleichen Name Gruppe befinden, und die Verwendung von ORDER BY id DESC verbindet alle Other_Columns in einer bestimmten Gruppe in absteigender Reihenfolge mit dem angegebenen Trennzeichen in meinem Fall, in dem ich || SUBSTRING_INDEX Sie SUBSTRING_INDEX über diese Liste verwenden, wird der erste ausgewählt

Geigen-Demo




Hi @Vijay Dev, wenn Ihre Tabellennachrichten eine ID enthalten, die ein automatischer Inkrement-Primärschlüssel ist, dann, um die neueste Datensatzbasis auf dem Primärschlüssel zu holen, sollte Ihre Abfrage wie folgt lauten:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId



UPD: 2017-03-31, die Version 5.7.5 von MySQL hat den ONLY_FULL_GROUP_BY-Schalter standardmäßig aktiviert (daher wurden nicht-deterministische GROUP BY-Abfragen deaktiviert). Darüber hinaus haben sie die GROUP BY-Implementierung aktualisiert und die Lösung funktioniert möglicherweise nicht mehr wie erwartet, selbst wenn der Schalter deaktiviert ist. Man muss das überprüfen.

Bill Karwins obige Lösung funktioniert gut, wenn die Elementanzahl in Gruppen relativ klein ist, aber die Leistung der Abfrage wird schlecht, wenn die Gruppen ziemlich groß sind, da die Lösung ungefähr n*n/2 + n/2 von nur IS NULL Vergleichen erfordert.

Ich habe meine Tests in einer InnoDB-Tabelle mit 18684446 Zeilen mit 1182 Gruppen durchgeführt. Die Tabelle enthält Testergebnisse für Funktionstests und hat die (test_id, request_id) als Primärschlüssel. Daher ist test_id eine Gruppe und ich suchte nach der letzten request_id für jede test_id .

Bills Lösung läuft schon mehrere Stunden auf meinem Dell E4310 und ich weiß nicht, wann es fertig sein wird, obwohl es auf einem Coverage-Index operiert (daher using index in EXPLAIN).

Ich habe ein paar andere Lösungen, die auf den gleichen Ideen basieren:

  • Wenn der zugrunde liegende Index der BTREE-Index ist (was normalerweise der Fall ist), ist das größte (group_id, item_value) der letzte Wert innerhalb jeder group_id , also der erste für jede group_id wenn wir den Index in absteigender Reihenfolge durchlaufen;
  • Wenn wir die Werte lesen, die von einem Index abgedeckt werden, werden die Werte in der Reihenfolge des Index gelesen;
  • Jeder Index enthält implizit Primärschlüsselspalten, die an diesen angehängt sind (dh der Primärschlüssel befindet sich im Überdeckungsindex). In den folgenden Lösungen arbeite ich direkt mit dem Primärschlüssel, in Ihrem Fall müssen Sie nur Primärschlüsselspalten in das Ergebnis einfügen.
  • In vielen Fällen ist es viel billiger, die erforderlichen Zeilen-IDs in der erforderlichen Reihenfolge in einer Unterabfrage zu erfassen und das Ergebnis der Unterabfrage für die ID zu verknüpfen. Da MySQL für jede Zeile des Unterabfrageergebnisses einen einzigen Abruf basierend auf dem Primärschlüssel benötigt, wird die Unterabfrage zuerst in den Join eingefügt, und die Zeilen werden in der Reihenfolge der IDs in der Unterabfrage ausgegeben (wenn explizite ORDER BY weggelassen werden) für den Beitritt)

3 Wege, wie MySQL Indizes verwendet, sind ein großartiger Artikel, um einige Details zu verstehen.

Lösung 1

Dieser ist unglaublich schnell, es dauert ungefähr 0,8 Sekunden auf meinen 18M + Zeilen:

SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;

Wenn Sie die Reihenfolge in ASC ändern möchten, fügen Sie sie in eine Unterabfrage ein, geben Sie nur die IDs zurück und verwenden Sie diese als Unterabfrage, um sie mit den restlichen Spalten zu verknüpfen:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id), request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

Dieser dauert ungefähr 1,2 Sekunden auf meinen Daten.

Lösung 2

Hier ist eine andere Lösung, die ungefähr 19 Sekunden für meinen Tisch benötigt:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)[email protected]:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

Es gibt Tests auch in absteigender Reihenfolge zurück. Es ist viel langsamer, da es einen vollständigen Index-Scan durchführt, aber es gibt hier eine Idee, wie N max-Zeilen für jede Gruppe ausgegeben werden.

Der Nachteil der Abfrage besteht darin, dass ihr Ergebnis nicht vom Abfrage-Cache zwischengespeichert werden kann.




Sie können auch von hier aus sehen.

http://sqlfiddle.com/#!9/ef42b/9

Erste Lösung

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

Zweite Lösung

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;



SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;



Ich kam zu einer anderen Lösung, nämlich die IDs für den letzten Beitrag innerhalb jeder Gruppe zu erhalten, und dann aus der Nachrichtentabelle das Ergebnis der ersten Abfrage als Argument für ein WHERE x IN Konstrukt auszuwählen:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

Ich weiß nicht, wie das im Vergleich zu anderen Lösungen funktioniert, aber es funktionierte spektakulär für meinen Tisch mit 3 Millionen Zeilen. (4 Sekunden Ausführung mit 1200+ Ergebnissen)

Dies sollte sowohl auf MySQL als auch auf SQL Server funktionieren.




Die unten stehende Abfrage funktioniert nach Ihrer Frage.

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;



Gibt es eine Möglichkeit, mit dieser Methode Duplikate in einer Tabelle zu löschen? Die Ergebnismenge ist im Grunde genommen eine Sammlung eindeutiger Datensätze. Wenn wir also alle Datensätze löschen könnten, die nicht in der Ergebnismenge enthalten sind, hätten wir effektiv keine Duplikate? Ich habe das versucht, aber mySQL gab einen 1093-Fehler.

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

Gibt es eine Möglichkeit, die Ausgabe möglicherweise in eine temporäre Variable zu speichern und dann von NOT IN (temporäre Variable) zu löschen? @ Bill danke für eine sehr nützliche Lösung.

EDIT: Denke ich habe die Lösung gefunden:

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);



Lösung durch Unterabfrage fiddle Link

select * from messages where id in
(select max(id) from messages group by Name)

Lösung Nach Join-Bedingung Geigen-Link

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

Grund für diesen Beitrag ist nur Geige Link zu geben. Dasselbe SQL ist bereits in anderen Antworten enthalten.




Die GROUP BY-Klausel wird in Verbindung mit den Aggregatfunktionen verwendet, um die Ergebnismenge nach einer oder mehreren Spalten zu gruppieren. z.B:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Erinnere dich an diese Reihenfolge:

1) SELECT (wird verwendet, um Daten aus einer Datenbank auszuwählen)

2) FROM (Klausel wird verwendet, um die Tabellen aufzulisten)

3) WHERE (Klausel wird verwendet, um Datensätze zu filtern)

4) GROUP BY (Klausel kann in einer SELECT-Anweisung verwendet werden, um Daten über mehrere Datensätze hinweg zu sammeln und die Ergebnisse nach einer oder mehreren Spalten zu gruppieren)

5) HAVING (Klausel wird in Kombination mit der GROUP BY-Klausel verwendet, um die Gruppen zurückgegebener Zeilen auf diejenigen zu beschränken, deren Bedingung TRUE ist)

6) ORDER BY (Schlüsselwort wird verwendet, um die Ergebnismenge zu sortieren)

Sie können alle diese verwenden, wenn Sie Aggregatfunktionen verwenden, und dies ist die Reihenfolge, in der sie festgelegt werden müssen, andernfalls können Sie einen Fehler erhalten.

Aggregatfunktionen sind:

MIN gibt den kleinsten Wert in einer bestimmten Spalte zurück

SUM gibt die Summe der numerischen Werte in einer bestimmten Spalte zurück

AVG gibt den Durchschnittswert einer bestimmten Spalte zurück

COUNT gibt die Gesamtzahl der Werte in einer bestimmten Spalte zurück

COUNT (*) gibt die Anzahl der Zeilen in einer Tabelle zurück





sql mysql group-by greatest-n-per-group