2

I am refactoring some business logic that we normally do as nightly processes in Java, and am now attempting to move into PostgreSQL as a set of materialized views. I've reduced the problem to its essentials, which results in the three tables below (category_source ~10 rows, category ~100k rows, category_tags ~10M rows).

The logic is fairly simple: for each Source, sum the values of each Tag in the set of Categories in that Source, and divide each of those sums by the total number of Categories in the Source.

--DROP TABLE category_tags;
--DROP TABLE category;
--DROP TABLE category_source;

CREATE TABLE category_source
(
  id integer NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE category
(
  label text NOT NULL,
  source integer NOT NULL REFERENCES category_source(id),
  PRIMARY KEY (label)
);

CREATE TABLE category_tags
(
  category text NOT NULL REFERENCES category(label),
  tag text NOT NULL,
  rank real NOT NULL,
  PRIMARY KEY (category,tag)
);

-- Load sample data.
INSERT INTO category_source VALUES (1);
INSERT INTO category_source VALUES (2);

INSERT INTO category VALUES ('C3035240',1);
INSERT INTO category VALUES ('C3035245',1);
INSERT INTO category VALUES ('C3035250',2);

INSERT INTO category_tags VALUES ('C3035240','test',24.00);
INSERT INTO category_tags VALUES ('C3035240','sample',24.00);
INSERT INTO category_tags VALUES ('C3035240','method',20.00);
INSERT INTO category_tags VALUES ('C3035240','variety',18.00);
INSERT INTO category_tags VALUES ('C3035240','explanation',15.00);
INSERT INTO category_tags VALUES ('C3035245','test',20.00);
INSERT INTO category_tags VALUES ('C3035245','extra',21.00);
INSERT INTO category_tags VALUES ('C3035245','method',20.00);
INSERT INTO category_tags VALUES ('C3035245','sample',18.00);
INSERT INTO category_tags VALUES ('C3035245','question',15.00);
INSERT INTO category_tags VALUES ('C3035250','method',10.00);
INSERT INTO category_tags VALUES ('C3035250','explanation',8.00);
INSERT INTO category_tags VALUES ('C3035250','test',6.00);
INSERT INTO category_tags VALUES ('C3035250','question',5.00);
INSERT INTO category_tags VALUES ('C3035250','sample',2.00);
INSERT INTO category_tags VALUES ('C3035250','variety',4.00);

Query 1 here gets most of the way there, but the source_category_count column contains the number of Categories that the Tag is in for the Source (varies), whereas what I really want is the total number of Categories in the Source.

SELECT category_source.id,category_tags.tag,
       SUM(category_tags.rank) AS tag_total,
       COUNT(category.label) AS source_category_count,
       SUM(category_tags.rank)/COUNT(category.label) AS source_tag_rank
 FROM
  category_source,category,category_tags
 WHERE
  category_source.id=category.source
  AND category.label=category_tags.category
 GROUP BY category_source.id,category_tags.tag ORDER BY category_source.id,tag_total DESC;

 id |     tag     | tag_total | source_category_count | source_tag_rank
----+-------------+-----------+-----------------------+-----------------
  1 | test        |        44 |                     2 |              22
  1 | sample      |        42 |                     2 |              21
  1 | method      |        40 |                     2 |              20
  1 | extra       |        21 |                     1 |              21
  1 | variety     |        18 |                     1 |              18
  1 | explanation |        15 |                     1 |              15
  1 | question    |        15 |                     1 |              15
  2 | method      |        10 |                     1 |              10
  2 | explanation |         8 |                     1 |               8
  2 | test        |         6 |                     1 |               6
  2 | question    |         5 |                     1 |               5
  2 | variety     |         4 |                     1 |               4
  2 | sample      |         2 |                     1 |               2
(13 rows)

Query 2 below produces the results I am really looking for:

SELECT q1.*,q2.source_category_count,q1.tag_total/q2.source_category_count AS tag_source_rank FROM
(
  SELECT category_source.id AS source,category_tags.tag,SUM(category_tags.rank) AS tag_total
   FROM category_source
   INNER JOIN category ON (category_source.id=category.source)
   INNER JOIN category_tags ON (category.label=category_tags.category)
   GROUP BY category_source.id,category_tags.tag
) q1,
(
  SELECT source,COUNT(category) AS source_category_count FROM category GROUP BY source
) q2
 WHERE q1.source=q2.source
 ORDER BY source,tag_source_rank DESC
;

     source |     tag     | tag_total | source_category_count | tag_source_rank
    --------+-------------+-----------+-----------------------+-----------------
          1 | test        |        44 |                     2 |              22
          1 | sample      |        42 |                     2 |              21
          1 | method      |        40 |                     2 |              20
          1 | extra       |        21 |                     2 |            10.5
          1 | variety     |        18 |                     2 |               9
          1 | explanation |        15 |                     2 |             7.5
          1 | question    |        15 |                     2 |             7.5
          2 | method      |        10 |                     1 |              10
          2 | explanation |         8 |                     1 |               8
          2 | test        |         6 |                     1 |               6
          2 | question    |         5 |                     1 |               5
          2 | variety     |         4 |                     1 |               4
          2 | sample      |         2 |                     1 |               2
    (13 rows)

Query 3 produces equivalent results using WITH x () SELECT ...:

WITH category_counts AS
(
  SELECT source,COUNT(category) AS source_category_count FROM category GROUP BY source
) 
SELECT category_counts.source,category_tags.tag,
       SUM(category_tags.rank) AS tag_total,
       COUNT(category.label) AS source_category_freq,
       category_counts.source_category_count,
       SUM(category_tags.rank)/category_counts.source_category_count AS source_tag_rank
 FROM category_counts
 INNER JOIN category ON (category_counts.source=category.source)
 INNER JOIN category_tags ON (category.label=category_tags.category)
 GROUP BY category_counts.source,category_counts.source_category_count,category_tags.tag ORDER BY category_counts.source,tag_total DESC;

 source |     tag     | tag_total | source_category_freq | source_category_count | source_tag_rank
--------+-------------+-----------+----------------------+-----------------------+-----------------
      1 | test        |        44 |                    2 |                     2 |              22
      1 | sample      |        42 |                    2 |                     2 |              21
      1 | method      |        40 |                    2 |                     2 |              20
      1 | extra       |        21 |                    1 |                     2 |            10.5
      1 | variety     |        18 |                    1 |                     2 |               9
      1 | explanation |        15 |                    1 |                     2 |             7.5
      1 | question    |        15 |                    1 |                     2 |             7.5
      2 | method      |        10 |                    1 |                     1 |              10
      2 | explanation |         8 |                    1 |                     1 |               8
      2 | test        |         6 |                    1 |                     1 |               6
      2 | question    |         5 |                    1 |                     1 |               5
      2 | variety     |         4 |                    1 |                     1 |               4
      2 | sample      |         2 |                    1 |                     1 |               2
(13 rows)

Although I have two working queries that both produce the results I am looking for, I am unsatisfied with using two subqueries over tables of this size (I have not yet loaded in all of my data or done any performance testing, I am simply working on this test case at the moment).

I feel that the value for source_category_count that I am looking for is buried somewhere in Query 1 and I just don't know how to access it.

Another alternative I am investigating is COUNT() OVER (PARTITION BY category_source), but I do not have a working query for that method at the moment.

Is there a simpler query that will produce the same results as Query 2 or Query 3 (i.e., a modification of Query 1)?

Update: Added a second working query.

12
  • 1
    you don't have to use the USING clause. It's great practice though. It forces you away from the convention you're adopting here with id which is pretty lame. The standard pushes for globally unique table-identifiers with USING Dec 8, 2017 at 18:07
  • 1
    And, I agree NATURAL JOINS are a horrible idea, so don't use them. That has nothing to do with SQL-92 syntax. Dec 8, 2017 at 18:08
  • 1
    @ypercubeᵀᴹ HEINOUS JOIN USING (...). Anyway, I revised my queries to use SQL-92 joins, except for join between the sub-queries, which I'm unclear on the most appropriate form.
    – Parker
    Dec 8, 2017 at 18:34
  • 1
    @vallismortis I went ahead and tried to answer that question too, if you want a better answer for it then the others stackoverflow.com/a/47720615/124486 Dec 8, 2017 at 19:05
  • 1
    What exactly does source_category_count count? The number of distinct category values in category table or the number of distinct category values in the category join category_tags? Because the two numbers may differ (if there are categories that do not appear in category_tags). Dec 8, 2017 at 19:10

4 Answers 4

1

Instead of joining straight to the category table you can join instead to a subquery with a window function. You only query the table once and also get your end result.

SELECT category_source.id AS source,
    category_tags.tag,
    SUM(category_tags.rank) AS tag_total,
    MAX(category_source_count) AS source_category_count,
    SUM(category_tags.rank)/MAX(category_source_count) AS source_tag_rank
FROM category_source
   INNER JOIN 
        (
        SELECT label, source, 
            COUNT(*) OVER (PARTITION BY source) category_source_count 
            FROM category
        ) AS category ON category_source.id=category.source
   INNER JOIN category_tags ON category.label=category_tags.category
GROUP BY category_source.id,category_tags.tag 
ORDER BY category_source.id,tag_total DESC;
3
  • Ok, now this is closer to what I'm looking for. This produces the expected results and uses one fewer sub-query. I still feel that there should be a more direct way to get to the category count without a sub-select, but given the current limitations in PostgreSQL, this looks like it is possibly the most direct solution to the query. I compared the execution plan of this query to Query 3 in the question, and the run-time for this one is slightly worse, but that could change when I run a test against the larger tables.
    – Parker
    Dec 8, 2017 at 20:09
  • @vallismortis I imagine query 3 would be even faster if you didn't use a CTE. Dec 8, 2017 at 20:15
  • @EvanCarroll Isn't that essentially what this answer is? The CTE produces a very small table, so I don't think it adds much overhead. I'm open to suggestions.
    – Parker
    Dec 8, 2017 at 20:20
1

whereas what I really want is the total number of Categories in the Source.

You're grouping by category_source.id, category_tags.tag -- that means you can never say you want "in the" without including that group, which in your case includes tags. The two sub-selects with different GROUP BY's is the accepted method of doing this, but there are other options to generate the data you want, like GROUPING SETS; however, the result won't look the same.

SELECT
        c.label,
        tag,
        SUM(ct.rank) AS tag_total,
        COUNT(c.label) AS source_category_count
FROM category AS c
JOIN category_tags AS ct
        ON (ct.category=c.label)
GROUP BY GROUPING SETS ((c.label, ct.tag), (c.label))

;
  label   |     tag     | tag_total | source_category_count 
----------+-------------+-----------+-----------------------
 C3035240 | explanation |        15 |                     1
 C3035240 | method      |        20 |                     1
 C3035240 | sample      |        24 |                     1
 C3035240 | test        |        24 |                     1
 C3035240 | variety     |        18 |                     1
 C3035240 |             |       101 |                     5
 C3035245 | extra       |        21 |                     1
 C3035245 | method      |        20 |                     1
 C3035245 | question    |        15 |                     1
 C3035245 | sample      |        18 |                     1
 C3035245 | test        |        20 |                     1
 C3035245 |             |        94 |                     5
 C3035250 | explanation |         8 |                     1
 C3035250 | method      |        10 |                     1
 C3035250 | question    |         5 |                     1
 C3035250 | sample      |         2 |                     1
 C3035250 | test        |         6 |                     1
 C3035250 | variety     |         4 |                     1
 C3035250 |             |        35 |                     6
(19 rows)

You can see here we have two GROUP BYs in the same SELECT and you can see how SQL displays such a query.

As a side note, I would suggest never using SQL-89 joins. There is no reason for it. Writes your joins explicitly with [INNER] JOIN.

1
  • 1
    As you recommended, I revised my queries to use SQL-92 joins, except in the case of the sub-selects. I haven't used GROUPING SETS before, but I am now investigating it.
    – Parker
    Dec 8, 2017 at 18:43
1

Just for fun*, the query could be done with no subqueries - if Postgres had implemented DISTINCT in window aggregates:

select distinct
    source,
    tag,
    sum(rank) over (partition by source, tag) as tag_total,
    count(*) over (partition by source, tag) as count,
    count(distinct category) over (partition by source) as count_category,
    sum(rank) over (partition by source, tag)
    / count(distinct category) over (partition by source) as avg_rank
from 
    category c 
    join category_tags ct
        on label = category
order by 
    source,
    tag_total desc ;

Tested in dbfiddle.uk (Oracle), it just works.

In dbfiddle.uk (Postgres), gives the error:

ERROR:  DISTINCT is not implemented for window functions  
LINE 6:     count(distinct category) over (partition by source) as c...  
            ^

* I wouldn't recommend using the above method anyway, even if the syntax was available. The need for two different aggregation sets in the same result require to use two different OVER () expressions and the use of SELECT DISTINCT. All in, is probably a recipe for mediocre efficiency.

A query with 2 derived tables and then joining them, would probably be more efficient.

1
  • 1
    I upvoted this, because (a) it's useful to point out that there are better methods of doing this that aren't yet supported, (b) when I do this others they should react the same way. wink wink mysql, and sql-server borg =) Dec 8, 2017 at 19:46
0

Maybe a starting point ...

select distinct
  CS.id
, CT.tag
, sum( CT.rank ) over ( partition by C.source, CT.tag order by C.source) as tag_total
, count( CT.category ) over ( partition by C.source, CT.tag order by C.source) as count
, ( sum( CT.rank ) over ( partition by C.source, CT.tag order by C.source) )
  / ( count( CT.category ) over ( partition by C.source, CT.tag order by C.source) 
  ) as tag_source_rank
from category_source CS
  join category C on CS.id = C.source
  join category_tags CT on C.label = CT.category
order by CS.id, tag_total desc
;

-- Result

 id |     tag     | tag_total | count | tag_source_rank 
----+-------------+-----------+-------+-----------------
  1 | test        |        44 |     2 |              22
  1 | sample      |        42 |     2 |              21
  1 | method      |        40 |     2 |              20
  1 | extra       |        21 |     1 |              21
  1 | variety     |        18 |     1 |              18
  1 | explanation |        15 |     1 |              15
  1 | question    |        15 |     1 |              15
  2 | method      |        10 |     1 |              10
  2 | explanation |         8 |     1 |               8
  2 | test        |         6 |     1 |               6
  2 | question    |         5 |     1 |               5
  2 | variety     |         4 |     1 |               4
  2 | sample      |         2 |     1 |               2
(13 rows)

Dbfiddle (Postgresql 9.5)

1
  • I see where you're going with this, and I was on the same path but haven't found the right partition to get that 4th column to produce the category count over the source (see results from Query 2 and Query 3 for the "correct" result set).
    – Parker
    Dec 8, 2017 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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