Log service allows you to use interval-valued comparison and periodicity-valued comparison functions to query and analyze log data.

Interval-valued comparison and periodicity-valued comparison functions are used to compare the calculation results of the current period with those of a specified previous period.

Function Description Example
compare(value, time_window) Compares the value calculated for the current time period with the value generated before time_window.

The values are of the double or long data type. The value of time_window is in seconds. The returned values are of the array data type.

The returned values are the current value, the value generated before time_window, and the ratio of the current value to the value generated before time_window.

*|select compare( pv , 86400) from (select count(1) as pv from log)
compare(value, time_window1, time_window2) Compares the value calculated for the current time period with the values generated before time_window1 and time_window2. The results are JSON arrays. The values must be placed in the following sequence: [current value, value generated before time_window1, value generated before time_window2, current value/value generated before time_window1, current value/value generated before time_window2]. * | select compare(pv, 86400, 172800) from ( select count(1) as pv from log)
compare(value, time_window1, time_window2, time_window3) Compares the value calculated for the current time period with the values generated before time_window1, time_window2, and time_window3. The results are JSON arrays. The values must be placed in the following sequence: [current value, value generated before time_window1, value generated before time_window2, value generated before time_window3, current value/value generated before time_window1, current value/value generated before time_window2, current value/value generated before time_window3]. * | select compare(pv, 86400, 172800,604800) from ( select count(1) as pv from log)
ts_compare(value, time_window) Compares the value calculated for the current time period with the values of time_window1 and time_window2. The results are JSON arrays. The values must be placed in the following sequence: [current value, value of time_window1, current value/value of time_window1, Unix timestamp of the previous start time].

This function is used to compare time series functions. This requires the GROUP BY operation to be included in SQL statements on the time column.

* | select t, ts_compare(pv, 86400 ) as d from(select date_trunc('minute',__time__ ) as t, count(1) as pv from log group by t order by t ) group by t: compares the calculation result of each minute in the current time period per minute with that of each minute in the last time period.

Results: d:[1251.0,1264.0, 0.9897151898734177, 1539843780.0,1539757380.0]t:2018-10-19 14:23:00.000.

Examples

  • Calculate the ratio of the PV in the current hour to that in the same time period as yesterday.

    The start time is set to 2018-07-25 14:00:00. The end time is set to 2018-07-25 15:00:00.

    The query statement is as follows:

    * | select compare( pv , 86400) from (select count(1) as pv from log)
    86400 indicates that 86,400 seconds are subtracted from the current time period.
    Results:
    [9.0,19.0,0.47368421052631579]
    In the results,
    • 9.0 indicates the PV value from 14:00:00 July 25, 2018 to 15:00:00 July 25, 2018.
    • 19.0 indicates the PV value from 14:00:00 July 24, 2018 to 15:00:00 July 24, 2018.
    • 0.47368421052631579 is the PV value of the current period to that of a previous period.
    To expand the array into three columns of numbers, use the following query statement:
    * | select diff[1],diff[2],diff[3] from(select compare( pv , 86400) as diff from (select count(1) as pv from log))
  • Calculate the PV ratio for every minute of the current hour to that in the same time period as yesterday, and display the results in a line chart.
    1. Calculate the PV ratio for every minute of the current hour to that in the same time period as yesterday. The start time is set to 2018-07-25 14:00:00. The end time is set to 2018-07-25 15:00:00.

      The query statement is as follows:

      *| 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
      Results:
      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]
      T indicates the time in format of hour:minute. The content of the diff column is an array that contains the following values:
      • The PV value of the current period.
      • The PV value of the previous period.
      • The ratio of the PV value in the current period to that in the previous period.
    2. To show the query results in a line chart, use the following statement:
      *|select t, diff[1] as current, diff[2] as yesterday, 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)
      The two lines indicate the PV values of today and yesterday.
      Figure 1. Line chart
      Line chart