Vertica Analytics Platform Version 9.3.x Documentation

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. By default, materialization is disabled, and must be explictly enabled at the session or query level. If disabled at the session level, 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: regional_sales and top_regions:

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 revenue specifies a column list of vkey and total_revenue. These map to column vendor_key and aggregate expression SUM(total_order_cost), respectively. The primary query references these aliases:

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.vkey 
WHERE r.total_revenue = (SELECT MAX(total_revenue) FROM revenue )
ORDER BY vendor_name;
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 region
  • top_regions uses the result set from regional_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)

See Also

WITH Clauses