主页 / dba / 问题 / 328023
Accepted
Joshua
Asked: 2023-06-08 07:53:00 +0800 CST

过滤后具有不同列的 DENSE_RANK

  • 772

我发现自己需要获取每个用户最近访问的 N 件事，并且我有一个所有访问的日志表。就像是

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

所以我写了

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

这是行不通的，因为如果同一用户重复访问同一事物，我们将获得相同的[ThingId]备份 N 次，而不是[ThingIds]每个用户 N 次不同的备份。

我对如何实际操作感到困惑。表太大了，如果我必须再次像 2000 一样编写 N 立方连接（一次连接表本身以排除重复副本，第二次生成计数），那么吸取整个表会更快在应用程序代码中处理它。

这几乎就像我想要的

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

但是这种性能也很糟糕，我最好再次将表格读入应用程序代码。

sql-server

1 个回答

  1. Best Answer

    @N = 3 你是正确的我想要每个用户 ID 的前 3 个 thingids。事实上 N 是常数；并且 select 子句使得它得到的是哪一行无关紧要。

    太棒了，所以这是游戏计划。AccessLog让我们按UserId和对表格进行分组ThingId，这样每个表格只有 1 个唯一行，我们将使用MAX()聚合函数获取AccessDate每个表格的最后一行，这样它们就可以在您的窗口函数中进行相对比较。

    这是一个查询：

    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

相关问题