云雷
2018-07-30
1813浏览量
在业务分析,以及线上监控中,我们常常遇到这些需求:
同比函数用于把当前区间的计算结果和之前一个指定区间的结果进行比较。
函数语法:
compare(value, time_window)
样例:
* | select compare( pv , 86400) from (select count(1) as pv from log)
查询的起止时间为: from=2018-7-25 14:00:00
, to=2018-07-25 15:00:00
:
* | select compare( pv , 86400) from (select count(1) as pv from log)
参数中86400表示当前窗口减去86400秒
返回结果:
[9.0,19.0,0.47368421052631579]
其中9.0为从`2018-7-25 14:00:00`到`2018-07-25 15:00:00`的pv值。
19.0表示`2018-7-24 14:00:00`到`2018-07-24 15:00:00`的pv值。
0.47368421052631579表示当前窗口相对于之前窗口的比值。
如果要把数组展开成3列数字,需要这样写:
* | select diff[1],diff[2],diff[3] from(select compare( pv , 86400) as diff from (select count(1) as pv from log))
查询的起止时间为:from=2018-7-25 14:00:00
, to=2018-07-25 15:00:00
:
*| select t, compare( pv , 86400) as diff from (select count(1) as pv, date_format(from_unixtime(__time__), '%H:%i') as t from log group by t) group by t order by t
输出结果:
t | diff |
---|---|
14:00 | [9520.0,7606.0,1.2516434393899554] |
14:01 | [8596.0,8553.0,1.0050274757395066] |
14:02 | [8722.0,8435.0,1.0340248962655603] |
14:03 | [7499.0,5912.0,1.2684370771312586] |
其中diff列的内容是一个数组,分别表示当前窗口的pv值,之前窗口的pv值,当前窗口/之前窗口的比例。
若要展开成曲线,则这样写:
*|select t, diff[1] as current, diff[2] as yestoday, diff[3] as percentage from(select t, compare( pv , 86400) as diff from (select count(1) as pv, date_format(from_unixtime(__time__), '%H:%i') as t from log group by t) group by t order by t)
结果可以配置成曲线图,两条曲线分别代表今天的值,昨天的值:
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。