sql - 根据另外两个表的计数创建一个指标表

Question

我有两个包含一些分析数据的表。

table a_2_bb_2_ccc
    id_a
    id_bb
    id_ccc

table a_2_bb_2_dddd
    id_a
    id_bb
    id_dddd

我正在尝试找到一种好方法来定期将每个表中的三元组计数缓存到一个统一的第三个表中，该表由共享的两个元素键控:

table a_2_bb_cache
    id_a 
    id_bb
    count_ccc
    count_dddd

虽然我无法解决这个问题。

我一直在考虑尝试通过单表分组和合并来解决这个问题:

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
;

但是我不能保证任何给定的 (id_a, id_bb) 组合都在一个或两个表中。所以我所有的加入尝试都给我错误的数据。

谁能指出我正确的方向？

Answer 1

我想你想要一个全外连接

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

using 子句简化了它，因为它使参数明确且非空。否则，当使用 on 时，有必要合并它们。