value - sql server top n per group




Receba o primeiro resultado de cada grupo (11)

Eu tenho uma tabela que eu quero obter a última entrada 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 função 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 função 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 banco de dados 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 atual

| 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 desses problemas.


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

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

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

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


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

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

Este é um tópico bastante antigo, mas eu pensei em lançar 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 conjunto de dados e achei que ela estava 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 estiver definido para a data atual com cada inserção, então, mesmo sem correr com a minha consulta acima, você poderia realmente obter um aumento de desempenho considerável para a solução do gbn (cerca de metade do tempo de execução) apenas a partir do ID em vez de encomendar no DateCreated, pois isso fornecerá uma ordem de classificação idêntica e uma classificação mais rápida.


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


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

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

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.


Tente isto:

        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]

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

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

Em relação à segunda metade da sua pergunta, parece-me razoável incluir 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

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;

;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 entradas por dia, então isso arbitrariamente escolherá uma. Para obter as duas entradas 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 é a desnormalização), você precisaria de um gatilho para manter o "status" no pai. ou solte essa tabela de histórico de status.





greatest-n-per-group