1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2022/9/19 5:11 下午
- # @Site :
- # @File : BytendaceOperationPerformanceBase.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from Apietl.conn.TidbProConn import TidbProConn
- class BytendaceOperationPerformanceBase:
- def __init__(self):
- self.conn = TidbProConn.get_connection()
- def handler(self):
- start_date_sql = """select concat(date_format(date_sub(now(), interval 1 month),'%Y%m'),'01')"""
- end_date_sql = """select date_format( last_day(date_format(date_sub(now(), interval 1 month),'%Y%m%d')),
- '%Y%m%d') """
- start_date = TidbProConn.quary(self.conn, start_date_sql)[0][0]
- end_date = TidbProConn.quary(self.conn, end_date_sql)[0][0]
- try:
- sql = """
- replace into application.bytedance_operation_performance_base
- select stat_datetime,
- company_id,
- company_name,
- t.project_id,
- project_name,
- user.id as user_id,
- user.realname as user_name,
- leader.id as leader_id,
- leader.realname leader_name,
- account_id,
- case
- when user.status = 1 then '在职'
- else '离职' end as user_status,
- round(cost,2) as cost
- from (
- select stat_datetime,
- account_id,
- cost,
- transferor_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select stat_datetime,
- account_id,
- cost,
- transferor_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- b.transferor_one,
- transferor_date_one,
- assignee_one,
- assignee_date_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_two = ''
- order by stat_datetime) t
- where stat_datetime >= {start_date}
- and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- b.transferor_one,
- transferor_date_one,
- assignee_one,
- assignee_date_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_two = ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
- and stat_datetime <= {enddate}
- union all
- -- 二次转户
- select stat_datetime,
- account_id,
- cost,
- transferor_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_two != ''
- and transferor_date_three = ''
- order by stat_datetime) t
- where stat_datetime >= {start_date}
- and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- transferor_two,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_two != ''
- and transferor_date_three = ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
- and stat_datetime <= date_format(transferor_date_two, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_two != ''
- and transferor_date_three = ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_two, '%Y%m%d')
- and stat_datetime <= {enddate}
- -- 三次转户
- union all
- select stat_datetime,
- account_id,
- cost,
- transferor_one,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- transferor_three,
- transferor_date_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_three != ''
- order by stat_datetime) t
- where stat_datetime >= {start_date}
- and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- transferor_two,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- transferor_three,
- transferor_date_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_three != ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
- and stat_datetime <= date_format(transferor_date_two, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- transferor_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- transferor_three,
- transferor_date_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_three != ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_two, '%Y%m%d')
- and stat_datetime <= date_format(transferor_date_three, '%Y%m%d')
- union all
- select stat_datetime,
- account_id,
- cost,
- transferor_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (
- select report.account_id as account_id,
- cost,
- stat_datetime,
- user_id,
- transferor_one,
- transferor_date_one,
- transferor_two,
- transferor_date_two,
- assignee_one,
- assignee_date_one,
- assignee_two,
- assignee_date_two,
- transferor_three,
- transferor_date_three,
- project_id,
- project_name,
- company_id,
- company_name
- from (select account_id,
- cost,
- stat_datetime,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) report
- left join(
- select account_id,
- transferor_one,
- if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
- if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
- transferor_date_one,
- if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
- if(transferor_date_one = transferor_date_three, '',
- transferor_date_three) as transferor_date_three,
- assignee_one,
- if(assignee_one = assignee_two, '', assignee_two) assignee_two,
- if(assignee_one = assignee_three, '', assignee_three) assignee_three,
- assignee_date_one,
- if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
- if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
- from (
- select account_id,
- substring_index(transferor, ':', 1) as transferor_one,
- replace(
- substring_index(substring_index(transferor, ':', 2), ':', -1),
- ',',
- '') as transferor_two,
- replace(
- substring_index(substring_index(transferor, ':', 3), ':', -1),
- ',',
- '') as transferor_three,
- replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
- replace(
- substring_index(substring_index(transferor_date, ':', 2), ':', -1),
- ',',
- '') as transferor_date_two,
- replace(
- substring_index(substring_index(transferor_date, ':', 3), ':', -1),
- ',',
- '') as transferor_date_three,
- replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
- replace(
- substring_index(substring_index(assignee, ':', 2), ':', -1),
- ',',
- '') as assignee_two,
- replace(
- substring_index(substring_index(assignee, ':', 3), ':', -1),
- ',',
- '') as assignee_three,
- substring_index(assignee_date, ':', 1) as assignee_date_one,
- replace(
- substring_index(substring_index(assignee_date, ':', 2), ':', -1),
- ',',
- '') as assignee_date_two,
- replace(
- substring_index(substring_index(assignee_date, ':', 3), ':', -1),
- ',',
- '') as assignee_date_three
- from (
- select account_id,
- group_concat(transferor, ':') as transferor,
- group_concat(transferor_date, ':') as transferor_date,
- group_concat(assignee, ':') as assignee,
- group_concat(assignee_date, ':') as assignee_date
- from (select account_id,transferor,transferor_date,assignee,assignee_date from
- `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status != 2
- order by transferor_date
- )t
- group by account_id) t
- ) t
- ) b
- on report.account_id = b.account_id
- where b.account_id is not null
- and cost > 0
- and transferor_date_three != ''
- order by stat_datetime) t
- where stat_datetime > date_format(transferor_date_three, '%Y%m%d')
- and stat_datetime <= {enddate}) t
- group by stat_datetime, account_id, cost
- -- 无转户
- union all
- select stat_datetime,
- a.account_id,
- cost,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from (select stat_datetime,
- account_id,
- cost,
- user_id,
- project_id,
- project_name,
- company_id,
- company_name
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between {start_date} and {enddate}) a
- left join
- (
- select account_id
- from `jeecg-boot`.ctop_transfer_account
- where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
- and status = 1
- ) b
- on a.account_id = b.account_id
- where b.account_id is null
- and cost > 0
- ) t
- left join `jeecg-boot`.sys_user user
- on transferor_one = user.id
- left join `jeecg-boot`.sys_user leader
- on user.leader_id = leader.id
- where user.id is not null
- group by stat_datetime, account_id, cost
- """.format(start_date=start_date, enddate=end_date)
- print(sql)
- TidbProConn.upsert(conn=self.conn, sql_buff=sql)
- self.conn.commit()
- TidbProConn.conn_close(conn=self.conn)
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- BytendaceOperationPerformanceBase().handler()
|