E:\Anaconda3-2024.02.1\python.exe E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:55: DeprecationWarning: "uni" parameter is deprecated since v2.5.1, unused and will soon be removed
self.pdf.add_font('SimSun', '', found_font, uni=True)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:61: DeprecationWarning: "uni" parameter is deprecated since v2.5.1, unused and will soon be removed
self.pdf.add_font('SimSun', 'B', found_font, uni=True)
执行的 SQL1:
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
nvl(a1.jshj,0) 本周总销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.zhml,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周综合毛利率,nvl(a1.kds,0) 本周总客单,case when nvl(a1.kds,0)=0 then 0 else round(nvl(a1.jshj,0)/nvl(a1.kds,0),2) end 本周客单价,
nvl(a1.dsxse,0) 本周线上销售,nvl(a1.dskds,0) 本周线上客单,nvl(a1.jshj,0)-nvl(a1.dsxse,0) 本周线下销售,nvl(a1.kds,0)-nvl(a1.dskds,0) 本周线下客单,
nvl(a1.dtp,0) 本周DTP销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.dtp,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周DTP销售占比,
nvl(b1.jshj,0) 本周排除DTP销售,case when nvl(b1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2) else 0 end || '%' 本周排除DTP综合毛利率,nvl(b1.kds,0) 本周排除DTP客单,case when nvl(b1.kds,0)=0 then 0 else round(nvl(b1.jshj,0)/nvl(b1.kds,0),2) end 本周排除DTP客单价,
nvl(a1.cyxse,0) 本周中药销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.cyxse,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周中药销售占比,
nvl(a1.nf,0) 本周内方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.nf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周内方销售占比,
nvl(a1.gf,0) 本周膏方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.gf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周膏方销售占比,
nvl(c1.jshj,0) 本周会员销售,case when nvl(a1.jshj,0)<>0 then round(nvl(c1.jshj,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周会员销售占比,
nvl(c1.kds,0) 本周会员客单,nvl(c1.hys,0) 本周会员数,case when nvl(c1.kds,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.kds,0),2) end 本周会员客单价,
case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.hys,0),2) end 本周会员人均消费,case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.kds,0)/nvl(c1.hys,0),2) end 本周会员消费频次,
nvl(d1.jshj,0) 本周医保会员销售,case when nvl(c1.jshj,0)<>0 then round(nvl(d1.jshj,0)*100/nvl(c1.jshj,0),2) else 0 end || '%' 本周医保会员销售占比,
nvl(d1.kds,0) 本周医保会员客单,nvl(d1.hys,0) 本周医保会员数,case when nvl(d1.kds,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.kds,0),2) end 本周医保会员客单价,
case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.hys,0),2) end 本周医保会员人均消费,case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.kds,0)/nvl(d1.hys,0),2) end 本周医保会员消费频次,
nvl(a2.jshj,0) 上周总销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.zhml,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周综合毛利率,nvl(a2.kds,0) 上周总客单,case when nvl(a2.kds,0)=0 then 0 else round(nvl(a2.jshj,0)/nvl(a2.kds,0),2) end 上周客单价,
nvl(a2.dsxse,0) 上周线上销售,nvl(a2.dskds,0) 上周线上客单,nvl(a2.jshj,0)-nvl(a2.dsxse,0) 上周线下销售,nvl(a2.kds,0)-nvl(a2.dskds,0) 上周线下客单,
nvl(a2.dtp,0) 上周DTP销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.dtp,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周DTP销售占比,
nvl(b2.jshj,0) 上周排除DTP销售,case when nvl(b2.jshj,0)<>0 then round(nvl(b2.ml,0)*100/nvl(b2.jshj,0),2) else 0 end || '%' 上周排除DTP综合毛利率,nvl(b2.kds,0) 上周排除DTP客单,case when nvl(b2.kds,0)=0 then 0 else round(nvl(b2.jshj,0)/nvl(b2.kds,0),2) end 上周排除DTP客单价,
nvl(a2.cyxse,0) 上周中药销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.cyxse,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周中药销售占比,
nvl(a2.nf,0) 上周内方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.nf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周内方销售占比,
nvl(a2.gf,0) 上周膏方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.gf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周膏方销售占比,
nvl(c2.jshj,0) 上周会员销售,case when nvl(a2.jshj,0)<>0 then round(nvl(c2.jshj,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周会员销售占比,
nvl(c2.kds,0) 上周会员客单,nvl(c2.hys,0) 上周会员数,case when nvl(c2.kds,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.kds,0),2) end 上周会员客单价,
case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.hys,0),2) end 上周会员人均消费,case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.kds,0)/nvl(c2.hys,0),2) end 上周会员消费频次,
nvl(d2.jshj,0) 上周医保会员销售,case when nvl(c2.jshj,0)<>0 then round(nvl(d2.jshj,0)*100/nvl(c2.jshj,0),2) else 0 end || '%' 上周医保会员销售占比,
nvl(d2.kds,0) 上周医保会员客单,nvl(d2.hys,0) 上周医保会员数,case when nvl(d2.kds,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.kds,0),2) end 上周医保会员客单价,
case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.hys,0),2) end 上周医保会员人均消费,case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.kds,0)/nvl(d2.hys,0),2) end 上周医保会员消费频次
from (select * from gl_custom where tjbh='03711033')e
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' group by a.subbh)a1 on a1.subbh= e.tjbh
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1) - 7 and a.subbh ='03711033' group by a.subbh)a2 on a2.subbh= e.tjbh
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b1 on b1.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1)- 7 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b2 on b2.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c1 on c1.subbh=e.tjbh ---会员
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1) - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c2 on c2.subbh=e.tjbh ---会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d1 on d1.subbh=e.tjbh ---医保会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE- 1)- 7 and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d2 on d2.subbh=e.tjbh ---医保会员
执行的 SQL3:
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
fk.fkfsname 付款方式,nvl(a1.je,0) 本周付款金额,nvl(a2.je,0) 上周付款金额
from (select * from gl_custom where tjbh='03711033')e
cross join (select distinct a.fkfsname from subdfkfs a where a.fkfsname like '%医保%'and a.subbh ='03711033' and ((a.kdrq BETWEEN TRUNC(SYSDATE-1, 'IW') AND TRUNC(SYSDATE-1)) or (a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7)) ) fk
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE-1, 'IW') AND TRUNC(SYSDATE-1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a1 on a1.subbh= e.tjbh and a1.fkfsname = fk.fkfsname
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE-1, 'IW') - 7 AND TRUNC(SYSDATE-1) - 7 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a2 on a2.subbh= e.tjbh and a2.fkfsname = fk.fkfsname
执行的 SQL5:
select a.c_mdfq 大区,a.c_dq 地市,a.c_mdfq1 地区,a.tjbh 编码,a.mc 门店,a.name 类别,
nvl(b.jshj,0) 本周销售,nvl(b.jshj,0)-nvl(c.jshj,0) 周销售环比,nvl(b.kds,0) 本周客流,nvl(b.kds,0)-nvl(c.kds,0) 周客流环比,
nvl(d1.jshj,0)-nvl(d2.jshj,0) 医保会员环比,nvl(d3.jshj,0)-nvl(d4.jshj,0) 非医保会员环比,nvl(d5.jshj,0)-nvl(d6.jshj,0) 非会员环比
from (select a.c_mdfq,a.c_dq,a.c_mdfq1,a.tjbh,a.mc,b.name from gl_custom a cross join (select distinct name from dataitem where dataitemtypeguid='313') b where a.tjbh='03711033') a
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,c.name) b on a.tjbh=b.subbh and a.name=b.name
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,c.name) c on a.tjbh=c.subbh and a.name=c.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d1 on a.tjbh=d1.subbh and a.name=d1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d2 on a.tjbh=d2.subbh and a.name=d2.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d3 on a.tjbh=d3.subbh and a.name=d3.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d4 on a.tjbh=d4.subbh and a.name=d4.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d5 on a.tjbh=d5.subbh and a.name=d5.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE- 1, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))d6 on a.tjbh=d6.subbh and a.name=d6.name
order by 周销售环比
执行的 SQL6:
select sum(a.jshj) total_sales1 from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE-1, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033'
and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
执行的 SQL9:
with subfhdls as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE-1, 'IW') AND TRUNC(SYSDATE-1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a1 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh),
subfhdls1 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE - 1, 'IW') - 7 AND TRUNC(SYSDATE - 1) - 7 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a2 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls1 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh)
select a.subbh 编码,c.mc 门店,a.dpl*100 "本周一单一品率",a.kps "本周客品数",a.kpc "本周客品次",b.dpl*100 "上周一单一品率",b.kps "上周客品数",b.kpc "上周客品次"
from a1 a left join a2 b on a.subbh=b.subbh
left join gl_custom c on a.subbh=c.tjbh
执行的 SQL2:
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
round(nvl(a3.jshj,0)/10000 ,2) "今年总销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(a3.zhml,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年综合毛利率,nvl(a3.kds,0) 今年总客单,case when nvl(a3.kds,0)=0 then 0 else round(nvl(a3.jshj,0)/nvl(a3.kds,0),2) end 今年客单价,
round(nvl(a3.dsxse,0)/10000 ,2) "今年线上销售(万)",nvl(a3.dskds,0) 今年线上客单,round((nvl(a3.jshj,0)-nvl(a3.dsxse,0))/10000 ,2) "今年线下销售(万)",nvl(a3.kds,0)-nvl(a3.dskds,0) 今年线下客单,
round(nvl(a3.dtp,0)/10000 ,2) "今年DTP销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(a3.dtp,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年DTP销售占比,
round(nvl(b3.jshj,0)/10000 ,2) "今年排除DTP销售(万)",case when nvl(b3.jshj,0)<>0 then round(nvl(b3.ml,0)*100/nvl(b3.jshj,0),2) else 0 end || '%' 今年排除DTP综合毛利率,nvl(b3.kds,0) 今年排除DTP客单,case when nvl(b3.kds,0)=0 then 0 else round(nvl(b3.jshj,0)/nvl(b3.kds,0),2) end 今年排除DTP客单价,
round(nvl(a3.cyxse,0)/10000 ,2) "今年中药销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(a3.cyxse,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年中药销售占比,
round(nvl(a3.nf,0)/10000 ,2) "今年内方销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(a3.nf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年内方销售占比,
round(nvl(a3.gf,0)/10000 ,2) "今年膏方销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(a3.gf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年膏方销售占比,
round(nvl(c3.jshj,0)/10000 ,2) "今年会员销售(万)",case when nvl(a3.jshj,0)<>0 then round(nvl(c3.jshj,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年会员销售占比,
nvl(c3.kds,0) 今年会员客单,nvl(c3.hys,0) 今年会员数,case when nvl(c3.kds,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.kds,0),2) end 今年会员客单价,
case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.hys,0),2) end 今年会员人均消费,case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.kds,0)/nvl(c3.hys,0),2) end 今年会员消费频次,
round(nvl(d3.jshj,0)/10000 ,2) "今年医保会员销售(万)",case when nvl(c3.jshj,0)<>0 then round(nvl(d3.jshj,0)*100/nvl(c3.jshj,0),2) else 0 end || '%' 今年医保会员销售占比,
nvl(d3.kds,0) 今年医保会员客单,nvl(d3.hys,0) 今年医保会员数,case when nvl(d3.kds,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.kds,0),2) end 今年医保会员客单价,
case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.hys,0),2) end 今年医保会员人均消费,case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.kds,0)/nvl(d3.hys,0),2) end 今年医保会员消费频次,
round(nvl(a4.jshj,0)/10000 ,2) "去年总销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(a4.zhml,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年综合毛利率,nvl(a4.kds,0) 去年总客单,case when nvl(a4.kds,0)=0 then 0 else round(nvl(a4.jshj,0)/nvl(a4.kds,0),2) end 去年客单价,
round(nvl(a4.dsxse,0)/10000 ,2) "去年线上销售(万)",nvl(a4.dskds,0) 去年线上客单,round((nvl(a4.jshj,0)-nvl(a4.dsxse,0))/10000 ,2) "去年线下销售(万)",nvl(a4.kds,0)-nvl(a4.dskds,0) 去年线下客单,
round(nvl(a4.dtp,0)/10000 ,2) "去年DTP销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(a4.dtp,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年DTP销售占比,
round(nvl(b4.jshj,0)/10000 ,2) "去年排除DTP销售(万)",case when nvl(b4.jshj,0)<>0 then round(nvl(b4.ml,0)*100/nvl(b4.jshj,0),2) else 0 end || '%' 去年排除DTP综合毛利率,nvl(b4.kds,0) 去年排除DTP客单,case when nvl(b4.kds,0)=0 then 0 else round(nvl(b4.jshj,0)/nvl(b4.kds,0),2) end 去年排除DTP客单价,
round(nvl(a4.cyxse,0)/10000 ,2) "去年中药销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(a4.cyxse,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年中药销售占比,
round(nvl(a4.nf,0)/10000 ,2) "去年内方销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(a4.nf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年内方销售占比,
round(nvl(a4.gf,0)/10000 ,2) "去年膏方销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(a4.gf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年膏方销售占比,
round(nvl(c4.jshj,0)/10000 ,2) "去年会员销售(万)",case when nvl(a4.jshj,0)<>0 then round(nvl(c4.jshj,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年会员销售占比,
nvl(c4.kds,0) 去年会员客单,nvl(c4.hys,0) 去年会员数,case when nvl(c4.kds,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.kds,0),2) end 去年会员客单价,
case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.hys,0),2) end 去年会员人均消费,case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.kds,0)/nvl(c4.hys,0),2) end 去年会员消费频次,
round(nvl(d4.jshj,0)/10000 ,2) "去年医保会员销售(万)",case when nvl(c4.jshj,0)<>0 then round(nvl(d4.jshj,0)*100/nvl(c4.jshj,0),2) else 0 end || '%' 去年医保会员销售占比,
nvl(d4.kds,0) 去年医保会员客单,nvl(d4.hys,0) 去年医保会员数,case when nvl(d4.kds,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.kds,0),2) end 去年医保会员客单价,
case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.hys,0),2) end 去年医保会员人均消费,case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.kds,0)/nvl(d4.hys,0),2) end 去年医保会员消费频次
from (select * from gl_custom where tjbh='03711033')e
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' group by a.subbh)a3 on a3.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml
from c_zkmdxshzb a where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' group by a.subbh)a4 on a4.subbh= e.tjbh----整体
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b3 on b3.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a
left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh
left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)b4 on b4.subbh=e.tjbh ---非dtp
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c3 on c3.subbh=e.tjbh ---会员
left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)c4 on c4.subbh=e.tjbh ---会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d3 on d3.subbh=e.tjbh ---医保会员
left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a
inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')b on b.lsh=a.lsh
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh)d4 on d4.subbh=e.tjbh ---医保会员
执行的 SQL4:
select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店,
fk.fkfsname 付款方式,round(nvl(a3.je,0)/10000,2) "今年付款金额(万)",round(nvl(a4.je,0)/10000,2) "去年付款金额(万)"
from (select * from gl_custom where tjbh='03711033')e
cross join (select distinct a.fkfsname from subdfkfs a where a.fkfsname like '%医保%'and a.subbh ='03711033' and ((a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 ) or (a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1))) ) fk
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a3 on a3.subbh= e.tjbh and a3.fkfsname = fk.fkfsname
left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a4 on a4.subbh= e.tjbh and a4.fkfsname = fk.fkfsname
执行的 SQL7:
select a.c_mdfq 大区,a.c_dq 地市,a.c_mdfq1 地区,a.tjbh 编码,a.mc 门店,a.name 类别,
nvl(b1.jshj,0) 今年销售,nvl(b1.jshj,0)-nvl(c1.jshj,0) 年销售同比,nvl(b1.kds,0) 今年客流,nvl(b1.kds,0)-nvl(c1.kds,0) 年客流同比,
nvl(e1.jshj,0)-nvl(e2.jshj,0) 医保会员同比,nvl(e3.jshj,0)-nvl(e4.jshj,0) 非医保会员同比,nvl(e5.jshj,0)-nvl(e6.jshj,0) 非会员同比
from (select a.c_mdfq,a.c_dq,a.c_mdfq1,a.tjbh,a.mc,b.name from gl_custom a cross join (select distinct name from dataitem where dataitemtypeguid='313') b where a.tjbh='03711033') a
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,c.name) b1 on a.tjbh=b1.subbh and a.name=b1.name
left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml
from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh
left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,c.name) c1 on a.tjbh=c1.subbh and a.name=c1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e1 on a.tjbh=e1.subbh and a.name=e1.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e2 on a.tjbh=e2.subbh and a.name=e2.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e3 on a.tjbh=e3.subbh and a.name=e3.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname not like '%医保%')d on d.lsh=a.lsh
left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e4 on a.tjbh=e4.subbh and a.name=e4.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e5 on a.tjbh=e5.subbh and a.name=e5.name
left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj
from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
group by a.subbh,nvl(e.name,'未分类'))e6 on a.tjbh=e6.subbh and a.name=e6.name
order by 年销售同比
执行的 SQL8:
select nvl(sum(a.jshj),0) total_sales2 from subfhd a
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE-1) and a.subbh ='03711033'
and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换'
执行的 SQL10:
with subfhdls2 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a3 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls2 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls2 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls2 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls2 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh),
subfhdls3 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh
left join gl_custom d on a.subbh=d.tjbh
where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000'
and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033'
and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ),
a4 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc
from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls3 a group by a.subbh) a
left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from
subfhdls3 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh
left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls3 group by subbh) c on c.subbh=a.subbh
left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls3 a group by a.subbh,a.lsh)
group by subbh) d on d.subbh=a.subbh)
select a.subbh 编码,e.mc 门店,a.dpl*100 "今年一单一品率",a.kps "今年客品数",a.kpc "今年客品次",b.dpl*100 "去年一单一品率",b.kps "去年客品数",b.kpc "去年客品次"
from a3 a left join a4 b on a.subbh=b.subbh
left join gl_custom e on a.subbh=e.tjbh
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:904: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, f"{self.store_info.get('name', '')}销售数据分析报告", 0, 1, 'C')
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:906: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, f"报告日期: {self.current_date}", 0, 1, 'C')
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:911: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10,
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:918: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "1. 整体周销售数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:808: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=0 use new_x=XPos.RIGHT, new_y=YPos.TOP.
self.pdf.cell(col_widths[i], 7, header, 1, 0, 'C')
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:815: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=0 use new_x=XPos.RIGHT, new_y=YPos.TOP.
self.pdf.cell(col_widths[i], 6, str(cell), 1, 0, align[i])
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:936: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "2. 医保付款周数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:954: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "3. 品类周数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:973: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "4. 销售能力环比", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:991: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "5. 整体销售数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:1004: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "未找到相关数据", 0, 1, 'C')
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:1009: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "6. 医保付款数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:1027: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "7. 品类数据", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:1046: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "8. 销售能力同比", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:823: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 10, "数据分析与建议", 0, 1)
E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据2.py:853: DeprecationWarning: The parameter "ln" is deprecated since v2.5.2. Instead of ln=1 use new_x=XPos.LMARGIN, new_y=YPos.NEXT.
self.pdf.cell(0, 6, line, 0, 1)
MERG NOT subset; don't know how to subset; dropped
MERG NOT subset; don't know how to subset; dropped
报告已生成: 郑州康复店G_销售分析报告_2025-08-12.pdf
成功生成门店 03711033 的报告
Process finished with exit code 0
最新发布
本文介绍了 SQL 中 NVL 函数的基本用法，包括如何在查询中使用 NVL 函数来处理 NULL 值，并将其替换为指定的默认值。此外，还提供了一个示例，演示了如何将 NVL 函数的查询结果存储到变量中。
扫一扫
4740
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
