SQL 查询示例

以下示例显示了可在 Log Explorer 中的 http_requests 数据集上使用的实用 SQL 查询。有关受支持的 SQL 语法的完整列表，请参阅受支持的 SQL 查询。

调整每个示例中的时间范围，以匹配您想要查询的时间段。

汇总 CDN 使用情况

获取特定时间段内总请求数和数据传输的高级汇总。结果包括传输的总字节数以及转换为兆字节 (MB) 和吉字节 (GB) 的数据。

SELECT
  COUNT(*) AS total_requests,
  SUM(EdgeResponseBytes) AS total_data_transfer,
  SUM(EdgeResponseBytes) / (1024.0 * 1024.0 * 1024.0) AS total_data_transfer_gb,
  SUM(EdgeResponseBytes) / (1024.0 * 1024.0) AS total_data_transfer_mb
FROM
  http_requests
WHERE {{ timeFilter }}

查看安全操作的分布

了解诸如阻止 (block) 和挑战 (challenge) 等安全操作在您的流量中是如何分布的，并确定对请求应用的最常见安全响应。

SELECT
  SecurityAction,
  COUNT(*) AS ActionCount
FROM http_requests
WHERE SecurityAction != 'unknown'
  AND SecurityAction IS NOT NULL
GROUP BY SecurityAction
ORDER BY ActionCount DESC

查找触发挑战的 IP

识别触发托管、JavaScript 或交互式挑战的前几名客户端 IP 地址和请求 URI，以调查潜在的机器人活动或有针对性的攻击。

SELECT
  ClientIP,
  ClientRequestURI,
  SecurityActions,
  COUNT(*) AS Count
FROM http_requests
WHERE {{ timeFilter }}
  AND (
    ARRAY_CONTAINS(SecurityActions, 'challenge')
    OR ARRAY_CONTAINS(SecurityActions, 'managedChallenge')
    OR ARRAY_CONTAINS(SecurityActions, 'jsChallenge')
    OR ARRAY_CONTAINS(SecurityActions, 'challengeSolved')
  )
GROUP BY
  ClientIP,
  ClientRequestURI,
  SecurityActions
ORDER BY Count DESC
LIMIT 20

按 URI 查找带宽消耗最高的对象

识别哪些请求 URI 消耗的带宽最多，以精确定位导致最多数据传输的大型资源或端点。

SELECT
  ClientRequestURI,
  SUM(EdgeResponseBytes) / (1024 * 1024) AS MegabytesTransferred
FROM http_requests
WHERE  {{ timeFilter }}
GROUP BY ClientRequestURI
ORDER BY MegabytesTransferred DESC
LIMIT 10

按国家/地区分析客户端往返时间 (RTT)

分析不同国家/地区的客户端 TCP 往返时间 (RTT)，以识别可能会从额外优化中受益的高延迟区域。

SELECT
  ClientCountry,
  COUNT(*) AS requests,
  AVG(ClientTCPRttMs) AS avg_rtt,
  MIN(ClientTCPRttMs) AS min_rtt,
  MAX(ClientTCPRttMs) AS max_rtt
FROM http_requests
WHERE {{ timeFilter }}
GROUP BY ClientCountry
ORDER BY avg_rtt DESC
LIMIT 20

按缓存状态汇总 CDN 流量

按缓存状态细分流量，并测量每个状态的平均首字节时间 (TTFB)，以评估缓存有效性并识别提高缓存命中率的机会。

SELECT
  CacheCacheStatus,
  COUNT(*) AS requests,
  SUM(EdgeResponseBytes) AS total_bytes,
  AVG(EdgeTimeToFirstByteMs) AS avg_ttfb
FROM http_requests
WHERE {{ timeFilter }}
GROUP BY CacheCacheStatus
ORDER BY requests DESC

按首字节时间查找最慢的路径

查找平均首字节时间 (TTFB) 最长的请求路径，以及请求计数和服务器错误计数，以识别可能需要优化的慢速端点。

SELECT
  ClientRequestPath,
  AVG(EdgeTimeToFirstByteMs) AS avg_ttfb,
  COUNT(*) AS requests,
  SUM(CASE WHEN EdgeResponseStatus >= 500 THEN 1 ELSE 0 END) AS errors
FROM http_requests
WHERE {{ timeFilter }}
GROUP BY ClientRequestPath
ORDER BY avg_ttfb DESC
LIMIT 10