sum(nvl(a,b))与nvl(sum(a),b)区别

本文探讨了在SQL中使用SUM和NVL函数的不同场景及结果差异。详细分析了这些函数在处理空值、无数据情况下的表现,并解释了它们在不同位置使用时产生的不同结果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在正常情况下,所有行的数据不全为空时:

select sum(nvl(1,1)) from dual ;
select nvl(sum(1),1) from dual ;

 结果是一样的。

 

在所有行数据都为空时:

select sum(nvl(null,1)) from dual ;
select nvl(sum(null),1) from dual ;

 结果也还是一样。

 

当查不到数据时:

select sum(nvl(1,1)) from dual where 1<>1;
select nvl(sum(1),1) from dual where 1<>1;
select sum(nvl(null,1)) from dual where 1<>1;
select nvl(sum(null),1) from dual where 1<>1;

 可以发现sum在里面和外面结果是不一样的。sum在外面,结果为空,sum在里面结果为1

这是因为sum和nvl的异常处理不一样导致的。

当处理数据时,如果碰到没有数据(注意没有数据不是null)时,sum返回"空",而nvl返回"没有数据"。

所以,当sum在外面时,nvl先返回"没有数据",sum再对"没有数据"做处理,返回结果为"空"。

当sum在里面时,sum先对"没有数据"做处理,返回结果为"空",nvl再对nvl处理,返回结果为1。

 

WITH C AS ( SELECT * FROM ( SELECT PRODUCT_ID, PLATFORM, SUB_PLATFORM, PROD_CATEGORY, MATERIAL_NO, MATERIAL_UNIT, MATERIAL_DESC, MATERIAL_PRICE, ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID, MATERIAL_NO ORDER BY MATERIAL_TYPE DESC) AS RN FROM VW_MC_MERGE_MATERIAL WHERE MERGE_MC_ID =1937059976879095809 AND MATERIAL_TYPE IN (&#39;Z006&#39;, &#39;Z007&#39;)) C1 WHERE C1.rn =1 ), A as( SELECT * FROM VW_RD_MERGE_WIP A1 WHERE A1.MERGE_MC_ID = 1937059976879095809 ), B as( SELECT * FROM VW_TINY_RE_LOT_AMOUNT_DATA B1 WHERE B1.MERGE_MC_ID = 1937059976879095809 ) SELECT NVL(A.PRODUCT_ID, &#39;NA&#39;) AS PRODUCT_ID, NVL(C.PROD_CATEGORY, &#39;NA&#39;) AS PROD_CATEGORY, NVL(C.PLATFORM, &#39;NA&#39;) AS PLATFORM, NVL(C.SUB_PLATFORM, &#39;NA&#39;) AS SUB_PLATFORM, NVL(LOT.COMPANY, &#39;NA&#39;) AS VAL_A, A.LOT_ID, COALESCE(OEM.LOT_FAB,LOT.FAB, &#39;NA&#39;) AS LOT_FAB, A.LOT_STATUS, NVL(A.LOT_TYPE, &#39;NA&#39;) AS LOT_TYPE, NVL(C.MATERIAL_NO, &#39;NA&#39;) AS FINISHED_MATERIAL_NO, &#39;13020000&#39; AS GENERAL_LEDGER_ACCOUNT, &#39;在产品&#39; AS GENERAL_LEDGER_ACCOUNT_DESC, &#39;CNY&#39; AS CURRENCY, NVL(A.COMPONENT_QTY, 0) AS TOTAL_STOCK, NVL(A.COMPONENT_QTY, 0) AS ORDER_NUMBER, NVL(C.MATERIAL_DESC, &#39;NA&#39;) AS CHI_DESC, NVL(C.MATERIAL_UNIT, &#39;NA&#39;) AS UNIT, NVL((CASE A.LOT_STATUS WHEN &#39;SHIPPED&#39; THEN A.COMPONENT_QTY ELSE 0 END), 0) AS FINISHED_NUMBER, NVL((CASE WHEN A.LOT_STATUS IN (&#39;SCRAPPED&#39;, &#39;TERMINATED&#39;) THEN A.COMPONENT_QTY ELSE 0 END), 0) AS BAD_NUMBER, NVL((CASE A.LOT_STATUS WHEN &#39;BONDED&#39; THEN A.COMPONENT_QTY ELSE 0 END), 0) AS BONDED_NUMBER, NVL((CASE WHEN A.LOT_STATUS NOT IN(&#39;SHIPPED&#39;, &#39;SCRAPPED&#39;, &#39;TERMINATED&#39;, &#39;BONDED&#39;) THEN A.COMPONENT_QTY ELSE 0 END), 0) AS PROCESS_NUMBER, NVL(B.AMT_S43ACS01, 0) AS AMT_S43ACS01, NVL(B.AMT_S43ACS02, 0) AS AMT_S43ACS02, NVL(B.AMT_S43ACS03, 0) AS AMT_S43ACS03, NVL(B.AMT_S43ACS04, 0) AS AMT_S43ACS04, NVL(B.AMT_S43ACS05, 0) AS AMT_S43ACS05, NVL(B.AMT_S43ACS06, 0) AS AMT_S43ACS06, NVL(B.AMT_S43ACS07, 0) AS AMT_S43ACS07, NVL(B.AMT_S43ACS08, 0) AS AMT_S43ACS08, NVL(B.AMT_S43ACS09, 0) AS AMT_S43ACS09, NVL(B.AMT_S43ACS10, 0) AS AMT_S43ACS10, NVL(B.AMT_S43ACS11, 0) AS AMT_S43ACS11, NVL(B.AMT_S43ACS12, 0) AS AMT_S43ACS12, NVL(B.AMT_S43ACS13, 0) AS AMT_S43ACS13, NVL(B.AMT_S43ACS14, 0) AS AMT_S43ACS14, NVL(B.AMT_S43ACS15, 0) AS AMT_S43ACS15, NVL(B.AMT_S43ACS16, 0) AS AMT_S43ACS16, NVL(B.AMT_S43ACS17, 0) AS AMT_S43ACS17, NVL(B.AMT_S43ACS18, 0) AS AMT_S43ACS18, NVL(B.AMT_S43ACS19, 0) AS AMT_S43ACS19, NVL(B.AMT_S43ACS20, 0) AS AMT_S43ACS20, NVL(B.AMT_S43ACS21, 0) AS AMT_S43ACS21, NVL(B.AMT_S43ACS22, 0) AS AMT_S43ACS22, NVL(B.AMT_S43ACS23, 0) AS AMT_S43ACS23, NVL(B.AMT_S43ACS24, 0) AS AMT_S43ACS24, NVL(B.AMT_S43ACS25, 0) AS AMT_S43ACS25, NVL(B.AMT_S43ACS26, 0) AS AMT_S43ACS26, NVL(B.AMT_S43ACS27, 0) AS AMT_S43ACS27, NVL(B.AMT_S43ACS28, 0) AS AMT_S43ACS28, NVL(B.AMT_S43ACS29, 0) AS AMT_S43ACS29, NVL(B.AMT_S43ACS30, 0) AS AMT_S43ACS30, NVL(B.AMT_S43ACS31, 0) AS AMT_S43ACS31, NVL(B.SUM_S43ACS01, 0) AS SUM_S43ACS01, NVL(B.SUM_S43ACS02, 0) AS SUM_S43ACS02, NVL(B.SUM_S43ACS03, 0) AS SUM_S43ACS03, NVL(B.SUM_S43ACS04, 0) AS SUM_S43ACS04, NVL(B.SUM_S43ACS05, 0) AS SUM_S43ACS05, NVL(B.SUM_S43ACS06, 0) AS SUM_S43ACS06, NVL(B.SUM_S43ACS07, 0) AS SUM_S43ACS07, NVL(B.SUM_S43ACS08, 0) AS SUM_S43ACS08, NVL(B.SUM_S43ACS09, 0) AS SUM_S43ACS09, NVL(B.SUM_S43ACS10, 0) AS SUM_S43ACS10, NVL(B.SUM_S43ACS11, 0) AS SUM_S43ACS11, NVL(B.SUM_S43ACS12, 0) AS SUM_S43ACS12, (NVL(B.SUM_S43ACS01, 0) + NVL(B.SUM_S43ACS02, 0) + NVL(B.SUM_S43ACS03, 0) + NVL(B.SUM_S43ACS04, 0) + NVL(B.SUM_S43ACS05, 0) + NVL(B.SUM_S43ACS06, 0) + NVL(B.SUM_S43ACS07, 0) + NVL(B.SUM_S43ACS08, 0) + NVL(B.SUM_S43ACS09, 0) + NVL(B.SUM_S43ACS10, 0) + NVL(B.SUM_S43ACS11, 0) + NVL(B.SUM_S43ACS12, 0)) AS ACTUAL_COST, (NVL(C.MATERIAL_PRICE, 0) * NVL(A.COMPONENT_QTY, 0)) AS STANDARD_COST FROM A LEFT JOIN B ON A.MERGE_MC_ID=B.MERGE_MC_ID AND A.LOT_ID=B.LOT_ID LEFT JOIN C ON C.PRODUCT_ID = A.PRODUCT_ID AND C.RN = 1 LEFT JOIN VW_CFG_FAB_LOT LOT ON LOT.LOT_ID = (CASE WHEN INSTR(A.LOT_ID, &#39;.&#39;) > 0 THEN SUBSTR(A.LOT_ID, 0, INSTR(A.LOT_ID, &#39;.&#39;) - 1) ELSE A.LOT_ID END) LEFT JOIN (SELECT DISTINCT MASTER_OEM_LOT, MASTER_ORIGINAL_LOT, ORIGINAL_FAB AS LOT_FAB FROM VW_LOT_TRANSFER) OEM ON OEM.MASTER_OEM_LOT = (CASE WHEN INSTR(A.LOT_ID, &#39;.&#39;) > 0 THEN SUBSTR(A.LOT_ID, 0, INSTR(A.LOT_ID, &#39;.&#39;) - 1) ELSE A.LOT_ID END) 优化这个Oracle查询
07-05
import cx_Oracle import pandas as pd from reportlab.lib import colors from reportlab.lib.pagesizes import landscape, letter from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, PageBreak, Spacer from reportlab.lib.units import inch import matplotlib.pyplot as plt from io import BytesIO import numpy as np from sqlalchemy import create_engine from sqlalchemy.exc import SQLAlchemyError def fetch_store_data(store_id): """从数据库获取门店数据""" engine = create_engine(&#39;oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata&#39;) # 执行四个SQL查询 QUERIES = [ # 整体销售数据 """ ------整体销售数据 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 || &#39;%&#39; 本周综合毛利率,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 || &#39;%&#39; 本周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 || &#39;%&#39; 本周排除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 || &#39;%&#39; 本周中药销售占比, 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 || &#39;%&#39; 本周内方销售占比, 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 || &#39;%&#39; 本周膏方销售占比, 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 || &#39;%&#39; 本周会员销售占比, 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 || &#39;%&#39; 本周医保会员销售占比, 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 || &#39;%&#39; 上周综合毛利率,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 || &#39;%&#39; 上周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 || &#39;%&#39; 上周排除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 || &#39;%&#39; 上周中药销售占比, 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 || &#39;%&#39; 上周内方销售占比, 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 || &#39;%&#39; 上周膏方销售占比, 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 || &#39;%&#39; 上周会员销售占比, 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 || &#39;%&#39; 上周医保会员销售占比, 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 上周医保会员消费频次, 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 || &#39;%&#39; 今年综合毛利率,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 || &#39;%&#39; 今年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 || &#39;%&#39; 今年排除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 || &#39;%&#39; 今年中药销售占比, 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 || &#39;%&#39; 今年内方销售占比, 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 || &#39;%&#39; 今年膏方销售占比, 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 || &#39;%&#39; 今年会员销售占比, 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 || &#39;%&#39; 今年医保会员销售占比, 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 || &#39;%&#39; 去年综合毛利率,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 || &#39;%&#39; 去年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 || &#39;%&#39; 去年排除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 || &#39;%&#39; 去年中药销售占比, 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 || &#39;%&#39; 去年内方销售占比, 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 || &#39;%&#39; 去年膏方销售占比, 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 || &#39;%&#39; 去年会员销售占比, 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 || &#39;%&#39; 去年医保会员销售占比, 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=&#39;03711033&#39;)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, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; 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, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; group by a.subbh)a2 on a2.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, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.subbh =&#39;03711033&#39; 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, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh =&#39;03711033&#39; 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=&#39;1&#39; ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(e.sx,&#39; &#39;) <> &#39;D&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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=&#39;1&#39; ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(e.sx,&#39; &#39;) <> &#39;D&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh)b2 on b2.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=&#39;1&#39; ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.subbh =&#39;03711033&#39; and nvl(e.sx,&#39; &#39;) <> &#39;D&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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=&#39;1&#39; ) e on e.hh=d.hh where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh =&#39;03711033&#39; and nvl(e.sx,&#39; &#39;) <> &#39;D&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39;and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39;and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh)c2 on c2.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, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39;and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39;and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and fkfsname like &#39;%医保%&#39;)b on b.lsh=a.lsh where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like &#39;%医保%&#39;)b on b.lsh=a.lsh where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh)d2 on d2.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, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and fkfsname like &#39;%医保%&#39;)b on b.lsh=a.lsh where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; 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, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like &#39;%医保%&#39;)b on b.lsh=a.lsh where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh)d4 on d4.subbh=e.tjbh ---医保会员 """, # 医保付款数据 """ -----二、医保付款数据 select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店, fk.fkfsname 付款方式,nvl(a1.je,0) 本周付款金额,nvl(a2.je,0) 上周付款金额,nvl(a3.je,0) 今年付款金额,nvl(a4.je,0) 去年付款金额 from (select * from gl_custom where tjbh=&#39;03711033&#39;)e cross join (select distinct a.fkfsname from subdfkfs a where a.fkfsname like &#39;%医保%&#39;and a.subbh =&#39;03711033&#39; and ((a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 ) or (a.kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) 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, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.fkfsname like &#39;%医保%&#39; and a.subbh =&#39;03711033&#39; 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, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.fkfsname like &#39;%医保%&#39; and a.subbh =&#39;03711033&#39; group by a.subbh,a.fkfsname)a2 on a2.subbh= e.tjbh and a2.fkfsname = fk.fkfsname left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.fkfsname like &#39;%医保%&#39; and a.subbh =&#39;03711033&#39; 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, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.fkfsname like &#39;%医保%&#39; and a.subbh =&#39;03711033&#39; group by a.subbh,a.fkfsname)a4 on a4.subbh= e.tjbh and a4.fkfsname = fk.fkfsname """, # 品类数据 """ -------品类数据 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) 周客流环比, case when nvl(b.jshj,0)<>0 and nvl(c.jshj,0)<>0 then round(nvl(b.ml,0)*100/nvl(b.jshj,0),2)- round(nvl(c.ml,0)*100/nvl(c.jshj,0),2) else 0 end || &#39;%&#39; 周毛利率环比, nvl(d1.jshj,0)-nvl(d2.jshj,0) 医保会员销售周销售环比,nvl(d3.jshj,0)-nvl(d4.jshj,0) 非医保会员周销售环比,nvl(d5.jshj,0)-nvl(d6.jshj,0) 非会员周销售环比, nvl(b1.jshj,0) 今年销售,nvl(b1.jshj,0)-nvl(c1.jshj,0) 年销售同比,nvl(b1.kds,0) 今年客流,nvl(b1.kds,0)-nvl(c1.kds,0) 年客流同比, case when nvl(b1.jshj,0)<>0 and nvl(c1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2)- round(nvl(c1.ml,0)*100/nvl(c1.jshj,0),2) else 0 end || &#39;%&#39; 年毛利率同比, 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=&#39;313&#39;) b where a.tjbh=&#39;03711033&#39;) a left join (select a.subbh,nvl(c.name,&#39;未分类&#39;) 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=&#39;313&#39;) c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(c.name,&#39;未分类&#39;)) b on a.tjbh=b.subbh and a.name=b.name left join (select a.subbh,nvl(c.name,&#39;未分类&#39;) 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=&#39;313&#39;) c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(c.name,&#39;未分类&#39;)) c on a.tjbh=c.subbh and a.name=c.name left join (select a.subbh,nvl(c.name,&#39;未分类&#39;) 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=&#39;313&#39;) c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.subbh =&#39;03711033&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(c.name,&#39;未分类&#39;)) b1 on a.tjbh=b1.subbh and a.name=b1.name left join (select a.subbh,nvl(c.name,&#39;未分类&#39;) 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=&#39;313&#39;) c on b.fzflsx2=c.code where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh =&#39;03711033&#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(c.name,&#39;未分类&#39;)) c1 on a.tjbh=c1.subbh and a.name=c1.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and fkfsname like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d1 on a.tjbh=d1.subbh and a.name=d1.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d2 on a.tjbh=d2.subbh and a.name=d2.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and fkfsname not like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d3 on a.tjbh=d3.subbh and a.name=d3.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname not like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d4 on a.tjbh=d4.subbh and a.name=d4.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) 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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)=&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d5 on a.tjbh=d5.subbh and a.name=d5.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) 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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)=&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))d6 on a.tjbh=d6.subbh and a.name=d6.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and fkfsname like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e1 on a.tjbh=e1.subbh and a.name=e1.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e2 on a.tjbh=e2.subbh and a.name=e2.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and fkfsname not like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e3 on a.tjbh=e3.subbh and a.name=e3.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname not like &#39;%医保%&#39;)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=&#39;313&#39;) e on b.fzflsx2=e.code where a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)<>&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e4 on a.tjbh=e4.subbh and a.name=e4.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) 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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)=&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e5 on a.tjbh=e5.subbh and a.name=e5.name left join (select a.subbh,nvl(e.name,&#39;未分类&#39;) 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=&#39;313&#39;) e on b.fzflsx2=e.code where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh =&#39;03711033&#39; and nvl(a.yhkh,&#39; &#39;)=&#39; &#39; and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; group by a.subbh,nvl(e.name,&#39;未分类&#39;))e6 on a.tjbh=e6.subbh and a.name=e6.name order by 周销售环比 """, # 销售能力 """ ------销售能力 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, &#39;IW&#39;) AND TRUNC(SYSDATE-1) and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; and nvl(a.posguid,&#39; &#39;)<>&#39;10000&#39; and b.fzflsx1<>&#39;14&#39;and b.fzflsx2<>&#39;08031&#39; and a.subbh =&#39;03711033&#39; and b.fzflsx2<>&#39;08032&#39; and b.fzflsx2<>&#39;08034&#39; and b.fzflsx2<>&#39;08035&#39; and b.fzflsx2<>&#39;08038&#39; and b.fzflsx2<>&#39;08039&#39; and b.fzflsx2<>&#39;08040&#39; and b.fzflsx2<>&#39;08041&#39; ), 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, &#39;IW&#39;) - 7 AND TRUNC(SYSDATE) - 1 - 7 and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; and nvl(a.posguid,&#39; &#39;)<>&#39;10000&#39; and b.fzflsx1<>&#39;14&#39;and b.fzflsx2<>&#39;08031&#39; and a.subbh =&#39;03711033&#39; and b.fzflsx2<>&#39;08032&#39; and b.fzflsx2<>&#39;08034&#39; and b.fzflsx2<>&#39;08035&#39; and b.fzflsx2<>&#39;08038&#39; and b.fzflsx2<>&#39;08039&#39; and b.fzflsx2<>&#39;08040&#39; and b.fzflsx2<>&#39;08041&#39; ), 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), 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, &#39;YEAR&#39;) AND TRUNC(SYSDATE- 1) and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; and nvl(a.posguid,&#39; &#39;)<>&#39;10000&#39; and b.fzflsx1<>&#39;14&#39;and b.fzflsx2<>&#39;08031&#39; and a.subbh =&#39;03711033&#39; and b.fzflsx2<>&#39;08032&#39; and b.fzflsx2<>&#39;08034&#39; and b.fzflsx2<>&#39;08035&#39; and b.fzflsx2<>&#39;08038&#39; and b.fzflsx2<>&#39;08039&#39; and b.fzflsx2<>&#39;08040&#39; and b.fzflsx2<>&#39;08041&#39; ), 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 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 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 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), 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, &#39;YEAR&#39;), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and nvl(a.bz,&#39; &#39;)<>&#39;会员礼品成本调整单&#39; and nvl(a.bz,&#39; &#39;)<>&#39;¥&#39; and nvl(a.bm,&#39; &#39;)<>&#39;积分兑换&#39; and nvl(a.posguid,&#39; &#39;)<>&#39;10000&#39; and b.fzflsx1<>&#39;14&#39;and b.fzflsx2<>&#39;08031&#39; and a.subbh =&#39;03711033&#39; and b.fzflsx2<>&#39;08032&#39; and b.fzflsx2<>&#39;08034&#39; and b.fzflsx2<>&#39;08035&#39; and b.fzflsx2<>&#39;08038&#39; and b.fzflsx2<>&#39;08039&#39; and b.fzflsx2<>&#39;08040&#39; and b.fzflsx2<>&#39;08041&#39; ), 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 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 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 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 编码,e.mc 门店,a.dpl "本周一单一品率",a.kps "本周客品数",a.kpc "本周客品次",b.dpl "上周一单一品率",b.kps "上周客品数",b.kpc "上周客品次", c.dpl "今年一单一品率",c.kps "今年客品数",c.kpc "今年客品次",d.dpl "去年一单一品率",d.kps "去年客品数",d.kpc "去年客品次" from a1 a left join a2 b on a.subbh=b.subbh left join a3 c on a.subbh=c.subbh left join a4 d on a.subbh=d.subbh left join gl_custom e on a.subbh=e.tjbh """ ] results = [] for query in QUERIES: formatted_query = query.replace("&#39;03711033&#39;", f"&#39;{store_id}&#39;") with engine.connect() as conn: df = pd.read_sql(formatted_query, conn) results.append(df) return results def create_pdf_report(store_id, store_name, data, filename): """创建PDF报告""" doc = SimpleDocTemplate( filename, pagesize=landscape(letter), rightMargin=30, leftMargin=30, topMargin=30, bottomMargin=30 ) styles = getSampleStyleSheet() elements = [] # 标题 title_style = ParagraphStyle( &#39;Title&#39;, parent=styles[&#39;Heading1&#39;], alignment=1, spaceAfter=12 ) elements.append(Paragraph(f"{store_name}销售分析报告", title_style)) # 表格1: 整体周销售数据 df1 = data[0] weekly_columns = [&#39;指标&#39;, &#39;本周&#39;, &#39;本周占比&#39;, &#39;上周&#39;, &#39;上周占比&#39;, &#39;环比&#39;] weekly_data = [ [&#39;总销售&#39;, df1[&#39;本周总销售&#39;][0], &#39;&#39;, df1[&#39;上周总销售&#39;][0], &#39;&#39;, df1[&#39;本周总销售&#39;][0] - df1[&#39;上周总销售&#39;][0]], [&#39;综合毛利率&#39;, df1[&#39;本周综合毛利率&#39;][0], &#39;&#39;, df1[&#39;上周综合毛利率&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;总客单&#39;, df1[&#39;本周总客单&#39;][0], &#39;&#39;, df1[&#39;上周总客单&#39;][0], &#39;&#39;, df1[&#39;本周总客单&#39;][0] - df1[&#39;上周总客单&#39;][0]], [&#39;客单价&#39;, df1[&#39;本周客单价&#39;][0], &#39;&#39;, df1[&#39;上周客单价&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;线上销售&#39;, df1[&#39;本周线上销售&#39;][0], &#39;&#39;, df1[&#39;上周线上销售&#39;][0], &#39;&#39;, df1[&#39;本周线上销售&#39;][0] - df1[&#39;上周线上销售&#39;][0]], [&#39;线上客单&#39;, df1[&#39;本周线上客单&#39;][0], &#39;&#39;, df1[&#39;上周线上客单&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;线下销售&#39;, df1[&#39;本周线下销售&#39;][0], &#39;&#39;, df1[&#39;上周线下销售&#39;][0], &#39;&#39;, df1[&#39;本周线下销售&#39;][0] - df1[&#39;上周线下销售&#39;][0]], [&#39;线下客单&#39;, df1[&#39;本周线下客单&#39;][0], &#39;&#39;, df1[&#39;上周线下客单&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;DTP销售&#39;, df1[&#39;本周DTP销售&#39;][0], df1[&#39;本周DTP销售占比&#39;][0], df1[&#39;上周DTP销售&#39;][0], df1[&#39;上周DTP销售占比&#39;][0], df1[&#39;本周DTP销售&#39;][0] - df1[&#39;上周DTP销售&#39;][0]], [&#39;排除DTP销售&#39;, df1[&#39;本周排除DTP销售&#39;][0], &#39;&#39;, df1[&#39;上周排除DTP销售&#39;][0], &#39;&#39;, df1[&#39;本周排除DTP销售&#39;][0] - df1[&#39;上周排除DTP销售&#39;][0]], [&#39;排除DTP综合毛利率&#39;, df1[&#39;本周排除DTP综合毛利率&#39;][0], &#39;&#39;, df1[&#39;上周排除DTP综合毛利率&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;排除DTP客单&#39;, df1[&#39;本周排除DTP客单&#39;][0], &#39;&#39;, df1[&#39;上周排除DTP客单&#39;][0], &#39;&#39;, df1[&#39;本周排除DTP客单&#39;][0] - df1[&#39;上周排除DTP客单&#39;][0]], [&#39;排除DTP客单价&#39;, df1[&#39;本周排除DTP客单价&#39;][0], &#39;&#39;, df1[&#39;上周排除DTP客单价&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;中药销售&#39;, df1[&#39;本周中药销售&#39;][0], df1[&#39;本周中药销售占比&#39;][0], df1[&#39;上周中药销售&#39;][0], df1[&#39;上周中药销售占比&#39;][0], df1[&#39;本周中药销售&#39;][0] - df1[&#39;上周中药销售&#39;][0]], [&#39;内方销售&#39;, df1[&#39;本周内方销售&#39;][0], df1[&#39;本周内方销售占比&#39;][0], df1[&#39;上周内方销售&#39;][0], df1[&#39;上周内方销售占比&#39;][0], df1[&#39;本周内方销售&#39;][0] - df1[&#39;上周内方销售&#39;][0]], [&#39;膏方销售&#39;, df1[&#39;本周膏方销售&#39;][0], df1[&#39;本周膏方销售占比&#39;][0], df1[&#39;上周膏方销售&#39;][0], df1[&#39;上周膏方销售占比&#39;][0], df1[&#39;本周膏方销售&#39;][0] - df1[&#39;上周膏方销售&#39;][0]], [&#39;会员销售&#39;, df1[&#39;本周会员销售&#39;][0], df1[&#39;本周会员销售占比&#39;][0], df1[&#39;上周会员销售&#39;][0], df1[&#39;上周会员销售占比&#39;][0], df1[&#39;本周会员销售&#39;][0] - df1[&#39;上周会员销售&#39;][0]], [&#39;会员客单&#39;, df1[&#39;本周会员客单&#39;][0], &#39;&#39;, df1[&#39;上周会员客单&#39;][0], &#39;&#39;, df1[&#39;本周会员客单&#39;][0] - df1[&#39;上周会员客单&#39;][0]], [&#39;会员数&#39;, df1[&#39;本周会员数&#39;][0], &#39;&#39;, df1[&#39;上周会员数&#39;][0], &#39;&#39;, df1[&#39;本周会员数&#39;][0] - df1[&#39;上周会员数&#39;][0]], [&#39;会员客单价&#39;, df1[&#39;本周会员客单价&#39;][0], &#39;&#39;, df1[&#39;上周会员客单价&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;会员人均消费&#39;, df1[&#39;本周会员人均消费&#39;][0], &#39;&#39;, df1[&#39;上周会员人均消费&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;会员消费频次&#39;, df1[&#39;本周会员消费频次&#39;][0], &#39;&#39;, df1[&#39;上周会员消费频次&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;医保会员销售&#39;, df1[&#39;本周医保会员销售&#39;][0], df1[&#39;本周医保会员销售占比&#39;][0], df1[&#39;上周医保会员销售&#39;][0], df1[&#39;上周医保会员销售占比&#39;][0], df1[&#39;本周医保会员销售&#39;][0] - df1[&#39;上周医保会员销售&#39;][0]], [&#39;医保会员客单&#39;, df1[&#39;本周医保会员客单&#39;][0], &#39;&#39;, df1[&#39;上周医保会员客单&#39;][0], &#39;&#39;, df1[&#39;本周医保会员客单&#39;][0] - df1[&#39;上周医保会员客单&#39;][0]], [&#39;医保会员数&#39;, df1[&#39;本周医保会员数&#39;][0], &#39;&#39;, df1[&#39;上周医保会员数&#39;][0], &#39;&#39;, df1[&#39;本周医保会员数&#39;][0] - df1[&#39;上周医保会员数&#39;][0]], [&#39;医保会员客单价&#39;, df1[&#39;本周医保会员客单价&#39;][0], &#39;&#39;, df1[&#39;上周医保会员客单价&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;医保会员人均消费&#39;, df1[&#39;本周医保会员人均消费&#39;][0], &#39;&#39;, df1[&#39;上周医保会员人均消费&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;医保会员消费频次&#39;, df1[&#39;本周医保会员消费频次&#39;][0], &#39;&#39;, df1[&#39;上周医保会员消费频次&#39;][0], &#39;&#39;, &#39;&#39;], ] # 创建表格1 weekly_table = Table([weekly_columns] + weekly_data) weekly_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#4F81BD&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#DCE6F1&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black), (&#39;VALIGN&#39;, (0, 0), (-1, -1), &#39;MIDDLE&#39;) ])) elements.append(Paragraph("<b>表1: 整体周销售数据</b>", styles[&#39;Heading2&#39;])) elements.append(weekly_table) elements.append(Spacer(1, 0.2 * inch)) # 表格2: 医保付款周数据 df2 = data[1] payment_columns = [&#39;付款方式&#39;, &#39;本周付款金额&#39;, &#39;上周付款金额&#39;, &#39;环比&#39;] payment_data = [] for _, row in df2.iterrows(): payment_data.append([ row[&#39;付款方式&#39;], row[&#39;本周付款金额&#39;], row[&#39;上周付款金额&#39;], row[&#39;本周付款金额&#39;] - row[&#39;上周付款金额&#39;] ]) payment_table = Table([payment_columns] + payment_data) payment_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#C0504D&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#F2DCDB&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表2: 医保付款周数据</b>", styles[&#39;Heading2&#39;])) elements.append(payment_table) elements.append(Spacer(1, 0.2 * inch)) # 表格3: 品类周数据 df3 = data[2] category_columns = [&#39;类别&#39;, &#39;本周销售&#39;, &#39;本周占比&#39;, &#39;周销售环比&#39;, &#39;本周客流&#39;, &#39;周客流环比&#39;, &#39;医保会员销售周销售环比&#39;, &#39;非医保会员周销售环比&#39;, &#39;非会员周销售环比&#39;] category_data = [] for _, row in df3.iterrows(): total_sales = df1[&#39;本周总销售&#39;][0] category_data.append([ row[&#39;类别&#39;], row[&#39;本周销售&#39;], f"{row[&#39;本周销售&#39;] / total_sales:.2%}" if total_sales > 0 else "0.00%", row[&#39;周销售环比&#39;], row[&#39;本周客流&#39;], row[&#39;周客流环比&#39;], row[&#39;医保会员销售周销售环比&#39;], row[&#39;非医保会员周销售环比&#39;], row[&#39;非会员周销售环比&#39;] ]) category_table = Table([category_columns] + category_data) category_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#9BBB59&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 9), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 10), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#EBF1DE&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表3: 品类周数据</b>", styles[&#39;Heading2&#39;])) elements.append(category_table) elements.append(Spacer(1, 0.2 * inch)) # 表格4: 销售能力环比 df4 = data[3] capability_columns = [&#39;时间&#39;, &#39;一单一品率&#39;, &#39;客品数&#39;, &#39;客品次&#39;] capability_data = [ [&#39;本周&#39;, df4[&#39;本周一单一品率&#39;][0], df4[&#39;本周客品数&#39;][0], df4[&#39;本周客品次&#39;][0]], [&#39;上周&#39;, df4[&#39;上周一单一品率&#39;][0], df4[&#39;上周客品数&#39;][0], df4[&#39;上周客品次&#39;][0]], [&#39;环比&#39;, df4[&#39;本周一单一品率&#39;][0] - df4[&#39;上周一单一品率&#39;][0], df4[&#39;本周客品数&#39;][0] - df4[&#39;上周客品数&#39;][0], df4[&#39;本周客品次&#39;][0] - df4[&#39;上周客品次&#39;][0]] ] capability_table = Table([capability_columns] + capability_data) capability_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#8064A2&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#E5E0EC&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表4: 销售能力环比</b>", styles[&#39;Heading2&#39;])) elements.append(capability_table) elements.append(PageBreak()) # 表格5: 整体销售数据(年度) annual_columns = [&#39;指标&#39;, &#39;今年&#39;, &#39;今年占比&#39;, &#39;去年&#39;, &#39;去年占比&#39;, &#39;同比&#39;] annual_data = [ [&#39;总销售&#39;, df1[&#39;今年总销售&#39;][0], &#39;&#39;, df1[&#39;去年总销售&#39;][0], &#39;&#39;, df1[&#39;今年总销售&#39;][0] - df1[&#39;去年总销售&#39;][0]], [&#39;综合毛利率&#39;, df1[&#39;今年综合毛利率&#39;][0], &#39;&#39;, df1[&#39;去年综合毛利率&#39;][0], &#39;&#39;, &#39;&#39;], [&#39;总客单&#39;, df1[&#39;今年总客单&#39;][0], &#39;&#39;, df1[&#39;去年总客单&#39;][0], &#39;&#39;, df1[&#39;今年总客单&#39;][0] - df1[&#39;去年总客单&#39;][0]], # 其他行...(类似表格1的结构) ] annual_table = Table([annual_columns] + annual_data) annual_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#4F81BD&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#DCE6F1&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表5: 整体销售数据(年度)</b>", styles[&#39;Heading2&#39;])) elements.append(annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格6: 医保付款数据(年度) payment_annual_columns = [&#39;付款方式&#39;, &#39;今年付款金额&#39;, &#39;去年付款金额&#39;, &#39;同比&#39;] payment_annual_data = [] for _, row in df2.iterrows(): payment_annual_data.append([ row[&#39;付款方式&#39;], row[&#39;今年付款金额&#39;], row[&#39;去年付款金额&#39;], row[&#39;今年付款金额&#39;] - row[&#39;去年付款金额&#39;] ]) payment_annual_table = Table([payment_annual_columns] + payment_annual_data) payment_annual_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#C0504D&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#F2DCDB&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表6: 医保付款数据(年度)</b>", styles[&#39;Heading2&#39;])) elements.append(payment_annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格7: 品类数据(年度) category_annual_columns = [&#39;类别&#39;, &#39;今年销售&#39;, &#39;今年占比&#39;, &#39;年销售同比&#39;, &#39;今年客流&#39;, &#39;年客流同比&#39;, &#39;医保会员销售年销售同比&#39;, &#39;非医保会员年销售同比&#39;, &#39;非会员年销售同比&#39;] category_annual_data = [] for _, row in df3.iterrows(): total_annual_sales = df1[&#39;今年总销售&#39;][0] category_annual_data.append([ row[&#39;类别&#39;], row[&#39;今年销售&#39;], f"{row[&#39;今年销售&#39;] / total_annual_sales:.2%}" if total_annual_sales > 0 else "0.00%", row[&#39;年销售同比&#39;], row[&#39;今年客流&#39;], row[&#39;年客流同比&#39;], row[&#39;医保会员销售年销售同比&#39;], row[&#39;非医保会员年销售同比&#39;], row[&#39;非会员年销售同比&#39;] ]) category_annual_table = Table([category_annual_columns] + category_annual_data) category_annual_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#9BBB59&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 9), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 10), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#EBF1DE&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表7: 品类数据(年度)</b>", styles[&#39;Heading2&#39;])) elements.append(category_annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格8: 销售能力同比 capability_annual_columns = [&#39;时间&#39;, &#39;一单一品率&#39;, &#39;客品数&#39;, &#39;客品次&#39;] capability_annual_data = [ [&#39;今年&#39;, df4[&#39;今年一单一品率&#39;][0], df4[&#39;今年客品数&#39;][0], df4[&#39;今年客品次&#39;][0]], [&#39;去年&#39;, df4[&#39;去年一单一品率&#39;][0], df4[&#39;去年客品数&#39;][0], df4[&#39;去年客品次&#39;][0]], [&#39;同比&#39;, df4[&#39;今年一单一品率&#39;][0] - df4[&#39;去年一单一品率&#39;][0], df4[&#39;今年客品数&#39;][0] - df4[&#39;去年客品数&#39;][0], df4[&#39;今年客品次&#39;][0] - df4[&#39;去年客品次&#39;][0]] ] capability_annual_table = Table([capability_annual_columns] + capability_annual_data) capability_annual_table.setStyle(TableStyle([ (&#39;BACKGROUND&#39;, (0, 0), (-1, 0), colors.HexColor(&#39;#8064A2&#39;)), (&#39;TEXTCOLOR&#39;, (0, 0), (-1, 0), colors.whitesmoke), (&#39;ALIGN&#39;, (0, 0), (-1, -1), &#39;CENTER&#39;), (&#39;FONTSIZE&#39;, (0, 0), (-1, 0), 10), (&#39;BOTTOMPADDING&#39;, (0, 0), (-1, 0), 12), (&#39;BACKGROUND&#39;, (0, 1), (-1, -1), colors.HexColor(&#39;#E5E0EC&#39;)), (&#39;GRID&#39;, (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表8: 销售能力同比</b>", styles[&#39;Heading2&#39;])) elements.append(capability_annual_table) # 分析页 elements.append(PageBreak()) analysis_title = Paragraph("销售数据分析报告", styles[&#39;Heading1&#39;]) elements.append(analysis_title) # 添加分析内容 analysis_content = [ "1. <b>总体销售趋势</b>: 本周总销售额为¥{:.2f},环比{:.2%}。今年累计销售额¥{:.2f},同比{:.2%}。".format( df1[&#39;本周总销售&#39;][0], (df1[&#39;本周总销售&#39;][0] - df1[&#39;上周总销售&#39;][0]) / df1[&#39;上周总销售&#39;][0] if df1[&#39;上周总销售&#39;][0] > 0 else 0, df1[&#39;今年总销售&#39;][0], (df1[&#39;今年总销售&#39;][0] - df1[&#39;去年总销售&#39;][0]) / df1[&#39;去年总销售&#39;][0] if df1[&#39;去年总销售&#39;][0] > 0 else 0 ), "2. <b>会员分析</b>: 会员销售占比{},较上周{}。医保会员贡献率{},人均消费¥{:.2f}。".format( df1[&#39;本周会员销售占比&#39;][0], "上升" if float(df1[&#39;本周会员销售占比&#39;][0].strip(&#39;%&#39;)) > float( df1[&#39;上周会员销售占比&#39;][0].strip(&#39;%&#39;)) else "下降", df1[&#39;本周医保会员销售占比&#39;][0], df1[&#39;本周医保会员人均消费&#39;][0] ), "3. <b>渠道表现</b>: 线上销售占比{:.2%},线下销售占比{:.2%}。DTP渠道占比{},环比{}。".format( df1[&#39;本周线上销售&#39;][0] / df1[&#39;本周总销售&#39;][0] if df1[&#39;本周总销售&#39;][0] > 0 else 0, (df1[&#39;本周总销售&#39;][0] - df1[&#39;本周线上销售&#39;][0]) / df1[&#39;本周总销售&#39;][0] if df1[&#39;本周总销售&#39;][0] > 0 else 0, df1[&#39;本周DTP销售占比&#39;][0], "上升" if float(df1[&#39;本周DTP销售占比&#39;][0].strip(&#39;%&#39;)) > float( df1[&#39;上周DTP销售占比&#39;][0].strip(&#39;%&#39;)) else "下降" ), "4. <b>品类表现</b>: 销售最好的品类是{},贡献率{:.2%}。增长最快的品类是{},环比增长{:.2%}。".format( df3.loc[df3[&#39;本周销售&#39;].idxmax()][&#39;类别&#39;], df3[&#39;本周销售&#39;].max() / df1[&#39;本周总销售&#39;][0] if df1[&#39;本周总销售&#39;][0] > 0 else 0, df3.loc[df3[&#39;周销售环比&#39;].idxmax()][&#39;类别&#39;], df3[&#39;周销售环比&#39;].max() / df3[&#39;本周销售&#39;].max() if df3[&#39;本周销售&#39;].max() > 0 else 0 ), "5. <b>销售能力</b>: 客品数{}件/单,客品次{}次/单。一单一品率{:.2%},较上周{}。".format( df4[&#39;本周客品数&#39;][0], df4[&#39;本周客品次&#39;][0], df4[&#39;本周一单一品率&#39;][0], "上升" if df4[&#39;本周一单一品率&#39;][0] > df4[&#39;上周一单一品率&#39;][0] else "下降" ) ] for content in analysis_content: elements.append(Paragraph(content, styles[&#39;Normal&#39;])) elements.append(Spacer(1, 0.1 * inch)) # 生成图表 fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5)) # 销售渠道占比图 channels = [&#39;线上&#39;, &#39;线下&#39;, &#39;DTP&#39;, &#39;其他&#39;] sales = [ df1[&#39;本周线上销售&#39;][0], df1[&#39;本周线下销售&#39;][0], df1[&#39;本周DTP销售&#39;][0], df1[&#39;本周总销售&#39;][0] - df1[&#39;本周线上销售&#39;][0] - df1[&#39;本周DTP销售&#39;][0] - df1[&#39;本周线下销售&#39;][0] ] ax1.pie(sales, labels=channels, autopct=&#39;%1.1f%%&#39;, startangle=90) ax1.set_title(&#39;销售渠道占比&#39;) # 品类销售TOP5 top_categories = df3.nlargest(5, &#39;本周销售&#39;) ax2.bar(top_categories[&#39;类别&#39;], top_categories[&#39;本周销售&#39;], color=&#39;skyblue&#39;) ax2.set_title(&#39;品类销售TOP5&#39;) ax2.tick_params(axis=&#39;x&#39;, rotation=45) plt.tight_layout() chart_buffer = BytesIO() plt.savefig(chart_buffer, format=&#39;png&#39;, dpi=150) plt.close() # 将图表添加到PDF from reportlab.platypus import Image elements.append(Image(chart_buffer, width=10 * inch, height=5 * inch)) # 生成PDF doc.build(elements) def main(): engine = create_engine(&#39;oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata&#39;) with engine.connect() as conn: store_df = pd.read_sql("SELECT tjbh, mc FROM gl_custom WHERE tjbh = &#39;03711033&#39;", conn) for _, row in store_df.iterrows(): store_id = row[&#39;tjbh&#39;] store_name = row[&#39;mc&#39;] print(f"正在处理门店: {store_name} ({store_id})") try: data = fetch_store_data(store_id) filename = f"{store_name}_销售分析报告.pdf" create_pdf_report(store_id, store_name, data, filename) print(f"已生成: {filename}") except Exception as e: print(f"处理门店 {store_name} 时出错: {str(e)}") if __name__ == "__main__": main() E:\Anaconda3-2024.02.1\python.exe E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据3.py 正在处理门店: 郑州康复店G (03711033) 处理门店 郑州康复店G 时出错: Wedge sizes &#39;x&#39; must be non negative values Process finished with exit code 0
最新发布
08-10
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值