单个sink表 和多个source表的统计

Fri, 30 Oct 2020 03:27:32 -0700 

Hi, 我这边有这样一种场景，我的一张sink表中的  指标统计分别来源于多张source表， 比如cnt_a 来源于 count(a), cnt_b来源于 
count(b)。 统计结果 sink到mysql时我现在只能用union all，如下：
```sql
CREATE TABLE  SourceA (
       day_time   STRING,
        a                 STRING
) WITH (
      'connector' = 'kafka'
)

CREATE TABLE  SourceB (
        day_time   STRING,
        b                  STRING
) WITH (
      'connector' = 'kafka'
)

CREATE TABLE  Sink (
         day_time    STRING,
        count_a       BIGINT,
        count_b       BIGINT
) WITH (
      'connector' = 'jdbc'
)

INSERT INTO Sink
SELECT
   day_time  ,
   count(1) AS count_a,
   0 AS count_b
FROM SourceA GROUP BY day_time
UNION ALL
SELECT
   day_time  ,
   0 AS count_a,
   count(1) AS count_b
FROM SourceA GROUP BY day_time
```

也就是没有的字段，要用0来填充，以满足所有字段，但是感觉这样很复杂，字段多就每个字段都要写
求助下社区有没有比较优雅的sql的实现方式，😍

回复