Postgres SQL - 比較行數，返回 a < b 或 b 不存在的地方

我正在嘗試從以下任一條件為真的標籤表中選擇“雜湊”值：

Count of the 'thumb\_%' rows is Greater than Count of 'dht\_%' rows
Count of the 'thumb\_%' is Greater than 0 but no 'dht\_%' rows exist

我正在使用 Postgres 版本 PostgreSQL 9.6.6

我有一個像這樣的“標籤”表：

CREATE TABLE tags (
   tag_id SERIAL PRIMARY KEY,
   hash VARCHAR(16),
   val VARCHAR(128),
   UNIQUE (hash, val)
);

INSERT INTO tags VALUES
(243882, 'b119ceb321058d7c', 'thumb_0_1.00'),
(243883, 'b119ceb321058d7c', 'thumb_1_2.79'),
(243884, 'b119ceb321058d7c', 'thumb_2_5.59'),
(243885, 'b119ceb321058d7c', 'thumb_3_8.38'),
(243886, 'b119ceb321058d7c', 'thumb_4_11.18'),
(243887, 'b119ceb321058d7c', 'thumb_5_13.97'),
(244237, '8dbb1085750696c5', 'dht_0_c0b19393ad2c3c84'),
(244238, '8dbb1085750696c5', 'dht_1_c0b193b3ad2d3884'),
(244239, '8dbb1085750696c5', 'dht_2_c0b193b3ad2c3c84'),
(244240, '8dbb1085750696c5', 'thumb_0_207.37'),
(244241, '8dbb1085750696c5', 'thumb_1_276.50'),
(244242, '8dbb1085750696c5', 'thumb_2_345.62'),
(244286, '8dbb1085750696c5', 'thumb_3_1.00');

除了這些之外，標籤表還有與散列相關的其他值，但是這個查詢不關心額外的值。

這是我到目前為止一直在測試的查詢：

SELECT * FROM
(
   SELECT tags.hash, COUNT(*) as dh FROM tags WHERE val ILIKE 'dht\_%' GROUP BY tags.hash
) AS b LEFT OUTER JOIN
(
   SELECT tags.hash, COUNT(*) as thm FROM tags WHERE val ILIKE 'thumb_%' GROUP BY tags.hash
) AS a
ON (a.hash = b.hash AND a.thm > b.dh AND a.thm > 0) OR (a.thm > 0 and b.hash IS NULL);

期望的結果將類似於以下內容：

      hash       | dh | thm 
------------------+----+------
b119ceb321058d7c |    | 6     <- no dht_ exist for this result
8dbb1085750696c5 |  3 | 4     <- dht_ is less than thumb_ results

---

如果您想閱讀，請提供一些背景知識。

這是我為我們為某些業務拍攝的一些攝影而編寫的媒體標記和管理系統的一部分。我們從影片中提取縮略圖並“標記”每個縮略圖的時間戳。我還有一些額外的自動標記功能，它們試圖收集特定的元數據並插入到這個標籤表中。我們有大約 19k 的唯一文件和大約 330k 的標籤。散列是通過 XXhash 完成的，幫助我們找到重複的文件。dht_ 標籤是給定縮略圖的 dhashes，讓我們能夠找到非常相似的影片。這一切都包含在一個漂亮的小節點快遞網站中並且執行良好，直到我嘗試實現這些縮略圖圖像的散列。我的查詢比上面的例子復雜得多，但這就是我正在努力解決的部分。可悲的是，近年來我對 sql 的了解有所下滑，因為這是一個副項目，我一直在努力讓它發揮作用。然而，我對其餘的設置非常滿意，對於媒體跟踪等，它已經做得很好了，它為我們節省了大量時間來尋找特定的媒體文件。

您可以使用下一個查詢獲取每個標籤的 COUNT；

SELECT hash,
       SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
       SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
FROM   tags
GROUP BY hash;

雜湊 | dht | 拇指
:--------------- | --: | ----:
8dbb1085750696c5 | 3 | 4
b119ceb321058d7c | 0 | 6

然後應用過濾條件：

WITH CTG AS
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
SELECT hash, dht, thumb
FROM   CTG
WHERE  (thumb > dht)
OR     (thumb >= 1 AND dht = 0);

雜湊 | dht | 拇指
:--------------- | --: | ----:
8dbb1085750696c5 | 3 | 4
b119ceb321058d7c | 0 | 6

條件可以進一步簡化為：

WHERE  (thumb > dht) ;

如果行（由 CTE/派生表生成）非常多，它作為子查詢（派生表）可能比 CTE 更有效：

SELECT hash, dht, thumb
FROM
(
   SELECT hash,
          SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
          SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
   FROM   tags
   GROUP BY hash
)
   AS CTG
WHERE  (thumb > dht) ;

dbfiddle在這裡

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