Obtenez haut 1 ligne de chaque groupe

Question

J'ai un tableau que je veux obtenir la dernière entrée pour chaque groupe. Voici le tableau:

DocumentStatusLogs Tableau

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

La table seront regroupés par DocumentID et triés par DateCreated dans l'ordre décroissant. Pour chaque DocumentID, je veux obtenir de l'état le plus récent.

Mon préféré de sortie:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |

• Est-il une fonction d'agrégation pour obtenir seulement le haut de chaque groupe? Voir le code pseudo- GetOnlyTheTop ci-dessous:

  select DocumentID, GetOnlyTheTop(Status), GetOnlyTheTop(DateCreated) from DocumentStatusLogs group by DocumentID order by DateCreated desc

• Si une telle fonction n'existe pas, est il possible que je puisse atteindre la sortie que je veux?

• Ou à la première place, cela pourrait être causé par unnormalized base de données? Je pense, depuis que je suis à la recherche juste une ligne, devraient - status également se trouver dans la table parent?

Veuillez consulter la table parent pour plus d'informations:

Courant Documents Tableau

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

Si la table parent et d'être comme ça pour que je puisse facilement accéder à son statut?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

Merci à l'avance!

Mise à JOUR J'ai juste appris à utiliser "appliquer", ce qui rend plus facile pour résoudre ces problèmes.

Answer 1

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

Si vous vous attendez à 2 entrées par jour, alors ce sera arbitrairement choisir un. Pour obtenir les deux entrées pour une journée, utilisez DENSE_RANK au lieu

Comme pour normalisé ou non, cela dépend si vous voulez:

• maintenir le statut dans 2 lieux
• préserver le statut de l'histoire
• ...

Comme il est, vous conservez le statut de l'histoire. Si vous voulez plus récents dans la table parent (qui est dé-normalisation), vous auriez besoin d'un déclencheur pour maintenir le "statut" dans le parent. ou, une baisse historique du statut de la table.

Answer 2

Je viens d'apprendre comment utiliser cross apply. Voici comment l'utiliser dans ce scénario:

 select d.DocumentID, ds.Status, ds.DateCreated 
 from Documents as d 
 cross apply 
     (select top 1 Status, DateCreated
      from DocumentStatusLogs 
      where DocumentID = d.DocumentId
      order by DateCreated desc) as ds

Answer 3

Si vous êtes inquiet au sujet de la performance, vous pouvez aussi le faire avec MAX():

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER() nécessite un tri de toutes les lignes de votre instruction SELECT, tandis que MAX ne l'est pas. Devrait considérablement augmenter la vitesse de votre requête.

Answer 4

SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

Ce serveur de base de données? Ce code ne fonctionne pas sur tous.

Quant à la deuxième partie de votre question, il me semble raisonnable d'inclure le statut de la colonne. Vous pouvez quitter DocumentStatusLogs comme un journal, mais toujours stocker les dernières infos dans la table principale.

BTW, si vous avez déjà l' DateCreated colonne dans le tableau des Documents, vous pouvez joignez DocumentStatusLogs en utilisant que (en tant que DateCreated est unique en DocumentStatusLogs).

Edit: MsSQL ne prend pas en charge l'UTILISATION, de manière à le modifier:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

Answer 5

C'est tout à fait un vieux thread, mais j'ai pensé que je jetterais mon grain de sel dans la même accepté de répondre ne fonctionne pas particulièrement bien pour moi. J'ai essayé gbn de la solution sur un vaste ensemble de données et trouve qu'il est terriblement lent (>45 secondes sur 5 millions, plus de dossiers dans SQL Server 2012). En regardant le plan d'exécution, il est évident que le problème est qu'il nécessite une opération de TRI qui ralentit les choses de manière significative.

Voici une autre que j'ai levé le cadre de l'entité qui a besoin d'aucune opération de TRI et un Index NON ordonné en Clusters de recherche. Cela réduit le temps d'exécution vers le bas à < 2 secondes) sur ledit ensemble d'enregistrements.

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

Maintenant, je suis en supposant que quelque chose qui n'est pas précisé dans la question d'origine, mais si votre table est conçue de telle manière que votre colonne ID est en auto-increment, l'ID et le DateCreated est fixé à la date du jour, avec chaque insertion, alors, même sans courir avec ma requête ci-dessus, vous pouvez effectivement bénéficier d'un joli coup de pouce de performance de gbn de la solution (environ la moitié du temps d'exécution) de la commande de l'ID au lieu de commander sur DateCreated ce qui permettra d'obtenir un nombre identique de l'ordre de tri et il est plus rapide de tri.