Oracle:通过联合等组合两个group by查询(使用聚合函数count())以获得合并结果

Question

我有两张 table 。 TABLE_A 和 TABLE_B。

两个表都维护用于保存CREATION_USER的列。但该列在各个表中具有不同的名称。

我的动机是获取每个用户在两个表中创建的记录数。也就是说，用很少的条件组合这两个查询的结果。用户名不应重复，对于在两个表中都创建了记录的用户名，计数应该是它们的总和。

SELECT A.CREATION_USER_A AS "USER",
COUNT(*)
FROM TABLE_A A
GROUP BY A.CREATION_USER_A;

SELECT B.CREATION_USER_B AS "USER",
COUNT(*)
FROM TABLE_B B
GROUP BY B.CREATION_USER_B;

例如，

USER_A 已在 TABLE_A 中创建 2 条记录，

USER_B 已在 TABLE_B 中创建了 3 条记录，并且

USER_C 在 TABLE_A 中创建了 4 条记录，在 TABLE_B 中创建了 3 条记录。

所以输出应该是这样的:

|  USER  | COUNT |
| USER_A | 2     |
| USER_B | 3     |
| USER_C | 7     |

我已经编写了一个查询来执行此操作，但它的性能非常糟糕。

SELECT A.CREATION_USER_A AS "USER",
(COUNT(A.CREATION_USER_A)+(SELECT COUNT(CREATION_USER_B) FROM TABLE_B WHERE CREATION_USER_B = A.CREATION_USER_A)) AS "COUNT"
FROM TABLE_A A
GROUP BY A.CREATION_USER_A
UNION
SELECT B.CREATION_USER_B,
COUNT(B.CREATION_USER_B)
FROM TABLE_B B
WHERE B.CREATION_USER_B NOT IN (SELECT CREATION_USER_A FROM TABLE_A)
GROUP BY B.CREATION_USER_B;

请提出一种完成此任务的方法。

Answer 1

您可以简单地构建一个由表中所有记录的并集(保留重复项)给出的集合，然后对按创建用户分组的记录进行计数:

构建一些示例数据:

create table table_a(id, creation_user_a) as ( 
    select 1, 'USER_A' from dual union all
    select 1, 'USER_A' from dual union all
    select 1, 'USER_C' from dual union all
    select 1, 'USER_C' from dual union all
    select 1, 'USER_C' from dual union all
    select 1, 'USER_C' from dual
);

create table table_b(id, creation_user_b) as ( 
    select 1, 'USER_B' from dual union all
    select 1, 'USER_B' from dual union all
    select 1, 'USER_B' from dual union all
    select 1, 'USER_C' from dual union all
    select 1, 'USER_C' from dual union all
    select 1, 'USER_C' from dual
)

查询:

select count(1), creation_user
from ( /* the union of all the records from table_a and table_b */
       select creation_user_a as creation_user from table_a
       union all /* UNION ALL keeps duplicates */
       select creation_user_B from table_b
     )
group by creation_user 
order by creation_user 

结果:

2   USER_A
3   USER_B
7   USER_C

解释计划:

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |    12 |    96 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY        |         |    12 |    96 |     8  (25)| 00:00:01 |
|   2 |   HASH GROUP BY       |         |    12 |    96 |     8  (25)| 00:00:01 |
|   3 |    VIEW               |         |    12 |    96 |     6   (0)| 00:00:01 |
|   4 |     UNION-ALL         |         |       |       |            |          |
|   5 |      TABLE ACCESS FULL| TABLE_A |     6 |    48 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| TABLE_B |     6 |    48 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------