from model.DataBaseUtils import MysqlUtils, CkUtils
from datetime import datetime, timedelta, timezone
from model.DateUtils import DateUtils
import logging
import time
db = MysqlUtils()
ck = CkUtils()
dt = DateUtils()
def platform_data_sum(ymd):
logging.info('dw_daily_platform_cost开始数据更新')
ck.execute("alter table game_data.dw_daily_platform_cost drop partition '{}' ".format(ymd))
sql = f'''
insert into game_data.dw_daily_platform_cost
select * from game_data.dw_daily_channel_cost b where dt='{ymd}'
'''
ck.execute(sql)
logging.info('dw_daily_platform_cost数据更新，结束')
def dw_daily_channel_cost_ck_gamedatasum(ymd):
logging.info("run> dw_daily_channel_cost")
datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).timetuple()
# datatime_str
ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).strftime('%Y-%m-%d')
ymd_tom_after = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=2)).strftime('%Y-%m-%d')
ymd_seven_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=7)).strftime('%Y-%m-%d')
ymd_fifteen_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=15)).strftime('%Y-%m-%d')
ymd_thirty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=30)).strftime('%Y-%m-%d')
ymd_fortyfive_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=45)).strftime('%Y-%m-%d')
ymd_sixty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=60)).strftime('%Y-%m-%d')
# timestamp
timestamp_ymd = time.mktime(datatime_ymd)
timestamp_tom = time.mktime(datatime_ymd_tom)
ck.execute(f"alter table game_data.dw_daily_channel_cost drop partition '{ymd}' ")
# TODO:创建角色量有问题
ck_sql = f'''
insert into game_data.dw_daily_channel_cost
(dt,channel,pitcher,stage,platform,book,view_count,click_count,follow_user,cost,
web_view_count,platform_view_count,web_order_count,type,require_roi,require_mult,
reg_num,create_user_num,today_active_user_rate,second_stay_rate,third_stay_rate,
seven_stay_rate,fifteen_stay_rate,thirty_stay_rate,fortyfive_stay_rate,
sixty_stay_rate,game_user_sum)
select x.dt,x.channel,pitcher,stage,x.platform,x.book,
ifnull(view_count,0),ifnull(click_count,0),
ifnull(follow_user,0),ifnull(cost,0)/100 as cost,
ifnull(web_view_count,0) web_view_count,
ifnull(platform_view_count,0) platform_view_count,
ifnull(web_order_count,0) web_order_count,
if(stage ='趣程15期' or stage ='趣程26期' or stage ='趣程30期','GDT','MP') type
,0 require_roi,0 require_mult,
ifnull(y.reg_num,0),ifnull(w.create_user_num,0),
v.today_active_user_rate,
v.second_stay_rate,
v.third_stay_rate,
v.seven_stay_rate,
v.fifteen_stay_rate,
v.thirty_stay_rate,
v.fortyfive_stay_rate,
v.sixty_stay_rate,
v.game_user_sum
from
( select dt, channel,stage,pitcher,platform,book from CostSourceData.channel_info_daily final
where dt='{ymd}' and channel !=''
and channel in
(select distinct(channel) from CostSourceData.channel_by_account_daily final
where dt='{ymd}'
and (type ='GDT' or type='MP')
)
) x -- 只允许渠道MP、GDT
left join
( select channel,
sum(cost) as cost,
sum(view_count) as view_count,
sum(valid_click_count) as click_count,
sum(from_follow_uv) as follow_user,
sum(web_view_count) as web_view_count,
sum(platform_view_count) as platform_view_count,
sum(web_order_count) as web_order_count
from
(select account_id,cost,view_count,valid_click_count,
round(valid_click_count*official_account_follow_rate,0) as from_follow_uv,
0 as web_view_count,
0 as platform_view_count,
0 as web_order_count
from CostSourceData.daily_vx final
where date=toDateTime('{ymd} 00:00:00')
union all
select account_id,cost,view_count,valid_click_count,from_follow_uv,
ifnull(web_commodity_page_view_count,0) as web_view_count,
ifnull(platform_page_view_count,0) as platform_view_count,
ifnull(web_order_count,0) as web_order_count
from CostSourceData.daily_qq final
where date=toDateTime('{ymd} 00:00:00')
) a
left join
(select toString(account_id) account_id,channel
from CostSourceData.channel_by_account_daily final
where dt=toDate('{ymd}')) b
on a.account_id=b.account_id group by channel)
z on x.channel=z.channel -- 只允许渠道cost 消耗
left join
(
select channel,wx_date,count(*) reg_num
from (
select h.name as channel ,toDate(create_time) wx_date
from
GameDataSum.h_member_sum origin left join
(select a.name name,d.app_id app_id ,d.agent_id agent_id
from ( select * from CostSourceData.advertiser_vx final) a
left join GameDataSum.h_game_sum b on a.book = b.name
left join GameDataSum.mp_mp_conf_sum c on a.name= c.wx_name
left join GameDataSum.mp_conf_agent_sum d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name,d.app_id ,d.agent_id ) h
on origin.app_id = h.app_id and origin.agent_id = h.agent_id
where h.name is not null
and origin.create_time > {timestamp_ymd} and origin.create_time < {timestamp_tom}
) tmp
group by channel, wx_date
) y on x.channel= y.channel
--- 注册用户数
left join
(
select channel, wx_date,
count(*) as create_user_num from
(select h.name as channel,DATE(FROM_UNIXTIME(c.create_time)) as wx_date
from ( select * from GameDataSum.h_mg_role_sum where create_time >= {timestamp_ymd} ) a
left join GameDataSum.h_mem_game_sum b on a.mg_mem_id = b.id
left join (select * from GameDataSum.h_member_sum
where create_time >= {timestamp_ymd} and create_time <= {timestamp_tom} ) c
on b.mem_id = c.id
left join
(select a.name name ,d.app_id app_id ,d.agent_id agent_id
from ( select * from CostSourceData.advertiser_vx final) a
left join GameDataSum.h_game_sum b on a.book = b.name
left join GameDataSum.mp_mp_conf_sum c on a.name= c.wx_name
left join GameDataSum.mp_conf_agent_sum d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name,d.app_id ,d.agent_id ) h
on c.app_id = h.app_id and c.agent_id = h.agent_id
where h.name is not null
) xx
group by channel,wx_date
) w on x.channel= w.channel
-----创建角色数
left join
( select channel ,
if(max(d_ct)=0,0,ifnull(max(e_ct),0)/max(d_ct)) as today_active_user_rate,
if(sum(a_ct)=0,0,ifnull(sum(b_ct),0)/sum(a_ct)) as second_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(c_ct),0)/sum(a_ct)) as third_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(seven_ct),0)/sum(a_ct)) as seven_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fifteen_ct),0)/sum(a_ct)) as fifteen_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(thirty_ct),0)/sum(a_ct)) as thirty_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fortyfive_ct),0)/sum(a_ct)) as fortyfive_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(sixty_ct),0)/sum(a_ct)) as sixty_stay_rate,
max(d_ct) game_user_sum from
(select h.name as channel ,
a.ct as a_ct,b.ct as b_ct,c.ct as c_ct,d.ct as d_ct,e.ct as e_ct,
seven.ct as seven_ct,fifteen.ct as fifteen_ct,thirty.ct as thirty_ct,
fortyfive.ct as fortyfive_ct,sixty.ct as sixty_ct
from
(select a.name as name,d.app_id as app_id ,d.agent_id as agent_id
from ( select * from CostSourceData.advertiser_vx final) a
left join GameDataSum.h_game_sum b on a.book = b.name
left join GameDataSum.mp_mp_conf_sum c on a.name= c.wx_name
left join GameDataSum.mp_conf_agent_sum d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name ,d.app_id ,d.agent_id) h
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) a on a.app_id = h.app_id and a.agent_id = h.agent_id
----第一天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_tom}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) b on h.app_id =b.app_id and h.agent_id =b.agent_id
----第二天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_tom_after}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) c on h.app_id =c.app_id and h.agent_id =c.agent_id
--第三天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_seven_day}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) seven on h.app_id = seven.app_id and h.agent_id = seven.agent_id
--第七天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_fifteen_day}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) fifteen on h.app_id =fifteen.app_id and h.agent_id = fifteen.agent_id
--第15天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_thirty_day}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) thirty on h.app_id =thirty.app_id and h.agent_id = thirty.agent_id
--第30天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_fortyfive_day}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) fortyfive on h.app_id =fortyfive.app_id and h.agent_id = fortyfive.agent_id
--第45天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
( select * from GameDataSum.h_member_sum
where create_time >={timestamp_ymd}
and create_time <={timestamp_tom}
) a
left join
(select DISTINCT mem_id mem_id
from GameDataSum.h_log_mem_login_sum
where date = '{ymd_sixty_day}'
) b on a.id=b.mem_id
where b.mem_id !=''
group by a.app_id ,a.agent_id
) sixty on h.app_id =sixty.app_id and h.agent_id = sixty.agent_id
--第60天
left join
(select app_id ,count(*) as ct from GameDataSum.h_member_sum hm
where create_time <={timestamp_tom}
group by app_id ) d on h.app_id =d.app_id
--游戏用户数量
left join
(select count(distinct(mem_id)) as ct,app_id from GameDataSum.h_log_mem_login_sum
where date = '{ymd}'
group by app_id ) e on h.app_id =e.app_id
--今日活跃用户数量
)
as keep_data
group by channel) v on x.channel= v.channel
'''
print(ck_sql)
ck.execute(ck_sql)
def dw_daily_channel_cost_ck(ymd):
def table_name(datatime_tmp, datatime_realtime):
datatime_use = min(datatime_tmp, datatime_realtime)
str_year = datatime_use.tm_year
str_mon = datatime_use.tm_mon
str_mon = str_mon if str_mon > 9 else '0' + str(str_mon)
res = 'h_log_mem_login_{}{}'.format(str_year, str_mon)
return res
logging.info("run> dw_daily_channel_cost")
datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).timetuple()
datatime_ymd_tom_after = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=2)).timetuple()
datatime_ymd_seven_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=7)).timetuple()
datatime_ymd_fifteen_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=15)).timetuple()
datatime_ymd_thirty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=30)).timetuple()
datatime_ymd_fortyfive_day = (
datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=45)).timetuple()
datatime_ymd_sixty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=60)).timetuple()
datatime_realtime = datetime.now().timetuple()
# datatime_str
ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).strftime('%Y-%m-%d')
ymd_tom_after = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=2)).strftime('%Y-%m-%d')
ymd_seven_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=7)).strftime('%Y-%m-%d')
ymd_fifteen_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=15)).strftime('%Y-%m-%d')
ymd_thirty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=30)).strftime('%Y-%m-%d')
ymd_fortyfive_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=45)).strftime('%Y-%m-%d')
ymd_sixty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=60)).strftime('%Y-%m-%d')
# timestamp
timestamp_ymd = time.mktime(datatime_ymd)
timestamp_tom = time.mktime(datatime_ymd_tom)
# table_name
table_name_login_today = table_name(datatime_ymd, datatime_realtime)
table_name_login_tom = table_name(datatime_ymd_tom, datatime_realtime)
table_name_login_tom_after = table_name(datatime_ymd_tom_after, datatime_realtime)
table_name_login_seven_day = table_name(datatime_ymd_seven_day, datatime_realtime)
table_name_login_fifteen_day = table_name(datatime_ymd_fifteen_day, datatime_realtime)
table_name_login_thirty_day = table_name(datatime_ymd_thirty_day, datatime_realtime)
table_name_login_fortyfive_day = table_name(datatime_ymd_fortyfive_day, datatime_realtime)
table_name_login_sixty_day = table_name(datatime_ymd_sixty_day, datatime_realtime)
database_names = {'GameDataTwoDbMpPart', 'GameDataOneDbMpPart'}
for database_name in database_names:
ck_sql = f'''
insert into game_data.dw_daily_channel_cost
(dt,channel,pitcher,stage,platform,book,view_count,click_count,follow_user,cost,
web_view_count,platform_view_count,web_order_count,type,require_roi,require_mult,
reg_num,create_user_num,today_active_user_rate,second_stay_rate,third_stay_rate,
seven_stay_rate,fifteen_stay_rate,thirty_stay_rate,fortyfive_stay_rate,
sixty_stay_rate,game_user_sum)
select x.dt,x.channel,pitcher,stage,x.platform,x.book,
ifnull(view_count,0),ifnull(click_count,0),
ifnull(follow_user,0),ifnull(cost,0)/100 as cost,
ifnull(web_view_count,0) web_view_count,
ifnull(platform_view_count,0) platform_view_count,
ifnull(web_order_count,0) web_order_count,
if(stage ='趣程15期' or stage ='趣程26期' or stage ='趣程30期','GDT','MP') type
,0 require_roi,0 require_mult,
ifnull(y.reg_num,0),ifnull(w.create_user_num,0),
v.today_active_user_rate,
v.second_stay_rate,
v.third_stay_rate,
v.seven_stay_rate,
v.fifteen_stay_rate,
v.thirty_stay_rate,
v.fortyfive_stay_rate,
v.sixty_stay_rate,
v.game_user_sum
from
( select dt, channel,stage,pitcher,platform,book from CostSourceData.channel_info_daily cid
where dt='{ymd}' and channel !=''
and channel in
(select distinct(channel) from CostSourceData.channel_by_account_daily cbad
where dt='{ymd}'
and (type ='GDT' or type='MP')
)
) x -- 只允许渠道MP、GDT
left join
( select channel,
sum(cost) as cost,
sum(view_count) as view_count,
sum(valid_click_count) as click_count,
sum(from_follow_uv) as follow_user,
sum(web_view_count) as web_view_count,
sum(platform_view_count) as platform_view_count,
sum(web_order_count) as web_order_count
from
(select account_id,cost,view_count,valid_click_count,
round(valid_click_count*official_account_follow_rate,0) as from_follow_uv,
0 as web_view_count,
0 as platform_view_count,
0 as web_order_count
from CostSourceData.daily_vx
where date=toDateTime('{ymd} 00:00:00')
union all
select account_id,cost,view_count,valid_click_count,from_follow_uv,
ifnull(web_commodity_page_view_count,0) as web_view_count,
ifnull(platform_page_view_count,0) as platform_view_count,
ifnull(web_order_count,0) as web_order_count
from CostSourceData.daily_qq
where date=toDateTime('{ymd} 00:00:00')
) a
left join
(select toString(account_id) account_id,channel
from CostSourceData.channel_by_account_daily
where dt=toDate('{ymd}')) b
on a.account_id=b.account_id group by channel)
z on x.channel=z.channel -- 只允许渠道cost 消耗
left join
(
select channel,wx_date,count(*) reg_num
from (
select h.name as channel ,toDate(create_time) wx_date
from
{database_name}.h_member origin left join
(select a.name name,d.app_id app_id ,d.agent_id agent_id from CostSourceData.advertiser_vx a
left join {database_name}.h_game b on a.book = b.name
left join {database_name}.mp_mp_conf c on a.name= c.wx_name
left join {database_name}.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name,d.app_id ,d.agent_id ) h
on origin.app_id = h.app_id and origin.agent_id = h.agent_id
where h.name is not null
and origin.create_time > {timestamp_ymd} and origin.create_time < {timestamp_tom}
) tmp
group by channel, wx_date
) y on x.channel= y.channel
--- 注册用户数
left join
(
select channel, wx_date,
count(*) as create_user_num from
(select h.name as channel,DATE(FROM_UNIXTIME(c.create_time)) as wx_date
from {database_name}.h_mg_role a
left join {database_name}.h_mem_game b on a.mg_mem_id = b.id
left join {database_name}.h_member c on b.mem_id = c.id
left join
(select a.name name ,d.app_id app_id ,d.agent_id agent_id from CostSourceData.advertiser_vx a
left join {database_name}.h_game b on a.book = b.name
left join {database_name}.mp_mp_conf c on a.name= c.wx_name
left join {database_name}.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name,d.app_id ,d.agent_id ) h
on c.app_id = h.app_id and c.agent_id = h.agent_id
where h.name is not null
and c.create_time >= {timestamp_ymd} and c.create_time <= {timestamp_tom}
) xx
group by channel,wx_date
) w on x.channel= w.channel
-----创建角色数
left join
( select channel ,
if(max(d_ct)=0,0,ifnull(max(e_ct),0)/max(d_ct)) as today_active_user_rate,
if(sum(a_ct)=0,0,ifnull(sum(b_ct),0)/sum(a_ct)) as second_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(c_ct),0)/sum(a_ct)) as third_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(seven_ct),0)/sum(a_ct)) as seven_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fifteen_ct),0)/sum(a_ct)) as fifteen_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(thirty_ct),0)/sum(a_ct)) as thirty_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fortyfive_ct),0)/sum(a_ct)) as fortyfive_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(sixty_ct),0)/sum(a_ct)) as sixty_stay_rate,
max(d_ct) game_user_sum from
(select h.name as channel ,
a.ct as a_ct,b.ct as b_ct,c.ct as c_ct,d.ct as d_ct,e.ct as e_ct,
seven.ct as seven_ct,fifteen.ct as fifteen_ct,thirty.ct as thirty_ct,
fortyfive.ct as fortyfive_ct,sixty.ct as sixty_ct
from
(select a.name as name,d.app_id as app_id ,d.agent_id as agent_id
from CostSourceData.advertiser_vx a
left join {database_name}.h_game b on a.book = b.name
left join {database_name}.mp_mp_conf c on a.name= c.wx_name
left join {database_name}.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by a.name ,d.app_id ,d.agent_id) h
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_today}
where date = '{ymd}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) a on a.app_id = h.app_id and a.agent_id = h.agent_id
----第一天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_tom}
where date = '{ymd_tom}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) b on h.app_id =b.app_id and h.agent_id =b.agent_id
----第二天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_tom_after}
where date = '{ymd_tom_after}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) c on h.app_id =c.app_id and h.agent_id = c.agent_id
--第三天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_seven_day}
where date = '{ymd_seven_day}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) seven on h.app_id = seven.app_id and h.agent_id = seven.agent_id
--第七天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_fifteen_day}
where date = '{ymd_fifteen_day}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) fifteen on h.app_id =fifteen.app_id and h.agent_id = fifteen.agent_id
--第15天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_thirty_day}
where date = '{ymd_thirty_day}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) thirty on h.app_id =thirty.app_id and h.agent_id = thirty.agent_id
--第30天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_fortyfive_day}
where date = '{ymd_fortyfive_day}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) fortyfive on h.app_id =fortyfive.app_id and h.agent_id = fortyfive.agent_id
--第45天
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) ct from
{database_name}.h_member a
left join
(select toUInt64(mem_id) mem_id from
(select Distinct(mem_id) mem_id,date
from {database_name}.{table_name_login_sixty_day}
where date = '{ymd_sixty_day}'
) ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id !=0
group by a.app_id ,a.agent_id
) sixty on h.app_id =sixty.app_id and h.agent_id = sixty.agent_id
--第60天
left join
(select app_id ,count(*) as ct from {database_name}.h_member hm
where create_time <={timestamp_tom}
group by app_id ) d on h.app_id =d.app_id
--游戏用户数量
left join
(select count(distinct(mem_id)) as ct,app_id from {database_name}.{table_name_login_today}
where date = '{ymd}'
group by app_id ) e on h.app_id =e.app_id
--今日活跃用户数量
)
as keep_data
group by channel) v on x.channel= v.channel
'''
print(ck_sql)
ck.execute(ck_sql)
def dw_daily_channel_cost(ymd):
def table_name(datatime_tmp, datatime_realtime):
datatime_use = min(datatime_tmp, datatime_realtime)
str_year = datatime_use.tm_year
str_mon = datatime_use.tm_mon
str_mon = str_mon if str_mon > 9 else '0' + str(str_mon)
res = 'h_log_mem_login_{}{}'.format(str_year, str_mon)
return res
logging.info("run> dw_daily_channel_cost")
datatime_ymd = datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))).timetuple()
datatime_ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).timetuple()
datatime_ymd_tom_after = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=2)).timetuple()
datatime_ymd_seven_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=7)).timetuple()
datatime_ymd_fifteen_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=15)).timetuple()
datatime_ymd_thirty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=30)).timetuple()
datatime_ymd_fortyfive_day = (
datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=45)).timetuple()
datatime_ymd_sixty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=60)).timetuple()
datatime_realtime = datetime.now().timetuple()
# datatime_str
ymd_tom = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=1)).strftime('%Y-%m-%d')
ymd_tom_after = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=2)).strftime('%Y-%m-%d')
ymd_seven_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=7)).strftime('%Y-%m-%d')
ymd_fifteen_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=15)).strftime('%Y-%m-%d')
ymd_thirty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=30)).strftime('%Y-%m-%d')
ymd_fortyfive_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=45)).strftime('%Y-%m-%d')
ymd_sixty_day = (datetime.strptime(ymd, '%Y-%m-%d').astimezone(timezone(timedelta(hours=8))) + timedelta(
days=60)).strftime('%Y-%m-%d')
# timestamp
timestamp_ymd = time.mktime(datatime_ymd)
timestamp_tom = time.mktime(datatime_ymd_tom)
# table_name
table_name_login_today = table_name(datatime_ymd, datatime_realtime)
table_name_login_tom = table_name(datatime_ymd_tom, datatime_realtime)
table_name_login_tom_after = table_name(datatime_ymd_tom_after, datatime_realtime)
table_name_login_seven_day = table_name(datatime_ymd_seven_day, datatime_realtime)
table_name_login_fifteen_day = table_name(datatime_ymd_fifteen_day, datatime_realtime)
table_name_login_thirty_day = table_name(datatime_ymd_thirty_day, datatime_realtime)
table_name_login_fortyfive_day = table_name(datatime_ymd_fortyfive_day, datatime_realtime)
table_name_login_sixty_day = table_name(datatime_ymd_sixty_day, datatime_realtime)
sql = f"""
select x.dt,x.channel,pitcher,stage,x.platform,x.book,
ifnull(view_count,0),ifnull(click_count,0),
ifnull(follow_user,0),ifnull(cost,0)/100 as cost,
ifnull(web_view_count,0) web_view_count,
ifnull(platform_view_count,0) platform_view_count,
ifnull(web_order_count,0) web_order_count,
if(stage ='趣程15期' or stage ='趣程26期' or stage ='趣程30期','GDT','MP') type
,0 require_roi,0 require_mult,
ifnull(y.reg_num,0),ifnull(w.create_user_num,0),
v.today_active_user_rate,
v.second_stay_rate,
v.third_stay_rate,
v.seven_stay_rate,
v.fifteen_stay_rate,
v.thirty_stay_rate,
v.fortyfive_stay_rate,
v.sixty_stay_rate,
v.game_user_sum
from
( select dt, channel,stage,pitcher,platform,book from channel_info_daily cid
where dt='{ymd}' and channel !=''
and channel in
(select distinct(channel) from channel_by_account_daily cbad
where dt='{ymd}'
and (type ='GDT' or type='MP')
)
) x -- 只允许渠道MP、GDT
left join
(select channel,sum(cost) as cost,sum(view_count) as view_count,sum(valid_click_count) as click_count,
sum(from_follow_uv) as follow_user,
sum(web_view_count) as web_view_count,
sum(platform_view_count) as platform_view_count,
sum(web_order_count) as web_order_count
from
(select account_id,cost,view_count,valid_click_count,
round(valid_click_count*official_account_follow_rate,0) as from_follow_uv,
0 as web_view_count,
0 as platform_view_count,
0 as web_order_count
from daily_vx where date='{ymd} 00:00:00'
union
select account_id,cost,view_count,valid_click_count,from_follow_uv,
ifnull(web_commodity_page_view_count,0) as web_view_count,
ifnull(platform_page_view_count,0) as platform_view_count,
ifnull(web_order_count,0) as web_order_count
from daily_qq where date='{ymd} 00:00:00' ) a
left join
(select account_id,channel from channel_by_account_daily where dt='{ymd}') b
on a.account_id=b.account_id group by channel)
z on x.channel=z.channel
left join
(
select h.name as channel ,DATE(FROM_UNIXTIME(origin.create_time)) as wx_date,
count(*) as reg_num
from
db_mp.h_member origin left join
(select a.name,d.app_id,d.agent_id from quchen_text.advertiser_vx a
left join db_mp.h_game b on a.book = b.name
left join db_mp.mp_mp_conf c on a.name= c.wx_name
left join db_mp.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by d.app_id ,d.agent_id ) h
on origin.app_id = h.app_id and origin.agent_id = h.agent_id
where h.name is not null
and origin.create_time > {timestamp_ymd} and origin.create_time < {timestamp_tom}
group by name,wx_date
) y on x.channel= y.channel
left join
(
select h.name as channel,DATE(FROM_UNIXTIME(c.create_time)) as wx_date,
count(*) as create_user_num
from db_mp.h_mg_role a
left join db_mp.h_mem_game b on a.mg_mem_id = b.id
left join db_mp.h_member c on b.mem_id = c.id
left join
(select a.name,d.app_id,d.agent_id from quchen_text.advertiser_vx a
left join db_mp.h_game b on a.book = b.name
left join db_mp.mp_mp_conf c on a.name= c.wx_name
left join db_mp.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by d.app_id ,d.agent_id ) h
on c.app_id = h.app_id and c.agent_id = h.agent_id
where h.name is not null
and c.create_time >= {timestamp_ymd} and c.create_time <= {timestamp_tom}
group by h.name,wx_date
order by wx_date desc
) w on x.channel= w.channel
left join
(
select channel ,
if(max(d_ct)=0,0,ifnull(max(e_ct),0)/max(d_ct)) as today_active_user_rate,
if(sum(a_ct)=0,0,ifnull(sum(b_ct),0)/sum(a_ct)) as second_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(c_ct),0)/sum(a_ct)) as third_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(seven_ct),0)/sum(a_ct)) as seven_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fifteen_ct),0)/sum(a_ct)) as fifteen_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(thirty_ct),0)/sum(a_ct)) as thirty_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(fortyfive_ct),0)/sum(a_ct)) as fortyfive_stay_rate,
if(sum(a_ct)=0,0,ifnull(sum(sixty_ct),0)/sum(a_ct)) as sixty_stay_rate,
max(d_ct) game_user_sum from
(select h.name as channel ,
a.ct as a_ct,b.ct as b_ct,c.ct as c_ct,d.ct as d_ct,e.ct as e_ct,
seven.ct as seven_ct,fifteen.ct as fifteen_ct,thirty.ct as thirty_ct,
fortyfive.ct as fortyfive_ct,sixty.ct as sixty_ct
from
(select a.name as name,d.app_id as app_id ,d.agent_id as agent_id
from quchen_text.advertiser_vx a
left join db_mp.h_game b on a.book = b.name
left join db_mp.mp_mp_conf c on a.name= c.wx_name
left join db_mp.mp_conf_agent d on c.id=d.advertiser_conf_id
where d.app_id =b.id
group by d.app_id ,d.agent_id) h
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_today}
where date = '{ymd}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) a on a.app_id = h.app_id and a.agent_id = h.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_tom}
where date = '{ymd_tom}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) b on h.app_id =b.app_id and h.agent_id =b.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_tom_after}
where date = '{ymd_tom_after}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) c on h.app_id =c.app_id and h.agent_id = c.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_seven_day}
where date = '{ymd_seven_day}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) seven on h.app_id =seven.app_id and h.agent_id = seven.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_fifteen_day}
where date = '{ymd_fifteen_day}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) fifteen on h.app_id =fifteen.app_id and h.agent_id = fifteen.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_thirty_day}
where date = '{ymd_thirty_day}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) thirty on h.app_id =thirty.app_id and h.agent_id = thirty.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_fortyfive_day}
where date = '{ymd_fortyfive_day}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) fortyfive on h.app_id =fortyfive.app_id and h.agent_id = fortyfive.agent_id
left join
(select '{ymd}',a.app_id,a.agent_id,count(*) as ct from
db_mp.h_member a
left join (select distinct(mem_id) from db_mp.{table_name_login_sixty_day}
where date = '{ymd_sixty_day}' ) b on a.id=b.mem_id
where a.create_time >={timestamp_ymd} and a.create_time <={timestamp_tom}
and b.mem_id is not null
group by a.app_id ,a.agent_id ) sixty on h.app_id =sixty.app_id and h.agent_id = sixty.agent_id
left join
(select app_id ,count(*) as ct from db_mp.h_member hm
where create_time <={timestamp_tom}
group by app_id ) d on h.app_id =d.app_id
left join
(select count(distinct(mem_id)) as ct,app_id from db_mp.{table_name_login_today}
where date = '{ymd}'
group by app_id ) e on h.app_id =e.app_id
) as keep_data
group by channel)
v on x.channel= v.channel
"""
# print(sql)
data = db.quchen_text.get_data_list(sql)
data1 = []
col = "dt,channel,pitcher,stage,platform,book,view_count,click_count,follow_user,cost,web_view_count,platform_view_count,web_order_count,type,require_roi,require_mult,reg_num,create_user_num,today_active_user_rate,second_stay_rate,third_stay_rate,seven_stay_rate,fifteen_stay_rate,thirty_stay_rate,fortyfive_stay_rate,sixty_stay_rate,game_user_sum"
for i in data:
i[0] = str(i[0])
i[9] = str(i[9])
i[6] = float(i[6])
i[7] = float(i[7])
i[8] = float(i[8])
i[9] = float(i[9])
i[10] = float(i[10])
i[11] = float(i[11])
i[12] = float(i[12])
i[18] = float(i[18]) if i[18] else 0
i[19] = float(i[19]) if i[19] else 0
i[20] = float(i[20]) if i[20] else 0
i[21] = float(i[21]) if i[21] else 0
i[22] = float(i[22]) if i[22] else 0
i[23] = float(i[23]) if i[23] else 0
i[24] = float(i[24]) if i[24] else 0
i[25] = float(i[25]) if i[25] else 0
i[26] = float(i[26]) if i[26] else 0
data1.append(tuple(i))
for _ in data1:
print(_)
ck.execute(f"alter table game_data.dw_daily_channel_cost drop partition '{ymd}' ")
logging.info(len(data1))
ck.insertMany("game_data.dw_daily_channel_cost", col, tuple(data1))
def channel_by_account_daily(ymd):
"""返回当天消耗账户对应的公众号表"""
logging.info("run> channel_by_account_daily")
sql = """replace into channel_by_account_daily
select '{0}' as dt,a.account_id as account_id, ifnull(ifnull(b.name,a.name),'') as channel,type from
(select account_id,name,'GDT' type from advertiser_qq
union
select account_id,name,'MP' type from advertiser_vx
union
select advertiser_id,channel,'BYTEDANCE' type from advertiser_bytedance
) a
left join
(select b.account_id,b.name from
(select min(end_time) as end_time,account_id from account_change where end_time>'{0}' GROUP BY account_id) a
left join account_change b on a.end_time=b.end_time and a.account_id=b.account_id) b on a.account_id=b.account_id""".format(
ymd)
db.quchen_text.execute(sql)
def channel_info_daily(ymd):
"""获取公众号某天的期数，投手，平台，书籍
@ return [[]]
"""
# 获取现在的全量公众号信息
logging.info("run> channel_info_daily")
sql = f"""select '{ymd}' as dt,a.name ,ifnull(stage,''),ifnull(pitcher,''),ifnull(platform,''),ifnull(book,'') from (
select name from advertiser_vx where name is not null group by name-- 公众号全量表
union
select name from account_change group by name
union
select channel as name from pitcher_change group by channel
union
select name from platform_change group by name
union
select name from book_change group by name) a
left join
( select name,ifnull(stage,'') stage,ifnull(pitcher,'') pitcher,
ifnull(platform,'') platform,ifnull(book,'') book
from advertiser_vx
where name is not null
and start_date <= '{ymd}'
and if(end_date is null,1,end_date >= '{ymd}')
group by name,stage,pitcher,platform,book
) b on a.name=b.name
"""
data = db.quchen_text.get_data_list(sql)
pitcher_change = db.quchen_text.getData(
"select b.channel as channel,pitcher from "
"(select max(start_time) as start_time,channel from pitcher_change "
" where start_time<='{}' GROUP BY channel) a"
" left join pitcher_change b on a.start_time=b.start_time and a.channel=b.channel".format(ymd))
platform_change = db.quchen_text.getData(
"select b.name as channel,current_platform as platform from (select max(change_date) as change_date,name from platform_change "
"where change_date<='{}' GROUP BY name) a "
"left join platform_change b on a.change_date=b.change_date and a.name=b.name".format(ymd))
book_change = db.quchen_text.getData(
"select b.name as channel,book from (select max(start_time) as start_time,name from book_change "
"where start_time<='{}' GROUP BY name) a "
"left join book_change b on a.start_time=b.start_time and a.name=b.name".format(ymd))
stage_change = db.quchen_text.getData(
"select channel,stage from (select max(start_date) as start_date,channel from stage_change "
"where start_date<='{}' GROUP BY channel) a "
"left join stage_change using(start_date,channel)".format(ymd))
for i in data:
for j in pitcher_change:
if i[1] == j[0]:
i[3] = j[1]
for k in platform_change:
if i[1] == k[0]:
i[4] = k[1]
for h in book_change:
if i[1] == h[0]:
i[5] = h[1]
for m in stage_change:
if i[1] == m[0]:
i[2] = m[1]
insert_sql = "replace into channel_info_daily values (%s,%s,%s,%s,%s,%s) "
db.quchen_text.executeMany(insert_sql, data)
def ods_order(dt):
sql = """ replace into ods_order
select
case platform when '掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d')
when '掌读' then from_unixtime(order_time, '%Y-%m-%d')
ELSE order_time end date,
stage,platform,channel,channel_id,user_id,
case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s')
when platform='掌读' then from_unixtime(order_time, '%Y-%m-%d %H:%i:%s')
ELSE order_time end order_time,
case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(reg_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s')
when platform='掌读' then from_unixtime(reg_time, '%Y-%m-%d %H:%i:%s')
ELSE reg_time end reg_time,
amount,from_novel,order_id,2 from `order` where date=UNIX_TIMESTAMP('{}')
""".format(dt)
db.quchen_text.execute(sql)
def order_account_text():
db.quchen_text.execute("truncate order_account_text")
with open('./wending_account_config.csv', encoding='utf-8') as f:
for i in f.readlines():
db.quchen_text.execute("insert into order_account_text(platform,text) values ('文鼎','{}')".format(i))
if __name__ == '__main__':
# channel_info_daily('2021-02-06')
# dw_daily_channel_cost('2021-09-19')
# exit()
# channel_by_account_daily('2021-02-05')
for i in dt.getDateLists('2021-09-01', '2022-01-02'):
# print(i)
# channel_by_account_daily(i)
# channel_info_daily(i)
#
# dw_daily_channel_cost(i)
# # ods_order('2021-05-06')
# platform_data_sum(i)
# dw_daily_channel_cost_ck(i)
dw_daily_channel_cost_ck_gamedatasum(i)