Ottieni la prima riga di ogni gruppo

Ho una tabella che voglio ottenere l’ultima voce per ogni gruppo. Ecco la tabella:

Tabella DocumentStatusLogs

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

La tabella verrà raggruppata per DateCreated DocumentID e ordinata per DateCreated in ordine decrescente. Per ogni DocumentID , voglio ottenere lo stato più recente.

La mia uscita preferita:

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

Si prega di consultare la tabella genitore per ulteriori informazioni:

Tabella dei Documents correnti

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

La tabella genitore dovrebbe essere così in modo da poter accedere facilmente al suo stato?

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

AGGIORNAMENTO Ho appena imparato come usare “apply” che rende più facile affrontare tali problemi.

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

Se si prevedono 2 voci al giorno, questo verrà scelto arbitrariamente. Per ottenere entrambe le voci per un giorno, utilizzare invece DENSE_RANK

Per quanto normalizzato o no, dipende se si desidera:

  • mantenere lo stato in 2 posizioni
  • preservare la cronologia dello stato

Così com’è, si conserva la cronologia dello stato. Se vuoi anche l’ultimo stato nella tabella genitore (che è denormalizzazione) avresti bisogno di un trigger per mantenere lo “stato” nel genitore. o rilasciare questa tabella della storia dello stato.

Ho appena imparato a usare cross apply . Ecco come usarlo in questo scenario:

  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 

Ho seguito alcuni tempi rispetto alle varie raccomandazioni qui, ei risultati dipendono davvero dalle dimensioni della tabella coinvolta, ma la soluzione più coerente è l’utilizzo dell’applicativo CROSS Questi test sono stati eseguiti su SQL Server 2008-R2, utilizzando una tabella con 6.500 record e un altro (schema identico) con 137 milioni di record. Le colonne interrogate fanno parte della chiave primaria sulla tabella e la larghezza della tabella è molto piccola (circa 30 byte). I tempi sono segnalati da SQL Server dal piano di esecuzione effettivo.

 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 

Penso che la cosa davvero sorprendente sia stata la coerenza del tempo per il CROSS APPLY indipendentemente dal numero di file coinvolte.

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

Quale server di database? Questo codice non funziona su tutti loro.

Per quanto riguarda la seconda metà della tua domanda, mi sembra ragionevole includere lo stato come colonna. È ansible lasciare DocumentStatusLogs come un registro, ma memorizzare ancora le ultime informazioni nella tabella principale.

BTW, se hai già la colonna DateCreated nella tabella Documenti puoi semplicemente unire DocumentStatusLogs usando quello (fintanto che DateCreated è univoco in DocumentStatusLogs ).

Modifica: MsSQL non supporta USING, quindi modificalo in:

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

Se sei preoccupato per le prestazioni, puoi farlo anche con MAX ():

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

ROW_NUMBER () richiede una sorta di tutte le righe nell’istruzione SELECT, mentre MAX no. Dovrebbe velocizzare drasticamente la tua richiesta.

Questo è un thread piuttosto vecchio, ma ho pensato di buttare i miei due centesimi nello stesso modo in cui la risposta accettata non ha funzionato particolarmente bene per me. Ho provato la soluzione di gbn su un grande set di dati e l’ho trovato terribilmente lento (> 45 secondi su 5 milioni di record in SQL Server 2012). Guardando al piano di esecuzione è ovvio che il problema è che richiede un’operazione SORT che rallenta notevolmente le cose.

Ecco un’alternativa che ho rimosso dal framework di quadro che non ha bisogno di operazioni SORT e fa una ricerca di indice NON-Cluster. Ciò riduce il tempo di esecuzione fino a <2 secondi sul set di record menzionato sopra.

 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] 

Ora sto assumendo qualcosa che non è interamente specificato nella domanda originale, ma se il design della tabella è tale che la colonna ID è un ID di incremento automatico e DateCreated è impostato sulla data corrente con ciascun inserto, quindi anche senza eseguire la mia query sopra, potresti effettivamente ottenere un notevole incremento delle prestazioni della soluzione di gbn (circa la metà del tempo di esecuzione) solo dall’ordinazione su ID anziché dall’ordinazione su DateCreated in quanto ciò fornirà un ordine di ordinamento identico ed è un ordinamento più rapido.

So che questo è un thread vecchio, ma le soluzioni TOP 1 WITH TIES sono piuttosto carine e potrebbero essere utili per leggere le soluzioni.

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

Maggiori informazioni sulla clausola TOP possono essere trovate qui .

Il mio codice per selezionare la prima parte di ogni gruppo

  seleziona a. * da #DocumentStatusLogs a where 
  DataCreated in (selezionare top 1 datacreativo da #DocumentStatusLogs b
 dove 
 a.documentid = b.documentid
 ordina per datecreated desc
 )

Questa è una delle domande più facilmente reperibili sull’argomento, quindi volevo dare una risposta moderna (sia per il mio riferimento che per aiutare gli altri). Usando over e first value puoi fare un lavoro breve della query sopra riportata:

 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 

Questo dovrebbe funzionare in SQL Server 2008 e versioni successive. Il primo valore può essere pensato come un modo per ottenere il primo top selezionato quando si usa una clausola over. Over consente di raggruppare l’elenco di selezione in modo che invece di scrivere sottoquery annidate (come fanno molte delle risposte esistenti), lo fa in un modo più leggibile. Spero che questo ti aiuti.

Verifica la risposta fantastica e corretta di Clint dall’alto:

La prestazione tra le due domande qui sotto è interessante. Il 52% è il migliore. E il 48% è il secondo. Un miglioramento del 4% delle prestazioni utilizzando DISTINCT anziché ORDER BY. Ma ORDER BY ha il vantaggio di ordinare per colonne multiple.

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

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

Opzione 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: dopo aver evidenziato ed eseguito il primo blocco, evidenzia sia l’Opzione 1 sia l’Opzione 2, tasto destro del mouse -> [Visualizza piano di esecuzione stimato]. Quindi eseguire l’intera cosa per vedere i risultati.

Opzione 1 Risultati:

 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 

Opzione 2 Risultati:

 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 

Nota:

Tendo ad usare APPLY quando voglio che un join sia 1-to- (1 di molti).

Io uso un JOIN se voglio che il join sia 1-a-molti o molti-a-molti.

Evito CTE con ROW_NUMBER () a meno che non sia necessario fare qualcosa di avanzato e sto bene con la penalizzazione delle prestazioni della finestra.

Evito anche le subquery EXISTS / IN nella clausola WHERE o ON, in quanto ho sperimentato ciò causando alcuni terribili piani di esecuzione. Ma il chilometraggio varia. Esamina il piano di esecuzione e le prestazioni del profilo dove e quando necessario!

Negli scenari in cui si desidera evitare l’uso di row_count (), è anche ansible utilizzare un join di sinistra:

 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 

Per lo schema di esempio, è ansible utilizzare anche "not in subquery", che generalmente viene compilato con lo stesso output del join sinistro:

 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) 

Nota, il pattern di subquery non funzionerebbe se la tabella non avesse almeno una chiave / vincolo / indice univoco a singola colonna, in questo caso la chiave primaria "Id".

Entrambe queste query tendono ad essere più "costose" della query row_count () (misurata da Query Analyzer). Tuttavia, potresti incontrare scenari in cui restituiscono risultati più velocemente o abilitano altre ottimizzazioni.

Prova questo:

  SELECT [DocumentID], [tmpRez].value('/x[2]','varchar(20)') as [Status], [tmpRez].value('/x[3]','datetime') as [DateCreated] FROM ( SELECT [DocumentID], cast(''+max(cast([ID] as varchar(10))+''+[Status]+'' +cast([DateCreated] as varchar(20)))+'' 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 ; 

Se si desidera restituire solo l'ordine di un documento recente da DateCreated, verrà restituito solo il primo documento di DocumentID

Questo è il TSQL più vaniglia che riesco a trovare

  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 

È stato verificato in SQLite che è ansible utilizzare la seguente query semplice con GROUP BY

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

Qui MAX aiuta a ottenere il massimo DateCreated DA ogni gruppo.

Ma sembra che MySQL non associ * -colonne con il valore di max DateCreated 🙁