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

Überprüfung von Clints großartiger und richtiger Antwort von oben:

Interessant ist die Leistung zwischen den beiden Abfragen unten. 52% ist die erste. Und 48 % bei der zweiten. Eine Leistungsverbesserung von 4 % bei Verwendung von DISTINCT anstelle von ORDER BY. ORDER BY hat jedoch den Vorteil, dass nach mehreren Spalten sortiert werden kann.

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

Option 1:

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

Option 2:

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #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 #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

In Microsoft SQL Server Management Studio: Nachdem Sie den ersten Block markiert und ausgeführt haben, markieren Sie sowohl Option 1 als auch Option 2, klicken Sie mit der rechten Maustaste -> [Geschätzten Ausführungsplan anzeigen]. Führen Sie dann den gesamten Block aus, um die Ergebnisse zu sehen.

Option 1 Ergebnisse:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

Option 2 Ergebnisse:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

注

Ich neige dazu, APPLY zu verwenden, wenn ich eine Verknüpfung von 1 zu (1 von vielen) haben möchte.

Ich verwende einen JOIN, wenn die Verknüpfung 1-to-many oder many-to-many sein soll.

Ich vermeide CTE mit ROW_NUMBER(), es sei denn, ich muss etwas Fortgeschrittenes tun und bin mit den Leistungseinbußen beim Windowing einverstanden.

Ich vermeide auch EXISTS / IN-Unterabfragen in der WHERE- oder ON-Klausel, da ich die Erfahrung gemacht habe, dass dies einige schreckliche Ausführungspläne verursacht. Aber die Erfahrungen variieren. Überprüfen Sie den Ausführungsplan und erstellen Sie bei Bedarf ein Leistungsprofil!

Answer 2

Diese Lösung kann verwendet werden, um die TOP N neuesten Zeilen für jede Partition zu erhalten (im Beispiel ist N gleich 1 in der WHERE-Anweisung und die Partition ist doc_id):

SELECT T.doc_id, T.status, T.date_created FROM 
(
    SELECT a.*, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY date_created DESC) AS rnk FROM doc a
) T
WHERE T.rnk = 1;

Answer 3

CROSS APPLY war die Methode, die ich für meine Lösung verwendet habe, da sie für mich und die Bedürfnisse meiner Kunden funktioniert hat. Und nach dem, was ich gelesen habe, sollte sie die beste Gesamtleistung bieten, wenn ihre Datenbank erheblich wächst.

Answer 4

SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

Wenn Sie nur die neuesten Dokumente zurückgeben möchten, sortiert nach DateCreated, werden nur die ersten 1 Dokumente nach DocumentID zurückgegeben.

Answer 5

Einige Datenbank-Engines* beginnen mit der Unterstützung der QUALIFY Klausel, die es ermöglicht, das Ergebnis von Fensterfunktionen zu filtern (die die akzeptierte Antwort verwendet).

Die akzeptierte Antwort kann also lauten

SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
QUALIFY rn = 1

In diesem Artikel finden Sie eine ausführliche Erklärung: https://jrandrews.net/the-joy-of-qualify

Mit diesem Tool können Sie feststellen, welche Datenbanken diese Klausel unterstützen: https://www.jooq.org/translate/ Es gibt eine Option zur Umwandlung der Qualifizierungsklausel, wenn der Zieldialekt sie nicht unterstützt.

*Teradata, BigQuery, H2, Snowflake...