数据中台之窗口函数应用案例

于 2020-11-17 10:48:36 发布
阅读量244 收藏
点赞数
分类专栏： 数据大屏 BI报表 文章标签： 大数据 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43728713/article/details/109737918
版权

标题 窗口函数的应用案例

insert overwrite ads_self_special_sale_detail

select 
    a2.fourth_daim as COMPNO,
    a2.fourth_name as COMPNM,
    a2.third_daim as ORG3NO,
    a2.third_name as ORG3NM,
    a2.second_daim as ORG2NO,
    a2.second_name as ORG2NM,
    '0' as shop_code1,
    '0' as shop_name1,
    a1.ORGNO as shop_code2,
    a2.quanc as shop_name2,
    a1.CHANNO,
    if(CHANNO='ZY','自营','购物中心') as CHANNM,
    a4.gouzzl as house_type,  --房产情况
    '0' as remarks,  --情况备注
    a5.sales_num, --营业人员数量(本期/人)
    a5.sales_num_avg/(if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int)))) as sales_num_avg,  --营业人员数量(平均/人)
    a5.BSAREA,  --营业面积(本期/m2)
    a5.areas_avg/(if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int)))) as areas_avg,  --营业面积(平均/m2)
    if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int))) as months, --经营月份
    a1.SAAM2, --有效金额
    sum(a1.SAAM2) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as SAAM2_total, --有效金额(累计)
    a1.SMAM, --结算金额(本期)
    sum(a1.SMAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as SMAM_total_tax, --结算金额(累计含税)
    sum(a1.SMAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/a6.VAT as SMAM_total_notax, --结算金额(累计不含税)    
    a1.SACTAM,  --销售成本_含税(本期)
    sum(a1.SACTAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as SACTAM_total_tax, --销售成本_含税(累计)
    sum(a1.SACTAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/a6.VAT as SACTAM_total_notax, --销售成本_不含税(累计)    
    a1.GPROFIT_notax,  --销售毛利_不含税(本期)
    sum(a1.GPROFIT_notax) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as GPROFIT_total_notax, --销售毛利_不含税(累计)    
    --a1.GPROFIT_notax/a1.REVENUE as GPROFIT_rate, --销售毛利率
    a1.REVENUE as GPROFIT_rate, --销售毛利率
    -- sum(a1.GPROFIT_notax) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/sum(a1.REVENUE) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as GPROFIT_total_rate, --销售毛利率_累计    
    sum(a1.REVENUE) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as GPROFIT_total_rate, --销售毛利率_累计    
    a3.total_expenses,  --费用总额(本期)
    sum(a3.total_expenses) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as total_expenses_total, --费用总额(累计)      
    a3.expenses_other,  --其他费用(本期)
    sum(a3.expenses_other) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as expenses_other_total, --其他费用(累计)        
    a3.expenses_rent,  --租赁费用(本期)
    sum(a3.expenses_rent) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as expenses_rent_total, --租赁费用(累计)   
    a3.expenses_fitup,  --装修费用(本期)
    sum(a3.expenses_fitup) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as expenses_fitup_total, --装修费用(累计)   
    a1.GPROFIT_notax-a3.total_expenses as GALOAM,   --盈亏总额
    sum(a1.GPROFIT_notax-a3.total_expenses) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as GALOAM_total, --盈亏总额(累计)   
    a3.yj_total_expenses,  --应计制费用总额(本期)
    sum(a3.yj_total_expenses) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as yj_total_expenses_total, --应计制费用总额(累计)      
    a3.yj_expenses_rent,  --应计制租赁费(本期)
    sum(a3.yj_expenses_rent) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as yj_expenses_rent_total, --应计制租赁费(累计)  
    a3.other_expenses_gz,  --工资费(本期)
    sum(a3.other_expenses_gz) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as other_expenses_gz_total, --工资费(累计)  
    a3.other_expenses_zj,  --折旧费(本期)
    sum(a3.other_expenses_zj) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as other_expenses_zj_total, --折旧费(累计)  
    a3.other_expenses_sd, --水电费(本期)
    sum(a3.other_expenses_sd) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as other_expenses_sd_total, --水电费(累计)  
    a3.other_expenses_qt,  --其他费(本期)
    sum(a3.other_expenses_qt) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as other_expenses_qt_total, --其他费(累计)  
    a1.GPROFIT_notax-a3.yj_total_expenses as yj_GALOAM,   --盈亏总额（应计制本期）
    sum(a1.GPROFIT_notax-a3.yj_total_expenses) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) ) as yj_GALOAM_total, --盈亏总额（应计制累计） 
    sum(a1.GPROFIT_notax-a3.yj_total_expenses) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/(sum(a1.SMAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )) as yj_GALOAM_rate,  --盈亏率(应计制累计)
    sum(a1.SMAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int)))/(a5.sales_num_avg/(if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int))))) as human_effect,  --人效(结算_累计)
     sum(a1.SMAM) over (partition by a1.ORGNO,substr(a1.accmm,1,4) order by a1.ORGNO,substr(a1.accmm,1,4),substr(a1.accmm,5,2) )/if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int)))/(a5.areas_avg/(if(substr(a1.accmm,1,4)>substr(nvl(a4.kaiyrq_day,'0000'),1,4),cast(substr(a1.accmm,5,2) as int),if(substr(a1.accmm,5,2)>substr(a4.kaiyrq_day,5,2),cast(substr(a1.accmm,5,2) as int)-cast(substr(a4.kaiyrq_day,5,2) as int),cast(substr(a1.accmm,5,2) as int))))) as areas_effect,  --平效(结算_累计)
    a4.kaiyrq,  --开业日期
    a4.guanbrq,  --关闭日期
    a1.accmm
from
--实售金额，结算金额，销售成本
(select ORGNO,CHANNO,accmm,sum(SAAM2) as SAAM2,sum(SMAM) as SMAM,sum(SACTAM) as SACTAM,sum(GPROFIT_NOTAX) as GPROFIT_notax,sum(REVENUE) as REVENUE from ${youngor_cdm}.fct_fi_settle_di where ds<=substr('${bizdate}',1,6) and orgtyno='SHOP' and CHANNO in ('ZY','GW') and substr(accmm,1,4)>=2018 group by ORGNO,CHANNO,accmm ) a1
left join 
(select code,quanc,second_daim,second_name,third_daim,third_name,fourth_daim,fourth_name from ${youngor_ods}.ods_organization_dimension
 where ds='${bizdate}' and organization_leix='销售') a2
 on a1.ORGNO=a2.code
left join
--费用  (费用总额 其他费用 租赁费 装修费 应计制费用总额 应计制租赁费 工资 折旧 水电 其他)
(select 
    b1.orgno,
    b1.accmm,
    b1.total_expenses,
    b1.expenses_other,
    b1.expenses_rent,
    b1.expenses_fitup,
    (b1.expenses_other+b1.expenses_fitup+b1.yj_expenses_rent) as yj_total_expenses,
    b1.yj_expenses_rent,
    b1.other_expenses_gz,
    b1.other_expenses_zj,
    b1.other_expenses_sd,
    (b1.expenses_other-b1.other_expenses_gz-b1.other_expenses_zj-b1.other_expenses_sd) as other_expenses_qt
from
(select 
    b.orgno,
    b.accmm,
    sum(total_expenses) as total_expenses,
    sum(expenses_other) as expenses_other,
    sum(expenses_rent) as expenses_rent,
    sum(expenses_fitup) as expenses_fitup,
    sum(yj_expenses_rent) as yj_expenses_rent,
    sum(other_expenses_gz) as other_expenses_gz,
    sum(other_expenses_zj) as other_expenses_zj,
    sum(other_expenses_sd) as other_expenses_sd
from
(
select 
    orgno,
    accmm,
    expensetype,
    if(expensetype in ('其他','租赁费_现金制','装修费'),sum(current_amount),0) as total_expenses ,
    if(expensetype in ('其他'),sum(current_amount),0) as expenses_other ,
    if(expensetype in ('租赁费_现金制'),sum(current_amount),0) as expenses_rent ,
    if(expensetype in ('装修费'),sum(current_amount),0) as expenses_fitup,
    if(expensetype in ('租赁费_应计制'),sum(current_amount),0) as yj_expenses_rent,
    if(expensetype in ('工资'),sum(current_amount),0) as other_expenses_gz,
    if(expensetype in ('折旧'),sum(current_amount),0) as other_expenses_zj,
    if(expensetype in ('水电费'),sum(current_amount),0) as other_expenses_sd
from ${youngor_cdm}.fct_fi_expense_category_df where ds <=substr('${bizdate}',1,6) and orgno is not null group by orgno,accmm,expensetype
) b group by b.orgno,b.accmm
) b1
) a3 on a1.ORGNO=a3.orgno and a1.accmm=a3.accmm
left join
--房产情况(购置类型) 开业日期 关闭日期
(select daim,gouzzl,substr(kaiyrq,1,10) as kaiyrq,substr(guanbrq,1,10) as guanbrq,kaiyrq_day from ${ld_fuzhuang}.dim_pub_cha_shop where ds='${bizdate}' 
) a4 on a1.ORGNO=a4.daim
left join
--营业员人数 营业面积
(select 
    a.ORGNO,
    a.accmm,
    a.EMPLFIQT as sales_num,
    sum(a.EMPLFIQT) over (partition by a.ORGNO,substr(a.accmm,1,4) order by a.ORGNO,substr(a.accmm,1,4),substr(a.accmm,5,2) ) as sales_num_avg,
    a.BSAREA,
    sum(a.BSAREA) over (partition by a.ORGNO,substr(a.accmm,1,4) order by a.ORGNO,substr(a.accmm,1,4),substr(a.accmm,5,2) ) as areas_avg
from (select ORGNO,accmm,sum(EMPLFIQT) as EMPLFIQT,sum(BSAREA) as BSAREA from ${youngor_ods}.s_SMA_ORG_ACCMM where ds<=substr('${bizdate}',1,6) group by ORGNO,accmm ) a 
) a5 on a1.ORGNO=a5.ORGNO and a1.accmm=a5.accmm
left join
(select ORGNO,accmm,VAT from ${youngor_cdm}.fct_fi_settle_di where ds<=substr('${bizdate}',1,6) and orgtyno='SHOP' and CHANNO in ('ZY','GW') and substr(accmm,1,4)>=2018 group by ORGNO,accmm,VAT
) a6 on a1.ORGNO=a6.ORGNO and a1.accmm=a6.accmm;
-- left join
-- (select code,quanc from ${youngor_ods}.ods_organization_dimension where ds='${bizdate}' and organization_leix='客商') a7
-- on a1.ORGNO=a7.code
数据年轮
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值