了解為什麼 rank() over 不適合不選擇重複行

我想了解為什麼我有不同的結果

我有一個名為 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         |

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;

引用自：https://dba.stackexchange.com/questions/165943