每个门店每月的销售额+ 行转列 + 分组求和弥补0

每个月销售总额

from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy import text 
import pandas as pd
engine=create_engine('mysql+pymysql://xx:mm$@IP地址:3306/demo')
sql = '''select substring( t1.STime,1,7) as YearMonth ,t1.shopID AS ShopID, sum(t.SaleValue) as sale_value_last
,count(distinct t1.MemberID) as member_nubmer_last 
from OrderItem t
join OrderList t1 on t1.SheetID = t.SheetID
group by substring(t1.STime,1,7),t1.ShopID;'''
df = pd.read_sql_query(sql, engine)
df.head(3)


5677.png

将上面的销售额结果行转列.把行的值(店铺id)转化为列名

一共就两家店铺

前置知识: sum(case 列名 when 行值 then 目标值 else 0 end ) as 行值

sum(case temp.shopID when 'CDLG'  then temp.sale_values else 0 end) as 'CDLG'
select temp.YearMonth,
        sum(case temp.ShopID when 'CDLG' then temp.sale_value_last else 0 end) as 'CDLG',
        sum(case temp.ShopID when 'WDGC' then temp.sale_value_last else 0 end) as 'WDGC'
from (
select substring( t1.STime,1,7) as YearMonth ,t1.shopID AS ShopID, sum(t.SaleValue) as sale_value_last
,count(distinct t1.MemberID) as member_nubmer_last 
from OrderItem t
join OrderList t1 on t1.SheetID = t.SheetID
group by substring(t1.STime,1,7),t1.ShopID
              ) temp
              
group by temp.ShopID,temp.YearMonth;


67678.png

select 后有 YearMonth 则 group by 后也要YearMonth
否则会产生only_full_group_by 的报错信息

报错信息
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains
 nonaggregated column 'temp.YearMonth' which is not functionally dependent on 
columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

结果还是不完美, 总是有0

需要对上面的结果按照年月进行分组求和

mysql 解法


select temp_out.YearMonth as YearMonth ,sum(CDLG) as CDLG , SUM(WDGC)  AS CDLG
from 
(
select temp.YearMonth,
        sum(case temp.ShopID when 'CDLG' then temp.sale_value_last else 0 end) as 'CDLG',
        sum(case temp.ShopID when 'WDGC' then temp.sale_value_last else 0 end) as 'WDGC'
from (
select substring( t1.STime,1,7) as YearMonth ,t1.shopID AS ShopID, sum(t.SaleValue) as sale_value_last
,count(distinct t1.MemberID) as member_nubmer_last 
from OrderItem t
join OrderList t1 on t1.SheetID = t.SheetID
group by substring(t1.STime,1,7),t1.ShopID
              ) temp
              
group by temp.ShopID,temp.YearMonth
 ) temp_out
GROUP BY temp_out.YearMonth


6789.png

pandas解法

df.groupby([''YearMonth]).sum()


分组求和.png
https://www.jianshu.com/p/3fe825b675ac

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论