with区とテンポラリーテーブルの差

過去にこんな記事を書きました。
最近思ったのはサブクエリは一度だけ使うけど、テンポラリテーブルは何度も使うときには便利だし、そもそもSQLのコードが見やすいのではないかと思った。

nade-nadegata.hatenablog.jp

そして、一方で、with区とテンポラリーテーブルの違いはなんとなくわかったのでそれについて忘れないうちに記載します。

大きな差は
　・with区：使うたびに計算するので複数回処理を行う場合には遅い、ただ、最初に構文のチェックをしてくれるのでデバッグには良い。
　・テンポラリーテーブル；一度作れば何度でも使い回せる、ただし、作るタイミングまで構文ミスがわからない。

ということでそんなに何度も同じテーブルを使い回す複雑な処理をするほど優秀ではないので、自分はwith区結構使っちゃうなーと思った。

【MySLQでテンポラリーテーブル作成コード】
■CSVをテーブルするパターン

CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL, 
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, 
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, 
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES
('cucumber', 100.25, 90, 2);

■とあるテーブルをテンポラリーテーブル化するパターン

CREATE TEMPORARY TABLE C AS
SELECT *
FROM countries
WHERE ranking=1;

SELECT P.name
FROM players AS P
INNER JOIN C ON C.id=P.country_id;

【Verticaでテンポラリーテーブル作成コード】

SELECT CAST(COST as INT) * 2 as COST_APPLE
into local temporary table APPLE on commit preserve rows
FROM  TEST
WHERE NAME = 'りんご'
;

SELECT CAST(COST as INT) * 3 as COST_TOMATO
into local temporary table TOMATO on commit preserve rows
FROM  TEST
WHERE NAME = 'トマト'
;

SELECT SUM(COST) as COST
FROM (
        SELECT COST_APPLE as COST
        FROM APPLE
        UNION ALL
        SELECT COST_TOMATO as COST
        FROM TOMATO
) AS T1
;

最近どっちも中途半端に使っていて、pythonも勉強し始めたので色々と混乱中。