使用SQL函數compare和ts_compare計算日誌同比環比-Log Service-阿里雲

本文介紹同比和環比函數的基礎文法和樣本。

Log Service支援如下同比和環比函數。

重要

在Log Service分析語句中，表示字串的字元必須使用單引號（''）包裹，無符號包裹或被雙引號（""）包裹的字元表示欄位名或列名。例如：'status'表示字串status，status或"status"表示日誌欄位status。

函數名稱	文法	說明	支援SQL	支援SPL
compare函數	compare(x, n)	對比目前時間周期內的計算結果與n秒之前時間周期內的計算結果。	√	×
compare函數	compare(x, n1, n2, n3...)	對比目前時間周期內的計算結果與n1、n2、n3秒之前時間周期內的計算結果。	√	×
ts_compare函數	ts_compare(x, n)	對比目前時間周期內整點時間（比如：每小時）的計算結果與n秒之前時間周期內整點時間的計算結果。	√	×
ts_compare函數	ts_compare(x, n1, n2, n3...)	對比目前時間周期內整點時間（比如：每小時）的計算結果與n1、n2、n3秒之前時間周期內整點時間的計算結果。	√	×

重要

ts_compare函數必須按照時間列進行分組（GROUP BY），並且僅支援一個時間列。
compare和ts_compare不支援嵌套使用。

compare函數

compare函數用於對比目前時間周期內的計算結果與n秒之前時間周期內的計算結果。

文法

對比目前時間周期內的計算結果與n秒之前時間周期內的計算結果。
```
compare(x, n)
```
對比目前時間周期內的計算結果與n1、n2、n3秒之前時間周期內的計算結果。
```
compare(x, n1, n2, n3...)
```

參數說明

參數	說明
x	參數值為double類型或long類型。
n	時間視窗，單位為秒。例如3600（1小時）、86400（1天）、604800（1周）、31622400（1年）。

傳回值類型

數組。格式為[當前計算結果, n秒前的計算結果, 當前計算結果與n秒前計算結果的比值]。

樣本

樣本1：計算當前1小時和昨天同時段的網站訪問量比值。

選取查詢和分析的時間範圍為1小時（整點時間），並執行如下查詢和分析語句。其中86400表示目前時間減去86400秒（1天），log表示LogStore名稱。
- 查詢和分析結果為數組形式
  - 查詢和分析語句（調試）
```
* |
SELECT
  compare(PV, 86400)
FROM  (
    SELECT
      count(*) AS PV
    FROM      log
  )
```
  - 查詢和分析結果
    - 3337.0表示當前1小時（例如2020-12-25 14:00:00~2020-12-25 15:00:00）的網站訪問量。
    - 3522.0表示昨天同時段（例如2020-12-24 14:00:00~2020-12-24 15:00:00）的網站訪問量。
    - 0.947473026689381表示當前1小時與昨天同時段的網站訪問量比值。
- 查詢和分析結果為分列顯示
  - 查詢和分析語句（調試）
```
* |
SELECT
  diff [1] AS today,
  diff [2] AS yesterday,
  diff [3] AS ratio
FROM  (
    SELECT
      compare(PV, 86400) AS diff
    FROM      (
        SELECT
          count(*) AS PV
        FROM          log
      )
  )
```
    compareFunction Compute結果為數組形式，其中diff為compareFunction Compute結果的別名，diff [1]表示擷取數組中的第一個值。
  - 查詢和分析結果
    - 3337.0表示當前1小時（例如2020-12-25 14:00:00~2020-12-25 15:00:00）的網站訪問量。
    - 3522.0表示昨天同時段（例如2020-12-24 14:00:00~2020-12-24 15:00:00）的網站訪問量。
    - 0.947473026689381表示當前1小時與昨天同時段的網站訪問量比值。
樣本2：統計當前1小時內各個請求狀態對應的要求方法，以及該請求狀態和要求方法對應的請求數量和昨天同時段的對比。

選取查詢和分析的時間範圍為1小時（整點時間），並執行如下查詢和分析語句。其中3600表示目前時間減去3600秒（1小時），log表示LogStore名稱。
- 查詢和分析語句（調試）
```
* |
SELECT
  status,
  request_method,
  compare(PV, 3600)
FROM  (
    SELECT
      status,
      request_method,
      count(*) AS PV
    FROM      log
    GROUP BY
      status,
      request_method
  )
GROUP BY
  status,
  request_method
```
- 查詢和分析結果

ts_compare函數

ts_compare函數用於對比目前時間周期內整點時間（比如：每小時）的計算結果與n秒之前時間周期內整點時間的計算結果。

重要

ts_compare函數必須按照時間列進行分組（GROUP BY），並且僅支援一個時間列。

文法

對比目前時間周期內整點時間（比如：每小時）的計算結果與n秒之前時間周期內整點時間的計算結果。
```
ts_compare(x, n)
```
對比目前時間周期內整點時間（比如：每小時）的計算結果與n1、n2、n3秒之前時間周期內整點時間的計算結果。
```
ts_compare(x, n1, n2, n3...)
```

參數說明

參數	說明
x	參數值為double類型或long類型。
n	時間視窗，單位為秒。例如3600（1小時）、86400（1天）、604800（1周）、31622400（1年）。

傳回值類型

數組。格式為[當前計算結果, n秒前的計算結果, 當前計算結果與n秒前計算結果的比值, n秒前的UNIX時間戳記]。

樣本

樣本1：計算今天每小時的網站訪問量與昨天同時段、前天同時段的網站訪問量比值。

選取查詢和分析的時間範圍為今天（整點時間），並執行如下查詢和分析語句。其中，86400表示目前時間減去86400秒（1天），172800表示目前時間減去172800秒（2天）， log表示LogStore名稱，date_trunc('hour',__time__ )表示使用date_trunc函數將時間對齊到小時。
- 查詢和分析結果為數組形式
  - 查詢和分析語句（調試）
```
* |
SELECT
  time,
  ts_compare(PV, 86400, 172800) as diff
FROM  (
    SELECT
      count(*) as PV,
      date_trunc('hour', __time__) AS time
    FROM      log
    GROUP BY
      time
  )
GROUP BY
  time
ORDER BY
  time
```
  - 查詢和分析結果
    - 1174.0表示當前時段（例如2022-09-22 00:00~01:00）的網站訪問量。
    - 1191.0表示昨天同時段（例如2022-09-21 00:00~01:00）的網站訪問量。
    - 1253.0表示前天同時段（例如2022-09-20 00:00~01:00）的網站訪問量。
    - 0.9857262804366079表示當前時段與昨天同時段的網站訪問量比值。
    - 0.936951316839585表示當前時段與前天同時段的網站訪問量比值。
    - 1663689600.0表示2022-09-21 00:00的Unix時間戳記。
    - 1663603200.0表示2022-09-20 00:00的Unix時間戳記。
    說明
    查詢和分析結果的時間，以實際執行結果為準。
- 查詢和分析結果為分列顯示
  - 查詢和分析語句（調試）
```
* |
SELECT
  time,
  diff [1] AS day1,
  diff [2] AS day2,
  diff [3] AS day3,
  diff [4] AS ratio1,
  diff [5] AS ratio2
FROM  (
    SELECT
      time,
      ts_compare(PV, 86400, 172800) AS diff
    FROM      (
        SELECT
          count(*) as PV,
          date_trunc('hour', __time__) AS time
        FROM          log
        GROUP BY
          time
      )
    GROUP BY
      time
    ORDER BY
      time
  )
```
  - 查詢和分析結果
樣本2：環比今天每小時的網站訪問量。

選取查詢和分析的時間範圍為今天（相對），並執行如下查詢和分析語句。其中3600表示目前時間減去3600秒（1小時）， log表示LogStore名稱，date_trunc('hour',__time__ )表示使用date_trunc函數將時間對齊到小時。
- 查詢和分析語句（調試）
```
* |
SELECT
  time,
  ts_compare(PV, 3600) AS data
FROM(
    SELECT
      date_trunc('hour', __time__) AS time,
      count(*) AS PV
    FROM      log
    GROUP BY
      time
    ORDER BY
      time
  )
GROUP BY
  time
```
- 查詢和分析結果
  
  說明
  查詢和分析結果的時間，以實際執行結果為準。