Basic syntax and example of the WITH clause - Simple Log Service

A WITH clause saves the result of a subquery to a temporary table so that subsequent SQL statements can analyze the data in that table. WITH clauses simplify SQL statements and improve readability.

Syntax

WITH table_name AS (select_statement) select_statement

Parameters

Parameter	Description
table_name	The name of the temporary table.
select_statement	The SELECT statement that defines the data for the temporary table.

Example

The following statement calculates the average request length per host in a Logstore named website_log and saves the result to temporary table T1, then does the same for a Logstore named access_log and saves the result to temporary table T2. A JOIN clause combines T1 and T2 to retrieve the average request length for hosts that exist in both Logstores.

Query statement

* | with T1 AS (
  SELECT
    host,
    avg(request_length) length
  FROM    website_log
  GROUP BY
    host
),
T2 AS (
  SELECT
    host,
    avg(request_length) length
  FROM    access_log
  GROUP BY
    host
)
SELECT
  T1.host,
  T1.length,
  T2.length
FROM  T1
  JOIN T2 ON T1.host = T2.host