嵌套子查询是将一个 SELECT 语句嵌套在另一个 SELECT 语句中，表示先对原始数据进行 SELECT 统计分析，再基于分析结果进行二次统计分析，适用于相对复杂的分析场景。

语法格式

嵌套子查询的语法格式如下：

* | SELECT Key FROM (sub_query)

参数说明如下所示。

语法示例

示例 1

• 场景
  根据访问日志统计各个版本客户端的用户数。

• 检索分析语句

  * | SELECT Client,COUNT(*) AS UserNum 
  FROM (SELECT Client,account_id GROUP BY Client,account_id) 
  GROUP BY Client 
  ORDER BY Client
  

  • 第一层语句 SELECT Client,account_id GROUP BY Client,account_id 表示按照 Client 和 account_id 分组去重。
  • 第二层语句 SELECT Client,count(*) AS UserNum FROM 表示对第一层语句的结果再做分组统计，获取各个客户端版本对应的用户数。

• 检索分析结果
  

示例 2

• 场景
  计算今天每小时的订单量与昨天同时段、前天同时段的订单量比值。

• 检索分析语句

  * |SELECT time,
    diff [1] AS day1,
    diff [2] AS day2,
    diff [3] AS day3,
    diff [4] AS ratio1,
    diff [5] AS ratio2
  FROM  (
      SELECT time,
        TS_COMPARE(PV, 86400, 172800) AS diff
      FROM      (
          SELECT
              COUNT(*) AS PV,
              DATE_TRUNC('hour', __time__) AS time
          GROUP BY time
        )
      GROUP BY time
      ORDER BY time
    )
  

  • 第一层语句 SELECT COUNT(*) AS PV, DATE_TRUNC('hour', __time__) AS time GROUP BY time 表示统计每小时的订单量。
  • 第二层语句 SELECT time, TS_COMPARE(PV, 86400, 172800) AS diff FROM 表示对第一层语句的结果再做二次统计，获取今天、昨天和前天同时段的订单量以及比值。
  • 第三层语句 SELECT diff [1] AS day1, diff [2] AS day2, diff [3] AS day3, diff [4] AS ratio1, diff [5] AS ratio2 FROM 表示展开第二层语句的结果。第二层语句的结果为数组形式，详细说明请参考同比与环比函数。

• 检索分析结果