Zdobądź 1 górny rząd każdej grupy

Mam tabelę, w której chcę uzyskać najnowszy wpis dla każdej grupy. Oto tabela:

DocumentStatusLogs Stół

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

Tabela zostanie pogrupowana DocumentIDi posortowana według DateCreatedmalejącej kolejności. Dla każdego DocumentIDchcę uzyskać najnowszy status.

Moja preferowana wydajność:

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

• Czy jest jakaś funkcja agregująca, która pozwala uzyskać tylko szczyt z każdej grupy? Zobacz pseudo-kod GetOnlyTheTopponiżej:

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

• Jeśli taka funkcja nie istnieje, czy jest jakiś sposób na osiągnięcie pożądanej wydajności?

• A może po pierwsze, może to być spowodowane nienormalizowaną bazą danych? Zastanawiam się, skoro szukam tylko jednego wiersza, czy powinien on statusrównież znajdować się w tabeli nadrzędnej?

Więcej informacji znajduje się w tabeli nadrzędnej:

Aktualna Documentstabela

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

Czy tabela nadrzędna powinna być taka, aby móc łatwo uzyskać dostęp do jej statusu?

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

AKTUALIZACJA Właśnie nauczyłem się używać „aplikuj”, co ułatwia rozwiązywanie takich problemów.

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

Jeśli oczekujesz 2 wpisów dziennie, to wybierze jeden. Aby uzyskać oba wpisy na jeden dzień, użyj zamiast tego DENSE_RANK

Jeśli chodzi o znormalizowane, czy nie, zależy to od tego, czy chcesz:

• utrzymać status w 2 miejscach
• zachowaj historię statusu
• ...

Na obecnym etapie zachowujesz historię statusu. Jeśli chcesz mieć również najnowszy status w tabeli nadrzędnej (czyli denormalizacji), potrzebujesz wyzwalacza, aby zachować „status” w rodzicu. lub upuść tę tabelę historii statusu.

Właśnie nauczyłem się korzystać 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

Dokonałem tutaj pewnych korekt w stosunku do różnych zaleceń tutaj, a wyniki naprawdę zależą od wielkości zaangażowanej tabeli, ale najbardziej spójnym rozwiązaniem jest użycie APLIKACJI KRZYŻOWEJ. Te testy zostały uruchomione na SQL Server 2008-R2, przy użyciu tabeli z 6500 rekordów i kolejny (identyczny schemat) z 137 milionami rekordów. Zapytane kolumny są częścią klucza podstawowego tabeli, a szerokość tabeli jest bardzo mała (około 30 bajtów). Czasy są raportowane przez SQL Server z rzeczywistego planu wykonania.

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ę niesamowitą rzeczą było to, jak konsekwentny był czas na APLIKACJĘ KRZYŻOWĄ, niezależnie od liczby zaangażowanych wierszy.

Wiem, że to stary wątek, ale TOP 1 WITH TIESrozwiązania są całkiem fajne i mogą być pomocne w lekturze tych rozwiązań.

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

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

Jeśli martwisz się wydajnością, możesz to zrobić 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ć zapytanie.

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

Jaki serwer bazy danych? Ten kod nie działa na wszystkich z nich.

Jeśli chodzi o drugą połowę twojego pytania, wydaje mi się rozsądne, aby dołączyć status jako kolumnę. Możesz wyjśćDocumentStatusLogs jako dziennik, ale nadal przechowywać najnowsze informacje w głównej tabeli.

BTW, jeśli masz już DateCreatedkolumnę w tabeli Dokumentów, możesz po prostu dołączyć, DocumentStatusLogsużywając tej (o ile DateCreatedjest to unikalne w DocumentStatusLogs).

Edycja: MsSQL nie obsługuje USING, więc zmień na:

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

To jedno z najłatwiejszych pytań na ten temat, dlatego chciałem udzielić na nie nowoczesnej odpowiedzi (zarówno w celach informacyjnych, jak i pomocy innym). Używając first_valuei overmożesz wykonać krótką pracę z powyższym zapytaniem:

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

Powinno to działać w Sql Server 2008 i nowszych wersjach. First_valuemoże być traktowany jako sposób na osiągnięcie celu Select Top 1przy użyciu overklauzuli. Overumożliwia grupowanie na liście wyboru, więc zamiast pisać zagnieżdżone podzapytania (jak robi to wiele istniejących odpowiedzi), robi to w bardziej czytelny sposób. Mam nadzieję że to pomoże.

To dość stary wątek, ale pomyślałem, że wrzucę moje dwa centy tak samo, ponieważ zaakceptowana odpowiedź nie zadziałała szczególnie dobrze. Wypróbowałem rozwiązanie gbn na dużym zbiorze danych i okazało się, że jest on bardzo powolny (> 45 sekund na 5 milionach rekordów w SQL Server 2012). Patrząc na plan wykonania jest oczywiste, że problem polega na tym, że wymaga operacji SORT, która znacznie spowalnia działanie.

Oto alternatywa, którą usunąłem ze struktury encji, która nie wymaga operacji SORT i wykonuje wyszukiwanie w indeksie nieklastrowanym. Skraca to czas wykonania do <2 sekund we wspomnianym zestawie rekordów.

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 całkowicie określone w pierwotnym pytaniu, ale jeśli projekt tabeli jest taki, że kolumna identyfikatora jest identyfikatorem automatycznego przyrostu, a funkcja DateCreated jest ustawiona na bieżącą datę dla każdej wstawki, to nawet bez uruchamiania powyższego zapytania można uzyskać znaczny wzrost wydajności rozwiązania gbn (około połowy czasu wykonania) po prostu z zamówienia na ID zamiast z DateCreated, ponieważ zapewni to identyczną kolejność sortowania i jest to szybsze sortowanie.

Mój kod do wyboru 1 z każdej grupy

wybierz a. * z #DocumentStatusLogs gdzie 
 utworzony w (wybierz 1 top utworzony z #DocumentStatusLogs b
gdzie 
a.documentid = b.documentid
zamów przez descreated danych
)

Weryfikacja niesamowitej i poprawnej odpowiedzi Clinta z góry:

Wydajność między dwoma poniższymi zapytaniami jest interesująca. 52% jest najlepszym. A 48% to drugi. 4% poprawa wydajności dzięki DISTINCT zamiast ORDER BY. Ale ORDER BY ma tę zaletę, że sortuje według wielu kolumn.

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

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

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

Management Studio firmy M $: Po podświetleniu i uruchomieniu pierwszego bloku zaznacz zarówno opcję 1, jak i opcję 2, kliknij prawym przyciskiem myszy -> [Wyświetl szacowany plan wykonania]. Następnie uruchom całą rzecz, aby zobaczyć wyniki.

Wyniki opcji 1:

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

Wyniki opcji 2:

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:

Zwykle używam APLIKUJ, gdy chcę, aby łączenie było 1 na 1 (1 z wielu).

Używam JOIN, jeśli chcę, aby łączenie było 1-do-wielu lub wiele-do-wielu.

Unikam CTE za pomocą ROW_NUMBER (), chyba że muszę zrobić coś zaawansowanego i nie mam nic przeciwko ograniczeniu wydajności okienkowania.

Unikam również podzapytań EXISTS / IN w klauzuli WHERE lub ON, ponieważ doświadczyłem, że powoduje to okropne plany wykonania. Ale przebieg jest różny. Przejrzyj plan wykonania i wydajność profilu tam, gdzie i kiedy jest to potrzebne!

Tego rozwiązania można użyć, aby uzyskać TOP N najnowszych wierszy dla każdej partycji (w przykładzie N wynosi 1 w instrukcji WHERE, a partycja to doc_id):

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

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 ostatnie zamówienie dokumentu według DateCreated, zwróci tylko 1 pierwszy dokument według DocumentID

CROSS APPLYbyła to metoda, którą zastosowałem dla mojego rozwiązania, ponieważ zadziałało dla mnie i dla potrzeb moich klientów. I z tego, co przeczytałem, powinien zapewnić najlepszą ogólną wydajność, jeśli ich baza danych znacznie się powiększy.

Oto 3 osobne podejścia do problemu wraz z najlepszym wyborem indeksowania dla każdego z tych zapytań (proszę wypróbować samodzielnie indeksy i zobaczyć logiczny odczyt, upływ czasu, plan wykonania. Podałem sugestie z mojego doświadczenia na temat takie zapytania bez wykonywania tego konkretnego problemu).

Podejście 1 : używając ROW_NUMBER (). Jeśli indeks magazynu wierszy nie jest w stanie zwiększyć wydajności, możesz wypróbować nieklastrowy / klastrowany indeks magazynu kolumn, jak w przypadku zapytań z agregacją i grupowaniem oraz dla tabel, które są uporządkowane według różnych kolumn przez cały czas, indeks magazynu kolumn jest zwykle najlepszym wyborem.

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

Podejście 2 : używając FIRST_VALUE. Jeśli indeks magazynu wierszy nie jest w stanie zwiększyć wydajności, możesz wypróbować nieklastrowy / klastrowany indeks magazynu kolumn, jak w przypadku zapytań z agregacją i grupowaniem oraz dla tabel, które są uporządkowane według różnych kolumn przez cały czas, indeks magazynu kolumn jest zwykle najlepszym wyborem.

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;

Podejście 3 : Używanie aplikacji CROSS. Utworzenie indeksu magazynu wierszy w tabeli DocumentStatusLogs obejmującego kolumny użyte w zapytaniu powinno wystarczyć do pokrycia zapytania bez potrzeby korzystania z indeksu magazynu kolumn.

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;

Wierzę, że można to zrobić w ten sposób. Może to wymagać drobnych poprawek, ale możesz po prostu wybrać maksimum z grupy.

Te odpowiedzi to przesada.

SELECT
  d.DocumentID,
  MAX(d.Status),
  MAX(d1.DateCreated)
FROM DocumentStatusLogs d, DocumentStatusLogs d1
USING(DocumentID)
GROUP BY d.DocumentID
ORDER BY DateCreated DESC

W scenariuszach, w których chcesz uniknąć używania row_count (), możesz również użyć lewego sprzężenia:

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 

W przykładowym schemacie można również użyć „nie w podzapytaniu”, które generalnie kompiluje się do tego samego wyniku, co lewe złą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 podzapytania nie działałby, gdyby tabela nie zawierała co najmniej jednego unikatowego klucza / ograniczenia / indeksu jednokolumnowego, w tym przypadku klucza podstawowego „Id”.

Oba te zapytania są zwykle „droższe” niż zapytanie row_count () (mierzone przez Query Analyzer). Można jednak spotkać się ze scenariuszami, w których wyniki zwracają się szybciej lub włączyć inne optymalizacje.

SELECT documentid, 
       status, 
       datecreated 
FROM   documentstatuslogs dlogs 
WHERE  status = (SELECT status 
                 FROM   documentstatuslogs 
                 WHERE  documentid = dlogs.documentid 
                 ORDER  BY datecreated DESC 
                 LIMIT  1) 

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]

To 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

W SQLite jest sprawdzone, że można użyć następującego prostego zapytania w GROUP BY

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

Tutaj MAX pomaga uzyskać maksymalną datę utworzenia z każdej grupy.

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