■ はじめに

WITH についてメモ。

【１】WITH 句

* 共通テーブル式 (CTE, Common Table Expression) と呼ばれる
　一時的な名前付き結果セットを指定できる。
* WITH句を使って、クエリに名前をつける

http://msdn.microsoft.com/ja-jp/library/ms175972(v=sql.90).aspx

【２】使用可能なDB

* SQL Server 2005 から使用可能
* PostgreSQL

http://lets.postgresql.jp/documents/technical/with_recursive

* Snowflake

https://docs.snowflake.com/ja/sql-reference/constructs/with

【３】使用上の注意

* 直前のSQL文の最後に「;」をつける必要がある
* 共通テーブル式(CTE)を参照できるのは、CTEの直後のステートメントのみ
　→これ以外で参照しようとすると、CTEはスコープ外となり、
　　例外「オブジェクト名'Xxx'が無効です」が発生する。

【４】構文

-- WITH CTE名1 AS -- AS 絶対必要
WITH CTE名1 (項目1, 項目2, ...) AS -- AS 絶対必要
(
select 文
),
-- 「,」で複数指定も可能
WITH CTE名2 (項目1, 項目2, ...) AS -- AS 絶対必要
(
select 文
);

イメージ

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
);

【５】サンプル

例１：親子関係に使用

準備：テーブル・データ

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

例２：最新データによる年間売上の増減

https://dk521123.hatenablog.com/entry/2018/10/06/230841

の「【２】PostgreSQL」の
「例２：その年の最新実行されたデータを一覧表示する」のデータを使って、
年間売上の増減をSQLで表示してみる。
複雑になるので、まずは、最新データをWITH句にして、
それから年間売上の増減を表現する。

なお、年間売上の増減については、以下の関連記事を参照のこと。

https://dk521123.hatenablog.com/entry/2021/08/26/222043

使用データ

　 以下の関連記事の
「【２】PostgreSQL」の「例２：その年の最新実行されたデータを一覧表示する」
と同じなので、省略。そちらを参照。

https://dk521123.hatenablog.com/entry/2018/10/06/230841

SQL文（PostgreSQL）

-- Step1) 以下の関連記事の
-- 「【２】PostgreSQL」の「例２：その年の最新実行されたデータを一覧表示する」
-- を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) 以下の関連記事の
-- 「【３】PostgreSQL」の「例１：最新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

参考文献

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

プログラムの超個人的なメモ

Memo for Programming.

【SQL】 WITH句～共通テーブル式～