WITH Clause
A WITH clause defines one or more named common table expressions (CTEs). Each CTE encapsulates a result set that can be referenced by another CTE in the same WITH clause, or by the primary query of the WITH clause. Vertica can execute the CTE on each reference (inline expansion), or materialize the result set as a temporary table that it reuses for all references. In both cases, WITH clauses can help simplify complicated queries and avoid statement repetition.
Syntax
WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ ] { cte‑identifier [ ( column-aliases ) ] AS ( [ subordinate-WITH-clause ] query-expression ) } [,…]
Parameters
/*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ | Enables materialization of all queries in the current WITH clause. Otherwise, materialization is set by configuration parameter WithClauseMaterialization, by default set to 0 (disabled). If WithClauseMaterialization is disabled, materialization is automatically cleared when the primary query of the WITH clause returns. For details, see Materialization of WITH Clause. |
cte‑identifier |
Identifies a common table expression (CTE) within a WITH clause. This identifier is available to CTEs of the same WITH clause, and of parent and child WITH clauses (if any). CTE identifiers of the outermost (primary) WITH clause are also available to the primary query. All CTE identifiers of the same WITH clause must be unique. For example, the following WITH clause defines two CTEs, so they require unique identifiers: WITH -- query sale amounts for each region regional_sales AS (SELECT … ), top_regions AS ( SELECT … ) ) |
column-aliases |
A comma-delimited list of result set column aliases. These aliases map to column expressions in the CTE query. If omitted, result set columns can only be referenced by the names used in the query. In the following example, CTE
WITH revenue ( vkey, total_revenue ) AS (
SELECT vendor_key, SUM(total_order_cost)
FROM store.store_orders_fact
GROUP BY vendor_key ORDER BY 1)
SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue
FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.
|
subordinate‑WITH‑clause |
A WITH clause that is nested within the current one. CTEs of this WITH clause can only reference CTEs of the same clause, and of parent and child WITH clauses. The primary query can only reference CTEs in the primary (outermost) WITH clause. It cannot reference the CTEs of any nested WITH clause. |
query-expression | The query of a given CTE. |
Restrictions
- A WITH query cannot refer to its own output.
- WITH clauses only support SELECT and INSERT statements. They do not support UPDATE, or DELETE statements.
Examples
Single WITH clause with single CTE
The following SQL defines a WITH clause with one CTE, revenue
, which aggregates data in table store.store_orders_fact
. The primary query references the WITH clause result set twice: in its JOIN
clause and predicate:
-- define WITH clause WITH revenue ( vkey, total_revenue ) AS ( SELECT vendor_key, SUM(total_order_cost) FROM store.store_orders_fact GROUP BY vendor_key ORDER BY 1) -- End WITH clause -- primary query SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.vkey WHERE r.total_revenue = (SELECT MAX(total_revenue) FROM revenue ) ORDER BY vendor_name; vendor_name | vendor_address | vendor_city | total_revenue ------------------+----------------+-------------+--------------- Frozen Suppliers | 471 Mission St | Peoria | 49877044 (1 row)
Single WITH clause and multiple CTEs
In the following example, the WITH clause contains two CTEs:
regional_sales
totals sales for each regiontop_regions
uses the result set fromregional_sales
to identify the three regions with the highest sales:
The primary query aggregates sales by region and departments in the top_regions
result set:
WITH -- query sale amounts for each region regional_sales (region, total_sales) AS ( SELECT sd.store_region, SUM(of.total_order_cost) AS total_sales FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key GROUP BY store_region ), -- query previous result set top_regions AS ( SELECT region, total_sales FROM regional_sales ORDER BY total_sales DESC LIMIT 3 ) -- primary query -- aggregate sales in top_regions result set SELECT sd.store_region AS region, pd.department_description AS department, SUM(of.total_order_cost) AS product_sales FROM store.store_orders_fact of JOIN store.store_dimension sd ON sd.store_key = of.store_key JOIN public.product_dimension pd ON of.product_key = pd.product_key WHERE sd.store_region IN (SELECT region FROM top_regions) GROUP BY ROLLUP (region, department) ORDER BY region, product_sales DESC, GROUPING_ID(); region | department | product_sales ---------+----------------------------------+--------------- East | | 1716917786 East | Meat | 189837962 East | Produce | 170607880 East | Photography | 162271618 East | Frozen Goods | 141077867 East | Gifts | 137604397 East | Bakery | 136497842 East | Liquor | 130410463 East | Canned Goods | 128683257 East | Cleaning supplies | 118996326 East | Dairy | 118866901 East | Seafood | 109986665 East | Medical | 100404891 East | Pharmacy | 71671717 MidWest | | 1287550770 MidWest | Meat | 141446607 MidWest | Produce | 125156100 MidWest | Photography | 122666753 MidWest | Frozen Goods | 105893534 MidWest | Gifts | 103088595 MidWest | Bakery | 102844467 MidWest | Canned Goods | 97647270 MidWest | Liquor | 97306898 MidWest | Cleaning supplies | 90775242 MidWest | Dairy | 89065443 MidWest | Seafood | 82541528 MidWest | Medical | 76674814 MidWest | Pharmacy | 52443519 West | | 2159765937 West | Meat | 235841506 West | Produce | 215277204 West | Photography | 205949467 West | Frozen Goods | 178311593 West | Bakery | 172824555 West | Gifts | 172134780 West | Liquor | 164798022 West | Canned Goods | 163330813 West | Cleaning supplies | 148776443 West | Dairy | 145244575 West | Seafood | 139464407 West | Medical | 126184049 West | Pharmacy | 91628523 | | 5164234493 (43 rows)
INSERT statement that includes WITH clause
The following SQL uses a WITH clause to insert data from a JOIN query into table total_store_sales
:
CREATE TABLE total_store_sales (store_key int, region VARCHAR(20), store_sales numeric (12,2)); INSERT INTO total_store_sales WITH store_sales AS ( SELECT sd.store_key, sd.store_region::VARCHAR(20), SUM (of.total_order_cost) FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key GROUP BY sd.store_region, sd.store_key ORDER BY sd.store_region, sd.store_key) SELECT * FROM store_sales; => SELECT * FROM total_store_sales ORDER BY region, store_key; store_key | region | store_sales -----------+-----------+------------- 2 | East | 47668303.00 6 | East | 48136354.00 12 | East | 46673113.00 22 | East | 48711211.00 24 | East | 48603836.00 31 | East | 46836469.00 36 | East | 48461449.00 37 | East | 48018279.00 41 | East | 48713084.00 44 | East | 47808362.00 49 | East | 46990023.00 50 | East | 47643329.00 9 | MidWest | 46851087.00 15 | MidWest | 48787354.00 27 | MidWest | 48497620.00 29 | MidWest | 47639234.00 30 | MidWest | 49013483.00 38 | MidWest | 48856012.00 42 | MidWest | 47297912.00 45 | MidWest | 48544521.00 46 | MidWest | 48887255.00 4 | NorthWest | 47580215.00 39 | NorthWest | 47136892.00 47 | NorthWest | 48477574.00 8 | South | 48131455.00 13 | South | 47605422.00 17 | South | 46054367.00 … (50 rows)