Simple Log Service:SQL の構文と関数

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

基本構文

検索文|分析文

* | 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__ フィールドはシステムフィールドであり、デフォルトでログのインジェスト時間を UNIX タイムスタンプとして表します。その後、date_format 関数を使用して揃えられたタイムスタンプをフォーマットし、group by 句を使用してフォーマット済みの時刻ごとに結果をグループ化し、sum 関数を使用して時間ごとのトラフィック合計を計算します。結果は折れ線グラフで表示され、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 を使用して回数をソートします。DESC キーワードは、結果を大きい順から小さい順に降順でソートすることを指定します。結果は縦棒グラフで表示されます。（デモを試す）

• クエリ文

  * |
  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 が配置されます。（デモを試す）

• クエリ文

  * |
  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 を比較する

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 はタイムスタンプを分単位に揃えて、stamp 値を生成します。その後、group by 句を使用して stamp ごとに結果をグループ化し、count 関数を使用して分ごとのイベント数を計算します。この結果は、ts_predicate_simple 関数のサブクエリとして使用され、次の 6 つのデータポイントを予測します。クエリ結果は時系列グラフで表示されます。（デモを試す）

• クエリ文

  * |
  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 に変換し、小数点以下 2 桁に四捨五入します。case when 式を使用して、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
    )

• 結果