mysql求回购率_python循环执行mysql计算每个月的回购率+pyecharts画图+tableau画仪表盘...

查询订单表from sqlalchemy import text

import pandas as pd

from sqlalchemy import create_engine

engine=create_engine(&＃39;mysql+pymysql://账户名:密码@ip:端口号/库名&＃39;)

sql = &＃39;&＃39;&＃39;

select *

from demo.OrderList

limit 8

&＃39;&＃39;&＃39;

df = pd.read_sql_query(sql, engine)

df

查询订单表.png

定义 回购率 = 在6月和7月都购物的会员数/ 6月的购物会员人数

先查询201606的购物会员sql = &＃39;&＃39;&＃39;

select distinct memberid, substring(STime,1,7) as YearMonth

from demo.OrderList

where substring(STime,1,7)=&＃39;2016-06&＃39; and memberid is not null

&＃39;&＃39;&＃39;

df = pd.read_sql_query(sql, engine)

df.head(3)

6月份购物的会员.png

同理，得到201607的会员

7月分购物的会员.png

6月份和7月份的会员进行left join

学习一下left join,想一想为什么要用left join sql = &＃39;&＃39;&＃39;

select * from

(

select distinct memberid as member_t1, substring(STime,1,7) as YearMonth_t1

from demo.OrderList

where substring(STime,1,7)=&＃39;2016-06&＃39; and memberid is not null) t1

left join

(

select distinct memberid as member_t2, substring(STime,1,7) as YearMonth_t2

from demo.OrderList

where substring(STime,1,7)=&＃39;2016-07&＃39; and memberid is not null) t2

on t1.member_t1 = t2.member_t2

&＃39;&＃39;&＃39;

df = pd.read_sql_query(sql, engine)

df.head(6)

学习leftjoin.png

这样 7月份的购物会员表就只剩下和6月份的公共部分了

学习inner join.png

则两张表的会员都只有公共部分

根据业务需求， 需要用left join，于是使用count 计数公共会员数 (即回购的人数)和 6月份的购物会员数sql = &＃39;&＃39;&＃39;

select t1.YearMonth_t1 as 上个月 , count(distinct t2.member_t2 )as 回购人数 ,count(distinct t1.member_t1) 当前月购物人数 ,

count(distinct t2.member_t2 )/count(distinct t1.member_t1) as 回购率

from

(

select distinct memberid as member_t1, substring(STime,1,7) as YearMonth_t1

from demo.OrderList

where substring(STime,1,7)=&＃39;2016-06&＃39; and memberid is not null) t1

left join

(

select distinct memberid as member_t2, substring(STime,1,7) as YearMonth_t2

from demo.OrderList

where substring(STime,1,7)=&＃39;2016-07&＃39; and memberid is not null) t2

on t1.member_t1 = t2.member_t2

group by t1.YearMonth_t1

&＃39;&＃39;&＃39;

df = pd.read_sql_query(sql, engine)

df.head(6)

回购率计算.png

这样就得到了单个月的回购人数

下面利用python循环求出每个月的回购率import arrow

start = arrow.get(&＃39;2016-01&＃39;,&＃39;YYYY-MM&＃39;)

end = arrow.get(&＃39;2017-12&＃39;,&＃39;YYYY-MM&＃39;)

month_list =[each.format(&＃39;YYYY-MM&＃39;) for each in arrow.Arrow.range(&＃39;month&＃39;, start, end)]

month_list

df_col =[&＃39;上个月&＃39;,&＃39;回购人数&＃39;,&＃39;当前月购物人数&＃39;,&＃39;回购率&＃39;]

df_final = pd.DataFrame(columns=df_col)

for i in range(0,len(month_list)-1,1):

YearMonth_AIM_FORMER = month_list[i]

YearMonth_AIM_NOW = month_list[i+1]

sql = &＃39;&＃39;&＃39;

select t1.YearMonth_t1 as 上个月 , count(distinct t2.member_t2 )as 回购人数 ,count(distinct t1.member_t1) 当前月购物人数 ,

count(distinct t2.member_t2 )/count(distinct t1.member_t1) as 回购率

from

(

select distinct memberid as member_t1, substring(STime,1,7) as YearMonth_t1

from demo.OrderList

where substring(STime,1,7)=&＃39;{0}&＃39; and memberid is not null) t1

left join

(

select distinct memberid as member_t2, substring(STime,1,7) as YearMonth_t2

from demo.OrderList

where substring(STime,1,7)=&＃39;{1}&＃39; and memberid is not null) t2

on t1.member_t1 = t2.member_t2

group by t1.YearMonth_t1;

&＃39;&＃39;&＃39;.format(YearMonth_AIM_FORMER,YearMonth_AIM_NOW)

#print(YearMonth_AIM_NOW)

df = pd.read_sql_query(sql, engine)

df_final= df_final.append(df,ignore_index=True)

#print(df)df_final.to_csv(&＃39;回购率.csv&＃39;,encoding=&＃39;utf_8_sig&＃39;)

df_final

每个月的回购率.png

使用pyecharts画图# 官方0.5版本文档 https://05x-docs.pyecharts.org/#/zh-cn/themes

## pip install echarts-themes-pypkg 安装主题

from pyecharts import configure

# 将这行代码置于首部

configure(global_theme="dark")

import pyecharts

from pyecharts import Overlap, Bar, Line, Grid, EffectScatter

grid = Grid()

#grid.use_theme("dark")

v1 = list( df_final.tail(23)[&＃39;当前月购物人数&＃39;].values)

v2 = list(df_final.tail(23)[&＃39;回购人数&＃39;].values)

v3 = list( df_final.tail(23)[&＃39;回购率&＃39;].values*100) #

my_attr = list(df_final.tail(23)[&＃39;上个月&＃39;].values) # ["{}号".format(i) for i in range(1, len(v1)+1)] #attr =

bar = Bar(title="DeepWind超市(南沙区)回购率", title_pos="20%")

bar.add("当前月购物人数", my_attr, v1)

bar.add("回购人数",my_attr,v2,yaxis_formatter=" 人",

yaxis_max=5200,

legend_pos="25%",

legend_orient="horizontal",

legend_top="15%",

)

overlap = Overlap(回购率",my_attr,v3,yaxis_formatter=" %",yaxis_max=100)

es = EffectScatter()

#overlap = Overlap(", my_attr, v3, effect_scale=8,is_add_yaxis=True, yaxis_index=1,yaxis_max=4000)

#overlap.add(es)

grid.add(overlap, grid_right="20%")

grid.render()

overlap.render()

#bar

grid

#overlap

图.png

使用tableau画出仪表盘

tableau回购率仪表盘.png

https://www.jianshu.com/p/ee16baa0fdc3