只需要输入当日时间，就可以得到当日、上周同日、30日日均的漏斗图，图片如下
from pyechartsimport optionsas opts
from pyecharts.chartsimport Funnel
from pyecharts.fakerimport Faker
import numpyas np
import datetime
import pandasas pd
import webbrowser
import pymysql
##输入当日时间
time ='2021/08/02'
time_lastweek = (datetime.datetime.strptime(time,"%Y/%m/%d")-datetime.timedelta(days=7)).strftime("%Y/%m/%d")
db = pymysql.connect(host="1ghfoc48gd0-7csowqjfug5w.cn-shenzhen.datalakeanalytics.aliyuncs.com",
user="dladev_s1708083130941856",
password="xlKs96AKfV1s57WvJ1", port=10000, database='ods', charset='utf8')
date_group = time.split('/')#拆分字符，用于查询
date_group_lastweek=time_lastweek.split('/')
data =list()
#title = ['商品详情页uv', '填写详情页uv', '支付选择页uv', '提交资料页uv', '免密签约页uv']
##当日和上周同日
sql_lst = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2])]
cursor = db.cursor()
for sqlin sql_lst:
cursor.execute(sql)
columns_names = ['num']
df = pd.DataFrame(cursor.fetchall(), columns=columns_names)
data.append(int(df['num'][0]))
#title = ['落地页：', '填写资料页：', '选择支付页：', '提交资料页：', '签约页：']
#30日均
time_lastmonth = (datetime.datetime.strptime(time, "%Y/%m/%d") - datetime.timedelta(days=29)).strftime("%Y/%m/%d")
sql_lst1 = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",
"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')"]
cursor = db.cursor()
for sqlin sql_lst1:
cursor.execute(sql)
columns_names = ['num']
df = pd.DataFrame(cursor.fetchall(), columns=columns_names)
data.append(round((df['num'][0])/30))
title = [' 落地页：', ' 填写资料页：', '选择支付页：', '提交资料页：', '签约页：']
print(data)
###画漏斗图
save_path ='C:/Users/EDZ/Desktop/工作/2021.08.06/' + time.replace('/', '') +'Funnel.html'
rate1=format((data[0]-data[5])/data[5], '.2%')
rate2=format((data[0]-data[10])/data[10], '.2%')
Title = [ title+'\n 当日：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% \n 上周同日：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% \n 30日均：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% '% (data[idx], (data[idx]/data[idx-1])*100, (data[idx]/data[0])*100,data[idx+5], (data[idx+5]/data[idx-1+5])*100, (data[idx+5]/data[0+5])*100,data[idx+10], (data[idx+10]/data[idx-1+10])*100, (data[idx+10]/data[0+10])*100)if idx >0 else title+'\n 当日： ' +str(data[0]) +'\n 上周同日： ' +str(data[5]) +' 同比变化率：'+str(rate1)+'\n 30日均： ' +str(data[10]) +' 同比变化率：'+str(rate2)for title, idxin zip(title, range(len(data)))]
c = (
Funnel(init_opts=opts.InitOpts(width="1400px"))
.add('Test', [list(z)for zin zip(Title, data)], sort_='none')
.set_global_opts(title_opts=opts.TitleOpts(title=time+' 新用户访问漏斗图'),
legend_opts=opts.LegendOpts(is_show=False))
.render(save_path)
)
webbrowser.open(save_path)
