grafana 做业务数据分析
原创
©著作权归作者所有:来自51CTO博客作者keyboard_sun的原创作品,请联系作者获取转载授权,否则将追究法律责任
grafana 是一个开源的时序性统计和监控平台,支持例如 elasticsearch、graphite、influxdb 等众多的数据源,并以功能强大的界面编辑器著称。
现在就以我们的业务为例,做了几个图像分析,如图所示:
以上只用到了grafana的两个panel,分别是singlestat,graph
下图这一块就是singlestat做的
主要的查询写法是在metrice中,选择mysql
SELECT count(1) as value,max(UNIX_TIMESTAMP(DATE_FORMAT(registerTime ,'%Y-%m-%d'))) as time_sec,
'XXXXXX' as metric from XXXXXX.registerInfo as a where $__timeFilter(a.registerTime ) and registerTime is not NULL and isDelete=0
而下图这块是使用graph做出来的
对应的代码段
select UNIX_TIMESTAMP(DATE_FORMAT(CASE WHEN DAYNAME(DATE(registerTime))='Sunday' THEN DATE(registerTime) ELSE DATE_ADD(registerTime,INTERVAL 7-DAYOFWEEK(registerTime)+1 DAY) END ,'%Y-%m-%d')) as time_sec
,count(1) as value,
'总注册数' as metric
FROM XXXXX.registerInfo
where $__timeFilter(raceEnroll.registerInfo.registerTime)
and raceEnroll.registerInfo.registerTime is not null
GROUP BY time_sec
ORDER BY time_sec asc
这个代码最重要的部分,分别是按周统计和分组,按周的mysql查询代码
UNIX_TIMESTAMP(DATE_FORMAT(CASE WHEN DAYNAME(DATE(registerTime))='Sunday' THEN DATE(registerTime) ELSE DATE_ADD(registerTime,INTERVAL 7-DAYOFWEEK(registerTime)+1 DAY) END ,'%Y-%m-%d')) as time_sec
然后在按照周进行分组即可。
其他的样式可以自己随意调整