123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883 |
- # 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)
|