全部产品
云市场

时序分析函数

更新时间:2019-05-05 12:34:34

first()

first(value, time)聚合函数允许用户按照time列的顺序得到value列值。 例如:first(temp, time) 会根据聚合组内的时间返回最早的温度值。

参数

Name Description
value 返回值
time 用来比较的时间戳 (TIMESTAMP/TIMESTAMPTZ 或者整型数值类型)

示例:

根据device_id分组获得第一个温度值

  1. SELECT device_id, first(temp, time)
  2. FROM metrics
  3. GROUP BY device_id;

警告⚠️: first 函数通过线性扫描计算结果,不使用索引。

histogram()

histogram()直方图函数用来表示一组值的分布,该分布通过一组等宽桶(bucket)表示。 直方图函数根据输入的最小边界(min)和最大边界(max)将数据分区到指定数量的桶中(nbuckets)。

返回值是一个包含n + 2个桶的数组,中间的n个桶用于指定范围内的值个数,第一个桶用于表示小于最小边界的值的个数,最后一个桶用于表示大于或等于的最大边界的值的个数。 对于中间的n个桶,每个桶的边界是左闭右开[min,max)。

参数

Name Description
value 直方图分布的值
min 直方图最小边界
max 直方图最大边界
nbuckets 直方图分区/桶个数

示例:

显示readings表的battery_level列的直方图(按device_id分组)。

  • 输入:value: battery_level, min: 20, max: 60, nbuckents: 5 。
  • 输出:返回7个值,第一个代表小于20区间的值的个数,最后一个代表大于等于60区间的值的个数。中间五个为20-60均分后(5个区间),每个区间的出现的值的个数
  1. SELECT device_id, histogram(battery_level, 20, 60, 5)
  2. FROM readings
  3. GROUP BY device_id
  4. LIMIT 10;

示例输出:

  1. device_id | histogram
  2. ------------+------------------------------
  3. demo000000 | {0,0,0,7,215,206,572}
  4. demo000001 | {0,12,173,112,99,145,459}
  5. demo000002 | {0,0,187,167,68,229,349}
  6. demo000003 | {197,209,127,221,106,112,28}
  7. demo000004 | {0,0,0,0,0,39,961}
  8. demo000005 | {12,225,171,122,233,80,157}
  9. demo000006 | {0,78,176,170,8,40,528}
  10. demo000007 | {0,0,0,126,239,245,390}
  11. demo000008 | {0,0,311,345,116,228,0}
  12. demo000009 | {295,92,105,50,8,8,442}

last()

last(value, time)聚合函数允许用户按照time列的顺序得到value列值。 顺序与first函数相反。例如:last(temp, time) 会根据聚合组内的时间返回最晚的温度值。

参数

Name Description
value 返回值
time 用来比较的时间戳(TIMESTAMP/TIMESTAMPTZ 或者整型数值类型)

示例:

根据device_id分组获得最后一个温度值

  1. SELECT device_id, last(temp, time)
  2. FROM metrics
  3. GROUP BY device_id;

警告⚠️:last 函数通过线性扫描计算结果,不使用索引。

time_bucket()

time_bucket()将时间进行分区,不同于PostgreSQL 的date_trunc函数,time_bucket允许任意时间间隔,而不把时间间隔限制成秒,分钟,小时。 返回值是分区的开始时间。 以下是有效使用它的必要信息。

必选参数

Name Description
bucket_width 分区时长
time 分区时间戳(time 为timestamp/timestamptz/date类型)

可选参数

Name Description
origin 分区起始时间戳(timestamp/timestamptz/date类型)

另一种格式,输入时间列为整型

必选参数

Name Description
bucket_width 分区时长
time 分区时间戳(time 为整型数值类型)

可选参数

Name Description
offset 时长偏移量(offset 为整型数值类型)

示例:

计算每五分钟的cpu平均值:

  1. SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
  2. FROM metrics
  3. GROUP BY five_min
  4. ORDER BY five_min DESC LIMIT 10;

用户可以传递origin参数(timestamp, timestamptz, date 类型)来偏移校准时间。下面示例中,每周起始日期从默认的周一修改成周日。

  1. SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31')
  2. AS one_week, avg(cpu)
  3. FROM metrics
  4. GROUP BY one_week
  5. WHERE time > TIMESTAMPTZ '2017-12-01' AND time < TIMESTAMPTZ '2018-01-03'
  6. ORDER BY one_week DESC LIMIT 10;

本例中的2017-12-31是个周日,origin可以是任意时间。 将TIMESTAMPTZ(UTC)转换成本地时区进行分区示例如下:

  1. SELECT time_bucket('5 minutes', timetz::TIMESTAMP)
  2. AS five_min, avg(cpu)
  3. FROM metrics
  4. GROUP BY five_min
  5. ORDER BY five_min DESC LIMIT 10;

注:上面例子根据服务器本地时区将TIMESTAMPTZ类型转换成TIMESTAMP类型