今年占比、去年占比两列的数据仍为 0%，可能是由于字段拼接逻辑错误或 SQL 查询结果中缺少对应的占比字段。以下是进一步修复后的代码片段：
```python
def generate_yearly_sales_table(self):
sql = f"""
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='{self.store_id}') 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 = '{self.store_id}'
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 = '{self.store_id}'
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 = '{self.store_id}'
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
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 = '{self.store_id}'
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
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 = '{self.store_id}'
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 = '{self.store_id}'
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 = '{self.store_id}'
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 = '{self.store_id}'
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
"""
print("执行的 SQL2:", sql)
df = self.fetch_data(sql)
if df.empty:
return None
# 构造年销售同比数据表格
table_data = []
indicators = [
('总销售(万)', '今年总销售(万)', '去年总销售(万)', False),
('综合毛利率', '今年综合毛利率', '去年综合毛利率', False),
('总客单', '今年总客单', '去年总客单', False),
('客单价', '今年客单价', '去年客单价', False),
('线上销售(万)', '今年线上销售(万)', '去年线上销售(万)', False),
('线上客单', '今年线上客单', '去年线上客单', False),
('线下销售(万)', '今年线下销售(万)', '去年线下销售(万)', False),
('线下客单', '今年线下客单', '去年线下客单', False),
('DTP销售(万)', '今年DTP销售(万)', '去年DTP销售(万)', True),
('排除DTP销售(万)', '今年排除DTP销售(万)', '去年排除DTP销售(万)', False),
('排除DTP综合毛利率', '今年排除DTP综合毛利率', '去年排除DTP综合毛利率', False),
('排除DTP客单', '今年排除DTP客单', '去年排除DTP客单', False),
('排除DTP客单价', '今年排除DTP客单价', '去年排除DTP客单价', False),
('中药销售(万)', '今年中药销售(万)', '去年中药销售(万)', True),
('内方销售(万)', '今年内方销售(万)', '去年内方销售(万)', True),
('膏方销售(万)', '今年膏方销售(万)', '去年膏方销售(万)', True),
('会员销售(万)', '今年会员销售(万)', '去年会员销售(万)', True),
('会员客单', '今年会员客单', '去年会员客单', False),
('会员数', '今年会员数', '去年会员数', False),
('会员客单价', '今年会员客单价', '去年会员客单价', False),
('会员人均消费', '今年会员人均消费', '去年会员人均消费', False),
('会员消费频次', '今年会员消费频次', '去年会员消费频次', False),
('医保会员销售(万)', '今年医保会员销售(万)', '去年医保会员销售(万)', True),
('医保会员客单', '今年医保会员客单', '去年医保会员客单', False),
('医保会员数', '今年医保会员数', '去年医保会员数', False),
('医保会员客单价', '今年医保会员客单价', '去年医保会员客单价', False),
('医保会员人均消费', '今年医保会员人均消费', '去年医保会员人均消费', False),
('医保会员消费频次', '今年医保会员消费频次', '去年医保会员消费频次', False)
]
for indicator in indicators:
name, curr_col, prev_col, has_ratio = indicator
curr_value = df.iloc[0][curr_col] if curr_col in df.columns else 0
prev_value = df.iloc[0][prev_col] if prev_col in df.columns else 0
ratio_curr = ''
ratio_prev = ''
if has_ratio:
# 显式指定今年和去年的占比字段名
ratio_col = f"{curr_col}占比"
ratio_prev_col = f"{prev_col}占比"
# 如果字段不存在，则设置默认值
ratio_curr = df.iloc[0][ratio_col] if ratio_col in df.columns else '0%'
ratio_prev = df.iloc[0][ratio_prev_col] if ratio_prev_col in df.columns else '0%'
try:
if isinstance(curr_value, str) and '%' in curr_value:
curr_num = float(curr_value.replace('%', ''))
prev_num = float(prev_value.replace('%', '')) if isinstance(prev_value, str) and '%' in prev_value else 0
yoy = f"{curr_num - prev_num:.2f}%"
else:
curr_num = float(curr_value) if isinstance(curr_value, (int, float)) else 0
prev_num = float(prev_value) if isinstance(prev_value, (int, float)) else 0
yoy = curr_num - prev_num
if name in ['综合毛利率', '排除DTP综合毛利率']:
yoy = f"{yoy:.2f}%"
else:
yoy = f"{yoy:.2f}"
except Exception as e:
yoy = '0'
table_data.append([
name,
str(curr_value),
str(ratio_curr),
str(prev_value),
str(ratio_prev),
str(yoy)
])
return table_data
```
扫一扫
651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
