思路:根据日期yyyy-MM-dd mm:HH:ss 格式,天数dd/3 归于dd/3×3 ,00,01,02 为00,进行分组统计。
sql如下:
select substr(FROM_UNIXTIME((curtime)/1000),1,14) ||
case when substr(FROM_UNIXTIME((curtime)/1000),15,2)/3*3 <10 then '0'||substr(FROM_UNIXTIME((curtime)/1000),15,2)/3*3
else substr(FROM_UNIXTIME((curtime)/1000),15,2)/3*3 end
||'00' as dt,
count(1)
FROM TXN
WHERE curtime > UNIX_TIMESTAMP('2018/10/11 14:22:51')
GROUP BY dt;