SQL on log : 同比分析各种指标

同比环比的需求场景

在业务分析，以及线上监控中，我们常常遇到这些需求：

1. 对比分析今天的uv和昨天的uv，以及增长率。
2. 对比分析今天每个小时的pv，和昨天每个小时的pv，进行对比画图。
3. 最近15分钟和上一个15分钟对比，如果pv下降，立即短信通知告警。

同比环比函数

同比函数用于把当前区间的计算结果和之前一个指定区间的结果进行比较。

• 函数语法：

  • compare(value, time_window)
• value为double或long类型，time_window为秒。比较当前窗口计算出来的value值和 time_window计算出来的结果进行比较,返回值为数组类型，分别是当前值，time_window之前的值，当前相对于之前的增长比例。

• 样例：

  • * | select compare( pv , 86400) from (select count(1) as pv from log)

1. 计算当前1小时和昨天同一时刻1小时的pv比例

查询的起止时间为： 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))

2. 计算当前1小时，每分钟的pv曲线，和昨天一时刻的pv曲线，

查询的起止时间为：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)

结果可以配置成曲线图，两条曲线分别代表今天的值，昨天的值：