JSON与TimeStamp

  1. select 
  2.    dt,model_name,hour_,min_ ,
  3.    avg(pctr) as pctr,avg(pcvr) as pcvr,
  4.    avg(pay) as pay, avg(click) as click 
  5. from 
  6. (
  7. select 
  8. substring(from_unixtime(event_ts,'yyyy-MM-dd HH:mm:ss'),12,2) as hour_,
  9. substring(from_unixtime(event_ts,'yyyy-MM-dd HH:mm:ss'),15,2) as min_,
  10. get_json_object(info,'$.pctr') as pctr,
  11. get_json_object(info,'$.pcvr') as pcvr,
  12. get_json_object(info,'$.pay') as pay,
  13. get_json_object(info,'$.click') as click,
  14. dt,model_name 
  15. from ba_dealrank.rank_online_rankscore_v5_modelproblem 
  17. where dt>='20210201' )t group by dt,model_name,hour_,min_ 
  19. limit 10000