日志服务
SQL 案例

最近更新时间：2024-02-02 15:28:01
我的收藏
本页目录：

操作场景
使用 SQL 对日志进行统计分析极为灵活，能够满足绝大多数的统计分析需求，但是 SQL 语法较为复杂，不易上手，本文介绍常用 SQL 案例以供参考。
样例数据
本文以 Nginx Demo 日志为例，介绍常用 SQL 。您可参照 使用 Demo 日志快速体验 CLS 开启  Nginx Demo 日志主题，实际执行本文中的 SQL。Nginx Demo 日志内容如下：
{
	"remote_addr": "124.78.118.162",
	"method": "GET",
	"upstream_addr": "169.254.128.14:60002",
	"upstream_response_length": "48",
	"body_bytes_sent": "59",
	"time_local": "2021-12-18T05:16:06+00:00",
	"version": "HTTP/1.1",
	"url": "/_ignition/execute-solution",
	"http_user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36",
	"remote_user": "-",
	"req_id": "37585e290318470aa57b148282aed99f",
	"upstream_status": "400",
	"request_time": "0.002",
	"sys_address": "11.149.171.195",
	"request_length": "272",
	"http_referer": "-",
	"sys_datasource": "gz.1.1.v1.2.19",
	"proxy_upstream_name": "default-kubernetes-443",
	"upstream_response_time": "0.000",
	"time": "1706701448003",
	"timestamp": "2024-01-31T19:44:08+08:00",
	"status": "400"
}
运维监控类案例
请求数 Top URL
* | select count(*) as "请求数量",
    url
    group by url
    order by "请求数量" desc
    limit 50
语句解释：
count(*)：统计所有日志条数，即请求数量。
group by url：按照 url 分组，统计每个 url 对应的请求数量。
order by "请求数量" desc：按照请求数量降序排序，优先展示请求数量多的 url。
limit 50：只返回查询结果中的前50条，即请求数量最多的50个 url。
请求数 Top URL（去除 URL 中的参数部分）
* | select count(*) as "请求数量",
    url_extract_path(url) as url_path
    group by url_path
    order by "请求数量" desc
    limit 50
语句解释：
url_extract_path(url)：去除 url 中的参数部分，仅保留 path，避免 url 参数变化影响统计。
其它语法含义同上例。
错误请求占比
* | select round(count_if(status >= 400)*1.0 / count(*),2) as "错误请求占比"
语句解释：
count_if(status >= 400)：统计满足指定条件的日志条数，此处以 http 状态码 status >= 400 代表错误请求。
*1.0：乘以1.0是为了将 count_if 的结果转换为 double 类型，以便后续进行除法运算时保留精度，否则整数相除结果仍为整数，将丢失精度。
count(*)：统计所有日志条数，即总请求数量。
round：保留小数位数，此处为保留2位小数。
错误请求数量按 url 分布
status >= 400 | select count(*) as "错误请求数量",url
    group by url
    order by "错误请求数量" desc
    limit 50
语句解释：
status >= 400：管道符|前的语句为检索条件，该条件代表仅对 http 状态码 status >= 400 的日志进行统计分析。
count(*)：统计所有日志条数，因为管道符|前已有检索条件，此处实际上统计的是所有错误请求日志条数。
group by url：按照 url 分组，统计每个 url 对应的错误请求数量。
order by "错误请求数量" desc：按照错误请求数量降序排序，优先展示错误请求数量多的 url。
limit 50：只返回查询结果中的前50条，即错误请求数量最多的50个 url。
错误请求占比变化趋势
* | select round(count_if(status >= 400)*1.0 / count(*),2) as "错误请求占比",
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time
    group by time limit 10000
语句解释：
count_if(status >= 400)：统计满足指定条件的日志条数，此处以 http 状态码 status >= 400 代表错误请求。
*1.0：乘以1.0是为了将 count_if 的结果转换为 double 类型，以便后续进行除法运算时保留精度，否则整数相除结果仍为整数，将丢失精度。
count(*)：统计所有日志条数，即总请求数量。
round：保留小数位数，此处为保留2位小数。
histogram(__TIMESTAMP__,INTERVAL ${__interval})：按时间查看指标变化趋势时，需指定趋势图中每个数据点之间的间隔，例如查询最近60分钟的变化趋势，数据点之间的间隔为30秒。此处：
__TIMESTAMP__ 代表日志时间，即请求的发生时间。
${__interval} 代表数据点之间的间隔，是一个变量，将根据查询时间范围的长度动态计算最合适的间隔。也可手动指定，例如修改为 1 MINUTE，代表间隔为1分钟。
group by time：使用 histogram 指定好时间间隔后，按该间隔分别统计每个间隔的错误请求占比。
limit 10000：SQL 未指定 limit 时，默认仅返回100条查询结果，为获取完整的查询结果，将 limit 指定为更大的值。
P95 耗时最高的 url
* | select approx_percentile(request_time,0.95) as P95,
    url
    group by url
    order by P95 desc
    limit 50
语句解释：
approx_percentile(request_time,0.95)：对 request_time 进行统计，获取其位于 95% 分位的值，即 95% 的请求耗时低于该值。
group by url：按照 url 分组，统计每个 url 对应 P95。
order by P95 desc：按照 P95 降序排序，优先展示 P95 高的 url。
limit 50：只返回查询结果中的前50条，即 P95 最高的50个 url。
P95 耗时变化趋势
* | select approx_percentile(request_time,0.95) as P95,
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time
    group by time limit 10000
语句解释：
approx_percentile(request_time,0.95)：对 request_time 进行统计，获取其位于 95% 分位的值，即 95% 的请求耗时低于该值。
histogram(__TIMESTAMP__,INTERVAL ${__interval})：按时间查看指标变化趋势时，需指定趋势图中每个数据点之间的间隔，例如查询最近60分钟的变化趋势，数据点之间的间隔为30秒。此处：
__TIMESTAMP__ 代表日志时间，即请求的发生时间。
${__interval} 代表数据点之间的间隔，是一个变量，将根据查询时间范围的长度动态计算最合适的间隔。也可手动指定，例如修改为 1 MINUTE，代表间隔为1分钟。
group by time：使用 histogram 指定好时间间隔后，按该间隔分别统计每个间隔的 P95 耗时。
limit 10000：SQL 未指定 limit 时，默认仅返回100条查询结果，为获取完整的查询结果，将 limit 指定为更大的值。
各 URL P95 耗时变化趋势（仅查看耗时最高的10个 URL）
* | select approx_percentile(request_time,0.95) as P95,
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time,
    url
    where url in  (select url group by url order by approx_percentile(request_time,0.95) desc limit 10)
    group by time,url
    order by P95 desc limit 10000
语句解释：
where url in  (select url group by url order by approx_percentile(request_time,0.95) desc limit 10)：URL 很多是，直接查看所有 URL 各自的耗时变化趋势会使得趋势图非常凌乱，通过该过滤条件仅查看耗时最高的10各 URL 。
其它语法含义同上例。
网络总流量最高的 url
* | select sum(body_bytes_sent) as "网络流量",
    url
    group by url
    order by "网络流量" desc
    limit 50
语句解释：
sum(body_bytes_sent)：对 body_bytes_sent 进行求和，获取网络总流量。
group by url：按照 url 分组，统计每个 url 对应的网络总流量。
order by "网络流量" desc：按照 P95 降序排序，优先展示网络总流量高的 url。
limit 50：只返回查询结果中的前50条，即网络总流量最高的50个 url。
运营分析类案例
各 url PV 及 UV
* | select count(*) as PV, approx_distinct(remote_addr) as UV, url
    group by url
    order by PV desc
    limit 50
语句解释：
approx_distinct(remote_addr)：对客户端 IP remote_addr 去重统计数量作为 UV。
group by url：按照 url 分组，统计每个 url 对应的 PV 及 UV。
order by PV desc：按照 PV 降序排序，优先展示 PV 高的 url。
limit 50：只返回查询结果中的前50条，即 PV 最高的50个 url。
UV 国内省份分布
* | select approx_distinct(remote_addr) as UV, ip_to_province(remote_addr) as province
    where ip_to_province(remote_addr) != 'NULL' and ip_to_country(remote_addr) = '中国'
    group by province
    order by UV desc
    limit 1000
语句解释：
approx_distinct(remote_addr)：对客户端 IP remote_addr 去重统计数量作为 UV。
ip_to_province(remote_addr)：根据客户端IP remote_addr 获取客户端所在的省份。
where ip_to_province(remote_addr) != 'NULL' and ip_to_country(remote_addr) = '中国'：过滤掉无省份信息的 IP（例如内网IP）并只保留国内 IP。
group by province：按照 province 分组，统计每个 province 对应的 UV。
order by UV desc：按照 UV 降序排序，优先展示 PV 高的 province。
limit 50：只返回查询结果中的前50条，即 UV 最高的50个 province。
UV 环比
* | 
select compare[1] as today, compare[2] as yesterday, round(compare[3],4) as ratio
from (
    select compare(UV, 86400) as compare
    from (
        select approx_distinct(remote_addr) as UV
    )
)
语句解释：
approx_distinct(remote_addr)：对客户端 IP remote_addr 去重统计数量作为 UV。
compare(UV, 100) as compare：对子查询中的 UV 计算其环比值，86400 代表计算其与 86400秒（即1天）前的比值，结果为数组，按顺序分别为当前 UV（compare[1]）、一天前的 UV（compare[2]）、当前 UV 与一天前的比值（compare[3]）。
PV 按移动端设备类型分布
* | select 
    case when 
           lower(http_user_agent) like '%iphone%' OR 
           lower(http_user_agent) like '%ipod%' OR 
           lower(http_user_agent) like '%ipad%' OR 
           lower(http_user_agent) like '%ios%'
         then 'IOS'
         when
           lower(http_user_agent) like '%android%'
         then 'Android'
         else 'other'
    end as type , 
    count(*) as PV
    group by type
    HAVING type != 'other'
语句解释：
case when：根据 http_user_agent 中包含的关键字将其分别归类至不同的设备类型（IOS、Android、other）。
lower(http_user_agent)：将 http_user_agent 转换为小写，方便匹配关键字。
count(*) as PV：统计所有日志条数，即 PV。
group by type：按照设备类型分组，统计每个设备类型对应的 PV。
HAVING type != 'other'：针对统计的结果进行二次过滤，去除 other 类型的设备。
Top 访问量 IP
* | select count(*) as PV, remote_addr as IP
    group by IP
    order by PV desc
    limit 50
语句解释：
count(*) as PV：统计所有日志条数，即 PV。
group by IP：按 IP 分组，统计每个 IP 对应的 PV。
order by PV desc：按照 PV 降序排序，优先展示 PV 高的 IP。
limit 50：只返回查询结果中的前50条，即 PV 最高的50个 IP。
?