日志服务 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 指定为更大的值。

九、各状态码请求数量变化趋势

* | select count(*) as cnt,status, histogram(__TIMESTAMP__,interval ${__interval}) as time group by time,status limit 10000

语句解释：

·count(*)：统计所有日志条数，即总请求数量。

·histogram(__TIMESTAMP__,INTERVAL ${__interval})：按时间查看指标变化趋势时，需指定趋势图中每个数据点之间的间隔，例如查询最近60分钟的变化趋势，数据点之间的间隔为30秒。此处：

·__TIMESTAMP__ 代表日志时间，即请求的发生时间。

·${__interval} 代表数据点之间的间隔，是一个变量，将根据查询时间范围的长度动态计算最合适的间隔。也可手动指定，例如修改为1 MINUTE，代表间隔为1分钟。

·group by time：使用 histogram 指定好时间间隔后，按该间隔分别统计每个间隔的错误请求占比。

·limit 10000：SQL 未指定 limit 时，默认仅返回100条查询结果，为获取完整的查询结果，将 limit 指定为更大的值。

十、各状态码请求占比变化趋势

* | select time,status, cnt*1.0/sum(cnt) over (partition by time) as pct from ( select count(*) as cnt,status, histogram(__TIMESTAMP__,interval ${__interval}) as time group by time,status) limit 10000

语句解释：

·select count(*) as cnt……group by time,status：该查询与 各状态码请求数量变化趋势 案例一致，统计各状态码请求数量变化趋势。

·sum(cnt) over (partition by time)：根据子查询的结果，把每个时间间隔下不同状态码的请求数量相加，作为该时间间隔下的请求总数。

·cnt*1.0/sum(cnt) over (partition by time) as pct：将每个状态码的请求数量除以请求总数，获得该状态码的请求占比。其中*1.0用于将 cnt 转换为 double 类型，以便后续进行除法运算时保留精度，否则整数相除结果仍为整数，将丢失精度。

十一、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。

十五、按照版本号范围过滤日志

* | select * where array_join(transform(split(version, '.'), x -> lpad(x, 10, '0')), '.') > array_join(transform(split('2.0.0', '.'), x -> lpad(x, 10, '0')), '.')

语句解释：

版本号 version 为字符串，例如“2.0.1”，无法直接使用检索条件按范围对其进行过滤，因此需在 SQL 中进行过滤。上面的 SQL 可过滤版本号大于等于“2.0.0”的日志。

·split(version, '.')：使用符号.将版本号拆分为多个部分，结果为数组。例如将2.0.1转换为["2","0","1"]。

·transform(split(version, '.'), x -> lpad(x, 10, '0'))：使用 transform 函数在数组中每个数字左侧补0，使长度均为10，避免版本号中每部分长度不一致影响后续比较。例如将2.0.1转换为["0000000002","0000000000","0000000001"]。

·array_join：使用符号.将数组拼接为字符串，由于已经使用 transform 函数将版本号中每部分的长度均统一为了10，此时的字符串可直接按字符进行比较。

十七、各 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 ratiofrom ( 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]）。

20、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 类型的设备。

21、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。