Récupérer le dernier enregistrement de chaque groupe - MySQL

il y a un tableau messages qui contient les données suivantes:

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

si je lance une requête select * from messages group by name , j'obtiendrai le résultat comme:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

quelle requête retournera le résultat suivant?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

, C'est le dernier enregistrement de chaque groupe doit être retourné.

actuellement, c'est la requête que j'utilise:

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

mais ça a l'air très inefficace. D'autres moyens d'atteindre le même résultat?

706
demandé sur DineshDB 2009-08-21 21:04:04
la source

21 ответов

MySQL 8.0 supporte maintenant les fonctions de fenêtrage, comme presque toutes les implémentations SQL populaires. Avec cette syntaxe standard, Nous pouvons écrire les plus grandes requêtes-n-Par-Groupe:

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;

ci-dessous est la réponse originale que j'ai écrit pour cette question en 2009:


j'écris la solution de cette façon:

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

en ce qui concerne les performances, une solution ou l'autre peut être meilleure, selon le la nature de vos données. Donc, vous devriez tester les deux requêtes et utiliser celle qui est meilleure à la performance étant donné votre base de données.

par exemple, j'ai une copie du StackOverflow August data dump . Je m'en servirai pour le benchmarking. Il y a 1114357 lignes dans le tableau Posts . Il tourne sur MySQL 5.0.75 sur mon Macbook Pro 2.40 GHz.

je vais écrire une requête pour trouver le poste le plus récent, pour un ID utilisateur donné (mine).

D'abord en utilisant la technique montré par @ Eric avec le GROUP BY dans un sous-query:

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)

même le EXPLAIN analyse prend plus de 16 secondes:

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

maintenant produire le même résultat de requête en utilisant ma technique avec 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)

l'analyse EXPLAIN montre que les deux tableaux peuvent utiliser leurs index:

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

Voici la DDL pour ma Posts table:

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;
732
répondu Bill Karwin 2017-12-26 23:38:20
la source

UPD: 2017-03-31, la version 5.7.5 de MySQL fait le seul commutateur onl_full_group_by activé par défaut (donc, groupe non-déterministe par requêtes est devenu désactivé). En outre, ils ont mis à jour le groupe par la mise en œuvre et la solution pourrait ne plus fonctionner comme prévu, même avec le commutateur désactivé. On a besoin de vérifier.

la solution de Bill Karwin ci-dessus fonctionne bien lorsque le nombre d'articles dans les groupes est plutôt petit, mais la performance de la requête devient mauvaise lorsque les groupes sont plutôt grands, puisque la solution nécessite environ n*n/2 + n/2 de seulement IS NULL comparaisons.

j'ai fait mes tests sur une table InnoDB de 18684446 lignes avec 1182 groupes. Le tableau contient les résultats d'essais pour les essais de fonctionnement et a le (test_id, request_id) comme clé principale. Ainsi, test_id est un groupe et je cherchais le dernier request_id pour chaque test_id .

la solution de Bill fonctionne déjà depuis plusieurs heures sur mon dell e4310 et je ne sais pas quand elle va finir, même si elle fonctionne sur un indice de couverture (d'où using index dans EXPLAIN).

j'ai quelques autres solutions qui sont basées sur les mêmes idées:

  • si l'indice sous-jacent est l'indice BTREE (ce qui est habituellement le cas), la plus grande paire (group_id, item_value) est la dernière valeur à l'intérieur de chaque group_id , qui est le premier pour chaque group_id si nous marchons par l'index dans l'ordre décroissant;
  • si nous lisons les valeurs qui sont couverts par un indice, les valeurs sont lues dans l'ordre de l'index;
  • chaque indice contient implicitement des colonnes de clés primaires qui y sont annexées (c'est-à-dire que la clé primaire se trouve dans l'indice de couverture). Dans les solutions ci-dessous, j'fonctionner directement sur la clé primaire, dans votre cas, vous aurez juste besoin d'ajouter des colonnes de clé primaire dans la résultat.
  • dans de nombreux cas, il est beaucoup moins coûteux d'avoir collecté id de ligne dans l'ordre requis dans une sous-requête et rejoindre le résultat de la sous-requête sur l'id. Puisque pour chaque ligne dans le résultat de la sous-requête MySQL aura besoin d'un seul fetch basé sur la clé primaire, la sous-requête sera mise en premier dans la jointure et les lignes seront sorties dans l'ordre des ids dans la sous-requête (si nous omettons l'ordre explicite par pour la jointure)

3 les voies MySQL utilise des index est un grand article pour comprendre certains détails.

Solution 1

celui-ci est incroyablement rapide, il prend environ 0,8 secondes sur mes 18m + rangées:

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

si vous voulez changer l'ordre en ASC, mettez-le dans un sousquery, retourner l'ids seulement et utiliser que comme le sousquery pour joindre au reste des colonnes:

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;

celui-ci prend sur les 1,2 secondes sur mes données.

Solution 2

Voici une autre solution qui prend environ 19 secondes pour ma table:

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

il renvoie aussi les tests par ordre décroissant. Il est beaucoup plus lent puisqu'il effectue un balayage complet de l'index mais il est ici pour vous donner une idée de la façon de produire N Lignes max pour chaque groupe.

L'inconvénient de la requête est que son résultat ne peut pas être mis en cache par le cache de requêtes.

120
répondu newtover 2017-03-31 18:08:26
la source

utilisez votre subquery pour retourner le groupe correct, parce que vous êtes à mi-chemin.

essayez ceci:

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

si ce n'est pas id vous voulez le max de:

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

de cette façon, vous évitez les sous-séries corrélées et/ou l'ordre dans vos sous-séries, qui ont tendance à être très lent/inefficace.

80
répondu Eric 2009-08-21 21:14:13
la source

je suis arrivé à une solution différente, qui est d'obtenir les IDs pour le dernier post dans chaque groupe, puis sélectionner à partir de la table messages en utilisant le résultat de la première requête comme argument pour un WHERE x IN construire:

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

Je ne sais pas comment cela fonctionne par rapport à certaines autres solutions, mais il a fonctionné de façon spectaculaire pour ma table avec 3+ millions de lignes. (4 seconde d'exécution avec 1200+ résultats)

This devrait fonctionner à la fois sur MySQL et SQL Server.

34
répondu JYelton 2012-02-21 01:46:38
la source

Solution par la sous-requête violon Lien

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

Solution Par condition de jointure violon lien

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

la raison de ce post est de donner fiddle link seulement. Même SQL est déjà fourni dans d'autres réponses.

24
répondu Vipin 2013-12-25 12:36:42
la source

Je n'ai pas encore testé avec grand DB, mais je pense que cela pourrait être plus rapide que de joindre des tables:

SELECT *, Max(Id) FROM messages GROUP BY Name
6
répondu Shai 2013-02-14 11:07:11
la source

voici deux suggestions. Tout d'abord, si mysql supporte ROW_NUMBER (), c'est très simple:

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;

je suppose que par "dernier" vous voulez dire dernier dans L'ordre D'identification. Sinon, changez l'ordre Par clause de la fenêtre ROW_NUMBER() en conséquence. Si ROW_NUMBER () n'est pas disponible, c'est une autre solution:

deuxièmement, si ce n'est pas le cas, c'est souvent une bonne façon de procéder:

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

en d'autres termes, sélectionnez messages où il n'est pas un message postérieur-Id avec le même nom.

4
répondu Steve Kass 2009-08-21 21:26:12
la source

voici ma solution:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;
4
répondu Abhishek Yadav 2017-06-08 22:03:49
la source

Voici une autre façon d'obtenir le dernier enregistrement lié en utilisant GROUP_CONCAT avec commande par et SUBSTRING_INDEX pour choisir un de l'enregistrement de la liste

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

la requête ci-dessus regroupera tous les Other_Columns qui sont dans le même groupe Name et en utilisant ORDER BY id DESC se joindra à tous les Other_Columns dans un groupe spécifique dans l'ordre décroissant avec le séparateur fourni dans mon cas, j'ai utilisé || , en utilisant SUBSTRING_INDEX au-dessus de cette liste choisira le premier

Violon Démo

3
répondu M Khalid Junaid 2014-03-30 10:01:52
la source
SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;
3
répondu jeet singh parmar 2014-05-04 15:38:30
la source

essayez ceci:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  
2
répondu Pro Web Design 2011-07-15 17:47:27
la source

vous pouvez voir d'ici aussi.

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

PREMIÈRE SOLUTION

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

DEUXIÈME SOLUTION

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
2
répondu Shrikant Gupta 2015-09-28 12:07:12
la source

y a-t-il un moyen d'utiliser cette méthode pour supprimer les doublons d'un tableau? Le jeu de résultats est essentiellement une collection d'enregistrements uniques, donc si nous pouvions supprimer tous les enregistrements qui ne sont pas dans le jeu de résultats, nous n'aurions effectivement pas de doublons? J'ai essayé mais mySQL a donné une erreur 1093.

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)

y a-t-il un moyen de sauvegarder la sortie sur une variable temp puis de supprimer De NOT IN (temp variable)? @Bill merci pour cette solution très utile.

EDIT: Pense que j'ai trouvé la solution:

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);
1
répondu Simon 2010-10-08 05:57:49
la source

la requête ci-dessous fonctionnera très bien selon votre question.

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;
1
répondu Teja 2011-11-19 00:21:00
la source

Salut @Vijay Dev si votre table messages contient Id qui est incrément automatique de clé primaire, puis à récupérer le dernier enregistrement de base sur la clé primaire de votre requête devrait se lire comme suit:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
1
répondu bikashphp 2014-10-21 18:08:16
la source

si vous voulez la dernière ligne pour chaque Name , alors vous pouvez donner un numéro de ligne à chaque groupe de ligne par le Name et l'ordre par Id dans l'ordre décroissant.

REQUÊTE

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL Violon

1
répondu Wanderer 2015-11-19 07:36:11
la source

une approche à grande vitesse est la suivante.

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

résultat

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1
1
répondu Song Zhengyi 2018-03-10 23:33:11
la source

Que pensez-vous de ceci:

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

j'ai eu un problème similaire (sur PostgreSQL tough) et sur une table de 1m records. Cette solution prend 1.7 S vs 44s produit par celui avec jointure gauche. Dans mon cas, j'ai dû filtrer le correspondant de votre champ nom contre les valeurs nulles, ce qui a donné des performances encore meilleures de 0,2 secondes

0
répondu Azathoth 2016-11-30 13:50:40
la source

Clairement, il y a beaucoup de façons différentes d'obtenir les mêmes résultats, votre question semble être ce qui est un moyen efficace d'obtenir les derniers résultats de chaque groupe dans MySQL. Si vous travaillez avec d'énormes quantités de données et en supposant que vous utilisez InnoDB avec même les dernières versions de MySQL (comme 5.7.21 et 8.0.4-rc) alors il pourrait ne pas y avoir une façon efficace de le faire.

nous avons parfois besoin de faire cela avec des tableaux avec encore plus de 60 millions de lignes.

dans ces exemples, je vais utiliser les données avec seulement environ 1,5 million de lignes où les requêtes faut trouver des résultats pour tous les groupes de données. Dans nos cas réels, nous aurions souvent besoin de retourner les données d'environ 2 000 groupes (ce qui, hypothétiquement, ne nécessiterait pas l'examen d'une grande partie des données).

j'utiliserai les tableaux suivants:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

la table des températures est peuplée avec environ 1,5 millions de et avec 100 groupes différents. Le groupe selected_group est peuplé de ces 100 groupes (dans nos cas, cela serait normalement inférieur à 20% pour tous les groupes).

comme ces données sont aléatoires, cela signifie que plusieurs lignes peuvent avoir les mêmes dates d'enregistrement. Ce que nous voulons, c'est obtenir une liste de tous les groupes sélectionnés afin de groupID avec la dernière recordedTimestamp pour chaque groupe, et si le même groupe a plus d'une ligne correspondante comme ça, puis la dernière ID correspondant à ces lignes.

si hypothétiquement MySQL avait une fonction last () qui retournait les valeurs de la dernière ligne dans un ordre spécial par clause, alors nous pourrions simplement faire:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

qui n'aurait besoin d'examiner que quelques 100 lignes dans ce cas car il n'utilise aucun des groupes normaux par fonctions. Cela s'exécuterait en 0 secondes et serait donc très efficace. Notez que normalement dans MySQL nous verrons un ordre Par clause suivant le groupe Par clause cependant cet ordre Par clause est utilisé pour déterminer l'ordre de la dernière fonction (), si elle était après le groupe à ce moment-là il serait ordonner les groupes. Si aucun groupe par clause n'est présent, les dernières valeurs seront les mêmes dans toutes les lignes retournées.

cependant MySQL n'a pas cela alors regardons différentes idées de ce qu'il a et prouvons qu'aucun de ceux-ci sont efficaces.

exemple 1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

cela a examiné 3 009 254 lignes et a pris ~0,859 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

cela a examiné 1.505.331 lignes et a pris ~1.25 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

cela a examiné 3 009 685 rangées et a pris ~1,95 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

cela a examiné 6 137 810 rangées et a pris ~2,2 secondes sur 5.7.21 et légèrement plus long sur 8.0.4-rc

exemple 5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

cela a examiné 6 017 808 lignes et a pris ~ 4,2 secondes sur 8.0.4-rc

exemple 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

cela a examiné 6 017 908 lignes et a pris ~17,5 secondes sur 8.0.4-rc

exemple 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

celui-ci prenait une éternité donc j'ai dû le tuer.

0
répondu Yoseph 2018-04-30 09:20:59
la source

si la performance est vraiment votre préoccupation, vous pouvez introduire une nouvelle colonne sur la table appelée IsLastInGroup de type BIT.

positionne true sur les colonnes qui sont les dernières et le maintient avec chaque ligne insert/update/delete. Les Écritures seront plus lentes, mais vous profiterez des lectures. Cela dépend de votre cas d'utilisation, et je recommande que si vous avez lu.

donc votre requête ressemblera à:

SELECT * FROM Messages WHERE IsLastInGroup = 1
0
répondu jabko87 2018-05-02 18:05:59
la source
select * from messages group by name desc
-2
répondu huuang 2016-06-18 17:21:07
la source

Autres questions sur sql greatest-n-per-group mysql group-by