【无标题】

DearNingning

已于 2022-08-19 10:44:05 修改

阅读量145

点赞数

分类专栏： Hive 文章标签：大数据

于 2022-08-19 10:37:32 首次发布

本文链接：https://blog.csdn.net/DearNingning/article/details/126419742

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了如何使用groupingsets高阶聚合函数对交易数据进行多级别汇总，包括按年、季度、月、周及不同机构层级进行统计，涉及正向、负向交易笔数、金额和净交易金额等多个关键指标，以实现全面的交易分析和监控。

摘要由CSDN通过智能技术生成

grouping sets 高阶聚合函数使用

先对分组字段判空：

insert overwrite table  org_judge
select
 nvl(org_one_code,'-')	                  --省机构编号	
,nvl(org_two_code,'-')	                  --地市机构编号
,nvl(org_three_code,'-')	              --区县机构编号
,nvl(org_four_code,'-')	                  --网点编号
,posi_trade_acc                            --正向交易笔数
,posi_trade_amt                            --正向交易额
,nega_trade_acc                            --负向交易笔数
,nega_trade_amt                            --负向交易额 
,net_trade_amt                             --净交易金额
,trade_date                               --交易日期
,internal_shop_code
from dws_trade_merc_td

insert overwrite table  agent_judge
select
 nvl(agent.four_agent_code,'-')	                       --所属四级服务商编号
,nvl(agent.three_agent_code,'-')                        --所属三级服务商编号
,nvl(agent.two_agent_code,'-')	                       --所属二级服务商编号
,nvl(agent.one_agent_code,'-')	                       --所属一级服务商编号
,posi_trade_acc                                        --正向交易笔数
,posi_trade_amt                                        --正向交易额
,nega_trade_acc                                        --负向交易笔数
,nega_trade_amt                                        --负向交易额 
,net_trade_amt                                         --净交易金额
,internal_shop_code                                    --内部商户号
,trade_date                                           --交易日期
from  dws_trade_merc_td agent

使用高阶聚合函数：

insert overwrite table dws_trade_date_org_td
select
    dt.date_year                                  --年
   ,dt.date_ym                                    --年月
   ,dt.date_yq                                    --年季度
   ,dt.date_yw                                    --年周
   ,merc.org_one_code	                           --省机构编号	
   ,merc.org_two_code	                           --地市机构编号
   ,merc.org_three_code	                            --区县机构编号
   ,merc.org_four_code	                           --网点编号
   ,sum(merc.posi_trade_acc)	                   --正向交易笔数
   ,sum(merc.posi_trade_amt)	                   --正向交易额	  
   ,sum(merc.nega_trade_acc)	                   --负向交易笔数 
   ,sum(merc.nega_trade_amt)	                   --负向交易额  
   ,sum(merc.net_trade_amt)                       --净交易金额 
   ,count(distinct merc.internal_shop_code)       --商户数
from dim_date dt  
join org_judge  merc   
on dt.date_ymd  = merc.trade_date
group by 
 dt.date_year                  --年
,dt.date_ym                    --年月
,dt.date_yq                    --年季度
,dt.date_yw                    --年周
,merc.org_one_code             --省机构编号	
,merc.org_two_code	           --地市机构编号	
,merc.org_three_code	       --区县机构编号	
,merc.org_four_code        
grouping sets(
(dt.date_year,merc.org_one_code) 
,(dt.date_year,merc.org_one_code,merc.org_two_code)
,(dt.date_year,merc.org_one_code,merc.org_two_code,merc.org_three_code)
,(dt.date_year,merc.org_one_code,merc.org_two_code,merc.org_three_code,merc.org_four_code)
,(dt.date_yq,merc.org_one_code) 
,(dt.date_yq,merc.org_one_code,merc.org_two_code)
,(dt.date_yq,merc.org_one_code,merc.org_two_code,merc.org_three_code)
,(dt.date_yq,merc.org_one_code,merc.org_two_code,merc.org_three_code,merc.org_four_code)
,(dt.date_ym,merc.org_one_code) 
,(dt.date_ym,merc.org_one_code,merc.org_two_code)
,(dt.date_ym,merc.org_one_code,merc.org_two_code,merc.org_three_code)
,(dt.date_ym,merc.org_one_code,merc.org_two_code,merc.org_three_code,merc.org_four_code)
,(dt.date_yw,merc.org_one_code) 
,(dt.date_yw,merc.org_one_code,merc.org_two_code)
,(dt.date_yw,merc.org_one_code,merc.org_two_code,merc.org_three_code)
,(dt.date_yw,merc.org_one_code,merc.org_two_code,merc.org_three_code,merc.org_four_code)
)

insert overwrite table  dws_trade_date_agent_td
select
 dt.date_year                                  --年
,dt.date_ym                                    --年月
,dt.date_yq                                    --年季度
,dt.date_yw                                    --年周
,agent.four_agent_code	                       --所属四级服务商编号
,agent.three_agent_code                        --所属三级服务商编号
,agent.two_agent_code	                       --所属二级服务商编号
,agent.one_agent_code	                       --所属一级服务商编号
,sum(agent.posi_trade_acc)	                   --正向交易笔数
,sum(agent.posi_trade_amt)	                   --正向交易额	  
,sum(agent.nega_trade_acc)	                   --负向交易笔数 
,sum(agent.nega_trade_amt)	                   --负向交易额  
,sum(agent.net_trade_amt)                      --净交易金额 
,count(distinct agent.internal_shop_code)
from dim_date dt
join agent_judge agent on dt.date_ymd  =  agent.trade_date
group by 
 dt.date_year                     --年
,dt.date_ym                      --年月
,dt.date_yq                      --年季度
,dt.date_yw                      --年周
,agent.four_agent_code	        --所属四级服务商编号
,agent.three_agent_code	        --所属三级服务商编号
,agent.two_agent_code		     --所属二级服务商编号
,agent.one_agent_code      
grouping sets(
 (dt.date_year,agent.one_agent_code) 
,(dt.date_year,agent.one_agent_code,agent.two_agent_code)
,(dt.date_year,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code)
,(dt.date_year,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code,agent.four_agent_code)
,(dt.date_yq,agent.one_agent_code) 
,(dt.date_yq,agent.one_agent_code,agent.two_agent_code)
,(dt.date_yq,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code)
,(dt.date_yq,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code,agent.four_agent_code)
,(dt.date_ym,agent.one_agent_code) 
,(dt.date_ym,agent.one_agent_code,agent.two_agent_code)
,(dt.date_ym,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code)
,(dt.date_ym,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code,agent.four_agent_code)
,(dt.date_yw,agent.one_agent_code) 
,(dt.date_yw,agent.one_agent_code,agent.two_agent_code)
,(dt.date_yw,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code)
,(dt.date_yw,agent.one_agent_code,agent.two_agent_code,agent.three_agent_code,agent.four_agent_code)
)

insert overwrite table dws_merc_date_orgincome_td
select
    dt.date_year                                    --年
   ,dt.date_ym                                      --年月
   ,dt.date_yq                                      --年季度
   ,dt.date_yw                                      --年周
   ,merc.org_code	 	                             --所属机构号
   ,sum(merc.merc_acc)                            --入网总商户数
from dim_date dt  
join dws_merc_org_td merc   
on dt.date_y_m_d  = merc.income_time
group by 
 dt.date_year                  --年
,dt.date_ym                    --年月
,dt.date_yq                    --年季度
,dt.date_yw                    --年周
,merc.org_code	                --所属机构号
grouping sets(
(dt.date_year,merc.org_code) 
,(dt.date_yq,merc.org_code) 
,(dt.date_ym,merc.org_code) 
,(dt.date_yw,merc.org_code) 
)