Uzyskaj pierwszy rząd z każdej grupy

Właśnie nauczyłem się używać cross apply. Oto jak go użyć w tym scenariuszu:

 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

Zrobiłem kilka timingów na różnych zaleceń tutaj, a wyniki naprawdę zależą od wielkości tabeli zaangażowanych, ale najbardziej spójne rozwiązanie jest za pomocą Cross APPLY te testy były uruchamiane przeciwko SQL Server 2008-R2, przy użyciu tabeli z 6500 rekordów, a inny (identyczny schemat) z 137 milionów rekordów. Kolumny będące przedmiotem zapytania są częścią klucza podstawowego tabeli, a szerokość tabeli jest bardzo mała (około 30 bajtów). Czasy są zgłaszane przez SQL Server z rzeczywisty plan egzekucji.

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

Myślę, że naprawdę zdumiewające było to, jak spójny był czas na krzyż aplikuj niezależnie od liczby wierszy zaangażowanych.

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

Jaki serwer baz danych? Ten kod nie działa na wszystkich.

BTW, jeśli masz już kolumnę DateCreated w tabeli dokumentów możesz po prostu dołączyć DocumentStatusLogs używając tego (o ile DateCreated jest unikalna w DocumentStatusLogs).

Edit: MsSQL nie obsługuje używania, więc zmień go na:

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

Jeśli martwisz się o wydajność, możesz to zrobić również za pomocą MAX():

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

ROW_NUMBER () wymaga rodzaju wszystkich wierszy w instrukcji SELECT, podczas gdy MAX nie. Powinno drastycznie przyspieszyć Twoje zapytanie.

To dość stary wątek, ale pomyślałem, że wrzucę moje dwa grosze tak samo, jak zaakceptowana odpowiedź nie działa szczególnie dobrze dla mnie. Wypróbowałem rozwiązanie gbn na dużym zbiorze danych i okazało się, że jest strasznie wolne (>45 sekund na 5 milionach rekordów plus W Sql Server 2012). Patrząc na plan wykonania jest oczywiste, że problem polega na tym, że wymaga operacji sortowania, która znacznie spowalnia rzeczy.

Oto alternatywa, którą usunąłem ze struktury bytu to nie wymaga operacji sortowania i nie-Clustered indeks wyszukiwania. Skraca to czas wykonania do

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]

Teraz zakładam coś, co nie jest do końca określone w oryginalnym pytaniu, ale jeśli twój projekt tabeli jest taki, że kolumna ID jest auto-increment ID, a DateCreated jest ustawiony na bieżącą datę z każdym wkładem, to nawet bez uruchamiania z mojego zapytania powyżej można rzeczywiście uzyskać znaczny wzrost wydajności do rozwiązanie gbn (około połowa czasu realizacji) tylko od zamawiania NA ID zamiast zamawiania na DateCreated , ponieważ zapewni to identyczną kolejność sortowania i jest to szybsze sortowanie.

Wiem, że to stary wątek, ale TOP 1 WITH TIES rozwiązania są całkiem miłe i mogą być pomocne dla niektórych czytających rozwiązania.

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

Więcej o klauzuli TOP można znaleźć tutaj .

Mój kod do wybrania top 1 z każdej grupy

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

Jest to jedno z najłatwiejszych pytań na ten temat, więc chciałem dać nowoczesną odpowiedź na to pytanie(zarówno w celach informacyjnych, jak i aby pomóc innym). Używając over I first value możesz wykonać krótkie działanie powyższego zapytania:

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

To powinno działać w sql server 2008 i nowszych. Pierwsza wartość może być traktowana jako sposób osiągnięcia select top 1 przy użyciu klauzuli over. Over pozwala na grupowanie na liście select, więc zamiast pisać zagnieżdżone zapytania podrzędne (jak wiele istniejących odpowiedzi zrobić), to robi to w bardziej czytelny sposób. Mam nadzieję, że to pomoże.

Sprawdzanie niesamowitej i poprawnej odpowiedzi z góry:

Wydajność pomiędzy dwoma zapytaniami poniżej jest interesująca. 52% jest na szczycie. A 48% to drugie. 4% poprawa wydajności przy użyciu odrębnych zamiast kolejności przez. Ale ORDER BY ma tę zaletę, że sortuje po wielu kolumnach.

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

Wariant 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])

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

M$'s Management Studio: po podświetleniu i uruchomieniu pierwszego bloku podświetl zarówno opcję 1, Jak I opcję 2, Kliknij prawym przyciskiem myszy - >[Pokaż szacunkowy Plan realizacji]. Następnie uruchom całą rzecz, aby zobaczyć wyniki.

Opcja 1 Wyniki:

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

Opcja 2 Wyniki:

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

Uwaga:

Zazwyczaj używam APPLY, gdy chcę, aby join był 1-to-(1 z wielu).

Używam JOIN, jeśli chcę, aby join był 1 do wielu, lub wiele do wielu.

Unikam CTE z ROW_NUMBER () chyba, że muszę zrobić coś Zaawansowanego i jestem ok z wydajnością okna kara.

Unikam również EXISTS / IN subqueries w klauzuli WHERE lub ON, ponieważ doświadczyłem tego, powodując straszne plany wykonania. Ale przebieg jest różny. Sprawdź plan realizacji i wydajność profilu, gdzie i kiedy jest to potrzebne!

W scenariuszach, w których chcesz uniknąć użycia metody row_count (), możesz również użyć lewego łącznika:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

Dla przykładowego schematu można również użyć zapytania podrzędnego "not in", które zazwyczaj kompiluje się do tego samego wyjścia, co lewe połączenie:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

Uwaga, wzorzec zapytań podrzędnych nie działałby, gdyby tabela nie miała co najmniej jednego jednokolumnowego unikalnego klucza / ograniczenia / indeksu, w tym przypadku klucz główny "Id".

Oba te zapytania wydają się być bardziej "drogie" niż row_count() zapytanie (mierzone analizatorem zapytań). Możesz jednak napotkać scenariusze, w których szybciej zwracają wyniki lub umożliwiają inne optymalizacje.

Spróbuj tego:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

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

Jeśli chcesz zwrócić tylko najnowszą kolejność dokumentów według DateCreated, zwróci tylko top 1 dokument według DocumentID

To jest najbardziej waniliowy TSQL jaki mogę wymyślić

    SELECT * FROM DocumentStatusLogs D1 JOIN
    (
      SELECT
        DocumentID,MAX(DateCreated) AS MaxDate
      FROM
        DocumentStatusLogs
      GROUP BY
        DocumentID
    ) D2
    ON
      D2.DocumentID=D1.DocumentID
    AND
      D2.MaxDate=D1.DateCreated

Jest zaznaczone w SQLite, że możesz użyć następującego prostego zapytania z GROUP BY

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

Tutaj MAX pomoc, aby uzyskać maksymalną DateCreated z każdej grupy.

Ale wygląda na to, że MYSQL nie kojarzy kolumn *-z wartością max DateCreated : (