嵌套子查询

基本语法

使用嵌套子查询时，需在SELECT语句中指定FROM子句。

* | SELECT key FROM (sub_query)

示例

计算各个请求方法对应的请求数量，然后获取最小的请求数量。

• 查询和分析语句

  * |
  SELECT
   min(PV)
  FROM  (
      SELECT
        count(1) as PV
      FROM      log
      GROUP BY
        request_method
    )

• 查询和分析结果

计算当前1小时和昨天同时段的网站访问量比值。其中，选择查询和分析的时间范围为1小时（整点时间），86400表示当前时间减去86400秒（1天），log表示Logstore名称。

• 查询和分析语句

  * |
  SELECT
    diff [1] AS today,
    diff [2] AS yesterday,
    diff [3] AS ratio
  FROM  (
      SELECT
        compare(PV, 86400) AS diff
      FROM      (
          SELECT
            count(*) AS PV
          FROM          log
        )
    )

• 查询和分析结果

  • 3337.0表示当前1小时（例如2020-12-25 14:00:00~2020-12-25 15:00:00）的网站访问量。

  • 3522.0表示昨天同时段（例如2020-12-24 14:00:00~2020-12-24 15:00:00）的网站访问量。

  • 0.947473026689381表示当前1小时与昨天同时段的网站访问量比值。

统计各个访问页面的访问次数及占比。

• 查询和分析语句

  * |
  SELECT
    request_uri AS "访问页面",
    c AS "次数",
    round(c * 100.0 /(sum(c) over()), 2) AS "百分比%"
  FROM  (
      SELECT
        request_uri AS request_uri,
        count(*) AS c
      FROM      log
      GROUP BY
        request_uri
      ORDER BY
        c DESC
    )

• 查询和分析结果