主页 / dba / 问题 / 165943
Accepted
Mio
Asked: 2017-03-03 02:00:27 +0800 CST

了解为什么 rank() over 不适合不选择重复行

  • 772

我想了解为什么我有不同的结果

我有一个名为 active_transfert 的表，我在其中记录图像 transfert

user_id | image_id | created_at
--------|----------|-----------
1       |1         |2014-07-10
1       |2         |2015-01-21
2       |1         |2015-05-23
3       |1         |2016-07-22
4       |6         |2017-06-01
4       |6         |2014-08-22

我想为每个 image_id 返回唯一的 user_id。

SELECT user_id,
       image_id
FROM active_transfert
GROUP BY user_id,
         image_id; --50


SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          rank() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_transfert) AS i
WHERE i.i_ranked = 1; -- 53

我对 Redshift 运行这些查询。为什么我的第二个查询不能防止重复记录（相同的 user_id 和 image_id）？

预期结果 ：

user_id | image_id |
--------|----------|
1       |1         |
1       |2         |
2       |1         |
3       |1         |
4       |6         |
redshift window-functions

1 个回答

  1. Best Answer

    RANK()是一个确定性函数，这意味着重复项将被标记为相同的等级值。您的查询输出向我表明，有多个记录具有相同的值user_id并且image_id也具有相同的created_at值。这些记录都将返回相同的RANK()值。

    如果您运行内部查询，您将看到所有三个属性都相同的这些重复项。如果created_date也是该组合的最大值，则user_id它们image_id的值都将为RANK()1。

    要获得所需的输出，您应该改用ROW_NUMBER(). OVER当子句中的参数不能唯一确定行时，这是一个非确定性函数，在这种情况下是正确的。这将为每一行分配一个唯一的结果，但是当您的RANK()查询提供重复时，它将随机为这些行中的每一行分配一个唯一的值。

    您的第二个查询使用ROW_NUMBER()：

    SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          ROW_NUMBER() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_downloads) AS i
WHERE i.i_ranked = 1;
    • 9

相关问题