Receba o primeiro resultado de cada grupo

Eu tenho uma tabela que eu quero obter a última input para cada grupo. Aqui está a mesa:

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

A tabela será agrupada por DocumentID e classificada por DateCreated em ordem decrescente. Para cada DocumentID , quero obter o status mais recente.

Minha saída preferida:

 | DocumentID | Status | DateCreated | | 1 | S1 | 8/02/2011 | | 2 | S3 | 8/01/2011 | | 3 | S1 | 8/02/2011 | 
  • Existe alguma function agregada para obter apenas o topo de cada grupo? Veja o pseudo-código GetOnlyTheTop abaixo:

     SELECT DocumentID, GetOnlyTheTop(Status), GetOnlyTheTop(DateCreated) FROM DocumentStatusLogs GROUP BY DocumentID ORDER BY DateCreated DESC 
  • Se tal function não existe, existe alguma maneira que eu possa alcançar a saída que eu quero?

  • Ou, em primeiro lugar, isso poderia ser causado por um database não normalizado? Eu estou pensando, desde que o que eu estou procurando é apenas uma linha, esse status também deve estar localizado na tabela pai?

Por favor, veja a tabela pai para mais informações:

Tabela de Documents atuais

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

A tabela pai deve ser assim para que eu possa acessar facilmente seu status?

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

ATUALIZAÇÃO Acabei de aprender como usar o “apply”, o que facilita o tratamento de tais problemas.

 ;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 você espera 2 inputs por dia, então isso arbitrariamente escolherá uma. Para obter as duas inputs por dia, use DENSE_RANK

Quanto a normalizado ou não, depende se você quiser:

  • manter status em 2 lugares
  • preservar histórico de status

Como está, você preserva o histórico de status. Se você quiser o status mais recente na tabela pai também (que é desnormalização), você precisaria de um gatilho para manter o “status” no pai. ou solte essa tabela de histórico de status.

Acabei de aprender como usar cross apply . Veja como usá-lo neste cenário:

  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 

Eu fiz alguns intervalos de tempo sobre as várias recomendações aqui, e os resultados realmente dependem do tamanho da tabela envolvida, mas a solução mais consistente é usar o CROSS APPLY Esses testes foram executados no SQL Server 2008-R2, usando uma tabela com 6.500 registros e outro (esquema idêntico) com 137 milhões de registros. As colunas que estão sendo consultadas são parte da chave primária na tabela e a largura da tabela é muito pequena (cerca de 30 bytes). Os horários são relatados pelo SQL Server a partir do plano de execução real.

 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 

Eu acho que a coisa realmente incrível foi quão consistente o tempo foi para o CROSS APPLY, independentemente do número de linhas envolvidas.

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

Qual servidor de database? Este código não funciona em todos eles.

Quanto à segunda metade da sua pergunta, parece-me razoável include o status como coluna. Você pode deixar DocumentStatusLogs como um log, mas ainda armazenar as últimas informações na tabela principal.

BTW, se você já tem a coluna DateCreated na tabela Documentos, você pode apenas juntar DocumentStatusLogs usando isso (contanto que DateCreated seja único em DocumentStatusLogs ).

Edit: MsSQL não suporta USING, então altere para:

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

Se você está preocupado com o desempenho, você também pode fazer isso com o MAX ():

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

ROW_NUMBER () requer um tipo de todas as linhas na sua instrução SELECT, enquanto o MAX não. Deve acelerar drasticamente a sua consulta.

Este é um tópico bastante antigo, mas eu pensei em jogar meus dois centavos da mesma forma que a resposta aceita não funcionou muito bem para mim. Eu tentei a solução da gbn em um grande dataset e descobri que ela é terrivelmente lenta (> 45 segundos em mais de 5 milhões de registros no SQL Server 2012). Olhando para o plano de execução, é óbvio que o problema é que requer uma operação SORT, que diminui significativamente as coisas.

Aqui está uma alternativa que tirei da estrutura da entidade que não precisa de uma operação SORT e faz uma pesquisa de índice não-clusterizado. Isso reduz o tempo de execução para <2 segundos no conjunto de registros acima mencionado.

 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] 

Agora, estou assumindo algo que não está totalmente especificado na pergunta original, mas se o design da tabela for tal que sua coluna de ID é um ID de incremento automático, e o DateCreated é definido como a data atual com cada inserção, então até sem correr com a minha consulta acima, você poderia realmente obter um aumento significativo de desempenho para a solução do gbn (cerca de metade do tempo de execução) apenas ao fazer o pedido no ID em vez de fazer o pedido no DateCreated, pois isso forneceria uma ordem de sorting idêntica.

Eu sei que este é um thread antigo, mas as soluções TOP 1 WITH TIES são bastante agradáveis ​​e podem ser úteis para algumas leituras através das soluções.

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

Mais sobre a cláusula TOP pode ser encontrada aqui .

Meu código para selecionar o top 1 de cada grupo

  selecione um. * de #DocumentStatusLogs a where 
  datacriado em (selecione o primeiro datagrama criado de #DocumentStatusLogs b
 Onde 
 a.documentid = b.documentid
 ordenar por datecreated desc
 )

Essa é uma das questões mais facilmente encontradas sobre o assunto, então eu queria dar uma resposta moderna para ela (tanto para minha referência quanto para ajudar os outros a sair). Ao usar o valor over e first, você pode fazer um breve trabalho com a consulta acima:

 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 

Isso deve funcionar no SQL Server 2008 e acima. O primeiro valor pode ser pensado como uma forma de realizar o top 1 selecionado ao usar uma cláusula over. Over permite agrupar na lista de seleção, assim, em vez de escrever subconsultas aninhadas (como muitas das respostas existentes), isso é feito de uma forma mais legível. Espero que isto ajude.

Verificando a resposta incrível e correta do Clint acima:

O desempenho entre as duas perguntas abaixo é interessante. 52% sendo o top. E 48% é o segundo. Melhoria de 4% no desempenho usando DISTINCT em vez de ORDER BY. Mas o ORDER BY tem a vantagem de classificar por várias colunas.

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

Opção 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]) 

Opção 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] 

Gerenciamento do M $: Depois de destacar e executar o primeiro bloco, destaque as Opções 1 e 2, clique com o botão direito -> [Exibir plano de execução estimada]. Em seguida, execute a coisa toda para ver os resultados.

Resultados da Opção 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 

Opção 2 Resultados:

 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:

Eu costumo usar APPLY quando quero que uma junit seja 1 para 1 (muitos).

Eu uso um JOIN se eu quiser que a junit seja de 1 para muitos ou de muitos para muitos.

Eu evito CTE com ROW_NUMBER () a menos que eu precise fazer algo avançado e estou bem com a penalidade de desempenho de janelas.

Também evito subconsultas EXISTS / IN na cláusula WHERE ou ON, pois experimentei isso causando alguns planos de execução terríveis. Mas a quilometragem varia. Revise o plano de execução e o desempenho do perfil onde e quando necessário!

Nos cenários em que você deseja evitar o uso de row_count (), você também pode usar uma junit à esquerda:

 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 

Para o esquema de exemplo, você também pode usar um "não na subconsulta", que geralmente é compilado para a mesma saída que a junit esquerda:

 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) 

Observe que o padrão de subconsulta não funcionaria se a tabela não tivesse pelo menos uma chave / restrição / índice única de coluna única, neste caso, a chave primária "Id".

Ambas as consultas tendem a ser mais "caras" do que a consulta row_count () medida pelo Query Analyzer. No entanto, você pode encontrar cenários em que eles retornam resultados mais rapidamente ou ativam outras otimizações.

Tente isto:

  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 você deseja retornar somente a ordem recente do documento por DateCreated, ela retornará apenas o documento do primeiro nível por DocumentID

Este é o TSQL mais baunilha que eu posso criar

  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 

É verificado no SQLite que você pode usar a seguinte consulta simples com GROUP BY

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

Aqui o MAX ajuda a obter o máximo de DataCriadas DE cada grupo.

Mas parece que o MYSQL não associa * colunas com o valor de max DateCreated 🙁