SQL分析语法与功能

日志服务Project支持使用SQL语句对查询结果进行分析。本文介绍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和昨日对比

先通过count函数计算总的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
    )

• 结果展示