找出每個具有不同時間戳的唯一記錄之間的最大差異

我有一個具有以下架構和數據的 PostgreSQL 表：

CREATE TABLE IF NOT EXISTS T(
    id uuid PRIMARY KEY,
    username varchar(15),
    person varchar(10),
    tweets int,
    followers int,
    following int,
    likes int,
    created_at date) 
;

id                                   | username     | person     | tweets | followers | following | likes | created_at
:----------------------------------- | :----------- | :--------- | -----: | --------: | --------: | ----: | :---------
3fa34100-d688-4051-a687-ec49d05e7212 | renok        | null       |    110 |         6 |         0 |     0 | 2020-10-10
bab9ceb9-2770-49ea-8489-77e5d763a223 | Lydia_C      | test user2 |    515 |      1301 |      1852 |  1677 | 2020-10-10
4649077a-9188-4821-a1ec-3b38608ea44a | Kingston_Sav | null       |   2730 |      1087 |      1082 |  1339 | 2020-10-10
eef80836-e140-4adc-9598-8b612ab1825b | TP_s         | null       |   1835 |       998 |       956 |  1832 | 2020-10-10
fd3ff8c7-0994-40b6-abe0-915368ab9ae5 | DKSnr4       | null       |    580 |       268 |       705 |   703 | 2020-10-10
3fa34100-d688-4051-a687-ec49d05e7312 | renok        | null       |    119 |         6 |         0 |     0 | 2020-10-12
bab9ceb9-2770-49ea-8489-77e5d763a224 | Lydia_C      | test user2 |    516 |      1301 |      1852 |  1687 | 2020-10-12
4649077a-9188-4821-a1ec-3b38608ea44B | Kingston_Sav | null       |   2737 |      1090 |      1084 |  1342 | 2020-10-12
eef80836-e140-4adc-9598-8b612ae1835c | TP_s         | null       |   1833 |       998 |       957 |  1837 | 2020-10-12
fd3ff8c7-0994-40b6-abe0-915368ab7ab5 | DKSnr4       | null       |    570 |       268 |       700 |   703 | 2020-10-12

我打算獲得每個唯一使用者名的最近日期和下一個最近日期之間的最大差異，並找到具有最大邊距（差異）的使用者名，例如..在上表中，最近日期是2020-10-12下一個最近的日期是2020-10-10。

所以我想得到這樣的東西

id                                   | username     | person | tweets | followers | following | likes | created_at | prev_followers | gain
:----------------------------------- | :----------- | :----- | -----: | --------: | --------: | ----: | :--------- | -------------: | ---:
4649077a-9188-4821-a1ec-3b38608ea44a | Kingston_Sav | null   |   2737 |      1090 |      1084 |  1342 | 2020-10-12 |           1087 |    3

許多方式導致羅馬。下面應該是一個很好的（快速和靈活的）“找到最大邊距的使用者名”

假設所有涉及的列都已定義NOT NULL。每個使用者名每天只能有*一個條目。*否則你必須做更多。

WITH cte AS (
  SELECT *, dense_rank() OVER (ORDER BY created_at DESC) AS rnk
  FROM   tbl
  )
SELECT d1.*
    , d2.followers AS prev_followers
    , d1.followers - d2.followers AS gain
FROM  (SELECT * FROM cte WHERE rnk = 1) d1
JOIN  (SELECT * FROM cte WHERE rnk = 2) d2 USING (username)
ORDER  BY gain DESC
       , d1.followers, username  -- added tiebreaker
LIMIT  1;

命名的 CTE用(not , not )cte附加排名數字。然後將最近一天 ( ) 與前一天 ( ) 連接起來，計算收益。顯然，使用者必須在這兩天都有條目才有資格。最後按增益排序並佔據第一行。dense_rank()rank()``row_number()``rnk = 1``rnk = 2

請注意添加的ORDER BY表達式以嘗試打破可能的聯繫：可能有多個使用者獲得相同的收益，因此您必須定義如何處理它。一種方法是添加平局。在我的範例中，首選絕對數量較少的使用者（相對增益較高），如果仍然不明確，按字母順序排列的使用者將獲勝。

或者您返回所有“贏家”：

同樣，有很多方法…… Postgres 13WITH TIES正是為此目的添加了標準 SQL 子句：

WITH cte AS (
  SELECT *, dense_rank() OVER (ORDER BY created_at DESC) AS rnk
  FROM   tbl
  )
SELECT d1.*
    , d2.followers AS prev_followers
    , d1.followers - d2.followers AS gain
FROM  (SELECT * FROM cte WHERE rnk = 1) d1
JOIN  (SELECT * FROM cte WHERE rnk = 2) d2 USING (username)
ORDER  BY gain DESC
FETCH  FIRST 1 ROWS WITH TIES;

db<>在這裡擺弄

詳細解釋WITH TIES：

• 大於或等於 ALL() 且等於 MAX() 速度

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