# Author renyupeng # coding=utf-8 # @Time : 2021/11/12 10:20 上午 # @Site : # @File : AppBytedanceFileVideoGetAll.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from Apietl.conn.TidbConn import TidbConn class AppBytedanceFileVideoGetAll: def __init__(self): self.conn = TidbConn.get_connection() def execSql(self): try: sql = """select advertiser_id from media_api.bytedance_file_video_get where advertiser_id not in( 1692563803312136 ,1692637066177544 ,1692734941630472 ,1692919157092360 ,1692919454960648 ,1693090923839496 ,1693287768425480 ,1693371801573384 ,1693645309136904 ,1693646227489800 ,1675624751056904 ,1675897101465608 ,1675966454540296 ,1676144418511880 ,1676146667275272 ,1676146667733000 ,1676158160337928 ,1676160382397448 ,1676160398628872 ,1676174942213128 ,1676238756104200 ,1676238936945672 ,1676238997632008 ,1676248136759304 ,1680134193021960 ,1680134195534856 ,1694380520355912 ,1694549540573256 ,1694899066896456 ,1694899069003848 ,1694921245395016 ,1695181755089992 ,1695624946691144 ,1695911235206216 ,1695917862054984 ,1696271580378184 ,1696450109157448 ,1697101870281800 ,1698173272088648 ,1698173272620104 ,1699005215444040 ,1699005217742920 ,1686925399771144 ,1686940056873992 ,1687382871519240 ,1678412146397192 ,1678412673520648 ,1678514382264328 ,1678514435981320 ,1678530058967048 ,1678607875737608 ,1678607876493320 ,1678686557119496 ,1678698933368840 ,1678793871814664 ,1678981078319112 ,1679046312948744 ,1679057371357192 ,1679058875884552 ,1679058877053960 ,1679058879179784 ,1680328994143240 ,1680329724018696 ,1680330066545672 ,1680333650512904 ,1680429009277960 ,1680429010550792 ,1680607875876872 ,1680615894628360 ,1680615980883976 ,1680686560990216 ,1680699732403208 ,1680700307234824 ,1680958833185800 ,1681959185387528 ,1682325850689544 ,1659129457351688 ,1659671532474376 ,1659671534049288 ,1659761301687304 ,1660021208560648 ,1660118711622664 ,1660224075803656 ,1657752565305352 ,1647082799292424 ,1642650353354760 ,1643182957272072 ,1647357966212104 ,1649600311463944 ,1661032681287688 ,1680960648659976 ,1680960649124872 ,1680960649680904 ,1685039399776264 ,1685040324937736 ,1685119400797192 ,1685397434055688 ,1685397673158664 ,1685398133282824 ,1685398894616584 ,1685496802777096 ,1685674825499656 ,1685680373642248 ,1686048178232328 ,1674104647669768 ,1674104844878856 ,1680154030893064 ,1680155352301576 ,1680156755411976 ,1680157011605512 ,1662649622597640 ,1663324042320904 ,1663580542178312 ,1664190085903368 ,1664483120077832 ,1677163693824008 ,1677423799008264 ,1677790088899592 ,1677804088429576 ,1677805199019016 ,1677805372313608 ,1677879495698440 ,1674104853174280 ,1674171899055112 ,1674207407305736 ,1674787460646920 ,1674876450068488 ,1675258486124552 ,1675328261704712 ,1675328432451592 ,1675357103466504 ,1675357222158344 ,1675433775660040 ,1675434231118856 ,1675440463578120 ,1662315864805384 ,1667355822132232 ,1667383047046152 ,1699005189262344 ,1699005189629960 ,1699069251329032 ,1699070416954376 ,1699176293489672 ,1699178867517448 ,1699178898611208 ,1699178902209544 ,1699178932030472 ,1699178934716424 ,1699178935058440 ,1699178967121928 ,1699178967849992 ,1699178968937480 ,1699178969719816 ,1699179005802504 ,1693648283614216 ,1693749110958088 ,1693838101081096 ,1693992130286600 ,1694262773463048 ,1694262775836680 ,1694380519416840 ,1694549539502088 ,1694551694275592 ,1694551694694408 ,1694899068499976 ,1694899069956104 ,1694916352734216 ,1695181753601032 ,1695286330947592 ,1695289737409544 ,1667383048419336 ,1667628253771784 ,1668362864699400 ,1668363441143816 ,1668462265963528 ,1668647109188616 ,1664572637977608 ,1664572640795656 ,1676522999475208 ,1676609233712136 ,1676621437855752 ,1676621463243784 ,1676887049021448 ,1676889501405192 ,1677161972011016 ,1664657972670472 ,1696812411444224 ,1697042156446720 ,1697093543567360 ,1697563089007616 ,1700814502879232 ,1700996738596872 ,1700996771790856 ,1700996772779016 ,1701344689370120 ,1701344689795080 ,1701344791797768 ,1701354414043144 ,1701354415115272 ,1701515699546120 ,1677881687625736 ,1678154283884552 ,1678156679964680 ,1666450659604488 ,1666450809847816 ,1666468921720840 ,1666468922339336 ,1666468923172872 ,1666737320386568 ,1667010026365960 ,1667284088260616 ,1667355820689416 ,1663462166252552 ,1663465141356552 ,1663465141771272 ,1663478415067144 ,1661468300385288 ,1661468301021192 ,1686199625508872 ,1686223506414600 ,1686295852123144 ,1686384860112904 ,1686396863192072 ,1686672934834184 ,1687571097467912 ,1687575634449416 ,1687758256277512 ,1687758257210376 ,1688956776310792 ,1692028892532744 ,1692030182490120 ,1692030183074824 ,1665199963924488 ,1665199965210632 ,1666450658743304 ,1669631848525832 ,1669714891201544 ,1673174999797768 ,1673610862935048 ,1673625556031496 ,1673637398661128 ,1673637400100872 ,1673725537539080 ,1673882012014600 ,1674063201838088 ,1674073270884360 ,1682331799449608 ,1682777245861896 ,1699178932728904 ,1699179057252424 ,1700909316228168 ,1700909943928904 ,1700973278115912 ,1700973278749768 ,1700996773734472 ,1700996856859720 ,1682331801013320 ,1683678576485448 ,1683688695033928 ,1684392886743112 ,1684482416298056 ,1666544266112072 ,1661032679433288 ,1664483118068808 ,1669631845292104 ,1672178052130888 ,1672973220712520 ,1674267932503112 ,1676790190091336 ,1678424950236232 ,1678981170777160 ,1680310008577096 ,1682863918754824 ,1682954679165960 ,1683150141399048 ,1683150155469832 ,1683596230583304 ,1683676478297096 ,1683688773458952 ,1683698727450632 ,1683711016125448 ,1683773704515592 ,1683863981569032 ,1683986894505992 ,1685024266033160 ,1685039309556744 ,1674080733656072 ,1675440465991688 ,1661470914582552 ,1697101866478616 ,1699179008606232 ,1700996853782552 ,1663580541247496 ,1663580541731848 ,1653782641200136 ,1680424713121864 ,1680960031028296 ,1680960650622984 ,1681052473134088 ,1681142487460872 ,1681221581751304 ,1681228459565128 ,1681328868763656 ,1681328870930440 ,1681584659663880 ,1681593093277704 ,1681669007158280 ,1681669102385224 ,1675556806279304 ,1674270341313672 ,1678335716586632 ,1686214747293832 ,1694262776784008 ,1694920924935304 ,1695289737874440 ,1695713009090696 ,1695713011267592 ,1696450109948936 ,1697101867696136 ,1697101919712264 ,1697174845285384 ,1697181501474824 ,1698007808972808 ,1698089060301832 ,1698359674963080 ,1699178868581512 ,1699178966755464 ,1699179006616584 ,1699179054103560 ,1699988640021512 ,1699988676399112 ,1700973281325064 ,1700992248720392 ,1700992249153544 ,1700992273628168 ,1700992274629640 ,1700992276939784 ,1700992277457928 ,1700996736486408 ,1700996737583112 ,1686672637332552 ,1686940056312904 ,1687561758944328 ,1687575633998920 ,1687745794538568 ,1688214709517384 ,1689483059947592 ,1689835831483464 ,1692014612021320 ,1692384596841544 ,1692724765758536 ,1692740174489672 ,1692931265520712 ,1693089846361160 ,1693749112287304 ,1694262775299144 ,1692118648930312 ,1692118991378440 ,1692384307770376 ,1692384595229704 ,1692477701573640 ,1692561163061256 ,1710388635843592 ,1707223171318792 ,1707223191343112 ,1707223191782408 ,1707223194847240 ,1707245910848520 ,1707315134063624 ,1710388734011400 ,1710388736285704 ,1712032308204680 ,1709680629018696 ,1705066764729416 ,1702793025527880 ,1702793028208712 ,1701879301855304 ,1705066685058120 ,1705066710650952 ,1710388594685000 ,1710388635398216 ,1710388636832840 ,1714136817430600 ,1702616355965080 ,1713300419193864 ,1713305311602696 ,1713469227381768 ,1714136819636232 ,1714136820654088 ,1714136870778888 ,1714753778210824 ,1714753778670600 ,1715213747268616 ,1715479724023816 ,1707315152598024 ,1707315154012168 ,1707315154450440 ,1707315171046408 ,1707315173419016 ,1708321952773128 ,1708321957831688 ,1708324513278984 ,1708591696262152 ,1705066712855560 ,1705066734150664 ,1705066735745032 ,1705066765207560 ,1705166066883592 ,1701879359621256 ,1701963077656712 ,1702616355352584 ,1702618206226440 ,1704409435250696 ,1701879360194568 ,1701879302488072 ,1701879303041032 ,1701879334363144 ,1704410312196104 ,1704410337222664 ,1704410339264520 ,1705066687095816 ,1705066689989640 ,1705066711730184 ,1706052947061768 ,1706052947591304 ,1706052950653960 ,1707223153526792 ,1710148825060360 ,1710388592682120 ,1710388594204680 ,1710388609250312 ,1666543802579979 ,1667679220285451 ,1660667273996292 ,1699178966417421 ,1699178969324557 ,1690820510779405 ,1675438939139085 ,1678414029860877 ,1661042079535117 ,1674634579279885 ,1650263841162253 ,1674898647952397 ,1675258730414093 ,1678959320699917 ,1678959391271949 ,1681669009084429 ,1683676478859341 ,1689936923505677 ,1699179008972877 ,1699179092969485 ,1699722996787213 ,1700100298581005 ,1704409436038157 ,1704409438412813 ,1700259083992077 ,1700259084559373 ,1700973280693261 ,1700973281951757 ,1700992244786253 ,1700992248236109 ,1700992276000781 ,1700996736067661 ,1700996809942093 ,1701879301362701 ,1701879361255437 ,1701963078173837 ,1702621140872205 ,1702793026134029 ,1702793027456013 ,1704409432898573 ,1690820509557773 ,1692747682954253 ,1692747684445197 ,1694295238968333 ,1695713008288845 ,1695713009903693 ,1695713010571277 ,1696831654935565 ,1696831655485453 ,1697101916133453 ,1697447026849805 ,1699003030460429 ,1699178901449741 ,1705433861124109 ,1706052946515981 ,1705066686054413 ,1705066686554125 ,1705066736272397 ,1705066764207117 ,1706052996864141 ,1706052999055373 ,1706407154199629 ,1681148988597262 ,1682488572147726 ,1683594410662030 ,1697628460992526 ,1698089061213198 ,1699005190033422 ,1702621139376142 ,1702793026707470 ,1704062283386894 ,1704409436621838 ,1705066685557774 ,1705066687610894 ,1705066689452046 ,1699178899009550 ,1699178899817486 ,1699178968547342 ,1699722995174414 ,1699722996207630 ,1700175629278222 ,1700177646859278 ,1700973279400974 ,1700973280043022 ,1700992246816782 ,1700992275550222 ,1701878931886094 ,1701963078586382 ,1683676479341582 ,1686927742317582 ,1686927743401998 ,1688285366202382 ,1688956102024206 ,1695181753059342 ,1696450107331598 ,1696543753895950 ,1696831656069134 ,1705066712296462 ,1698359675409486 ,1702621138298958 ,1699005218135118 ,1699722995722318 ,1701354502185038 ,1686744063525966 ,1686927742875726 ,1692747685698638 ,1705066763651150 ,1684774905463838 ,1705066733616142 ,1705066734709774 ,1705066736862222 ,1705066737462286 ,1705612376620046 ,1696831656944654 ,1697101869802510 ,1697101920214030 ,1683690331851790 ,1678414029198350 ,1678429997736974 ,1678981367885838 ,1668464420106254 ,1669181787105294 ,1673358822909966 ,1675440464386062 ,1680616215823374 ,1700996810393751 ,1660666858777607 ,1705066760964103 ,1705066761970695 ,1705611373687815 ,1706052944837767 ,1706052945892359 ,1706052997449735 ,1706052997965831 ,1707055231045639 ,1707055690074119 ,1707152951972871 ,1661042078996487 ,1664657972166663 ,1666543657457671 ,1671015349173255 ,1674063202781191 ,1674898544464903 ,1677163693115399 ,1678155396642823 ,1678411706704903 ,1679058877607943 ,1683594409340935 ,1686835166313479 ,1696831657412615 ,1697101916675079 ,1697436286240775 ,1702621140350983 ,1704409434053639 ,1705066688955399 ,1705066733125639 ,1705066735217671 ,1697447027396615 ,1697447027991559 ,1699005188486151 ,1699005188923399 ,1699178933414023 ,1700156294363143 ,1700992275080199 ,1700996738066439 ,1700996858369031 ,1701879336066055 ,1701879361778695 ,1701963077243015 ,1702613211262983 ,1702621138757639 ,1686836793708551 ,1688285467119623 ,1688285736560647 ,1688666544989191 ,1688955779857415 ,1690820510209031 ,1692028891068423 ,1692028891783175 ,1692747685258247 ,1695181750846471 ,1695828405060615 ,1696368945704967 ,1696543753597959 ,1642648003678215 ,1676611386980423 ,1696831658005575 ,1704076576607303 ,1705066688201799 ,1700996857927751 ,1701878930303047 ,1706052948182087 ) group by advertiser_id""" account_list = TidbConn.quary(sql) for account_id in account_list: try: sql = """ replace into application.app_bytedance_file_video_get select advertiser_id, id, size, width, height, url, format, v.signature, poster_url, bit_rate, duration, material_id, source, create_time, filename, labels, video_url, case when tag_category_id is null then '' else tag_category_id end as tag_category_id, tag_category_name, case when parent_id is null then '' else parent_id end as parent_id, parent_name, case when tag_code is null then '' else tag_code end as tag_code, tag_name, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name, material_innovate, hot.stat_datetime, effect.stat_datetime from ( select advertiser_id, id, size, width, height, url, format, signature, poster_url, bit_rate, duration, a.material_id, source, create_time, case when t.material_name is null then a.filename else t.material_name end as filename, labels, video_url, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name, material_innovate from (select advertiser_id, id, size, width, height, url, format, signature, poster_url, bit_rate, duration, material_id, source, create_time, filename, labels, video_url from media_api.bytedance_file_video_get where advertiser_id={account_id} ) a left join (select material_id, clip_id, clip_name, shot_id, shot_name, plan_id, plan_name, leader_id, leader_name, material_name, material_innovate from (select cma.material_id, clip_id, clip.realname as clip_name, shot_id, shot.realname as shot_name, plan_id, plan.realname as plan_name, cma.leader_id, leader.realname as leader_name, material_name, material_innovate from `jeecg-boot`.ctop_material_ascription cma left join `jeecg-boot`.ctop_material_info b on cma.material_id = b.id left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id ) v) t on a.signature = t.material_id) v left join (select code, tag_category_id, tag_category_name, parent_id, parent_name, group_concat(tag_code order by tag_code separator '-') tag_code, group_concat(tag_name order by tag_code separator '-') tag_name from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name from `jeecg-boot`.ctop_material_tag_info a left join (select if(tag_category_id is null,'',tag_category_id) as tag_category_id, tag_category_name, case when parent_id is not null then parent_id when tag_category_id = 2 then '' else tag_code end parent_id, case when parent_id = 60155 then '真人' when parent_id = 60156 then '制作' when parent_id = 60160 then '外拍场景' when parent_id = 60161 then '公司内景' when tag_category_id = 2 then '' else '其他' end parent_name, tag_code, tag_name from `jeecg-boot`.ctop_tag_info) b on a.tag_code = b.tag_code) c group by code, tag_category_id, tag_category_name, parent_id, parent_name) m on v.signature = m.code left join ( select stat_datetime, signature, sum from ( select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn from ( select stat_datetime, signature, sum(cost) over (partition by signature order by stat_datetime) as sum from ( select stat_datetime, video.signature, cost from (select material_id, signature, create_time from (select material_id, min(create_time) as create_time, signature from media_api.bytedance_file_video_get where material_id is not null and advertiser_id={account_id} group by material_id, signature) s) video inner join ( select stat_datetime, material_id, sum(cost) as cost from media_api.bytedance_material_report_daily where image_mode in ( 'CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO' ) and advertiser_id={account_id} group by stat_datetime, material_id ) rep on video.material_id = rep.material_id where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 6 DAY), '%Y%m%d')) t) t where sum >=5000 ) t where rn = 1 ) effect on v.signature = effect.signature left join ( select stat_datetime, signature, sum from ( select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn from ( select stat_datetime, signature, sum(cost) over (partition by signature order by stat_datetime) as sum from ( select stat_datetime, video.signature, cost from (select material_id, signature, create_time from (select material_id, min(create_time) as create_time, signature from media_api.bytedance_file_video_get where material_id is not null and advertiser_id={account_id} group by material_id, signature) s) video inner join ( select stat_datetime, material_id, sum(cost) as cost from media_api.bytedance_material_report_daily where image_mode in ( 'CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO' ) and advertiser_id={account_id} group by stat_datetime, material_id ) rep on video.material_id = rep.material_id where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 13 DAY), '%Y%m%d')) t) t where sum >=50000 ) t where rn = 1 ) hot on v.signature = hot.signature group by advertiser_id, id, v.signature, material_id, tag_category_id, parent_id, tag_code """.format(account_id=account_id[0]) print(sql) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() except Exception as e: logging.error(e) return -1 TidbConn.conn_close(conn=self.conn) return 0 except Exception as e: logging.error(e) if __name__ == '__main__': status = AppBytedanceFileVideoGetAll().execSql() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)