1

I'm trying to select the 'hash' value from the tags table where either of the following conditions are true:

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

I'm using Postgres version PostgreSQL 9.6.6

I have a 'tags' table like so:

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');

The tags table has other values associated with a hash besides these, however this query isn't concerned with the extra values.

This is the query I've been testing so far:

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);

The desired results would be similar to the following:

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

A little background if you want to read.

This is part of a media tagging and management system I've written for some photography we take for some of our businesses. We extract thumbnails from videos and 'tag' the timestamp of each thumbnail. I also have some additional auto-tagging features which attempts to gather specific metadata and insert into this tags table. We have about 19k unique files with about 330k tags. Hashing is done via XXhash, helping us find duplicate files. The dht_ tags are dhashes of a given thumbnail image, to allow us to find videos that are very similar. This is all wrapped up in a nice little node express website and working quite well, until I tried implementing the dhashing of these thumbnail images. My query is much more complicated than the above example, however that's the portion I'm struggling with. Sadly my knowledge of sql has slipped in recent years and as this is a side-project, I've been struggling to get this to work. I am however very happy about the rest of the setup, and for media tracking and such, it's doing it's job well enough already that its saving us a bunch of time hunting down a specific media file.

1

You can get COUNT of each tag by using next query;

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;
hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

Then apply filter conditions:

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);
hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

The condition can be further simplified to:

WHERE  (thumb > dht) ;

And it might be more efficient as a subquery (derived table) than a CTE, if the rows (that are produced by the CTE/derived table) are very many:

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 here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.