dw_book_trend.py 1.9 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
  1. """
  2. @desc 书维度全量表
  3. @auth ck
  4. """
  5. from model.sql_models import DB
  6. from config import using_config
  7. import logging
  8. def book_trend():
  9. logging.info('书籍趋势数据处理,开始')
  10. sql = """
  11. truncate table book_trend;
  12. insert into book_trend
  13. select a.dt,book,type,
  14. sum(cost) cost,
  15. sum(order_count),
  16. sum(order_user),
  17. sum(order_amount),
  18. sum(reg_num),
  19. sum(create_user_num),
  20. sum(first_order_count) first_order_count,
  21. sum(first_order_user) first_order_user,
  22. sum(first_order_amount) first_order_amount,
  23. sum(reg_order_count) reg_order_count,
  24. sum(reg_order_user) reg_order_user,
  25. sum(reg_order_amount) reg_order_amount,
  26. sum(view_count) view_count,
  27. sum(click_count) click_count,
  28. sum(follow_user) follow_user,
  29. sum(da1),sum(da2),sum(da3),sum(da4),sum(da5),sum(da6),sum(da7),sum(da8),sum(da9),sum(da10),sum(da11),sum(da12),sum(da13),sum(da14),sum(da15),sum(da16),sum(da17),sum(da18),sum(da19),sum(da20),sum(da21),sum(da22),sum(da23),sum(da24),sum(da25),sum(da26),sum(da27),sum(da28),sum(da29),sum(da30),sum(da31),sum(da32),sum(da33),sum(da34),sum(da35),sum(da36),sum(da37),sum(da38),sum(da39),sum(da40),sum(da41),sum(da42),sum(da43),sum(da44),sum(da45),sum(da46),sum(da47),sum(da48),sum(da49),sum(da50),sum(da51),sum(da52),sum(da53),sum(da54),sum(da55),sum(da56),sum(da57),sum(da58),sum(da59),sum(da60),sum(dm3),sum(dm4),sum(dm5),
  30. sum(ba1),sum(ba2),sum(ba3),sum(ba4),sum(ba5),sum(ba6),sum(ba7)
  31. from dw_channel a
  32. left join dw_channel_amount_daily b on a.dt=b.dt and a.channel=b.channel
  33. left join dw_channel_amount_daily_reverse c on a.dt=c.dt and a.channel=c.channel
  34. where book !=''
  35. GROUP BY dt,book,type"""
  36. dm_db=DB(config=using_config.dm)
  37. dm_db.session.execute(sql)
  38. dm_db.session.commit()
  39. logging.info('书籍趋势数据处理,结束')
  40. if __name__ == '__main__':
  41. book_trend()