MySQL按区间分组查询统计报表

       今天的在工作中遇到需要在接口调用记录表中统计数据, 初步实现使用存储过程. 发现查询次数过多且效率较低. 经过几个小时各种百度,谷歌得到以下成功(使用SQL语句优化存储过程查询次数), 其中仍有不足之处, 先记录下来, 后期优化;

 

表结构如下:

列名	中文名
userTripId	自增ID
eventTime	记录时间, 数据格式 yyyy-MM-dd hh:mm:ss
h	小时时间
m	分钟时间
userId	用户ID

 

部分数据内容如下, 正常情况下数据量在35W左右:

 查询语句:

SELECT
	d.eventTime,
	h,
	d.m,
	elt(
		INTERVAL (d.m, 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 59),
		'0~5分钟',
		'5~10分钟',
		'10~15分钟',
		'15~20分钟',
		'20~25分钟',
		'30~35分钟',
		'35~40分钟',
		'40~45分钟',
		'45~50分钟',
		'50~55分钟',
		'55~59分钟'
	) AS yb_level,
	count(DISTINCT d.userId) AS cnt
FROM
	t_utrip d
where
	d.eventTime >= '2015-4-20'
	and d.eventTime < '2015-4-21'
GROUP BY
	h,
	elt(
		INTERVAL (d.m, 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 59),
		'0~5分钟',
		'5~10分钟',
		'10~15分钟',
		'15~20分钟',
		'20~25分钟',
		'30~35分钟',
		'35~40分钟',
		'40~45分钟',
		'45~50分钟',
		'50~55分钟',
		'55~59分钟'
	)
ORDER BY
	d.eventTime, d.h, d.m

 

SQL语句分析:

1. 按小时(h列)分组
2. 将按分钟区间分组, 通过INTERVAL(N, N1,N2,N3,...)函数将60分钟分割为: 0~4, 5~9, 10~14, 15~19, ... 55~59; 函数INTERVAL()将返回对应的下标值, 起始值为1(查看INTERVAL函数详情)
3. 使用ELT(N, N1,N2,N3,...)函数将对应区间转换为指定值, 即: '0~5分钟', '5~10分钟',  '10~15分钟', ... (查看ELT函数详情)