mysql 每5分鐘聚合并擷取昨日值和7日值

event_timestamp  毫秒
           

SELECT
  CASE 
  	WHEN (event_timestamp/1000)%300<=300 
  	THEN FROM_UNIXTIME(event_timestamp/1000-(event_timestamp/1000)%300, '2018-06-21 %H:%i:%s') END AS time_part,  
  SUM(CASE WHEN event_day= '20180621' THEN pv ELSE NULL END ) AS pv,
  SUM(CASE WHEN DATE_FORMAT(DATE_ADD(event_day,INTERVAL +1 day),'%Y%m%d') = '20180621'  THEN pv ELSE 0 END ) AS y_pv,
  SUM(CASE WHEN DATE_FORMAT(DATE_ADD(event_day,INTERVAL +7 day),'%Y%m%d') = '20180621'  THEN pv ELSE 0 END ) AS w_pv
FROM
  realtime_1min_click_task_with_dim
WHERE
(event_day= '20180621'
OR  DATE_FORMAT(DATE_ADD(event_day,INTERVAL +1 day),'%Y%m%d') = '20180621'
OR  DATE_FORMAT(DATE_ADD(event_day,INTERVAL +7 day),'%Y%m%d') = '20180621')
GROUP BY
  time_part