Uzyskaj pierwszy rząd z każdej grupy
Mam tabelę, w której chcę uzyskać najnowszy wpis dla każdej grupy. Oto tabela:
DocumentStatusLogs
tabela
|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 według DocumentID
i posortowana według DateCreated
w porządku malejącym. Dla każdego DocumentID
chcę uzyskać najnowszy status.
Moje preferowane wyjście:
| DocumentID | Status | DateCreated |
| 1 | S1 | 8/02/2011 |
| 2 | S3 | 8/01/2011 |
| 3 | S1 | 8/02/2011 |
-
Czy istnieje jakaś funkcja agregująca, aby uzyskać tylko wierzchołek z każdej grupy? Zobacz pseudo-kod
GetOnlyTheTop
poniż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?
- czy może to być spowodowane przez nieznormalizowaną bazę danych? Zastanawiam się, ponieważ to, czego szukam, to tylko jeden wiersz, czy
status
również powinien znajdować się w tabeli nadrzędnej?
Zobacz tabelę nadrzędną, aby uzyskać więcej informacji:
Current Documents
Table
| DocumentID | Title | Content | DateCreated |
| 1 | TitleA | ... | ... |
| 2 | TitleB | ... | ... |
| 3 | TitleC | ... | ... |
Czy tabela nadrzędna powinna być taka, abym mógł łatwo uzyskać dostęp do jej statusu?
| DocumentID | Title | Content | DateCreated | CurrentStatus |
| 1 | TitleA | ... | ... | s1 |
| 2 | TitleB | ... | ... | s3 |
| 3 | TitleC | ... | ... | s1 |
Aktualizacja Ja tylko nauczył się używać "apply", co ułatwia rozwiązywanie takich problemów.
15 answers
;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 spodziewasz się 2 wpisów dziennie, to arbitralnie wybierze jeden. Aby uzyskać oba wpisy na jeden dzień, użyj DENSE_RANK zamiast
Co do znormalizowanych czy nie, to zależy czy chcesz:
- utrzymać status w 2 miejscach
- Zachowaj historię statusu
- ...
W obecnym stanie zachowujesz historię statusu. Jeśli chcesz mieć najnowszy status również w tabeli rodzica (co jest denormalizacją), potrzebujesz wyzwalacza, aby utrzymać "status" w rodzicu. or drop ta tabela historii statusu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-07-27 09:52:26
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-01 05:28:45
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-07 14:57:07
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.
Jeśli chodzi o drugą połowę twojego pytania, wydaje mi się rozsądne włączenie statusu jako kolumny. Możesz zostawićDocumentStatusLogs
jako dziennik, ale nadal przechowywać najnowsze informacje w głównej tabeli.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-07-27 08:49:46
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-01-15 20:57:21
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-03 08:34:13
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 .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-24 00:14:52
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 )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-23 11:22:46
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-18 00:55:16
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!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-27 21:38:31
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-04 20:47:23
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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-06 09:10:51
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-19 15:10:28
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-07-30 12:25:21
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 : (
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-22 18:35:57