Domanda Ottieni la prima riga di ogni gruppo


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

DocumentStatusLogs tavolo

|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 sarà raggruppata per DocumentID e ordinati per DateCreated in ordine decrescente Per ciascuno DocumentID, Voglio ottenere l'ultimo stato.

La mia uscita preferita:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |
  • Esiste una funzione aggregata per ottenere solo la parte superiore di ogni gruppo? Vedi lo pseudo-codice GetOnlyTheTop sotto:

    SELECT
      DocumentID,
      GetOnlyTheTop(Status),
      GetOnlyTheTop(DateCreated)
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ORDER BY DateCreated DESC
    
  • Se tale funzione non esiste, c'è un modo per raggiungere l'output che voglio?

  • O in primo luogo, ciò potrebbe essere causato da un database non normalizzato? Sto pensando, dato che quello che sto cercando è solo una riga, dovrebbe quello status si trova anche nella tabella padre?

Si prega di consultare la tabella genitore per ulteriori informazioni:

attuale Documents tavolo

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

AGGIORNARE Ho appena imparato come usare "apply" che rende più facile affrontare tali problemi.


377
2017-07-27 08:41


origine


risposte:


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


550
2017-07-27 08:44



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

116
2017-08-30 06:10



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.


39
2018-03-07 14:57



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. Puoi andare DocumentStatusLogs come un log, ma conserva ancora le ultime informazioni nella tabella principale.

A proposito, se hai già il DateCreated colonna nella tabella Documenti è sufficiente unirsi DocumentStatusLogs usando quello (finché DateCreated è unico in DocumentStatusLogs).

Modifica: MsSQL non supporta USING, quindi modificalo in:

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

24
2017-07-27 08:44



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.


19
2018-01-15 20:57



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 entità 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 aver eseguito la mia query sopra, si potrebbe effettivamente ottenere un notevole incremento delle prestazioni della soluzione di gbn (circa la metà dei tempi di esecuzione) ordinare su ID invece di ordinare su DateCreated in quanto ciò fornirà un ordinamento identico ed è un tipo più veloce.


9
2018-06-03 08:34



So che questo è un vecchio thread ma il TOP 1 WITH TIES le soluzioni sono abbastanza 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)

Ulteriori informazioni sulla clausola TOP possono essere trovate Qui.


6
2018-01-24 00:14



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
)

5
2017-09-23 11:22



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!


2
2017-10-28 22:10