SQL分析文法與功能概述 - Simple Log Service

Simple Log ServiceProject支援使用SQL語句對查詢結果進行分析。本文介紹SQL分析語句基礎文法。

基礎文法

查詢語句和分析語句以|分割。其格式為：

查詢語句|分析語句

查詢語句可單獨使用，分析語句必須與查詢語句一起使用。即分析功能是基於查詢結果或全量資料進行的。

重要

查詢語句中建議不超過30個條件。
分析語句中不寫FROM子句和WHERE子句時，預設分析當前Logstore中的資料。分析語句不支援使用offset，不區分大小寫，末尾無需加分號。

語句類型

說明

查詢語句

查詢條件，可以為關鍵詞、數值、數值範圍、空格、星號（*）等。

如果為空白格或星號（*），表示無過濾條件。

分析語句

對查詢結果或全量資料進行計算和統計。Log Service支援的分析函數和文法，請參見：

SQL分析語句樣本：

* | SELECT status, count(*) AS PV GROUP BY status

SQL函數與SQL子句

SQL函數通常用於對資料進行計算、轉換和格式化。例如，計算總和、平均值、字串操作、日期處理等。SQL函數通常嵌入在SQL子句中使用。

SQL子句用於構建完整的SQL查詢或資料動作陳述式，決定資料的來源、條件、分組、排序等。

樣本1. 查詢昨天的日誌

通過current_date函數返回當前日期。再使用date_add函數在當前日期中減去指定的時間間隔。通過表格進行展示，可以較為直觀的看到這些資料。（試用 Demo）

查詢與分析語句

* |
SELECT
  *
FROM  log
WHERE
  __time__ < to_unixtime(current_date)
  AND __time__ > to_unixtime(date_add('day', -1, current_date))

結果展示

樣本2. 查看日誌來源IP的分布情況

通過ip_to_province函數得出ip對應的省地址，用group by對地址彙總，用count函數計算出每個地址出現的次數。通過餅圖進行展示。（試用 Demo）

查詢與分析語句

* |
select  
  count(1) as c,
  ip_to_province(remote_addr) as address
group by
  address
limit
  100

結果展示

樣本3. 統計Nginx流入流出的流量

通過date_trunc函數將__time__對齊到小時（__time__為系統欄位，日誌採集的時間，預設為秒時間戳記），用date_format函數將對齊的結果進行格式化，用group by將對齊的時間彙總，用sum函數計算出每小時流量合計，通過線圖進行展示，X軸設定為time，左Y軸選擇net_out和net_in。（試用 Demo）

查詢與分析語句

* |
select
  sum(body_bytes_sent) as net_out,
  sum(request_length) as net_in,
  date_format(date_trunc('hour', __time__), '%m-%d %H:%i') as time
group by
  date_format(date_trunc('hour', __time__), '%m-%d %H:%i')
order by
  time
limit
  10000

結果展示

樣本4. 查看Nginx訪問前十的地址

通過split_part函數將request_uri按?分割成array，取分割後的第一個字串，得出請求的路徑。按這個路徑group by進行彙總，用count函數計算每個路徑訪問的次數，用order by對次數進行排序，desc表示順序是從大到小，通過柱狀圖進行展示。（試用 Demo）

查詢與分析語句

* |
select
  count(1) as pv,
  split_part(request_uri, '?', 1) as path
group by
  path
order by
  pv desc
limit
  10

結果展示

樣本5. 查看要求方法分類pv趨勢

使用date_trunc函數將時間按照分鐘對齊，然後與request_method一起分組彙總計算pv。然後按照時間繼續排序，使用流圖展示，x軸為時間，y軸為pv，彙總列為request_method。（試用 Demo）

查詢與分析語句

* |
select
  date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as t,
  request_method,
  count(*) as pv
group by
  t,
  request_method
order by
  t asc
limit
  10000

結果展示

樣本6. 查看今日PV和昨日對比

先通過countFunction Compute總的pv，再用compare函數得出今日的 pv 與昨日的同比。（試用 Demo）

查詢與分析語句

* |
select
  diff [1] as today,
  round((diff [3] -1.0) * 100, 2) as growth
FROM
  (
    SELECT
      compare(pv, 86400) as diff
    FROM
      (
        SELECT
          COUNT(1) as pv
        FROM
          log
      )
  )

結果展示

樣本7. 預測Nginx訪問日誌的PV

time - time % 60 （將time時間戳記減去time時間戳記對60的餘數），得到按分鐘對齊的時間stamp，用group by對stamp彙總，用count函數計算每分鐘的次數，將得到的結果作為一個子查詢，用ts_predicate_simple函數預測未來6個點的情況，查詢結果按時序圖進行展示。（試用 Demo）

查詢與分析語句

* |
select
  ts_predicate_simple(stamp, value, 6)
from
  (
    select
      __time__ - __time__ % 60 as stamp,
      COUNT(1) as value
    from
      log
    GROUP BY
      stamp
    order by
      stamp
  )
LIMIT
  1000

結果展示

樣本8. 統計HTTP_USER_AGENT並根據PV進行排序展示

通過http_user_agent分組彙總，然後查詢出各個代理的請求、以及返回用戶端流量的和，由於單位是byte，使用round函數運算轉為MB並保留兩位小數。再使用case when為status分層，分為2xx、3xx、4xx、5xx以及各層所佔的比例。使用表格展示，可以較為直觀的看到這些資料及含義。（試用 Demo）

查詢與分析語句

* |
select
  http_user_agent as "使用者代理程式",
  count(*) as pv,
  round(sum(request_length) / 1024.0 / 1024, 2) as "請求報文流量(MB)",
  round(sum(body_bytes_sent) / 1024.0 / 1024, 2) as "返回用戶端流量(MB)",
  round(
    sum(
      case
        when status >= 200
        and status < 300 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "2xx比例(%)",
  round(
    sum(
      case
        when status >= 300
        and status < 400 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "3xx比例(%)",
  round(
    sum(
      case
        when status >= 400
        and status < 500 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "4xx比例(%)",
  round(
    sum(
      case
        when status >= 500
        and status < 600 then 1
        else 0
      end
    ) * 100.0 / count(1),
    6
  ) as "5xx比例(%)"
group by
  "使用者代理程式"
order by
  pv desc
limit
  100

結果展示

樣本9. 分析Nginx日誌錯誤請求佔比

先在SQL內部擷取到請求status超過400的錯誤請求數量，以及總的請求數量，然後再外部計算比值，展示時使用統計圖。（試用 Demo）

查詢與分析語句

* |
select
  round((errorCount * 100.0 / totalCount), 2) as errorRatio
from
  (
    select
      sum(
        case
          when status >= 400 then 1
          else 0
        end
      ) as errorCount,
      count(1) as totalCount
    from
      log
  )

結果展示