| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141 | # Author renyupeng# coding=utf-8# @Time    : 2022/9/19 5:11 下午# @Site    : # @File    : KuaishouOperationPerformanceBase.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbProConn import TidbProConnclass KuaishouOperationPerformanceBase:    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.kuaishou_operation_performance_base       select stat_date,       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(charge,2) as chargefrom (         select stat_date,                account_id,                charge,                transferor_one,                project_id,                project_name,                company_id,                company_name         from (                  select stat_date,                         account_id,                         charge,                         transferor_one,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_two = ''                           order by stat_date) t                  where stat_date >= {start_date}                    and stat_date <= date_format(transferor_date_one, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         assignee_one,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_two = ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_one, '%Y%m%d')                    and stat_date <= {enddate}                  union all-- 二次转户                  select stat_date,                         account_id,                         charge,                         transferor_one,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_two != ''                             and transferor_date_three = ''                           order by stat_date) t                  where stat_date >= {start_date}                    and stat_date <= date_format(transferor_date_one, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         transferor_two,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_two != ''                             and transferor_date_three = ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_one, '%Y%m%d')                    and stat_date <= date_format(transferor_date_two, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         assignee_two,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_two != ''                             and transferor_date_three = ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_two, '%Y%m%d')                    and stat_date <= {enddate}-- 三次转户                  union all                  select stat_date,                         account_id,                         charge,                         transferor_one,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_three != ''                           order by stat_date) t                  where stat_date >= {start_date}                    and stat_date <= date_format(transferor_date_one, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         transferor_two,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_three != ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_one, '%Y%m%d')                    and stat_date <= date_format(transferor_date_two, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         transferor_three,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_three != ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_two, '%Y%m%d')                    and stat_date <= date_format(transferor_date_three, '%Y%m%d')                  union all                  select stat_date,                         account_id,                         charge,                         assignee_three,                         project_id,                         project_name,                         company_id,                         company_name                  from (                           select report.account_id as account_id,                                  charge,                                  stat_date,                                  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,                                  assignee_three                           from (select account_id,                                        charge,                                        stat_date,                                        user_id,                                        project_id,                                        project_name,                                        company_id,                                        company_name                                 from application.kuaishou_account_report_daily_dw                                 where stat_date 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_two = 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(date_sub({start_date},interval 1 day),'%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 charge > 0                             and transferor_date_three != ''                           order by stat_date) t                  where stat_date > date_format(transferor_date_three, '%Y%m%d')                    and stat_date <= {enddate}) t         group by stat_date, account_id, charge-- 无转户         union all         select stat_date,                a.account_id,                charge,                user_id,                project_id,                project_name,                company_id,                company_name         from (select stat_date,                      account_id,                      charge,                      user_id,                      project_id,                      project_name,                      company_id,                      company_name               from application.kuaishou_account_report_daily_dw               where stat_date between {start_date} and {enddate}) a                  left join              (                  select account_id                  from `jeecg-boot`.ctop_transfer_account                  where   transferor_date between date_format(date_sub({start_date},interval 1 day),'%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 charge > 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.idwhere     user.id  is not null               group by stat_date, account_id, charge            """.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 -1if __name__ == '__main__':    KuaishouOperationPerformanceBase().handler()
 |