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

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

Wenn Sie 2 Einträge pro Tag erwarten, wird willkürlich einer ausgewählt. Um beide Einträge für einen Tag zu erhalten, verwenden Sie stattdessen DENSE_RANK

Ob normalisiert oder nicht, hängt davon ab, ob Sie das wollen:

• Status an 2 Stellen beibehalten
• Statusgeschichte bewahren
• ...

So wie es jetzt ist, behalten Sie den Statusverlauf bei. Wenn Sie den neuesten Status auch in der übergeordneten Tabelle haben möchten (was eine Denormalisierung darstellt), benötigen Sie einen Trigger, um den "Status" in der übergeordneten Tabelle zu erhalten. oder Sie lassen diese Statusverlaufstabelle fallen.

Answer 2

Ich habe gerade gelernt, wie man cross apply . Hier ist, wie man es in diesem Szenario verwendet:

 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

Ich weiß, dies ist ein altes Thema, aber die TOP 1 WITH TIES Lösungen ist ganz nett und könnte für einige beim Durchlesen der Lösungen hilfreich sein.

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

El select top 1 with ties Klausel teilt SQL Server mit, dass Sie die erste Zeile pro Gruppe zurückgeben möchten. Aber woher weiß SQL Server, wie die Daten zu gruppieren sind? Hier kommt die order by row_number() over (partition by DocumentID order by DateCreated desc kommt rein. Die Spalte/Spalten nach partition by definiert, wie SQL Server die Daten gruppiert. Innerhalb jeder Gruppe werden die Zeilen auf der Grundlage der order by Spalten. Nach der Sortierung wird die oberste Zeile in jeder Gruppe in der Abfrage zurückgegeben.

Weitere Informationen über die TOP-Klausel finden Sie unter aquí .

Answer 4

Ich habe einige Tests mit den verschiedenen Empfehlungen hier durchgeführt, und die Ergebnisse hängen wirklich von der Größe der betroffenen Tabelle ab, aber die konsistenteste Lösung ist die Verwendung von CROSS APPLY Diese Tests wurden mit SQL Server 2008-R2 durchgeführt, wobei eine Tabelle mit 6.500 Datensätzen und eine andere (identisches Schema) mit 137 Millionen Datensätzen verwendet wurde. Die abgefragten Spalten sind Teil des Primärschlüssels der Tabelle, und die Tabellenbreite ist sehr gering (etwa 30 Byte). Die Zeiten werden von SQL Server anhand des tatsächlichen Ausführungsplans gemeldet.

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

Das wirklich Erstaunliche war, wie konstant die Zeit für das CROSS APPLY war, unabhängig von der Anzahl der beteiligten Reihen.

Answer 5

Wenn Sie sich Sorgen um die Leistung machen, können Sie dies auch mit MAX() tun:

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

ROW_NUMBER() erfordert eine Sortierung aller Zeilen in Ihrer SELECT-Anweisung, während MAX dies nicht tut. Das sollte Ihre Abfrage drastisch beschleunigen.