■ はじめに
WITH についてメモ。
目次
【1】WITH 句 【2】使用可能なDB 【3】使用上の注意 【4】構文 1)イメージ 【5】サンプル 例1:親子関係に使用 例2:最新データによる年間売上の増減 【6】Tips 1)WITH句を使ったCTAS
【1】WITH 句
* 共通テーブル式 (CTE, Common Table Expression) と呼ばれる 一時的な名前付き結果セットを指定できる。 * WITH句を使って、クエリに名前をつける
http://msdn.microsoft.com/ja-jp/library/ms175972(v=sql.90).aspx
【2】使用可能なDB
* SQL Server 2005 から使用可能 * PostgreSQL
http://lets.postgresql.jp/documents/technical/with_recursive
* Snowflake
https://docs.snowflake.com/ja/sql-reference/constructs/with
【3】使用上の注意
* 直前のSQL文の最後に「;」をつける必要がある * 共通テーブル式(CTE)を参照できるのは、CTEの直後のステートメントのみ →これ以外で参照しようとすると、CTEはスコープ外となり、 例外「オブジェクト名'Xxx'が無効です」が発生する。
【4】構文
-- WITH CTE名1 AS -- AS 絶対必要 WITH CTE名1 (項目1, 項目2, ...) AS -- AS 絶対必要 ( select 文 ), -- 「,」で複数指定も可能 WITH CTE名2 (項目1, 項目2, ...) AS -- AS 絶対必要 ( select 文 );
1)イメージ
WITH OrderAndCustomerData (Id, Name, ItemName) AS ( SELECT p.Id, p.Name, o.ItemName FROM Customer AS c INNER JOIN Order AS o ON o.CustomerId = c.Id );
【5】サンプル
例1:親子関係に使用
準備:テーブル・データ
CREATE TABLE Emp ( Id int NOT NULL, name varchar(20) NULL, bossId int NULL, sex char char(1) -- m : male, f : famale ) INSERT INTO Emp VALUES(1001, 'Mike', NULL, 'm') INSERT INTO Emp VALUES(1002, 'Tony', NULL, 'f') INSERT INTO Emp VALUES(1003, 'Johnson', 1001, 'f') INSERT INTO Emp VALUES(1004, 'John', 1002, 'f') INSERT INTO Emp VALUES(1005, 'Ken', 1001, 'f') INSERT INTO Emp VALUES(1006, 'Smith', 1003, 'f')
サンプル
WITH cte ([Id], [name], [bossId], Hierarchy) AS ( -- 親 SELECT [Id], [name], [bossId], 0 FROM [Emp] WHERE [Id] = 1001 UNION ALL -- 子(再帰) SELECT Sub.[Id], Sub.[name], Sub.[bossId], Main.Hierarchy + 1 FROM [Emp] AS Sub INNER JOIN cte AS Main ON Sub.[bossId] = Main.[Id] ) SELECT * FROM cte
例2:最新データによる年間売上の増減
https://dk521123.hatenablog.com/entry/2018/10/06/230841
の「【2】PostgreSQL」の 「例2:その年の最新実行されたデータを一覧表示する」のデータを使って、 年間売上の増減をSQLで表示してみる。 複雑になるので、まずは、最新データをWITH句にして、 それから年間売上の増減を表現する。 なお、年間売上の増減については、以下の関連記事を参照のこと。
https://dk521123.hatenablog.com/entry/2021/08/26/222043
使用データ
以下の関連記事の 「【2】PostgreSQL」の「例2:その年の最新実行されたデータを一覧表示する」 と同じなので、省略。そちらを参照。
https://dk521123.hatenablog.com/entry/2018/10/06/230841
-- Step1) 以下の関連記事の -- 「【2】PostgreSQL」の「例2:その年の最新実行されたデータを一覧表示する」 -- をWITH句にする -- https://dk521123.hatenablog.com/entry/2018/10/06/230841 WITH latest_sale AS ( SELECT s1.year AS year, s1.sales AS sales, s1.create_at FROM sale AS s1 WHERE s1.create_at=(SELECT MAX(create_at) FROM sale AS s2 WHERE s1.year=s2.year) ) -- Step2) 以下の関連記事の -- 「【3】PostgreSQL」の「例1:最新5年間の変化を表示」 -- をStep1)のWITH句を使って表現するだけ。 -- https://dk521123.hatenablog.com/entry/2021/08/26/222043 SELECT s1.year AS year, CASE WHEN s1.sales = s2.sales THEN '→' WHEN s1.sales > s2.sales THEN '↑' WHEN s1.sales < s2.sales THEN '↓' ELSE '-' END AS up_or_down, s1.sales AS sales, s2.sales AS previous_sales, s1.sales - s2.sales AS diff FROM latest_sale AS s1, latest_sale AS s2 WHERE s2.year=(SELECT MAX(year) FROM sale AS s3 WHERE s1.year > s3.year) ORDER BY s1.year DESC LIMIT 5;
出力結果
year,up_or_down,sales,previous_sales,diff 2021,→,2710,2710,0 2020,↓,2710,2810,-100 2018,↑,2810,2310,500 2017,↑,2310,1640,670 2014,↓,1640,1720,-80
【6】Tips
1)WITH句を使ったCTAS
* CTAS = CREATE TABLE ... AS SELECT (データが入ったテーブルを作成)
文法的にエラーになる例 (Snowflake)
WITH sub_table AS ( SELECT ... ) -- ★ここでエラー CREATE TABLE main_table AS ( SELECT ... FROM sub_table ) ;
OKになる例 (Snowflake)
CREATE TABLE main_table AS ( WITH sub_table AS ( SELECT ... ) SELECT ... FROM sub_table ) ;
参考文献
http://msdn.microsoft.com/ja-jp/magazine/cc163346.aspx
http://d.hatena.ne.jp/haradago/20110314/p1
http://blog.livedoor.jp/akf0/archives/51395599.html
http://d.hatena.ne.jp/miho_matsumoto/20100211/1265878135
http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html
http://b.drmg.net/archives/18427595.html
http://kaya.wankuma.com/tips/sqlserver/cteRc.aspx
関連記事
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841
SQLで変化(増加/変化なし/減少)について考える
https://dk521123.hatenablog.com/entry/2021/08/26/222043