SQL查询按照3分钟间隔分组统计

SELECT FLOOR((window_start/1000)/180)*180*1000 as window_start, SUM(vip_first_view_by_doris) 
from kela_doris_vip_first_view_by_doris 
GROUP BY window_start 
ORDER BY window_start
LIMIT 20

代码中涉及到的时间字段是精确到毫秒的UNIX时间戳类型，我们先除以1000得到秒，然后除以180整数然后乘以180则为3分钟间隔的时间戳，FLOOR函数就是取整的函数。最终得到的结果如下，

将结果以字符串的格式展示。

SELECT FROM_UNIXTIME((FLOOR((window_start/1000)/180)*180)) as window_start, SUM(vip_first_view_by_doris) 
from kela_doris_vip_first_view_by_doris 
GROUP BY window_start 
ORDER BY window_start
LIMIT 20