Nested subqueries in Simple Log Service - Simple Log Service

Nested subqueries embed one SELECT statement inside another, enabling multi-step analysis such as aggregation over aggregated results.

Syntax

Use a FROM clause in the outer SELECT to wrap a subquery.

* | SELECT key FROM (sub_query)

Important

Enclose the subquery in parentheses ().
Include the FROM log clause in the subquery to query the current Logstore.

Examples

Example 1

Count requests by method, then find the minimum count.

Query statement

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

Query and analysis results

Example 2

Compare page views (PVs) in the current hour with PVs from the same hour yesterday. The query time range is 1 hour (on the hour). 86400 is the number of seconds in one day. log is the Logstore name.

Query statement

* |
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
      )
  )

Query and analysis results
- 3337.0: PVs in the current hour (e.g., 14:00–15:00 on December 25, 2020).
- 3522.0: PVs in the same hour yesterday (e.g., 14:00–15:00 on December 24, 2020).
- 0.947473026689381: Ratio of current-hour PVs to yesterday's PVs.

Example 3

Count PVs per page and calculate each page's percentage of total PVs.

Query statement

* |
SELECT
  request_uri AS "Accessed Page",
  c AS "Count",
  round(c * 100.0 /(sum(c) over()), 2) AS "Percentage (%)"
FROM  (
    SELECT
      request_uri AS request_uri,
      count(*) AS c
    FROM      log
    GROUP BY
      request_uri
    ORDER BY
      c DESC
  )

Query and analysis results