AppBytedanceFileVideoGetAll.py 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/12 10:20 上午
  4. # @Site :
  5. # @File : AppBytedanceFileVideoGetAll.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbConn import TidbConn
  11. class AppBytedanceFileVideoGetAll:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. def execSql(self):
  15. try:
  16. sql = """select advertiser_id from media_api.bytedance_file_video_get
  17. where advertiser_id not in( 1692563803312136
  18. ,1692637066177544
  19. ,1692734941630472
  20. ,1692919157092360
  21. ,1692919454960648
  22. ,1693090923839496
  23. ,1693287768425480
  24. ,1693371801573384
  25. ,1693645309136904
  26. ,1693646227489800
  27. ,1675624751056904
  28. ,1675897101465608
  29. ,1675966454540296
  30. ,1676144418511880
  31. ,1676146667275272
  32. ,1676146667733000
  33. ,1676158160337928
  34. ,1676160382397448
  35. ,1676160398628872
  36. ,1676174942213128
  37. ,1676238756104200
  38. ,1676238936945672
  39. ,1676238997632008
  40. ,1676248136759304
  41. ,1680134193021960
  42. ,1680134195534856
  43. ,1694380520355912
  44. ,1694549540573256
  45. ,1694899066896456
  46. ,1694899069003848
  47. ,1694921245395016
  48. ,1695181755089992
  49. ,1695624946691144
  50. ,1695911235206216
  51. ,1695917862054984
  52. ,1696271580378184
  53. ,1696450109157448
  54. ,1697101870281800
  55. ,1698173272088648
  56. ,1698173272620104
  57. ,1699005215444040
  58. ,1699005217742920
  59. ,1686925399771144
  60. ,1686940056873992
  61. ,1687382871519240
  62. ,1678412146397192
  63. ,1678412673520648
  64. ,1678514382264328
  65. ,1678514435981320
  66. ,1678530058967048
  67. ,1678607875737608
  68. ,1678607876493320
  69. ,1678686557119496
  70. ,1678698933368840
  71. ,1678793871814664
  72. ,1678981078319112
  73. ,1679046312948744
  74. ,1679057371357192
  75. ,1679058875884552
  76. ,1679058877053960
  77. ,1679058879179784
  78. ,1680328994143240
  79. ,1680329724018696
  80. ,1680330066545672
  81. ,1680333650512904
  82. ,1680429009277960
  83. ,1680429010550792
  84. ,1680607875876872
  85. ,1680615894628360
  86. ,1680615980883976
  87. ,1680686560990216
  88. ,1680699732403208
  89. ,1680700307234824
  90. ,1680958833185800
  91. ,1681959185387528
  92. ,1682325850689544
  93. ,1659129457351688
  94. ,1659671532474376
  95. ,1659671534049288
  96. ,1659761301687304
  97. ,1660021208560648
  98. ,1660118711622664
  99. ,1660224075803656
  100. ,1657752565305352
  101. ,1647082799292424
  102. ,1642650353354760
  103. ,1643182957272072
  104. ,1647357966212104
  105. ,1649600311463944
  106. ,1661032681287688
  107. ,1680960648659976
  108. ,1680960649124872
  109. ,1680960649680904
  110. ,1685039399776264
  111. ,1685040324937736
  112. ,1685119400797192
  113. ,1685397434055688
  114. ,1685397673158664
  115. ,1685398133282824
  116. ,1685398894616584
  117. ,1685496802777096
  118. ,1685674825499656
  119. ,1685680373642248
  120. ,1686048178232328
  121. ,1674104647669768
  122. ,1674104844878856
  123. ,1680154030893064
  124. ,1680155352301576
  125. ,1680156755411976
  126. ,1680157011605512
  127. ,1662649622597640
  128. ,1663324042320904
  129. ,1663580542178312
  130. ,1664190085903368
  131. ,1664483120077832
  132. ,1677163693824008
  133. ,1677423799008264
  134. ,1677790088899592
  135. ,1677804088429576
  136. ,1677805199019016
  137. ,1677805372313608
  138. ,1677879495698440
  139. ,1674104853174280
  140. ,1674171899055112
  141. ,1674207407305736
  142. ,1674787460646920
  143. ,1674876450068488
  144. ,1675258486124552
  145. ,1675328261704712
  146. ,1675328432451592
  147. ,1675357103466504
  148. ,1675357222158344
  149. ,1675433775660040
  150. ,1675434231118856
  151. ,1675440463578120
  152. ,1662315864805384
  153. ,1667355822132232
  154. ,1667383047046152
  155. ,1699005189262344
  156. ,1699005189629960
  157. ,1699069251329032
  158. ,1699070416954376
  159. ,1699176293489672
  160. ,1699178867517448
  161. ,1699178898611208
  162. ,1699178902209544
  163. ,1699178932030472
  164. ,1699178934716424
  165. ,1699178935058440
  166. ,1699178967121928
  167. ,1699178967849992
  168. ,1699178968937480
  169. ,1699178969719816
  170. ,1699179005802504
  171. ,1693648283614216
  172. ,1693749110958088
  173. ,1693838101081096
  174. ,1693992130286600
  175. ,1694262773463048
  176. ,1694262775836680
  177. ,1694380519416840
  178. ,1694549539502088
  179. ,1694551694275592
  180. ,1694551694694408
  181. ,1694899068499976
  182. ,1694899069956104
  183. ,1694916352734216
  184. ,1695181753601032
  185. ,1695286330947592
  186. ,1695289737409544
  187. ,1667383048419336
  188. ,1667628253771784
  189. ,1668362864699400
  190. ,1668363441143816
  191. ,1668462265963528
  192. ,1668647109188616
  193. ,1664572637977608
  194. ,1664572640795656
  195. ,1676522999475208
  196. ,1676609233712136
  197. ,1676621437855752
  198. ,1676621463243784
  199. ,1676887049021448
  200. ,1676889501405192
  201. ,1677161972011016
  202. ,1664657972670472
  203. ,1696812411444224
  204. ,1697042156446720
  205. ,1697093543567360
  206. ,1697563089007616
  207. ,1700814502879232
  208. ,1700996738596872
  209. ,1700996771790856
  210. ,1700996772779016
  211. ,1701344689370120
  212. ,1701344689795080
  213. ,1701344791797768
  214. ,1701354414043144
  215. ,1701354415115272
  216. ,1701515699546120
  217. ,1677881687625736
  218. ,1678154283884552
  219. ,1678156679964680
  220. ,1666450659604488
  221. ,1666450809847816
  222. ,1666468921720840
  223. ,1666468922339336
  224. ,1666468923172872
  225. ,1666737320386568
  226. ,1667010026365960
  227. ,1667284088260616
  228. ,1667355820689416
  229. ,1663462166252552
  230. ,1663465141356552
  231. ,1663465141771272
  232. ,1663478415067144
  233. ,1661468300385288
  234. ,1661468301021192
  235. ,1686199625508872
  236. ,1686223506414600
  237. ,1686295852123144
  238. ,1686384860112904
  239. ,1686396863192072
  240. ,1686672934834184
  241. ,1687571097467912
  242. ,1687575634449416
  243. ,1687758256277512
  244. ,1687758257210376
  245. ,1688956776310792
  246. ,1692028892532744
  247. ,1692030182490120
  248. ,1692030183074824
  249. ,1665199963924488
  250. ,1665199965210632
  251. ,1666450658743304
  252. ,1669631848525832
  253. ,1669714891201544
  254. ,1673174999797768
  255. ,1673610862935048
  256. ,1673625556031496
  257. ,1673637398661128
  258. ,1673637400100872
  259. ,1673725537539080
  260. ,1673882012014600
  261. ,1674063201838088
  262. ,1674073270884360
  263. ,1682331799449608
  264. ,1682777245861896
  265. ,1699178932728904
  266. ,1699179057252424
  267. ,1700909316228168
  268. ,1700909943928904
  269. ,1700973278115912
  270. ,1700973278749768
  271. ,1700996773734472
  272. ,1700996856859720
  273. ,1682331801013320
  274. ,1683678576485448
  275. ,1683688695033928
  276. ,1684392886743112
  277. ,1684482416298056
  278. ,1666544266112072
  279. ,1661032679433288
  280. ,1664483118068808
  281. ,1669631845292104
  282. ,1672178052130888
  283. ,1672973220712520
  284. ,1674267932503112
  285. ,1676790190091336
  286. ,1678424950236232
  287. ,1678981170777160
  288. ,1680310008577096
  289. ,1682863918754824
  290. ,1682954679165960
  291. ,1683150141399048
  292. ,1683150155469832
  293. ,1683596230583304
  294. ,1683676478297096
  295. ,1683688773458952
  296. ,1683698727450632
  297. ,1683711016125448
  298. ,1683773704515592
  299. ,1683863981569032
  300. ,1683986894505992
  301. ,1685024266033160
  302. ,1685039309556744
  303. ,1674080733656072
  304. ,1675440465991688
  305. ,1661470914582552
  306. ,1697101866478616
  307. ,1699179008606232
  308. ,1700996853782552
  309. ,1663580541247496
  310. ,1663580541731848
  311. ,1653782641200136
  312. ,1680424713121864
  313. ,1680960031028296
  314. ,1680960650622984
  315. ,1681052473134088
  316. ,1681142487460872
  317. ,1681221581751304
  318. ,1681228459565128
  319. ,1681328868763656
  320. ,1681328870930440
  321. ,1681584659663880
  322. ,1681593093277704
  323. ,1681669007158280
  324. ,1681669102385224
  325. ,1675556806279304
  326. ,1674270341313672
  327. ,1678335716586632
  328. ,1686214747293832
  329. ,1694262776784008
  330. ,1694920924935304
  331. ,1695289737874440
  332. ,1695713009090696
  333. ,1695713011267592
  334. ,1696450109948936
  335. ,1697101867696136
  336. ,1697101919712264
  337. ,1697174845285384
  338. ,1697181501474824
  339. ,1698007808972808
  340. ,1698089060301832
  341. ,1698359674963080
  342. ,1699178868581512
  343. ,1699178966755464
  344. ,1699179006616584
  345. ,1699179054103560
  346. ,1699988640021512
  347. ,1699988676399112
  348. ,1700973281325064
  349. ,1700992248720392
  350. ,1700992249153544
  351. ,1700992273628168
  352. ,1700992274629640
  353. ,1700992276939784
  354. ,1700992277457928
  355. ,1700996736486408
  356. ,1700996737583112
  357. ,1686672637332552
  358. ,1686940056312904
  359. ,1687561758944328
  360. ,1687575633998920
  361. ,1687745794538568
  362. ,1688214709517384
  363. ,1689483059947592
  364. ,1689835831483464
  365. ,1692014612021320
  366. ,1692384596841544
  367. ,1692724765758536
  368. ,1692740174489672
  369. ,1692931265520712
  370. ,1693089846361160
  371. ,1693749112287304
  372. ,1694262775299144
  373. ,1692118648930312
  374. ,1692118991378440
  375. ,1692384307770376
  376. ,1692384595229704
  377. ,1692477701573640
  378. ,1692561163061256
  379. ,1710388635843592
  380. ,1707223171318792
  381. ,1707223191343112
  382. ,1707223191782408
  383. ,1707223194847240
  384. ,1707245910848520
  385. ,1707315134063624
  386. ,1710388734011400
  387. ,1710388736285704
  388. ,1712032308204680
  389. ,1709680629018696
  390. ,1705066764729416
  391. ,1702793025527880
  392. ,1702793028208712
  393. ,1701879301855304
  394. ,1705066685058120
  395. ,1705066710650952
  396. ,1710388594685000
  397. ,1710388635398216
  398. ,1710388636832840
  399. ,1714136817430600
  400. ,1702616355965080
  401. ,1713300419193864
  402. ,1713305311602696
  403. ,1713469227381768
  404. ,1714136819636232
  405. ,1714136820654088
  406. ,1714136870778888
  407. ,1714753778210824
  408. ,1714753778670600
  409. ,1715213747268616
  410. ,1715479724023816
  411. ,1707315152598024
  412. ,1707315154012168
  413. ,1707315154450440
  414. ,1707315171046408
  415. ,1707315173419016
  416. ,1708321952773128
  417. ,1708321957831688
  418. ,1708324513278984
  419. ,1708591696262152
  420. ,1705066712855560
  421. ,1705066734150664
  422. ,1705066735745032
  423. ,1705066765207560
  424. ,1705166066883592
  425. ,1701879359621256
  426. ,1701963077656712
  427. ,1702616355352584
  428. ,1702618206226440
  429. ,1704409435250696
  430. ,1701879360194568
  431. ,1701879302488072
  432. ,1701879303041032
  433. ,1701879334363144
  434. ,1704410312196104
  435. ,1704410337222664
  436. ,1704410339264520
  437. ,1705066687095816
  438. ,1705066689989640
  439. ,1705066711730184
  440. ,1706052947061768
  441. ,1706052947591304
  442. ,1706052950653960
  443. ,1707223153526792
  444. ,1710148825060360
  445. ,1710388592682120
  446. ,1710388594204680
  447. ,1710388609250312
  448. ,1666543802579979
  449. ,1667679220285451
  450. ,1660667273996292
  451. ,1699178966417421
  452. ,1699178969324557
  453. ,1690820510779405
  454. ,1675438939139085
  455. ,1678414029860877
  456. ,1661042079535117
  457. ,1674634579279885
  458. ,1650263841162253
  459. ,1674898647952397
  460. ,1675258730414093
  461. ,1678959320699917
  462. ,1678959391271949
  463. ,1681669009084429
  464. ,1683676478859341
  465. ,1689936923505677
  466. ,1699179008972877
  467. ,1699179092969485
  468. ,1699722996787213
  469. ,1700100298581005
  470. ,1704409436038157
  471. ,1704409438412813
  472. ,1700259083992077
  473. ,1700259084559373
  474. ,1700973280693261
  475. ,1700973281951757
  476. ,1700992244786253
  477. ,1700992248236109
  478. ,1700992276000781
  479. ,1700996736067661
  480. ,1700996809942093
  481. ,1701879301362701
  482. ,1701879361255437
  483. ,1701963078173837
  484. ,1702621140872205
  485. ,1702793026134029
  486. ,1702793027456013
  487. ,1704409432898573
  488. ,1690820509557773
  489. ,1692747682954253
  490. ,1692747684445197
  491. ,1694295238968333
  492. ,1695713008288845
  493. ,1695713009903693
  494. ,1695713010571277
  495. ,1696831654935565
  496. ,1696831655485453
  497. ,1697101916133453
  498. ,1697447026849805
  499. ,1699003030460429
  500. ,1699178901449741
  501. ,1705433861124109
  502. ,1706052946515981
  503. ,1705066686054413
  504. ,1705066686554125
  505. ,1705066736272397
  506. ,1705066764207117
  507. ,1706052996864141
  508. ,1706052999055373
  509. ,1706407154199629
  510. ,1681148988597262
  511. ,1682488572147726
  512. ,1683594410662030
  513. ,1697628460992526
  514. ,1698089061213198
  515. ,1699005190033422
  516. ,1702621139376142
  517. ,1702793026707470
  518. ,1704062283386894
  519. ,1704409436621838
  520. ,1705066685557774
  521. ,1705066687610894
  522. ,1705066689452046
  523. ,1699178899009550
  524. ,1699178899817486
  525. ,1699178968547342
  526. ,1699722995174414
  527. ,1699722996207630
  528. ,1700175629278222
  529. ,1700177646859278
  530. ,1700973279400974
  531. ,1700973280043022
  532. ,1700992246816782
  533. ,1700992275550222
  534. ,1701878931886094
  535. ,1701963078586382
  536. ,1683676479341582
  537. ,1686927742317582
  538. ,1686927743401998
  539. ,1688285366202382
  540. ,1688956102024206
  541. ,1695181753059342
  542. ,1696450107331598
  543. ,1696543753895950
  544. ,1696831656069134
  545. ,1705066712296462
  546. ,1698359675409486
  547. ,1702621138298958
  548. ,1699005218135118
  549. ,1699722995722318
  550. ,1701354502185038
  551. ,1686744063525966
  552. ,1686927742875726
  553. ,1692747685698638
  554. ,1705066763651150
  555. ,1684774905463838
  556. ,1705066733616142
  557. ,1705066734709774
  558. ,1705066736862222
  559. ,1705066737462286
  560. ,1705612376620046
  561. ,1696831656944654
  562. ,1697101869802510
  563. ,1697101920214030
  564. ,1683690331851790
  565. ,1678414029198350
  566. ,1678429997736974
  567. ,1678981367885838
  568. ,1668464420106254
  569. ,1669181787105294
  570. ,1673358822909966
  571. ,1675440464386062
  572. ,1680616215823374
  573. ,1700996810393751
  574. ,1660666858777607
  575. ,1705066760964103
  576. ,1705066761970695
  577. ,1705611373687815
  578. ,1706052944837767
  579. ,1706052945892359
  580. ,1706052997449735
  581. ,1706052997965831
  582. ,1707055231045639
  583. ,1707055690074119
  584. ,1707152951972871
  585. ,1661042078996487
  586. ,1664657972166663
  587. ,1666543657457671
  588. ,1671015349173255
  589. ,1674063202781191
  590. ,1674898544464903
  591. ,1677163693115399
  592. ,1678155396642823
  593. ,1678411706704903
  594. ,1679058877607943
  595. ,1683594409340935
  596. ,1686835166313479
  597. ,1696831657412615
  598. ,1697101916675079
  599. ,1697436286240775
  600. ,1702621140350983
  601. ,1704409434053639
  602. ,1705066688955399
  603. ,1705066733125639
  604. ,1705066735217671
  605. ,1697447027396615
  606. ,1697447027991559
  607. ,1699005188486151
  608. ,1699005188923399
  609. ,1699178933414023
  610. ,1700156294363143
  611. ,1700992275080199
  612. ,1700996738066439
  613. ,1700996858369031
  614. ,1701879336066055
  615. ,1701879361778695
  616. ,1701963077243015
  617. ,1702613211262983
  618. ,1702621138757639
  619. ,1686836793708551
  620. ,1688285467119623
  621. ,1688285736560647
  622. ,1688666544989191
  623. ,1688955779857415
  624. ,1690820510209031
  625. ,1692028891068423
  626. ,1692028891783175
  627. ,1692747685258247
  628. ,1695181750846471
  629. ,1695828405060615
  630. ,1696368945704967
  631. ,1696543753597959
  632. ,1642648003678215
  633. ,1676611386980423
  634. ,1696831658005575
  635. ,1704076576607303
  636. ,1705066688201799
  637. ,1700996857927751
  638. ,1701878930303047
  639. ,1706052948182087
  640. )
  641. group by advertiser_id"""
  642. account_list = TidbConn.quary(sql)
  643. for account_id in account_list:
  644. try:
  645. sql = """
  646. replace into application.app_bytedance_file_video_get
  647. select advertiser_id,
  648. id,
  649. size,
  650. width,
  651. height,
  652. url,
  653. format,
  654. v.signature,
  655. poster_url,
  656. bit_rate,
  657. duration,
  658. material_id,
  659. source,
  660. create_time,
  661. filename,
  662. labels,
  663. video_url,
  664. case when tag_category_id is null then '' else tag_category_id end as tag_category_id,
  665. tag_category_name,
  666. case when parent_id is null then '' else parent_id end as parent_id,
  667. parent_name,
  668. case when tag_code is null then '' else tag_code end as tag_code,
  669. tag_name,
  670. clip_id,
  671. clip_name,
  672. shot_id,
  673. shot_name,
  674. plan_id,
  675. plan_name,
  676. leader_id,
  677. leader_name,
  678. material_innovate,
  679. hot.stat_datetime,
  680. effect.stat_datetime
  681. from (
  682. select advertiser_id,
  683. id,
  684. size,
  685. width,
  686. height,
  687. url,
  688. format,
  689. signature,
  690. poster_url,
  691. bit_rate,
  692. duration,
  693. a.material_id,
  694. source,
  695. create_time,
  696. case when t.material_name is null then a.filename else t.material_name end as filename,
  697. labels,
  698. video_url,
  699. clip_id,
  700. clip_name,
  701. shot_id,
  702. shot_name,
  703. plan_id,
  704. plan_name,
  705. leader_id,
  706. leader_name,
  707. material_innovate
  708. from (select advertiser_id,
  709. id,
  710. size,
  711. width,
  712. height,
  713. url,
  714. format,
  715. signature,
  716. poster_url,
  717. bit_rate,
  718. duration,
  719. material_id,
  720. source,
  721. create_time,
  722. filename,
  723. labels,
  724. video_url
  725. from media_api.bytedance_file_video_get where advertiser_id={account_id}
  726. ) a
  727. left join
  728. (select material_id,
  729. clip_id,
  730. clip_name,
  731. shot_id,
  732. shot_name,
  733. plan_id,
  734. plan_name,
  735. leader_id,
  736. leader_name,
  737. material_name,
  738. material_innovate
  739. from (select cma.material_id,
  740. clip_id,
  741. clip.realname as clip_name,
  742. shot_id,
  743. shot.realname as shot_name,
  744. plan_id,
  745. plan.realname as plan_name,
  746. cma.leader_id,
  747. leader.realname as leader_name,
  748. material_name,
  749. material_innovate
  750. from `jeecg-boot`.ctop_material_ascription cma
  751. left join
  752. `jeecg-boot`.ctop_material_info b
  753. on cma.material_id = b.id
  754. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  755. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  756. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  757. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  758. ) v) t
  759. on a.signature = t.material_id) v
  760. left join
  761. (select code,
  762. tag_category_id,
  763. tag_category_name,
  764. parent_id,
  765. parent_name,
  766. group_concat(tag_code order by tag_code separator '-') tag_code,
  767. group_concat(tag_name order by tag_code separator '-') tag_name
  768. from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name
  769. from `jeecg-boot`.ctop_material_tag_info a
  770. left join
  771. (select if(tag_category_id is null,'',tag_category_id) as tag_category_id,
  772. tag_category_name,
  773. case
  774. when parent_id is not null then parent_id
  775. when tag_category_id = 2 then ''
  776. else tag_code end parent_id,
  777. case
  778. when parent_id = 60155 then '真人'
  779. when parent_id = 60156 then '制作'
  780. when parent_id = 60160 then '外拍场景'
  781. when parent_id = 60161 then '公司内景'
  782. when tag_category_id = 2 then ''
  783. else '其他' end parent_name,
  784. tag_code,
  785. tag_name
  786. from `jeecg-boot`.ctop_tag_info) b
  787. on a.tag_code = b.tag_code) c
  788. group by code, tag_category_id, tag_category_name, parent_id, parent_name) m
  789. on v.signature = m.code
  790. left join (
  791. select stat_datetime, signature, sum
  792. from (
  793. select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
  794. from (
  795. select stat_datetime,
  796. signature,
  797. sum(cost) over (partition by signature order by stat_datetime) as sum
  798. from (
  799. select stat_datetime, video.signature, cost
  800. from (select material_id, signature, create_time
  801. from (select material_id, min(create_time) as create_time, signature
  802. from media_api.bytedance_file_video_get
  803. where material_id is not null and advertiser_id={account_id}
  804. group by material_id, signature) s) video
  805. inner join
  806. (
  807. select stat_datetime, material_id, sum(cost) as cost
  808. from media_api.bytedance_material_report_daily
  809. where image_mode in (
  810. 'CREATIVE_IMAGE_MODE_VIDEO',
  811. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  812. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'
  813. ) and advertiser_id={account_id}
  814. group by stat_datetime, material_id
  815. ) rep
  816. on video.material_id = rep.material_id
  817. 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
  818. where sum >=5000
  819. ) t
  820. where rn = 1
  821. ) effect
  822. on v.signature = effect.signature
  823. left join (
  824. select stat_datetime, signature, sum
  825. from (
  826. select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
  827. from (
  828. select stat_datetime,
  829. signature,
  830. sum(cost) over (partition by signature order by stat_datetime) as sum
  831. from (
  832. select stat_datetime, video.signature, cost
  833. from (select material_id, signature, create_time
  834. from (select material_id, min(create_time) as create_time, signature
  835. from media_api.bytedance_file_video_get
  836. where material_id is not null and advertiser_id={account_id}
  837. group by material_id, signature) s) video
  838. inner join
  839. (
  840. select stat_datetime, material_id, sum(cost) as cost
  841. from media_api.bytedance_material_report_daily
  842. where image_mode in (
  843. 'CREATIVE_IMAGE_MODE_VIDEO',
  844. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  845. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'
  846. ) and advertiser_id={account_id}
  847. group by stat_datetime, material_id
  848. ) rep
  849. on video.material_id = rep.material_id
  850. 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
  851. where sum >=50000
  852. ) t
  853. where rn = 1
  854. ) hot
  855. on v.signature = hot.signature
  856. group by advertiser_id, id, v.signature, material_id, tag_category_id, parent_id, tag_code
  857. """.format(account_id=account_id[0])
  858. print(sql)
  859. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  860. self.conn.commit()
  861. except Exception as e:
  862. logging.error(e)
  863. return -1
  864. TidbConn.conn_close(conn=self.conn)
  865. return 0
  866. except Exception as e:
  867. logging.error(e)
  868. if __name__ == '__main__':
  869. status = AppBytedanceFileVideoGetAll().execSql()
  870. if status == 0:
  871. print("作业执行成功")
  872. exit(0)
  873. else:
  874. print("作业执行失败")
  875. exit(-1)