create table if not exists 表的名称A(
type_code string comment '01为省 02为市 03为区县 04为xx部 05为aa线 06为bb所 07为cc线',
type_name string comment '区域名称',
type_uuid string comment '维度的id',
sale_amount string comment '销售额(万元)'
)comment '销量指标' partitioned by(ds string comment '日分区');
insert
overwrite table 表的名称A partition(ds = '${bizdate}')
--全省销量
select
'01' as type_code,
'全省' as type_name,
'1' as type_uuid,
sum(sale_amount) as sale_amount
from 表的名称
where ds='${bizdate}' and biz_date >='日期(可以是yyyymmdd也可以是yyyy-mm-dd 在DB中的调度配置中设置具体格式)' and biz_date<='${bizdate}'
union all
--市销量
select
'02' as type_code,
b.org_name type_name,
a.city as type_uuid,
sum(a.sale_amount) as sale_amount
from (select city,sale_amount from 来源表的名称X where ds='${bizdate}' and biz_date >='日期(可以是yyyymmdd也可以是yyyy-mm-dd 在DB中的调度配置中设置具体格式)' and biz_date<='${bizdate}')a
join
(select org_name,org_uuid from 来源表的名称Y where ds='${bizdate}')b
on a.city=b.org_uuid
group by a.city,b.org_name
--区县
union all
select
'03' as type_code,
b.org_name as type_name,
a.county as type_uuid,
sum(a.sale_amount) as sale_amount
from (select county,sale_amount from 来源表的名称X where ds='${bizdate}' and biz_date >='日期(可以是yyyymmdd也可以是yyyy-mm-dd 在DB中的调度配置中设置具体格式)' and biz_date<='${bizdate}')a
join
(select org_name,org_uuid from 来源表的名称Y where ds='${bizdate}')b
on a.county=b.org_uuid
group by a.county,b.org_name;