准备好这两张表
1.商品的效期库存表：
直接拉生产及结束日期的商品库存，留下几列空白字段，后续update处理
image.png
2.商品的预算表：把预算表加工处理
image.png
业务逻辑：
最短的效期：
若效期时长能销售的预算 <= 库途，即到效期结束，仍还有库途没销万，即
产生折损（折损 = 库途 - 效期时长的预算），且实际销售天数为效期时长若效期时长能销售的预算 > 库途，即效期还没结束，库途就已经被销完了，不产生折损，但
实际销售天数为本次库途的可销天，而非效期时长其他效期：
(若效期时长能销售的预算 - 本效期之前的累计销售预算) <= 库途，即到效期结束，仍还有库途没销万，即
产生折损（折损 = 库途 - 效期时长的预算 - 本效期之前的累计销售预算），且实际销售天数为效期时长 - 本效期之前的累计销售天数(若效期时长能销售的预算 - 本效期之前的累计销售预算) > 库途，即效期还没结束，库途就已经被销完了，不产生折损，但
实际销售天数为本次（库途 + 本效期之前的累计销售预算）算出的可销天 再 - 本效期之前的累计销售天数，而非效期时长深红色文字：超过两年的效期或者库途的处理的业务逻辑
- 当效期的时长是超过两年，即超过的那部分的预算 = （效期 - 总累计销售天数）* 24个月的预算日均
- 当库途超过两年后的预算，即超过的那部分的可销天 = （库途 - 两年的累计预算）/ 24个月的预算日均
python 实现过程
# -*- coding: utf-8 -*-
"""
Created on Thu Nov 21 10:55:23 2019
@author: liand
"""
# mysql连接包
from pymysql import connect
# import pandas as pd
# 不发出警告
# import warnings
# warnings.filterwarnings("ignore")
# 连接数据库
conn = connect(host='localhost', user='root', password='123', db='test', port=3306)
# 获取操作游标
cursor = conn.cursor()
# 获取需要计算到超存货可销天的商品
sql = "SELECT DISTINCT 商品 FROM xiaoqi;"
# 执行查询语句
cursor.execute(sql)
skus = list(cursor.fetchall())
for i in skus:
the_sku = i[0]
# print(the_sku)
################################## 开始计算某个商品（sku）各个效期的存货超可销成本################################
# 执行查询语句
sql = "SELECT * FROM xiaoqi WHERE 商品 = {1}{0}{1} ORDER BY 效期 ;".format(the_sku,"'")
cursor.execute(sql)
# 获取查询结果（元组形式转列表）
data = list(cursor.fetchall())
# 获取第一条，为效期最短的
a = data[0]
# print(a)
# 设置序列号，方便检查结果，后续校验好了可去掉
num = 0
for i in data:
num = num + 1
sku = i[0] # 商品
xiaoqi = i[1] # 效期（天数）
kucun = i[2] # 库存（这批效期的库存）
# 当次条记录等于第一条的时候，则行以下结果
if i == a:
# sql比较长，且部分语句是重复使用，先拆分，再合并
# 定位要分析效期的商品
sub_sql1 = """SELECT * FROM test_one WHERE 商品 = {1}{0}{1}""".format(the_sku,"'")
# 效期不大于两年：月初预算 +（效期-月初累计天数）*日均 效期大于两年：月尾预算+（效期-月尾天数）*（月尾预算/730-当月过去天数）
sub_sql2 = """IF(a.月份 = REPLACE(LEFT(date_add(CURDATE(),interval 24 month),7),'-',''),
a.月尾累计预算 + ({0} - a.月尾累计天数)*(a.月尾累计预算/(730 - DAYOFMONTH( CURDATE() ) )),
a.月初累计预算+({0}-a.月初累计天数)*a.日均)""".format(xiaoqi)
# 定位效期的位置
sub_sql3 = """SELECT ss.商品, max(ss.月份) AS 月份 FROM ({0}) ss WHERE {1}>= ss.月初累计天数 GROUP BY ss.商品""".format(sub_sql1,xiaoqi)
# 定位库途能卖多少天
sub_sql4 = """SELECT s.商品,max(s.月份) as 月份 FROM ({0}) s WHERE {1}>= s.月初累计预算 GROUP BY s.商品""".format(sub_sql1,kucun)
# 库途不大于两年后累计：月初天数+（库途-月初预算）*日均 库途大于两年：月尾天数+（库途-月尾预算）/（月尾预算/730-当月过去天数）
sub_sql5 = """IF(a.月份 = REPLACE(LEFT(date_add(CURDATE(),interval 24 month),7),'-',''), -- 当定位到的月份在2年后时
a.月尾累计天数 + ({0} - a.月尾累计预算)/(a.月尾累计预算/(730 - DAYOFMONTH( CURDATE() ) )),
a.月初累计天数+({0} -a.月初累计预算)/a.日均)""".format(kucun)
# 拼接sql语句，业务逻辑：
# 以次批次的效期定位到要计算的记录行上，通过结构计算出折损与实际销售天数
# 若效期时长能销售的预算 <= 库途，即到效期结束，仍还有库途没销万，即产生折损，且实际销售天数为效期时长
# 若效期时长能销售的预算 > 库途，即效期还没结束，库途就已经被销完了，不产生折损，但实际销售天数为本次库途的可销天，而非效期时长
sql ="""SELECT if(({3})<={0},{0}-(({3})),0) 折损,
if(({3})<={0},{1},
(SELECT ({6}) FROM ({2}) a INNER JOIN ({5}) b ON a.商品 = b.商品 AND a.月份 = b.月份)
) 实际用了多少天销售
FROM ({2}) a INNER JOIN ({4}) b on a.月份 = b.月份;""".format(kucun,xiaoqi,sub_sql1,sub_sql2,sub_sql3,sub_sql4,sub_sql5)
# print(sql)
# 执行sql语句
cursor.execute(sql)
# 返回结果
data2 = list(cursor.fetchall())
# print(num,data2)
# 折损
zesun = data2[0][0]
# 实际销售天数（简称可销天）
kexiaotian = data2[0][1]
# 实际销售库途 = 库途 - 能销售的预算
xiaohao = float(kucun) - zesun
# print(kexiaotian,zesun,xiaohao)
# print(zesun,kexiaotian,xiaohao)
print(num,"商品：",the_sku,">>","销售天数：",kexiaotian,"折损：",zesun,"实际卖出：",xiaohao,"\n")
# 更新表数据
sql = """
UPDATE xiaoqi SET
销售天数 = {6}{0}{6},
折损 = {6}{1}{6},
实际卖出 = {6}{2}{6}
WHERE 商品 = {6}{3}{6} AND 效期 = {6}{4}{6} AND 库存 = {6}{5}{6};""".format(kexiaotian,zesun,xiaohao,sku,xiaoqi,kucun,"'")
cursor.execute(sql)
# 提交更新操作，完成事务
conn.commit()
# 由于第一条为最低的效期，所以并没有截止到目前（即汇总前面效期的累计预算）的累计预算及累计天数，
# 相当于初始化，所以直接设置为零，也可以不更新，让它为空也行。
# sql = """UPDATE xiaoqi SET 截止目前累计销售 = '0',截止目前累计销售天数 = '0'
# WHERE 商品 = {3}{0}{3} AND 效期 = {3}{1}{3} AND 库存 = {3}{2}{3};""".format(sku,xiaoqi,kucun,"'")
# cursor.execute(sql)
# conn.commit()
# 查看目前最后的计算结果，方便检查，后续检查完毕可去掉
sql = """
SELECT * FROM xiaoqi WHERE
商品 = {3}{0}{3} AND 效期 = {3}{1}{3} AND 库存 = {3}{2}{3};""".format(sku,xiaoqi,kucun,"'")
cursor.execute(sql)
data = list(cursor.fetchall())
# print(num, data)
# 获取非第一条，为效期不是最短的其他数据
else:
# 更新在本效期之前的所有效期的累计销售预算及累计销售天数
data = list(cursor.fetchall())
sql = """UPDATE xiaoqi b INNER JOIN (SELECT (SELECT SUM(b.实际卖出) FROM xiaoqi AS b WHERE b.效期 < a.效期 AND b.商品 = a.商品) 累计,
(SELECT SUM(b.销售天数) FROM xiaoqi AS b WHERE b.效期 < a.效期 AND b.商品 = a.商品) 累计天数,
商品,效期,库存
FROM xiaoqi a) a ON b.商品=a.商品 AND a.效期=b.效期 AND a.库存 = a.库存 SET b.截止目前累计销售=a.累计,b.截止目前累计销售天数 = a.累计天数;"""
cursor.execute(sql)
conn.commit()
# 拿到累计销售及累计天数
sql = """SELECT * FROM xiaoqi WHERE 商品 = {0}{1}{0} AND 效期 = {0}{2}{0} AND 库存 = {0}{3}{0};""".format("'",the_sku,xiaoqi,kucun)
cursor.execute(sql)
data = list(cursor.fetchall())
leijixiaoshou = data[0][6] # 累计销售 （截止到这批之前商品已累计销售了多少库存）
leijitianshu = data[0][7] # 累计天数 （截止到这批之前商品已累计销售了多少天数）
# sql比较长，且部分语句是重复使用，先拆分，再合并
# 月初预算 +（效期-月初累计天数）*日均 - 累计预算
sub_sql1 = """IF(a.月份 = REPLACE(LEFT(date_add(CURDATE(),interval 24 month),7),'-',''),
a.月尾累计预算 + ({0} - a.月尾累计天数)*(a.月尾累计预算/(730 - DAYOFMONTH( CURDATE() ) )),
a.月初累计预算+({0}-a.月初累计天数)*a.日均) - {1}""".format(xiaoqi,leijixiaoshou)
# 定位到分析效期的商品
sub_sql2 = """SELECT * FROM test_one WHERE 商品 = {1}{0}{1}""".format(the_sku,"'")
# 定位库途能销售到哪，用于算可销天
sub_sql3 = """SELECT s.商品,max(s.月份) as 月份 FROM
(SELECT * FROM test_one WHERE 商品 = {1}{0}{1}) s WHERE ({2}+{3})>= s.月初累计预算 GROUP BY s.商品""".format(the_sku,"'",kucun,leijixiaoshou)
# 效期定位到要计算的记录行
sub_sql4 = """SELECT ss.商品, max(ss.月份) AS 月份
FROM (SELECT * FROM test_one WHERE 商品 = {1}{0}{1}) ss
WHERE {2}>= ss.月初累计天数 GROUP BY ss.商品""".format(the_sku,"'",xiaoqi)
# 库途+之前批次累计预算不大于两年后累计：月初天数+（库途+之前批次累计预算-月初预算）*日均
# 库途+之前批次累计预算大于两年后累计：月尾天数+（库途+之前批次累计预算-月尾预算）/（月尾预算/730-当月过去天数）
sub_sql5 = """IF(a.月份 = REPLACE(LEFT(date_add(CURDATE(),interval 24 month),7),'-',''),
a.月尾累计天数 + ({0}+{1} - a.月尾累计预算)/(a.月尾累计预算/(730 - DAYOFMONTH( CURDATE() ) )),
a.月初累计天数+({0}+{1} -a.月初累计预算)/a.日均)""".format(kucun,leijixiaoshou)
# 拼接sql语句，业务逻辑：
# 以次批次的效期定位到要计算的记录行上，通过结构计算出折损与实际销售天数
# (若效期时长能销售的预算 - 本效期之前的累计销售预算) <= 库途，即到效期结束，仍还有库途没销万，即产生折损（折损 = 库途 - 效期时长的预算 - 本效期之前的累计销售预算），且实际销售天数为效期时长 - 本效期之前的累计销售天数
# (若效期时长能销售的预算 - 本效期之前的累计销售预算) > 库途，即效期还没结束，库途就已经被销完了，不产生折损，但实际销售天数为本次（库途 + 本效期之前的累计销售预算）算出的可销天 再 - 本效期之前的累计销售天数，而非效期时长
sql = """SELECT if(({4})<={0},{0}-({4}),0) 折损,
if(({4})<={0},{1}-{2},((SELECT ({8}) FROM ({5}) a
INNER JOIN ({6}) b ON a.商品 = b.商品 AND a.月份 = b.月份)-{2})) 实际用了多少天销售
FROM ({5}) a INNER JOIN ({7}) b on a.月份 = b.月份;""".format(kucun,xiaoqi,leijitianshu,leijixiaoshou,sub_sql1,sub_sql2,sub_sql3,sub_sql4,sub_sql5)
# 执行sql语句
cursor.execute(sql)
data2 = list(cursor.fetchall())
zesun = data2[0][0] # 折损
kexiaotian = data2[0][1] # 实际销售天数
xiaohao = float(kucun) - zesun # 实际销售预算
print(num,"商品：",the_sku,">>","销售天数：",kexiaotian,"折损：",zesun,"实际卖出：",xiaohao,"\n")
# 更新表数据
sql = """
UPDATE xiaoqi SET
销售天数 = {6}{0}{6},
折损 = {6}{1}{6},
实际卖出 = {6}{2}{6}
WHERE 商品 = {6}{3}{6} AND 效期 = {6}{4}{6} AND 库存 = {6}{5}{6};""".format(kexiaotian,zesun,xiaohao,sku,xiaoqi,kucun,"'")
cursor.execute(sql)
# 提交更新操作，完成事务
conn.commit()
# 查看目前最后的计算结果，方便检查，后续检查完毕可去掉
sql = """
SELECT * FROM xiaoqi WHERE
商品 = {3}{0}{3} AND 效期 = {3}{1}{3} AND 库存 = {3}{2}{3};""".format(sku,xiaoqi,kucun,"'")
cursor.execute(sql)
data = list(cursor.fetchall())
# print(num,"商品：",the_sku,">>",data,"\n")
# 关闭游标
cursor.close()
# 关闭数据库连接
conn.close()
最后的表，呈现商品每个效期的折损成本
image.png
