#工作杂记
#维度：行业、仓库、全部行业、全部仓库
#为减少tmp表 使用grouping sets
insert overwrite table tpm_p1
partition(pt_date='${hivevar:partition_day}')
select '${hivevar:partition_day}',
t.company_code,
nvl(t.industry,'全部行业'),
nvl(t2.warehouse_name,'全部仓库'),
t.sku_no,
row_number() over(partition by t.company_code, nvl(t.industry,'全部行业') , nvl(t2.warehouse_name,'全部仓库') order by t.sku_num desc) rn ,
regexp_replace('${hivevar:partition_day}','-','')
from (
select
company_code,
industry,
warehouse_code,
sku_no,
sum(sku_num) as sku_num
from tmp_p2 t1
where data_date='${hivevar:partition_day}'
and intime_sign = '1'
group by company_code,industry,warehouse_code,sku_no
grouping sets(
(company_code,industry,warehouse_code,sku_no),
(company_code,industry,sku_no),
(company_code,warehouse_code,sku_no),
(company_code,sku_no)
)
) t
left join tmp_p3 t2
on t.warehouse_code = t2.warehouse_code
and t.company_code=t2.company_code;
使用grouping sets的查询中不能用别名
