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

本文链接：https://blog.youkuaiyun.com/weixin_42300424/article/details/113460449

这篇博客介绍了如何使用Python连接MySQL数据库，查询订单表来计算每月的回购率，并利用PyEcharts绘制图表以及Tableau创建仪表盘展示结果。通过SQL查询获取6月和7月的购物会员数据，使用LEFT JOIN找到公共部分以计算回购率，然后通过循环计算所有月份的回购率并存储到CSV文件中。最后，展示了PyEcharts的柱状图和折线图以及Tableau的仪表盘来直观呈现数据。

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

查询订单表from sqlalchemy import text

import pandas as pd

from sqlalchemy import create_engine

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

sql = '''

select *

from demo.OrderList

limit 8

'''

df = pd.read_sql_query(sql, engine)

wAAACwAAAAAAQABAEACAkQBADs=

查询订单表.png

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

先查询201606的购物会员sql = '''

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

from demo.OrderList

where substring(STime,1,7)='2016-06' and memberid is not null

'''

df = pd.read_sql_query(sql, engine)

df.head(3)

wAAACwAAAAAAQABAEACAkQBADs=

6月份购物的会员.png

同理，得到201607的会员

wAAACwAAAAAAQABAEACAkQBADs=

7月分购物的会员.png

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

学习一下left join,想一想为什么要用left join sql = '''

select * from

(

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

from demo.OrderList

where substring(STime,1,7)='2016-06' 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)='2016-07' and memberid is not null) t2

on t1.member_t1 = t2.member_t2

'''

df = pd.read_sql_query(sql, engine)

df.head(6)

wAAACwAAAAAAQABAEACAkQBADs=

学习leftjoin.png

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

wAAACwAAAAAAQABAEACAkQBADs=

学习inner join.png

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

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

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)='2016-06' 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)='2016-07' and memberid is not null) t2

on t1.member_t1 = t2.member_t2

group by t1.YearMonth_t1

'''

df = pd.read_sql_query(sql, engine)

df.head(6)

wAAACwAAAAAAQABAEACAkQBADs=

回购率计算.png

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

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

start = arrow.get('2016-01','YYYY-MM')

end = arrow.get('2017-12','YYYY-MM')

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

month_list

df_col =['上个月','回购人数','当前月购物人数','回购率']

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 = '''

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)='{0}' 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)='{1}' and memberid is not null) t2

on t1.member_t1 = t2.member_t2

group by t1.YearMonth_t1;

'''.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('回购率.csv',encoding='utf_8_sig')

df_final

wAAACwAAAAAAQABAEACAkQBADs=

每个月的回购率.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)['当前月购物人数'].values)

v2 = list(df_final.tail(23)['回购人数'].values)

v3 = list( df_final.tail(23)['回购率'].values*100) #

my_attr = list(df_final.tail(23)['上个月'].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(width=1200, height=600)

overlap.add(bar)

line = Line()

line.add("回购率",my_attr,v3,yaxis_formatter=" %",yaxis_max=100)

es = EffectScatter()

#overlap = Overlap(width=1200, height=600)

overlap.add(line, is_add_yaxis=True, yaxis_index=1)

#es.add("", 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

wAAACwAAAAAAQABAEACAkQBADs=

图.png

使用tableau画出仪表盘

wAAACwAAAAAAQABAEACAkQBADs=

tableau回购率仪表盘.png

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