SQL分析语法与功能概述_日志服务(SLS)-阿里云帮助中心

日志服务Project支持使用SQL语句对查询结果进行分析。本文介绍SQL分析语句基础语法。

基础语法

查询语句和分析语句以|分割。其格式为：

查询语句|分析语句

查询语句可单独使用，分析语句必须与查询语句一起使用。即分析功能是基于查询结果或全量数据进行的。

重要

查询语句中建议不超过30个条件。
分析语句中不写FROM子句和WHERE子句时，默认分析当前Logstore中的数据。分析语句不支持使用offset，不区分大小写，末尾无需加分号。

语句类型

说明

查询语句

查询条件，可以为关键词、数值、数值范围、空格、星号（*）等。

如果为空格或星号（*），表示无过滤条件。

分析语句

对查询结果或全量数据进行计算和统计。日志服务支持的分析函数和语法，请参见：

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
  )

结果展示

上一篇: 高精度时间戳（精确到纳秒）和全局排序下一篇: SQL函数