例 - Teradata Database - Teradata Vantage NewSQL Engine - Examples of using the WITH Statement Modifier in CREATE VIEW and REPLACE VIEW SQL Statements

例: CREATE VIEWでのWITH修飾子の使用

WITH修飾子の共通テーブル式(CTE)は、CTEが間接的に自己参照を行なわない場合、WITH修飾子で定義された先行するCTEまたは後続のCTEを参照できます。つまり、循環参照は許可されません。

この例では、テーブルの定義は次のとおりです。

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);
INSERT INTO orders (43563, 734.12);
INSERT INTO orders (65758, 211.15);
INSERT INTO orders (23235, 1264.98);
INSERT INTO orders (43563, 583.23);
INSERT INTO orders (89786, 278.66);
INSERT INTO orders (13253, 401.97);
INSERT INTO orders (98765, 1042.23);
INSERT INTO orders (23235, 699.23);
INSERT INTO orders (43563, 935.35);
INSERT INTO orders (88354, 375.09);

この例では、WITH修飾子の名前付きクエリー使用してsales_vビューを作成します。WITH修飾子には、multiple_order_totalsとして指定された非再帰的共通テーブル式(CTE)が含まれており、WITH句で以前定義されたmultiple_ordersテーブルを参照します。

CREATE VIEW sales_v AS
   WITH multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
),
multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
)
SELECT * FROM multiple_order_totals;

次に、ビューにクエリーをかけることができます。

SELECT * FROM sales_v
ORDER BY total_spend DESC;

このクエリーは、以下のような応答セットを返します。

この例のWITH修飾子にはmultiple_order_totalsとして指定された非再帰的共通テーブル式(CTE)が含まれており、WITH句でそれ以降に定義されたmultiple_ordersテーブルを参照します。

CREATE VIEW sales_v AS
WITH multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders)
   GROUP BY customer_id
),
multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
)
SELECT * FROM multiple_order_totals;

次に、ビューにクエリーをかけることができます。

SELECT * FROM sales_v
ORDER BY total_spend DESC;

このクエリーは、以下のような応答セットを返します。

例: CREATE VIEWのWITH修飾子での再帰的問合わせの使用

次に、再帰的問合わせs5に基づいたt1_viewビューを示します。

CREATE VIEW t1_view AS WITH RECURSIVE  s5 (MinVersion_view) AS (SELECT a1 FROM t1 WHERE a1 > 1
UNION ALL
SEL MinVersion_view FROM s5 WHERE MinVersion_view > 3),
RECURSIVE s6 (MinVersion_view2) AS (SELECT a1 FROM t1 WHERE a1 = 2
UNION ALL
SEL MinVersion_view2 FROM S6 WHERE MinVersion_view2 > 2)
SEL * FROM s5,s6;

SEL * FROM t1_view;