Die oberste 1 Zeile jeder Gruppe erhalten

Question

Ich habe eine Tabelle, aus der ich den neuesten Eintrag für jede Gruppe abrufen möchte. Hier ist die Tabelle:

DocumentStatusLogs Tabelle

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

Die Tabelle wird gruppiert nach DocumentID und sortiert nach DateCreated in absteigender Reihenfolge. Für jede DocumentID Ich möchte den neuesten Stand erfahren.

Meine bevorzugte Ausgabe:

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

• Gibt es eine Aggregatfunktion, um nur die besten Ergebnisse aus jeder Gruppe zu erhalten? Siehe Pseudocode GetOnlyTheTop unten:

  SELECT
    DocumentID,
    GetOnlyTheTop(Status),
    GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC

• Wenn eine solche Funktion nicht existiert, gibt es dann eine Möglichkeit, die gewünschte Ausgabe zu erreichen?

• Oder könnte dies in erster Linie auf eine nicht normalisierte Datenbank zurückzuführen sein? Ich denke, da ich nur nach einer Zeile suche, sollte diese status auch in der übergeordneten Tabelle zu finden sein?

Weitere Informationen finden Sie in der übergeordneten Tabelle:

Aktuell Documents Tabelle

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

Sollte die übergeordnete Tabelle so aussehen, dass ich leicht auf ihren Status zugreifen kann?

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

アップデイト Ich habe gerade gelernt, wie man "Anwenden" benutzt, was es einfacher macht, solche Probleme zu lösen.

Answer 1

Dies ist eine der am leichtesten zu findenden Fragen zu diesem Thema, daher wollte ich eine moderne Antwort darauf geben (sowohl für meine Referenz als auch um anderen zu helfen). Unter Verwendung von first_value y over können Sie die obige Abfrage schnell erledigen:

Select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

Dies sollte in Sql Server 2008 und höher funktionieren. First_value kann als ein Weg betrachtet werden, um Folgendes zu erreichen Select Top 1 bei Verwendung eines over Klausel. Over ermöglicht die Gruppierung in der Auswahlliste, so dass anstelle des Schreibens von verschachtelten Unterabfragen (wie viele der vorhandenen Antworten tun), dies tut es in einer besser lesbaren Weise. Hoffentlich hilft das.

Answer 2

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

Welcher Datenbankserver? Dieser Code funktioniert nicht auf allen.

Was die zweite Hälfte Ihrer Frage betrifft, so erscheint es mir sinnvoll, den Status als Spalte aufzunehmen. Sie können den DocumentStatusLogs als Protokoll, speichert aber trotzdem die neuesten Informationen in der Haupttabelle.

Übrigens, wenn Sie bereits die DateCreated Spalte in der Tabelle Dokumente können Sie einfach verbinden DocumentStatusLogs verwenden (solange die DateCreated ist einzigartig in DocumentStatusLogs ).

Bearbeiten: MsSQL unterstützt USING nicht, also ändern Sie es in:

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

Answer 3

Hier sind 3 separate Ansätze für das Problem in der Hand zusammen mit den besten Entscheidungen der Indizierung für jede dieser Abfragen (bitte versuchen Sie, die Indizes selbst und sehen Sie die logische lesen, verstrichene Zeit, Ausführungsplan. Ich habe die Vorschläge aus meiner Erfahrung mit solchen Abfragen gemacht, ohne sie für dieses spezielle Problem auszuführen).

Ansatz 1 : Verwendung von ROW_NUMBER(). Wenn rowstore index nicht in der Lage ist, die Leistung zu verbessern, können Sie nonclustered/clustered columnstore index ausprobieren, da für Abfragen mit Aggregation und Gruppierung und für Tabellen, die nach in verschiedenen Spalten geordnet sind, columnstore index normalerweise die beste Wahl ist.

;WITH CTE AS
    (
       SELECT   *,
                RN = ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
       FROM     DocumentStatusLogs
    )
    SELECT  ID      
        ,DocumentID 
        ,Status     
        ,DateCreated
    FROM    CTE
    WHERE   RN = 1;

Ansatz 2 : Verwendung von FIRST_VALUE. Wenn der Rowstore-Index nicht in der Lage ist, die Leistung zu verbessern, können Sie den nicht geclusterten/geclusterten Columnstore-Index ausprobieren, da für Abfragen mit Aggregation und Gruppierung und für Tabellen, die immer nach verschiedenen Spalten geordnet sind, der Columnstore-Index normalerweise die beste Wahl ist.

SELECT  DISTINCT
    ID      = FIRST_VALUE(ID) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DocumentID
    ,Status     = FIRST_VALUE(Status) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
    ,DateCreated    = FIRST_VALUE(DateCreated) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
FROM    DocumentStatusLogs;

Ansatz 3 : CROSS APPLY verwenden. Das Erstellen eines Rowstore-Index auf der Tabelle DocumentStatusLogs, der die in der Abfrage verwendeten Spalten abdeckt, sollte ausreichen, um die Abfrage ohne die Notwendigkeit eines Columnstore-Index abzudecken.

SELECT  DISTINCT
    ID      = CA.ID
    ,DocumentID = D.DocumentID
    ,Status     = CA.Status 
    ,DateCreated    = CA.DateCreated
FROM    DocumentStatusLogs D
    CROSS APPLY (
            SELECT  TOP 1 I.*
            FROM    DocumentStatusLogs I
            WHERE   I.DocumentID = D.DocumentID
            ORDER   BY I.DateCreated DESC
            ) CA;

Answer 4

Dies ist ein ziemlich altes Thema, aber ich dachte, ich würde meine Meinung dazu sagen, da die akzeptierte Antwort für mich nicht besonders gut funktioniert hat. Ich habe die Lösung von gbn mit einem großen Datensatz ausprobiert und festgestellt, dass sie furchtbar langsam ist (>45 Sekunden bei mehr als 5 Millionen Datensätzen in SQL Server 2012). Wenn man sich den Ausführungsplan ansieht, ist es offensichtlich, dass das Problem darin besteht, dass eine SORT-Operation erforderlich ist, die die Dinge erheblich verlangsamt.

Hier ist eine Alternative, die ich von der Entität-Framework, die keine SORT-Operation benötigt und tut eine NON-Clustered Index-Suche angehoben. Dies reduziert die Ausführungszeit auf < 2 Sekunden für den oben erwähnten Datensatz.

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]

Jetzt nehme ich etwas an, das in der ursprünglichen Frage nicht ganz spezifiziert ist, aber wenn Ihr Tabellendesign so ist, dass Ihre ID-Spalte eine automatisch inkrementierende ID ist und das DateCreated bei jeder Einfügung auf das aktuelle Datum gesetzt wird, dann könnten Sie sogar ohne meine obige Abfrage eine beträchtliche Leistungssteigerung gegenüber der Lösung von gbn erhalten (etwa die Hälfte der Ausführungszeit), nur durch Sortierung nach ID statt nach Erstellungsdatum da dies eine identische Sortierreihenfolge ergibt und eine schnellere Sortierung ist.

Answer 5

Mein Code zur Auswahl der besten 1 aus jeder Gruppe

select a.\* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)