BytendaceOperationPerformanceBase.py 78 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/9/19 5:11 下午
  4. # @Site :
  5. # @File : BytendaceOperationPerformanceBase.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbProConn import TidbProConn
  11. class BytendaceOperationPerformanceBase:
  12. def __init__(self):
  13. self.conn = TidbProConn.get_connection()
  14. def handler(self):
  15. start_date_sql = """select concat(date_format(date_sub(now(), interval 1 month),'%Y%m'),'01')"""
  16. end_date_sql = """select date_format( last_day(date_format(date_sub(now(), interval 1 month),'%Y%m%d')),
  17. '%Y%m%d') """
  18. start_date = TidbProConn.quary(self.conn, start_date_sql)[0][0]
  19. end_date = TidbProConn.quary(self.conn, end_date_sql)[0][0]
  20. try:
  21. sql = """
  22. replace into application.bytedance_operation_performance_base
  23. select stat_datetime,
  24. company_id,
  25. company_name,
  26. t.project_id,
  27. project_name,
  28. user.id as user_id,
  29. user.realname as user_name,
  30. leader.id as leader_id,
  31. leader.realname leader_name,
  32. account_id,
  33. case
  34. when user.status = 1 then '在职'
  35. else '离职' end as user_status,
  36. round(cost,2) as cost
  37. from (
  38. select stat_datetime,
  39. account_id,
  40. cost,
  41. transferor_one,
  42. project_id,
  43. project_name,
  44. company_id,
  45. company_name
  46. from (
  47. select stat_datetime,
  48. account_id,
  49. cost,
  50. transferor_one,
  51. project_id,
  52. project_name,
  53. company_id,
  54. company_name
  55. from (
  56. select report.account_id as account_id,
  57. cost,
  58. stat_datetime,
  59. user_id,
  60. b.transferor_one,
  61. transferor_date_one,
  62. assignee_one,
  63. assignee_date_one,
  64. project_id,
  65. project_name,
  66. company_id,
  67. company_name
  68. from (select account_id,
  69. cost,
  70. stat_datetime,
  71. user_id,
  72. project_id,
  73. project_name,
  74. company_id,
  75. company_name
  76. from application.bytedance_advertiser_report_daily_dw
  77. where stat_datetime between {start_date} and {enddate}) report
  78. left join(
  79. select account_id,
  80. transferor_one,
  81. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  82. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  83. transferor_date_one,
  84. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  85. if(transferor_date_one = transferor_date_three, '',
  86. transferor_date_three) as transferor_date_three,
  87. assignee_one,
  88. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  89. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  90. assignee_date_one,
  91. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  92. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  93. from (
  94. select account_id,
  95. substring_index(transferor, ':', 1) as transferor_one,
  96. replace(
  97. substring_index(substring_index(transferor, ':', 2), ':', -1),
  98. ',',
  99. '') as transferor_two,
  100. replace(
  101. substring_index(substring_index(transferor, ':', 3), ':', -1),
  102. ',',
  103. '') as transferor_three,
  104. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  105. replace(
  106. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  107. ',',
  108. '') as transferor_date_two,
  109. replace(
  110. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  111. ',',
  112. '') as transferor_date_three,
  113. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  114. replace(
  115. substring_index(substring_index(assignee, ':', 2), ':', -1),
  116. ',',
  117. '') as assignee_two,
  118. replace(
  119. substring_index(substring_index(assignee, ':', 3), ':', -1),
  120. ',',
  121. '') as assignee_three,
  122. substring_index(assignee_date, ':', 1) as assignee_date_one,
  123. replace(
  124. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  125. ',',
  126. '') as assignee_date_two,
  127. replace(
  128. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  129. ',',
  130. '') as assignee_date_three
  131. from (
  132. select account_id,
  133. group_concat(transferor, ':') as transferor,
  134. group_concat(transferor_date, ':') as transferor_date,
  135. group_concat(assignee, ':') as assignee,
  136. group_concat(assignee_date, ':') as assignee_date
  137. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  138. `jeecg-boot`.ctop_transfer_account
  139. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  140. and status != 2
  141. order by transferor_date
  142. )t
  143. group by account_id) t
  144. ) t
  145. ) b
  146. on report.account_id = b.account_id
  147. where b.account_id is not null
  148. and cost > 0
  149. and transferor_date_two = ''
  150. order by stat_datetime) t
  151. where stat_datetime >= {start_date}
  152. and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
  153. union all
  154. select stat_datetime,
  155. account_id,
  156. cost,
  157. assignee_one,
  158. project_id,
  159. project_name,
  160. company_id,
  161. company_name
  162. from (
  163. select report.account_id as account_id,
  164. cost,
  165. stat_datetime,
  166. user_id,
  167. b.transferor_one,
  168. transferor_date_one,
  169. assignee_one,
  170. assignee_date_one,
  171. project_id,
  172. project_name,
  173. company_id,
  174. company_name
  175. from (select account_id,
  176. cost,
  177. stat_datetime,
  178. user_id,
  179. project_id,
  180. project_name,
  181. company_id,
  182. company_name
  183. from application.bytedance_advertiser_report_daily_dw
  184. where stat_datetime between {start_date} and {enddate}) report
  185. left join(
  186. select account_id,
  187. transferor_one,
  188. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  189. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  190. transferor_date_one,
  191. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  192. if(transferor_date_one = transferor_date_three, '',
  193. transferor_date_three) as transferor_date_three,
  194. assignee_one,
  195. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  196. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  197. assignee_date_one,
  198. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  199. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  200. from (
  201. select account_id,
  202. substring_index(transferor, ':', 1) as transferor_one,
  203. replace(
  204. substring_index(substring_index(transferor, ':', 2), ':', -1),
  205. ',',
  206. '') as transferor_two,
  207. replace(
  208. substring_index(substring_index(transferor, ':', 3), ':', -1),
  209. ',',
  210. '') as transferor_three,
  211. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  212. replace(
  213. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  214. ',',
  215. '') as transferor_date_two,
  216. replace(
  217. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  218. ',',
  219. '') as transferor_date_three,
  220. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  221. replace(
  222. substring_index(substring_index(assignee, ':', 2), ':', -1),
  223. ',',
  224. '') as assignee_two,
  225. replace(
  226. substring_index(substring_index(assignee, ':', 3), ':', -1),
  227. ',',
  228. '') as assignee_three,
  229. substring_index(assignee_date, ':', 1) as assignee_date_one,
  230. replace(
  231. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  232. ',',
  233. '') as assignee_date_two,
  234. replace(
  235. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  236. ',',
  237. '') as assignee_date_three
  238. from (
  239. select account_id,
  240. group_concat(transferor, ':') as transferor,
  241. group_concat(transferor_date, ':') as transferor_date,
  242. group_concat(assignee, ':') as assignee,
  243. group_concat(assignee_date, ':') as assignee_date
  244. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  245. `jeecg-boot`.ctop_transfer_account
  246. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  247. and status != 2
  248. order by transferor_date
  249. )t
  250. group by account_id) t
  251. ) t
  252. ) b
  253. on report.account_id = b.account_id
  254. where b.account_id is not null
  255. and cost > 0
  256. and transferor_date_two = ''
  257. order by stat_datetime) t
  258. where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
  259. and stat_datetime <= {enddate}
  260. union all
  261. -- 二次转户
  262. select stat_datetime,
  263. account_id,
  264. cost,
  265. transferor_one,
  266. project_id,
  267. project_name,
  268. company_id,
  269. company_name
  270. from (
  271. select report.account_id as account_id,
  272. cost,
  273. stat_datetime,
  274. user_id,
  275. transferor_one,
  276. transferor_date_one,
  277. transferor_two,
  278. transferor_date_two,
  279. assignee_one,
  280. assignee_date_one,
  281. assignee_two,
  282. assignee_date_two,
  283. project_id,
  284. project_name,
  285. company_id,
  286. company_name
  287. from (select account_id,
  288. cost,
  289. stat_datetime,
  290. user_id,
  291. project_id,
  292. project_name,
  293. company_id,
  294. company_name
  295. from application.bytedance_advertiser_report_daily_dw
  296. where stat_datetime between {start_date} and {enddate}) report
  297. left join(
  298. select account_id,
  299. transferor_one,
  300. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  301. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  302. transferor_date_one,
  303. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  304. if(transferor_date_one = transferor_date_three, '',
  305. transferor_date_three) as transferor_date_three,
  306. assignee_one,
  307. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  308. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  309. assignee_date_one,
  310. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  311. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  312. from (
  313. select account_id,
  314. substring_index(transferor, ':', 1) as transferor_one,
  315. replace(
  316. substring_index(substring_index(transferor, ':', 2), ':', -1),
  317. ',',
  318. '') as transferor_two,
  319. replace(
  320. substring_index(substring_index(transferor, ':', 3), ':', -1),
  321. ',',
  322. '') as transferor_three,
  323. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  324. replace(
  325. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  326. ',',
  327. '') as transferor_date_two,
  328. replace(
  329. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  330. ',',
  331. '') as transferor_date_three,
  332. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  333. replace(
  334. substring_index(substring_index(assignee, ':', 2), ':', -1),
  335. ',',
  336. '') as assignee_two,
  337. replace(
  338. substring_index(substring_index(assignee, ':', 3), ':', -1),
  339. ',',
  340. '') as assignee_three,
  341. substring_index(assignee_date, ':', 1) as assignee_date_one,
  342. replace(
  343. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  344. ',',
  345. '') as assignee_date_two,
  346. replace(
  347. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  348. ',',
  349. '') as assignee_date_three
  350. from (
  351. select account_id,
  352. group_concat(transferor, ':') as transferor,
  353. group_concat(transferor_date, ':') as transferor_date,
  354. group_concat(assignee, ':') as assignee,
  355. group_concat(assignee_date, ':') as assignee_date
  356. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  357. `jeecg-boot`.ctop_transfer_account
  358. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  359. and status != 2
  360. order by transferor_date
  361. )t
  362. group by account_id) t
  363. ) t
  364. ) b
  365. on report.account_id = b.account_id
  366. where b.account_id is not null
  367. and cost > 0
  368. and transferor_date_two != ''
  369. and transferor_date_three = ''
  370. order by stat_datetime) t
  371. where stat_datetime >= {start_date}
  372. and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
  373. union all
  374. select stat_datetime,
  375. account_id,
  376. cost,
  377. transferor_two,
  378. project_id,
  379. project_name,
  380. company_id,
  381. company_name
  382. from (
  383. select report.account_id as account_id,
  384. cost,
  385. stat_datetime,
  386. user_id,
  387. transferor_one,
  388. transferor_date_one,
  389. transferor_two,
  390. transferor_date_two,
  391. assignee_one,
  392. assignee_date_one,
  393. assignee_two,
  394. assignee_date_two,
  395. project_id,
  396. project_name,
  397. company_id,
  398. company_name
  399. from (select account_id,
  400. cost,
  401. stat_datetime,
  402. user_id,
  403. project_id,
  404. project_name,
  405. company_id,
  406. company_name
  407. from application.bytedance_advertiser_report_daily_dw
  408. where stat_datetime between {start_date} and {enddate}) report
  409. left join(
  410. select account_id,
  411. transferor_one,
  412. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  413. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  414. transferor_date_one,
  415. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  416. if(transferor_date_one = transferor_date_three, '',
  417. transferor_date_three) as transferor_date_three,
  418. assignee_one,
  419. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  420. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  421. assignee_date_one,
  422. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  423. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  424. from (
  425. select account_id,
  426. substring_index(transferor, ':', 1) as transferor_one,
  427. replace(
  428. substring_index(substring_index(transferor, ':', 2), ':', -1),
  429. ',',
  430. '') as transferor_two,
  431. replace(
  432. substring_index(substring_index(transferor, ':', 3), ':', -1),
  433. ',',
  434. '') as transferor_three,
  435. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  436. replace(
  437. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  438. ',',
  439. '') as transferor_date_two,
  440. replace(
  441. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  442. ',',
  443. '') as transferor_date_three,
  444. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  445. replace(
  446. substring_index(substring_index(assignee, ':', 2), ':', -1),
  447. ',',
  448. '') as assignee_two,
  449. replace(
  450. substring_index(substring_index(assignee, ':', 3), ':', -1),
  451. ',',
  452. '') as assignee_three,
  453. substring_index(assignee_date, ':', 1) as assignee_date_one,
  454. replace(
  455. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  456. ',',
  457. '') as assignee_date_two,
  458. replace(
  459. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  460. ',',
  461. '') as assignee_date_three
  462. from (
  463. select account_id,
  464. group_concat(transferor, ':') as transferor,
  465. group_concat(transferor_date, ':') as transferor_date,
  466. group_concat(assignee, ':') as assignee,
  467. group_concat(assignee_date, ':') as assignee_date
  468. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  469. `jeecg-boot`.ctop_transfer_account
  470. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  471. and status != 2
  472. order by transferor_date
  473. )t
  474. group by account_id) t
  475. ) t
  476. ) b
  477. on report.account_id = b.account_id
  478. where b.account_id is not null
  479. and cost > 0
  480. and transferor_date_two != ''
  481. and transferor_date_three = ''
  482. order by stat_datetime) t
  483. where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
  484. and stat_datetime <= date_format(transferor_date_two, '%Y%m%d')
  485. union all
  486. select stat_datetime,
  487. account_id,
  488. cost,
  489. assignee_two,
  490. project_id,
  491. project_name,
  492. company_id,
  493. company_name
  494. from (
  495. select report.account_id as account_id,
  496. cost,
  497. stat_datetime,
  498. user_id,
  499. transferor_one,
  500. transferor_date_one,
  501. transferor_two,
  502. transferor_date_two,
  503. assignee_one,
  504. assignee_date_one,
  505. assignee_two,
  506. assignee_date_two,
  507. project_id,
  508. project_name,
  509. company_id,
  510. company_name
  511. from (select account_id,
  512. cost,
  513. stat_datetime,
  514. user_id,
  515. project_id,
  516. project_name,
  517. company_id,
  518. company_name
  519. from application.bytedance_advertiser_report_daily_dw
  520. where stat_datetime between {start_date} and {enddate}) report
  521. left join(
  522. select account_id,
  523. transferor_one,
  524. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  525. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  526. transferor_date_one,
  527. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  528. if(transferor_date_one = transferor_date_three, '',
  529. transferor_date_three) as transferor_date_three,
  530. assignee_one,
  531. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  532. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  533. assignee_date_one,
  534. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  535. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  536. from (
  537. select account_id,
  538. substring_index(transferor, ':', 1) as transferor_one,
  539. replace(
  540. substring_index(substring_index(transferor, ':', 2), ':', -1),
  541. ',',
  542. '') as transferor_two,
  543. replace(
  544. substring_index(substring_index(transferor, ':', 3), ':', -1),
  545. ',',
  546. '') as transferor_three,
  547. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  548. replace(
  549. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  550. ',',
  551. '') as transferor_date_two,
  552. replace(
  553. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  554. ',',
  555. '') as transferor_date_three,
  556. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  557. replace(
  558. substring_index(substring_index(assignee, ':', 2), ':', -1),
  559. ',',
  560. '') as assignee_two,
  561. replace(
  562. substring_index(substring_index(assignee, ':', 3), ':', -1),
  563. ',',
  564. '') as assignee_three,
  565. substring_index(assignee_date, ':', 1) as assignee_date_one,
  566. replace(
  567. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  568. ',',
  569. '') as assignee_date_two,
  570. replace(
  571. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  572. ',',
  573. '') as assignee_date_three
  574. from (
  575. select account_id,
  576. group_concat(transferor, ':') as transferor,
  577. group_concat(transferor_date, ':') as transferor_date,
  578. group_concat(assignee, ':') as assignee,
  579. group_concat(assignee_date, ':') as assignee_date
  580. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  581. `jeecg-boot`.ctop_transfer_account
  582. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  583. and status != 2
  584. order by transferor_date
  585. )t
  586. group by account_id) t
  587. ) t
  588. ) b
  589. on report.account_id = b.account_id
  590. where b.account_id is not null
  591. and cost > 0
  592. and transferor_date_two != ''
  593. and transferor_date_three = ''
  594. order by stat_datetime) t
  595. where stat_datetime > date_format(transferor_date_two, '%Y%m%d')
  596. and stat_datetime <= {enddate}
  597. -- 三次转户
  598. union all
  599. select stat_datetime,
  600. account_id,
  601. cost,
  602. transferor_one,
  603. project_id,
  604. project_name,
  605. company_id,
  606. company_name
  607. from (
  608. select report.account_id as account_id,
  609. cost,
  610. stat_datetime,
  611. user_id,
  612. transferor_one,
  613. transferor_date_one,
  614. transferor_two,
  615. transferor_date_two,
  616. assignee_one,
  617. assignee_date_one,
  618. assignee_two,
  619. assignee_date_two,
  620. transferor_three,
  621. transferor_date_three,
  622. project_id,
  623. project_name,
  624. company_id,
  625. company_name
  626. from (select account_id,
  627. cost,
  628. stat_datetime,
  629. user_id,
  630. project_id,
  631. project_name,
  632. company_id,
  633. company_name
  634. from application.bytedance_advertiser_report_daily_dw
  635. where stat_datetime between {start_date} and {enddate}) report
  636. left join(
  637. select account_id,
  638. transferor_one,
  639. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  640. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  641. transferor_date_one,
  642. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  643. if(transferor_date_one = transferor_date_three, '',
  644. transferor_date_three) as transferor_date_three,
  645. assignee_one,
  646. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  647. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  648. assignee_date_one,
  649. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  650. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  651. from (
  652. select account_id,
  653. substring_index(transferor, ':', 1) as transferor_one,
  654. replace(
  655. substring_index(substring_index(transferor, ':', 2), ':', -1),
  656. ',',
  657. '') as transferor_two,
  658. replace(
  659. substring_index(substring_index(transferor, ':', 3), ':', -1),
  660. ',',
  661. '') as transferor_three,
  662. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  663. replace(
  664. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  665. ',',
  666. '') as transferor_date_two,
  667. replace(
  668. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  669. ',',
  670. '') as transferor_date_three,
  671. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  672. replace(
  673. substring_index(substring_index(assignee, ':', 2), ':', -1),
  674. ',',
  675. '') as assignee_two,
  676. replace(
  677. substring_index(substring_index(assignee, ':', 3), ':', -1),
  678. ',',
  679. '') as assignee_three,
  680. substring_index(assignee_date, ':', 1) as assignee_date_one,
  681. replace(
  682. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  683. ',',
  684. '') as assignee_date_two,
  685. replace(
  686. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  687. ',',
  688. '') as assignee_date_three
  689. from (
  690. select account_id,
  691. group_concat(transferor, ':') as transferor,
  692. group_concat(transferor_date, ':') as transferor_date,
  693. group_concat(assignee, ':') as assignee,
  694. group_concat(assignee_date, ':') as assignee_date
  695. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  696. `jeecg-boot`.ctop_transfer_account
  697. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  698. and status != 2
  699. order by transferor_date
  700. )t
  701. group by account_id) t
  702. ) t
  703. ) b
  704. on report.account_id = b.account_id
  705. where b.account_id is not null
  706. and cost > 0
  707. and transferor_date_three != ''
  708. order by stat_datetime) t
  709. where stat_datetime >= {start_date}
  710. and stat_datetime <= date_format(transferor_date_one, '%Y%m%d')
  711. union all
  712. select stat_datetime,
  713. account_id,
  714. cost,
  715. transferor_two,
  716. project_id,
  717. project_name,
  718. company_id,
  719. company_name
  720. from (
  721. select report.account_id as account_id,
  722. cost,
  723. stat_datetime,
  724. user_id,
  725. transferor_one,
  726. transferor_date_one,
  727. transferor_two,
  728. transferor_date_two,
  729. assignee_one,
  730. assignee_date_one,
  731. assignee_two,
  732. assignee_date_two,
  733. transferor_three,
  734. transferor_date_three,
  735. project_id,
  736. project_name,
  737. company_id,
  738. company_name
  739. from (select account_id,
  740. cost,
  741. stat_datetime,
  742. user_id,
  743. project_id,
  744. project_name,
  745. company_id,
  746. company_name
  747. from application.bytedance_advertiser_report_daily_dw
  748. where stat_datetime between {start_date} and {enddate}) report
  749. left join(
  750. select account_id,
  751. transferor_one,
  752. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  753. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  754. transferor_date_one,
  755. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  756. if(transferor_date_one = transferor_date_three, '',
  757. transferor_date_three) as transferor_date_three,
  758. assignee_one,
  759. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  760. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  761. assignee_date_one,
  762. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  763. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  764. from (
  765. select account_id,
  766. substring_index(transferor, ':', 1) as transferor_one,
  767. replace(
  768. substring_index(substring_index(transferor, ':', 2), ':', -1),
  769. ',',
  770. '') as transferor_two,
  771. replace(
  772. substring_index(substring_index(transferor, ':', 3), ':', -1),
  773. ',',
  774. '') as transferor_three,
  775. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  776. replace(
  777. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  778. ',',
  779. '') as transferor_date_two,
  780. replace(
  781. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  782. ',',
  783. '') as transferor_date_three,
  784. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  785. replace(
  786. substring_index(substring_index(assignee, ':', 2), ':', -1),
  787. ',',
  788. '') as assignee_two,
  789. replace(
  790. substring_index(substring_index(assignee, ':', 3), ':', -1),
  791. ',',
  792. '') as assignee_three,
  793. substring_index(assignee_date, ':', 1) as assignee_date_one,
  794. replace(
  795. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  796. ',',
  797. '') as assignee_date_two,
  798. replace(
  799. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  800. ',',
  801. '') as assignee_date_three
  802. from (
  803. select account_id,
  804. group_concat(transferor, ':') as transferor,
  805. group_concat(transferor_date, ':') as transferor_date,
  806. group_concat(assignee, ':') as assignee,
  807. group_concat(assignee_date, ':') as assignee_date
  808. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  809. `jeecg-boot`.ctop_transfer_account
  810. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  811. and status != 2
  812. order by transferor_date
  813. )t
  814. group by account_id) t
  815. ) t
  816. ) b
  817. on report.account_id = b.account_id
  818. where b.account_id is not null
  819. and cost > 0
  820. and transferor_date_three != ''
  821. order by stat_datetime) t
  822. where stat_datetime > date_format(transferor_date_one, '%Y%m%d')
  823. and stat_datetime <= date_format(transferor_date_two, '%Y%m%d')
  824. union all
  825. select stat_datetime,
  826. account_id,
  827. cost,
  828. transferor_three,
  829. project_id,
  830. project_name,
  831. company_id,
  832. company_name
  833. from (
  834. select report.account_id as account_id,
  835. cost,
  836. stat_datetime,
  837. user_id,
  838. transferor_one,
  839. transferor_date_one,
  840. transferor_two,
  841. transferor_date_two,
  842. assignee_one,
  843. assignee_date_one,
  844. assignee_two,
  845. assignee_date_two,
  846. transferor_three,
  847. transferor_date_three,
  848. project_id,
  849. project_name,
  850. company_id,
  851. company_name
  852. from (select account_id,
  853. cost,
  854. stat_datetime,
  855. user_id,
  856. project_id,
  857. project_name,
  858. company_id,
  859. company_name
  860. from application.bytedance_advertiser_report_daily_dw
  861. where stat_datetime between {start_date} and {enddate}) report
  862. left join(
  863. select account_id,
  864. transferor_one,
  865. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  866. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  867. transferor_date_one,
  868. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  869. if(transferor_date_one = transferor_date_three, '',
  870. transferor_date_three) as transferor_date_three,
  871. assignee_one,
  872. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  873. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  874. assignee_date_one,
  875. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  876. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  877. from (
  878. select account_id,
  879. substring_index(transferor, ':', 1) as transferor_one,
  880. replace(
  881. substring_index(substring_index(transferor, ':', 2), ':', -1),
  882. ',',
  883. '') as transferor_two,
  884. replace(
  885. substring_index(substring_index(transferor, ':', 3), ':', -1),
  886. ',',
  887. '') as transferor_three,
  888. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  889. replace(
  890. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  891. ',',
  892. '') as transferor_date_two,
  893. replace(
  894. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  895. ',',
  896. '') as transferor_date_three,
  897. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  898. replace(
  899. substring_index(substring_index(assignee, ':', 2), ':', -1),
  900. ',',
  901. '') as assignee_two,
  902. replace(
  903. substring_index(substring_index(assignee, ':', 3), ':', -1),
  904. ',',
  905. '') as assignee_three,
  906. substring_index(assignee_date, ':', 1) as assignee_date_one,
  907. replace(
  908. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  909. ',',
  910. '') as assignee_date_two,
  911. replace(
  912. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  913. ',',
  914. '') as assignee_date_three
  915. from (
  916. select account_id,
  917. group_concat(transferor, ':') as transferor,
  918. group_concat(transferor_date, ':') as transferor_date,
  919. group_concat(assignee, ':') as assignee,
  920. group_concat(assignee_date, ':') as assignee_date
  921. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  922. `jeecg-boot`.ctop_transfer_account
  923. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  924. and status != 2
  925. order by transferor_date
  926. )t
  927. group by account_id) t
  928. ) t
  929. ) b
  930. on report.account_id = b.account_id
  931. where b.account_id is not null
  932. and cost > 0
  933. and transferor_date_three != ''
  934. order by stat_datetime) t
  935. where stat_datetime > date_format(transferor_date_two, '%Y%m%d')
  936. and stat_datetime <= date_format(transferor_date_three, '%Y%m%d')
  937. union all
  938. select stat_datetime,
  939. account_id,
  940. cost,
  941. assignee_three,
  942. project_id,
  943. project_name,
  944. company_id,
  945. company_name
  946. from (
  947. select report.account_id as account_id,
  948. cost,
  949. stat_datetime,
  950. user_id,
  951. transferor_one,
  952. transferor_date_one,
  953. transferor_two,
  954. transferor_date_two,
  955. assignee_one,
  956. assignee_date_one,
  957. assignee_two,
  958. assignee_date_two,
  959. transferor_three,
  960. transferor_date_three,
  961. project_id,
  962. project_name,
  963. company_id,
  964. company_name,
  965. assignee_three
  966. from (select account_id,
  967. cost,
  968. stat_datetime,
  969. user_id,
  970. project_id,
  971. project_name,
  972. company_id,
  973. company_name
  974. from application.bytedance_advertiser_report_daily_dw
  975. where stat_datetime between {start_date} and {enddate}) report
  976. left join(
  977. select account_id,
  978. transferor_one,
  979. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  980. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  981. transferor_date_one,
  982. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  983. if(transferor_date_one = transferor_date_three, '',
  984. transferor_date_three) as transferor_date_three,
  985. assignee_one,
  986. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  987. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  988. assignee_date_one,
  989. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  990. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  991. from (
  992. select account_id,
  993. substring_index(transferor, ':', 1) as transferor_one,
  994. replace(
  995. substring_index(substring_index(transferor, ':', 2), ':', -1),
  996. ',',
  997. '') as transferor_two,
  998. replace(
  999. substring_index(substring_index(transferor, ':', 3), ':', -1),
  1000. ',',
  1001. '') as transferor_three,
  1002. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  1003. replace(
  1004. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  1005. ',',
  1006. '') as transferor_date_two,
  1007. replace(
  1008. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  1009. ',',
  1010. '') as transferor_date_three,
  1011. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  1012. replace(
  1013. substring_index(substring_index(assignee, ':', 2), ':', -1),
  1014. ',',
  1015. '') as assignee_two,
  1016. replace(
  1017. substring_index(substring_index(assignee, ':', 3), ':', -1),
  1018. ',',
  1019. '') as assignee_three,
  1020. substring_index(assignee_date, ':', 1) as assignee_date_one,
  1021. replace(
  1022. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  1023. ',',
  1024. '') as assignee_date_two,
  1025. replace(
  1026. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  1027. ',',
  1028. '') as assignee_date_three
  1029. from (
  1030. select account_id,
  1031. group_concat(transferor, ':') as transferor,
  1032. group_concat(transferor_date, ':') as transferor_date,
  1033. group_concat(assignee, ':') as assignee,
  1034. group_concat(assignee_date, ':') as assignee_date
  1035. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  1036. `jeecg-boot`.ctop_transfer_account
  1037. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  1038. and status != 2
  1039. order by transferor_date
  1040. )t
  1041. group by account_id) t
  1042. ) t
  1043. ) b
  1044. on report.account_id = b.account_id
  1045. where b.account_id is not null
  1046. and cost > 0
  1047. and transferor_date_three != ''
  1048. order by stat_datetime) t
  1049. where stat_datetime > date_format(transferor_date_three, '%Y%m%d')
  1050. and stat_datetime <= {enddate}) t
  1051. group by stat_datetime, account_id, cost
  1052. -- 无转户
  1053. union all
  1054. select stat_datetime,
  1055. a.account_id,
  1056. cost,
  1057. user_id,
  1058. project_id,
  1059. project_name,
  1060. company_id,
  1061. company_name
  1062. from (select stat_datetime,
  1063. account_id,
  1064. cost,
  1065. user_id,
  1066. project_id,
  1067. project_name,
  1068. company_id,
  1069. company_name
  1070. from application.bytedance_advertiser_report_daily_dw
  1071. where stat_datetime between {start_date} and {enddate}) a
  1072. left join
  1073. (
  1074. select account_id
  1075. from `jeecg-boot`.ctop_transfer_account
  1076. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  1077. and status = 1
  1078. ) b
  1079. on a.account_id = b.account_id
  1080. where b.account_id is null
  1081. and cost > 0
  1082. ) t
  1083. left join `jeecg-boot`.sys_user user
  1084. on transferor_one = user.id
  1085. left join `jeecg-boot`.sys_user leader
  1086. on user.leader_id = leader.id
  1087. where user.id is not null
  1088. group by stat_datetime, account_id, cost
  1089. """.format(start_date=start_date, enddate=end_date)
  1090. print(sql)
  1091. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  1092. self.conn.commit()
  1093. TidbProConn.conn_close(conn=self.conn)
  1094. except Exception as e:
  1095. logging.error(e)
  1096. return -1
  1097. if __name__ == '__main__':
  1098. BytendaceOperationPerformanceBase().handler()