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.
WITH table_name AS (select_statement) select_statement
|table_name||The name of the temporary table.|
|select_statement||The complete SELECT statement.|
Analyze the average request length for each host in a Logstore named website_log and save the analysis result to a table named T1. Analyze the average request length for each host in a Logstore named access_log and save the analysis result to a table named T2. Then, use a JOIN clause to combine T1 and T2 and query the average request length for each host that is contained 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 and analysis result