用 row_number() 和 dense_rank() 解決“差距和孤島”？
如何用和解決間隙和島嶼的島嶼部分。我現在已經看過幾次了，我想知道是否有人可以解釋一下，
dense_rank()``row_number()讓我們使用類似這樣的數據作為範例數據（範例使用 PostgreSQL），
CREATE TABLE foo AS SELECT x AS id, trunc(random()*3+1) AS x FROM generate_series(1,50) AS t(x);應該產生這樣的東西。
id | x ----+--- 1 | 3 2 | 1 3 | 3 4 | 3 5 | 3 6 | 2 7 | 3 8 | 2 9 | 1 10 | 3 ...我們想要的是這樣的…… （
z我們可以使用的一些價值在哪裡GROUP BY）id | x | grp ----+------ 1 | 3 | z 2 | 1 | z 3 | 3 | z 4 | 3 | z 5 | 3 | z 6 | 2 | z 7 | 3 | z 8 | 2 | z 9 | 1 | z 10 | 3 | z ...這樣我們就可以做到
GROUP BY x,grp``(x,grp)獨特的島嶼在哪裡GROUP BY grp哪裡grp是一個獨特的島嶼。在上面的例子中，(x,grp) 看起來像這樣。 （我們可以使用的一些值**在哪裡）
z``GROUP BYid | x | grp ----+------ 1 | 3 | 1 2 | 1 | 1 3 | 3 | 2 4 | 3 | 2 5 | 3 | 2 6 | 2 | z -- Because we're grouping by an (x,grp) 7 | 3 | z -- Does not have to be `3` (or something unique) 8 | 2 | z -- z=1, is fine to use again if we 9 | 1 | z -- GROUP BY (x,grp) 10 | 3 | z背景
這在我的研究中只出現過幾次。
- 使用 Row_Number 查找@Martin Smith 的連續行數
- 通過@Martin Smith獲取表格中的每個狀態更改
- @ErikE分組或視窗
儘管從未在此網路上解釋過，但它似乎也明確地混淆了其他人，
所以這裡的訣竅是兩個相等遞增的系列的屬性，它們產生可用於辨識島嶼的差異
{11,12,13} - {1,2,3} = {10,10,10}。此屬性不足以辨識島嶼本身，但它是我們可以利用的關鍵步驟。背景
拋開問題。讓我們看看這個..我們在這裡
- 將 1組定義為 3 行。
- 為 xoffsets 中的每一對/行偏移創建一個組。
這是一些程式碼。
SELECT x1, x2, x1-x2 AS difference FROM (VALUES (2,42), (13,7), (42,2) ) AS xoffsets(o1,o2) CROSS JOIN LATERAL ( SELECT x+o1,x+o2 -- here we add the offsets above to x FROM generate_series(1,3) AS t(x) ) AS t(x1, x2) ORDER BY x1, x2; x1 | x2 | difference ----+----+------------ 3 | 43 | -40 4 | 44 | -40 5 | 45 | -40 14 | 8 | 6 15 | 9 | 6 16 | 10 | 6 43 | 3 | 40 44 | 4 | 40 45 | 5 | 40 (9 rows)這看起來很不錯，
difference在這個例子中按 分組就足夠了。您可以看到我們有三個組，從(2,42)、(13,7)和開始(42,2)，對應於 中的組xoffsets。這本質上是相反的問題。但是我們有一個主要問題，因為我們正在用靜態偏移量來證明這一點。如果任何兩個偏移量之間的差異o1-o2相同，我們就會遇到問題。像這樣，SELECT x1, x2, x1-x2 AS difference FROM (VALUES (100,90), (90,80) ) AS xoffsets(o1,o2) CROSS JOIN LATERAL ( SELECT x+o1,x+o2 -- here we add the offsets above to x FROM generate_series(1,3) AS t(x) ) AS t(x1, x2) ORDER BY x1, x2; x1 | x2 | difference -----+----+------------ 91 | 81 | 10 92 | 82 | 10 93 | 83 | 10 101 | 91 | 10 102 | 92 | 10 103 | 93 | 10我們必須找到一種靜態定義第二個偏移量的方法。
SELECT x1, x2, x1-x2 AS difference FROM (VALUES (100,0), (90,0) ) AS xoffsets(o1,o2) CROSS JOIN LATERAL ( SELECT x+o1,x+o2 -- here we add the offsets above to x FROM generate_series(1,3) AS t(x) ) AS t(x1, x2) ORDER BY x1, x2; x1 | x2 | difference -----+----+------------ 91 | 1 | 90 92 | 2 | 90 93 | 3 | 90 101 | 1 | 100 102 | 2 | 100 103 | 3 | 100 (6 rows)而且，我們又回到了為每對偏移量分組的軌道上。這不是我們正在做的，但它非常接近，希望它有助於說明如何減去這兩個集合來創建島嶼。
應用
現在讓我們用 table 重新審視上面的問題
foo。我們將變數夾在兩個副本之間，x僅用於顯示目的。SELECT id, x, dense_rank() OVER (w1) AS lhs, dense_rank() OVER (w2) AS rhs, dense_rank() OVER (w1) - dense_rank() OVER (w2) AS diff, ( x, dense_rank() OVER (w1) - dense_rank() OVER (w2) ) AS grp, x FROM foo WINDOW w1 AS (ORDER BY id), w2 AS (PARTITION BY x ORDER BY id) ORDER BY id LIMIT 10; id | x | lhs | rhs | diff | grp | x ----+---+-----+-----+------+-------+--- 1 | 2 | 1 | 1 | 0 | (2,0) | 2 2 | 1 | 2 | 1 | 1 | (1,1) | 1 3 | 2 | 3 | 2 | 1 | (2,1) | 2 4 | 3 | 4 | 1 | 3 | (3,3) | 3 5 | 3 | 5 | 2 | 3 | (3,3) | 3 6 | 2 | 6 | 3 | 3 | (2,3) | 2 7 | 3 | 7 | 3 | 4 | (3,4) | 3 8 | 1 | 8 | 2 | 6 | (1,6) | 1 9 | 3 | 9 | 4 | 5 | (3,5) | 3 10 | 1 | 10 | 3 | 7 | (1,7) | 1 (10 rows)你可以在這裡看到所有的變數，除了
x和id
lhs很簡單。我們只是用它生成一個唯一的順序標識符（因為我們給它提供了一個唯一的順序標識符：id——儘管永遠不要忘記ids 很少是無縫的）
rhs稍微複雜一些。我們劃分x並生成一個順序標識符，其中包含不同的x值。觀察rhs每次它看到具有它已經看到的值的行時如何在集合上增加。的屬性rhs是該值被看到的次數。這
diff是減法的簡單結果，但這樣想它並沒有太大用處。更像是減去一個序列而不是數字（儘管它們是任何單行的數字）。我們有一個序列增加一個值的次數。而且，對於每個不同的 id（每次），我們都有一個序列增加一。減去這兩個序列將返回相同的重複值數字，就像我們上面的範例一樣。(11,12,13) - (1,2,3) = (10,10,10). 這與此答案第一部分中的原理相同
diff不獨立標記組本身diff將所有相同的島嶼強制歸x為一組（可能有誤報，例如diff=3上面的三種情況及其相應的x值）群
grp是 的函式(x, diff)。它用作分組 ID，儘管格式有點奇怪。如果我們僅按差異分組，這有助於減少可能發生的誤報。簡單的未優化查詢
所以現在我們有了簡單的未優化查詢
SELECT x, diff, count(*) FROM ( SELECT id, x, dense_rank() OVER (ORDER BY id) - dense_rank() OVER (PARTITION BY x ORDER BY id) AS diff FROM foo ) AS t GROUP BY x, diff;優化
關於
dense_rank()用其他東西替換的問題，比如row_number(). @ypercube 評論它也適用於 ROW_NUMBER() 但我認為它可能會用不同的數據給出不同的結果。取決於表中是否有重複數據。
所以讓我們回顧一下，這裡有一個查詢顯示
row_number()和dense_rank()- 計算出各自的差異。
- 在包含 ids和vals
1,2,3,4,5,6,7,8,6,7,8的兩個不同“島”的結果集上。xSQL查詢，
SELECT id, x, dense_rank() OVER (w1) AS dr_w1, dense_rank() OVER (w2) AS dr_w2, ( x, dense_rank() OVER (w1) - dense_rank() OVER (w2) ) AS dense_diffgrp, row_number() OVER (w1) AS rn_w1, row_number() OVER (w2) AS rn_w2, ( x, row_number() OVER (w1) - row_number() OVER (w2) ) AS rn_diffgrp, x FROM ( SELECT id, CASE WHEN id<4 THEN 1 ELSE 0 END FROM ( SELECT * FROM generate_series(1,8) UNION ALL SELECT * FROM generate_series(6,8) ) AS t(id) ) AS t(id,x) WINDOW w1 AS (ORDER BY id), w2 AS (PARTITION BY x ORDER BY id) ORDER BY id;結果集，
drdense_rank，rnrow_numberid | x | dr_w1 | dr_w2 | dense_diffgrp | rn_w1 | rn_w2 | rn_diffgrp | x ----+---+-------+-------+---------------+-------+-------+------------+--- 1 | 1 | 1 | 1 | (1,0) | 1 | 1 | (1,0) | 1 2 | 1 | 2 | 2 | (1,0) | 2 | 2 | (1,0) | 1 3 | 1 | 3 | 3 | (1,0) | 3 | 3 | (1,0) | 1 4 | 0 | 4 | 1 | (0,3) | 4 | 1 | (0,3) | 0 5 | 0 | 5 | 2 | (0,3) | 5 | 2 | (0,3) | 0 6 | 0 | 6 | 3 | (0,3) | 7 | 3 | (0,4) | 0 6 | 0 | 6 | 3 | (0,3) | 6 | 4 | (0,2) | 0 7 | 0 | 7 | 4 | (0,3) | 8 | 6 | (0,2) | 0 7 | 0 | 7 | 4 | (0,3) | 9 | 5 | (0,4) | 0 8 | 0 | 8 | 5 | (0,3) | 10 | 8 | (0,2) | 0 8 | 0 | 8 | 5 | (0,3) | 11 | 7 | (0,4) | 0您將在此處看到，當您排序的列具有重複項時，您不能使用此方法，因為您無法確保
ORDER BY子句中具有重複項的查詢的排序。只是重複排序的影響消除了差異：全域增量值與分區上的增量值的關係。但是，當您有一個唯一的 id 列或一系列定義唯一性的列時，請務必使用row_number()而不是dense_rank().