KuaishouOperationPerformanceBase.py 76 KB

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