Related
- Anatomy of a PostgreSQL Query Plan
- Beyond Partitioning and Z-Order: A Deep Dive into Liquid Clustering for Unity Catalog Managed Tables
- Reconciling Privacy Preferences Across Two Datastores With Snowflake and Airflow
- Stop Adding Indexes: What's Actually Slowing Your SQL Server Queries When SSIS Loads Data
- DZone
- Data Engineering
- Data
- Common Table Expression in ClickHouse
Common Table Expression in ClickHouse
What is Common Table Expression(CTE)? In this post, learn how to use CTE in the ClickHouse database and follow along with use cases with examples.
Join the DZone community and get the full member experience.
Join For FreeIt is convenient to use CTE in the following cases:
- When one request can get data, and its size fits in memory space
- Multiple uses of the results of this query are required
- Creating recursive queries
A bonus would be the improved readability of your SQL query.
What is the difference between CTE and temporary tables and nested queries?
- If the subquery is correlated, then its call is repeated for each row from the selection, dramatically increasing the cost of executing this query.
- Filling the temporary table with a large amount of data creates a load on the disk.
- Due to the peculiarities of storing temporary tables, executing queries using them increases the execution time.
Syntax
ClickHouse supports both the WITH <expression> AS <identifier> as well as the WITH <identifier> AS <subquery expression> syntaxes.
- Initiate a CTE using
WITH. - Provide a name for a query.
- Follow with
AS. - Define the query.
- If multiple CTEs are required, separate them with a comma.
SQL
WITH locations AS
(
SELECT location
FROM table
WHERE date > (today() - 10)
)
SELECT *
FROM locations- Initiate a CTE using
WITH. - Define an expression.
- Follow with
AS. - Provide a name for the expression.
- If multiple CTEs are required, separate them with a comma.
SQL
WITH ('USA', 'BRA') AS locations
SELECT 'ARG' IN (locations)Example
Create:
SQL
CREATE TABLE SpareParts
(
`id` UInt32,
`partName` String,
`partOrigin` String,
`storeID` UInt32
)
ENGINE = MergeTree()
ORDER BY idInsert:
SQL
INSERT INTO SpareParts VALUES (1, 'headlight', 'USA', 1)
INSERT INTO SpareParts VALUES (2, 'hood', 'JPN', 1)
INSERT INTO SpareParts VALUES (3, 'bumper', 'USA', 1)
INSERT INTO SpareParts VALUES (4, 'radiator', 'BRA', 3)
INSERT INTO SpareParts VALUES (5, 'wheel', 'BRA', 2)
INSERT INTO SpareParts VALUES (6, 'stabilizer', 'ARG', 3)
INSERT INTO SpareParts VALUES (7, 'absorber', 'TUR', 2)
INSERT INTO SpareParts VALUES (8, 'cable', 'MEX', 1)
INSERT INTO SpareParts VALUES (9, 'spring', 'MEX', 3)
INSERT INTO SpareParts VALUES (10, 'door', 'USA', 2)Select:
SQL
WITH
originsByStore AS
(
SELECT
storeID,
groupArray(partOrigin) AS origins
FROM SpareParts
GROUP BY storeID
),
partsByStore AS
(
SELECT
storeID,
groupArray(partName) AS partNames
FROM SpareParts
GROUP BY storeID
),
has(origins, 'USA') = 1 AS isUSA
SELECT
storeID,
origins,
partNames,
isUSA
FROM originsByStore AS t1
LEFT JOIN
(
SELECT
storeID,
partNames
FROM partsByStore
) AS t2 USING (storeID)Result:
ClickHouse
Data (computing)
Execution (computing)
Memory (storage engine)
sql
Opinions expressed by DZone contributors are their own.
Related
-
Anatomy of a PostgreSQL Query Plan
-
Beyond Partitioning and Z-Order: A Deep Dive into Liquid Clustering for Unity Catalog Managed Tables
-
Reconciling Privacy Preferences Across Two Datastores With Snowflake and Airflow
-
Stop Adding Indexes: What's Actually Slowing Your SQL Server Queries When SSIS Loads Data
Partner Resources
×