Simple Log Service:SQL構文と関数

Simple Log Serviceでは、SQL文を使用して検索結果のデータを分析できます。 このトピックでは、SQL分析ステートメントの基本構文について説明します。

基本構文

Search statement|Analytic statement

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

SQL関数とSQL句

ほとんどの場合、SQL関数はデータの計算、変換、およびフォーマットに使用されます。 詳細については、「SQL関数」をご参照ください。 たとえば、SQL関数を使用して、値の合計と平均を計算したり、文字列に対する操作を実行したり、日付を処理したりできます。 ほとんどの場合、SQL関数はSQL句に埋め込まれます。

SQL句は、完全なSQL検索文またはデータ処理文を作成して、データのソース、条件、グループ、および順序を識別するために使用されます。 詳細は、「SQL句」をご参照ください。

例1。 前日のクエリログ

current_date関数は、現在の日付を返します。 date_add関数は、現在の日付から特定の間隔を減算します。 結果はテーブルに表示され、直感的にデータを表示できます。 (デモ)

• クエリ文

  * |
  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関数は各州からのリクエスト数を計算します。 結果は円グラフで表示されます。 (デモ)

• クエリ文

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

例3。 受信および送信NGINXトラフィックの照会

date_trunc関数は、__time__ フィールドの値を時間単位で切り捨てます。 __time__ フィールドは、ログ収集時間を指定するシステムフィールドです。 __time__フィールドのデフォルト値は、秒単位のタイムスタンプです。 date_format関数は、切り捨てられた時間値をフォーマットします。 GROUP BY句は、時間値を集計します。 sum関数は、1時間あたりの総トラフィック量を計算します。 結果は折れ線グラフで表示され、x軸はtimeに設定され、左側のy軸はnet_outおよびnet_inに設定されます。 (デモ)

• クエリ文

  * |
  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で最もアクセスされた上位10のURLを照会する

split_part関数は、疑問符 (?) を使用してrequest_uriフィールドを配列に分割し、配列の最初の文字列からリクエストパスを取得します。 GROUP BY句は、リクエストパスを集約します。 count関数は、各パスがアクセスされた回数を計算します。 ORDER BY句は、各パスがアクセスされた回数に基づいて、パスを降順にソートします。 結果は縦棒グラフに表示されます。 (デモ)

• クエリ文

  * |
  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関数は、時間値を分単位で切り捨てます。 GROUP BY句は、切り捨てられた時間値を集計およびグループ化して、request_methodによるページビュー (PV) の数を計算します。 ORDER BY句は、時間値を昇順でソートします。 結果はフローチャートで表示されます。 チャートにおいて、x軸は時間値を示し、y軸はPVの数を示す。 集計列はrequest_methodです。 (デモ)

• クエリ文

  * |
  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の数と、当日と前日のPVの1日間の比較を照会します。

count関数は、当日のPVの数を計算します。 compare関数は、現在の日と前日の間のPVでの日々の比較を返します。 (デモ)

• クエリ文

  * |
  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式は、時間値から60で割った時間値の余りを減算して、分単位の正確なタイムスタンプを取得します。 GROUP BY句は、タイムスタンプを集約します。 count関数は1分あたりのPV数を計算し、その結果は新しいクエリのサブクエリとして使用されます。 ts_predicate_simple関数は、次の6分以内のPVの数を予測します。 結果は時系列チャートに表示されます。 (デモ)

• クエリ文

  * |
  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フィールドに基づいてグループ化および集約されます。 各エージェントからの要求の数とクライアントに返される合計応答サイズが照会されます。 総レスポンスサイズの単位はバイトです。 round関数は、バイト単位のサイズをMB単位のサイズに変換し、サイズを小数点以下2桁に丸めます。 CASE EXPRESSION式は、status文字列を2xx、3xx、4xx、5xxなどのレイヤーに分類し、各レイヤーの割合を計算します。 結果はテーブルに表示され、データとデータの意味を直感的に表示できます。 (デモ)

• クエリ文

  * |
  select
    http_user_agent as "User agent",
    count(*) as pv,
    round(sum(request_length) / 1024.0 / 1024, 2) as "Request traffic (MB)",
    round(sum(body_bytes_sent) / 1024.0 / 1024, 2) as "Response traffic (MB)",
    round(
      sum(
        case
          when status >= 200
          and status < 300 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Percentage of status code 2xx (%)",
    round(
      sum(
        case
          when status >= 300
          and status < 400 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Percentage of status code 3xx (%)",
    round(
      sum(
        case
          when status >= 400
          and status < 500 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Percentage of status code 4xx (%)",
    round(
      sum(
        case
          when status >= 500
          and status < 600 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Percentage of status code 5xx (%)"
  group by
    "User agent"
  order by
    pv desc
  limit
    100

例9. NGINXログのエラーリクエスト数の割合を照会する

ステータスコードが400を超えるエラーリクエストの数とリクエストの総数は、SQL文に基づいて取得されます。 次に、エラー要求の数に対するパーセンテージが計算される。 結果は統計チャートに表示されます。 (デモ)

• クエリ文

  * |
  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
    )

• 結果