PostgresDB の with を使いこなす / TypeScript エンジニアのための PostgresDB
Tweetwith を使うことで何ができる？
一時的な擬似テーブルを作ることができます。（擬似テーブルなので実際には DB には記録されていません。）計算結果を擬似テーブルとして保持し、それに何度も計算を加えていく、といった際に便利です。
例えば、連続ログイン日数を集計する際には、何回か計算を重ねていく必要があります。そういった際に擬似テーブルが活躍します。計算を加えた後の「結果」を with を使って擬似テーブルとして保持することで、さらにその擬似テーブルに計算を加えて、加えて…という複雑な処理をさせることが可能になります。
以下の SQL は、ある user がログインした履歴である log_data を解析して、連続で何日ログインしたか等の値を計算しています。計算結果のテーブルを一旦保持してさらに計算を重ね合わせる必要があるので、そのための一時保存のために with の機構を活用しています。
WITH "user"("id", "log_date") AS
(
VALUES (1, '2020-01-01' :: date)
, (1, '2020-01-02' :: date)
, (1, '2020-01-03' :: date)
, (1, '2020-01-05' :: date)
, (2, '2020-01-01' :: date)
, (2, '2020-01-02' :: date)
),
log as (
select *, lag("user".log_date, 1) over (PARTITION BY "user".id) as lag_date
from "user"
),
log2 as (
select *,
case
when log.log_date - 1 = log.lag_date then 0
else 1
end as not_cont_date_flg
from log
),
log3 as (
select *,
sum(log2.not_cont_date_flg)
OVER (PARTITION BY log2.id ORDER BY log2.log_date) as cum_not_cont_date_flg
from log2
),
log4 as (
select SUM(1) OVER (PARTITION BY id, cum_not_cont_date_flg) AS continue_num_max,
row_number()
OVER (PARTITION BY id, cum_not_cont_date_flg ORDER BY cum_not_cont_date_flg) AS continue_num,
*
from log3
)
select *
from log4;with の基礎
ではまず with の基本系からいきましょう。
一旦 artcile というテーブルを作って、その後に with を使っています。
-- テーブルを作ってデータを入れる
create table article
(
id int,
title text
);
insert into article (id, title)
values (1, 'this is 1'),
(2, 'this is 2');
select *
from article;
-- with を使う
with temp_table as (
select *,
1 as fixed_number
from article
)
select *
from temp_table;
with 仮で作るテーブルの名前 as ( ここにいつものセレクト文などを書く ) という書き方です。
こうして作った擬似テーブルが（例えばここでは temp_table という名称です）をあとで使うことができます。（select * from temp_table; の部分で登場していますね。）
まだ一回しか with が登場しない、かつ計算が単純なので with の効果がわかりにくいと思います。しかしどんどん複雑になってきます。まずは構文になれましょう。
with 連続がけ
さて、少し難しくなりますよ！
with は、何度でもかけあわせていくことができます。
次の SQL では、with を使って作った擬似テーブルに対して、さらに計算を加えて擬似テーブルを生成し、さらにそれに計算を加えて…と何度も実行しています。
注意点は以下です。
- with は最初に一回出てくればいい
- 連続する場合は
as ()の後ろに,を配置するのを忘れないこと as ()の最後には,をつけては「ダメ」
with temp_table as (
select *,
1 as fixed_number
from article
),
temp_table2 as (
select *, 2 as fixed_number2
from temp_table
),
temp_table3 as (
select *, 3 as fixed_number3
from temp_table2
)
select *
from temp_table3;
ちょっと難しくなってきましたよね。落ち着いて書き方を把握してみましょう。
自分で書いてみるのが何より重要ですよ！
with を使って擬似テーブルを最初から作ってしまう
今までのクエリは、まず select * from article と既存の article というテーブルから始めていました。しかし、そもそもこの起点となるテーブルをも with を使って作ってしまうことが可能です。
WITH "user"(id, log_date) AS
(
VALUES (1, '2020-01-01' :: date)
, (1, '2020-01-02' :: date)
, (1, '2020-01-03' :: date)
, (1, '2020-01-05' :: date)
, (2, '2020-01-01' :: date)
, (2, '2020-01-02' :: date)
)
select *
from "user";こうすることで、いちいちテーブルの record を編集せずに複雑な計算を試すことができるので、特に開発中に便利です。
詳しくみてみる
まず冒頭 "user"(id, log_date) AS という宣言で、user という名前の擬似テーブルを作成することを宣言し、そのテーブルは id, log_data というカラムを持つことも宣言しています。
そして as () の内部で values を使って、テーブルの値を複数宣言しています。その際 :: date とつけることで、型を明示的に date 型 つまり日付を扱う値に変換しています。（これは後ほど日付の前後関係などを計算するために必要な処理です。これがなければ単なる文字列として扱われてしまい、日付計算ができません。）
WITH "user"(id, log_date) AS
(
VALUES (1, '2020-01-01' :: date)
, (1, '2020-01-02' :: date)
, (1, '2020-01-03' :: date)
, (1, '2020-01-05' :: date)
, (2, '2020-01-01' :: date)
, (2, '2020-01-02' :: date)
)
select *
from "user";ちなみに、なぜ "user"(id, log_date) AS と user を "" で囲んでいるかというと、正確にはわからないのですが、既存のデータベースにあるテーブルと被らせないためだと思われます。こうすることで仮に user というテーブルが存在しても、それとは別の存在として扱われます。
with を使ってテーブルを作る利点は？
開発中に、まだ計算が正しいかわからない場合、このように計算の起点になるテーブルを自由に変更させることができると、検証が非常に簡単になります。
例えば連続判定が正しいか確認するために「1日履歴を変更したい」という場合にも、update sql を発行せずとも、一行目の as () 内を直接変更すればいいので非常に簡単です。
このテクニックは create SQL function の開発でも有効です。