CODEWITHSUNDEEP
sqlpostgresqljoinaggregate
Jonathan Vanasco
Jonathan Vanasco

Reputation: 15680

creating a metrics table based on the counts of two other tables

I have two tables that contain some analytics data.

table a_2_bb_2_ccc
    id_a
    id_bb
    id_ccc

table a_2_bb_2_dddd
    id_a
    id_bb
    id_dddd

I'm trying to find a good way to periodically cache the count of triplets in each table, into a unified third table that is keyed by the shared two elements:

table a_2_bb_cache
    id_a 
    id_bb
    count_ccc
    count_dddd

I can't wrap my head around this though.

I keep thinking of trying to solve this with single table groupings, and merging:

CREATE TEMP TABLE a_2_bb_2_ccc__count AS
    SELECT
        id_a,
        id_bb,
        COUNT(id_ccc) AS count_ccc
    FROM
        a_2_bb_2_ccc
    GROUP BY id_a, id_bb
;

CREATE TEMP TABLE a_2_bb_2_dddd__count AS
    SELECT
        id_a,
        id_bb,
        COUNT(id_dddd) AS count_dddd
    FROM
        a_2_bb_2_dddd
    GROUP BY id_a, id_bb
;

however I can't be assured that any given (id_a, id_bb) combination is in one, or both, tables. so all of my attempts to join give me the wrong data.

can anyone point me in the right direction?

Upvotes: 1

Views: 65

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

I think you want a full outer join

select 
    id_a, 
    id_bb, 
    CASE WHEN count_ccc IS NOT NULL count_ccc ELSE 0 END,
    CASE WHEN count_dddd IS NOT NULL count_dddd ELSE 0 END
from
    (
        select
            id_a,
            id_bb,
            count(id_ccc) as count_ccc
        from a_2_bb_2_ccc
        group by id_a, id_bb
    ) sq1
    full outer join
    (
        select
            id_a,
            id_bb,
            count(id_dddd) as count_dddd
        from a_2_bb_2_dddd
        group by id_a, id_bb
    ) sq2 using(id_a, id_bb);

The using clause simplifies it as it makes its parameters unambiguous and non null. Otherwise, when using on it would be necessary to coalesce them.

Upvotes: 1

Related Questions