今天的在工作中遇到需要在接口调用记录表中统计数据, 初步实现使用存储过程. 发现查询次数过多且效率较低. 经过几个小时各种百度,谷歌得到以下成功(使用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语句分析:
- 按小时(h列)分组
- 将按分钟区间分组, 通过INTERVAL(N, N1,N2,N3,...)函数将60分钟分割为: 0~4, 5~9, 10~14, 15~19, ... 55~59; 函数INTERVAL()将返回对应的下标值, 起始值为1(查看INTERVAL函数详情)
- 使用ELT(N, N1,N2,N3,...)函数将对应区间转换为指定值, 即: '0~5分钟', '5~10分钟', '10~15分钟', ... (查看ELT函数详情)