Simplify SQL Subqueries with WITH Clause for Multi-Logstore Analysis - Simple Log Service

You can use a WITH clause to save the result of a subquery to a temporary table. Then, you can execute an SQL statement to analyze the data in the temporary table. You can use WITH clauses to simplify SQL statements and improve readability. This topic describes the syntax of WITH clauses and provides examples on how to use WITH clauses.

Syntax

WITH table_name AS (select_statement) select_statement

Parameters

Parameter	Description
table_name	The name of the temporary table.
select_statement	A complete SELECT statement that defines the subquery.

Example

This example calculates the average request length per host from two Logstores, website_log and access_log, and stores the results in temporary tables T1 and T2. It then joins these tables to find the average request length for hosts that appear in both tables.

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

Query result