Início / dba / Perguntas / 328023
Accepted
Joshua
Asked: 2023-06-08 07:53:00 +0800 CST

DENSE_RANK com colunas distintas após o filtro

  • 772

Acho que preciso obter as N coisas mais recentes acessadas por cada usuário e tenho uma tabela de log de todos os acessos. Algo como

CREATE TABLE [AccessLog] ([UserId] UNIQUEIDENTIFIER, [ThingId] UNIQUEIDENTIFIER, [AccessDate] DATETIME)

Então eu escrevi

SELECT [UserId], [ThingId], [SequenceNumber]
  FROM (SELECT [UserId], [ThingId], DENSE_RANK()
          OVER (PARTITION BY [UserId] ORDER BY [AccessDate] DESC) SequenceNumber
          FROM [AccessLog]
     ) [AccessLog]
 WHERE [SequenceNumber] < @N

Isso não funciona porque, se o mesmo usuário acessou a mesma coisa repetidamente, obtemos o mesmo [ThingId]de volta até N vezes, em vez de N [ThingIds]de volta distintas para cada usuário.

Estou perplexo como realmente fazê-lo. A tabela é tão grande que, se eu tiver que escrever a junção N ao cubo como se fosse 2.000 novamente (juntar a tabela novamente uma vez para excluir cópias duplicadas e uma segunda vez para gerar a contagem), será mais rápido sugar toda a tabela e processá-lo no código do aplicativo.

É quase como eu quero

FOR EACH [UserId] IN (SELECT [UserId] IN [User])
    SELECT TOP (@N) [UserId], [ThingId]
      FROM [AccessLog]
     ORDER BY [AccessDate] DESC

Mas esse desempenho também é tão ruim que eu gostaria de ler melhor a tabela no código do aplicativo novamente.

sql-server

1 respostas

  1. Best Answer

    Você está correto para @N = 3. Quero os 3 principais IDs de usuário por ID de usuário. Na verdade, N é constante; e a cláusula select faz com que não importe qual das linhas obteve.

    Legal, então aqui está o plano de jogo. Vamos agrupar a AccessLogtabela por UserIde ThingIdpara que haja apenas 1 linha única por, e usaremos a MAX()função agregada para obter a última AccessDatede cada uma, para que todas possam ser comparadas relativamente dentro de sua função de janela.

    Aqui está uma consulta para isso:

    DECLARE @N INT = 3;

SELECT
    UserId,
    ThingId,
    SequenceRankId
FROM
(
    SELECT
        UserId,
        ThingId,
        DENSE_RANK() OVER
        (
            PARTITION BY UserId
            ORDER BY MAX(AccessDate) DESC -- Ordering by the last access of each Thing per User (MAX() is the key here)
        ) AS SequenceRankId
    FROM AccessLog
    GROUP BY UserId, ThingId -- Ensures only 1 row per ThingId per UserId
) AS AccessLogsRanked
WHERE SequenceRankId <= @N
ORDER BY UserId, SequenceRankId;
    • 4

relate perguntas