面白い記事があったので、
https://qiita.com/zackey2/items/883616dff71e51bf6563#comment-bf5fbfb1f782dce723d5
私なりにどういう風にSQLを書いているかを整理ついでに書いていこうと思います。
（今後も加筆修正はすると思います。）
コーディングスタイルというより、こんなコードの整理術ができるよとかのほうが近いです。
修正履歴
2023-09-17 初版
2023-09-18 in（）の追加
注意事項
分析用SQLで書いている中で身に着けた整理術なので、
我流だったり、お行儀が悪いSQLも多々あります。
ここのSQL記載を参考にしてレビューアから怒られても、私は責任は取りません。
自己責任でお願いします。
特に★がついている奴は怒られると思います。
あと、いくつかの書き方はパフォーマンス懸念が発生する可能性のものがあります。
使用する場合は実際の条件で試してみて、問題ないことを確認してみてください。
動作検証環境
PostgreSQL 9.5.24, compiled by Visual C++ build 1800, 64-bit
（あれ……だいぶ古いな）
coalesce、Greatest、leastはすごい便利
正直、coalesceの有用性に気づいたとき、with句を初めて知った際と同じくらい興奮しました。
例えば、こんな感じのテーブルがあるとします。
（ユーザーと……なんかの購入額ですかね？）
なにかしらの事情があって、特例金額があるとします。
Aさんは6,000円、Bさんは3,000円でいいことになりました。
with tb_1 as (
select *
from ( values ('A', 5000) , ('B', 4000)
, ('C', 3000) , ('D', NULL)
) as t (user_name, amount)
), tokurei as (
select *
from (values ('A', 6000), ('B', 3000)) as t (user_name, amount)
)
select
tb_1.user_name
, coalesce(tokurei.amount, tb_1.amount) as last_amount
from
tb_1
left join tokurei as tokurei
on tb_1.user_name = tokurei.user_name
これ、すごくないです？
正直、私、coalesceを知ったとき、nullを変換するだけの関数か～～とか思ってましたよ！！
上記のことができるということはこんなことまでできるってことなんですよ！！！
例えば、特例的な金額2つがAさん、Bさん、Cさんに発生していて
その中で特例金額がある人は小さいほうの特例金額を、ない人は通常金額を出力みたいな
「それ……SQLでやるんです？？」みたいな要件に対して、
with tb_1 as (
select *
from ( values ('A', 5000) , ('B', 4000)
, ('C', 3000) , ('D', NULL),('E',1000) ) as t (user_name, amount)
), tokurei_1 as (
-- 特例事情Aでの金額
select *
from (values ('A', 4000), ('B', 1500)) as t (user_name, amount)
), tokurei_2 as (
-- 特例事情Bでの金額
select *
from (values ('A', 3000), ('B', 3000),('C', 6000)) as t (user_name, amount)
)
select
tb_1.user_name
, coalesce(least(tok_1.amount
, tok_2.amount)
, tb_1.amount) as last_amount
from tb_1
left join tokurei_1 as tok_1
on tb_1.user_name = tok_1.user_name
left join tokurei_2 as tok_2
on tb_1.user_name = tok_2.user_name
ほら、抽出できちゃう！
最後のクエリのとこだけ抜粋すると、
select
tb_1.user_name
-- この部分だけで出力内容の制御ができちゃう！！°˖✧◝(⁰▿⁰)◜✧˖°
, coalesce(least(tok_1.amount
, tok_2.amount)
, tb_1.amount) as last_amount
from tb_1
left join tokurei_1 as tok_1
on tb_1.user_name = tok_1.user_name
left join tokurei_2 as tok_2
on tb_1.user_name = tok_2.user_name
それだけで、今回みたいな要件が出力できるのはすごいよね。実際……
という「SQLって、仕様書と対応させようとするの簡単なんだ！！」と感動した部分でした。
あと、これは一般的な需要がないでしょうけど
自宅（code_home）、勤務先（code_work）、実家（code_parent）をそれぞれ登録できる場合に
自宅→勤務先→実家の順で、最初に登録されている地区で集計するみたいなこともできたりします。
with tb_1 as (
select *
from
(
values ('A', 'α地区', NULL,NULL)
, ('B', 'β地区', NULL,NULL)
, ('C', NULL, 'α地区', NULL)
, ('D', NULL, 'β地区', 'γ地区')
, ('E', NULL, NULL, 'β地区')
) as t(user_name, code_home , code_work, code_parent)
)
select
coalesce(code_home , code_work, code_parent) as code
, string_agg(user_name , '@'::text) as users
from
tb_1
group by
coalesce(code_home
, code_work
, code_parent)
正直、この書き方は業務では書きたくないです。
DBの設計ミスった感あるもの（´・ω・｀）
★in（）による抽出条件（多分、怒られるやつ）
多分、通常パターンの方がいいやつです。
これをレビューに出したら怒られる覚悟をした方がいいやつです。
こんなテーブルがあるとします。
正規化もなにもないテーブルですね。
要件としては
「引数（prm）の値が'ALL'の場合は全件出力、それ以外の場合はcol_aの値でフィルターする」といったものです。
それでは、一般的なパターンです。
with tb_1 as (
select *
from
( values ('ミカン', 'ブドウ', 'イチゴ')
, ('オレンジ', 'グレープ', 'ストロベリー')
, ('不知火', '巨峰', 'あまおう')
) as t(col_A, col_B, col_C))
, prm as (
select * from (values ( 'ALL' , '')) as t (filter_A , filter_ALL)
)
select tb_1.*
from tb_1 , prm
where -- ALLの場合に全件出力
case when prm.filter_A = 'ALL' then 1 = 1
-- col_aをフィルタリングする。
else prm.filter_A in (tb_1.col_A )
end
これはcase式でわかりやすいですね。
これをB列にも適用する必要が増えました。
まあ、同じようにかくだけではあるのですが、行数が膨らんでしまいます。
with tb_1 as (
select *
from
( values ('ミカン', 'ブドウ', 'イチゴ')
, ('オレンジ', 'グレープ', 'ストロベリー')
, ('不知火', '巨峰', 'あまおう')
) as t(col_A, col_B, col_C)
)
, prm as (
select * from (values ( 'ALL' , 'ALL', '')) as t (filter_A , filter_B,filter_ALL)
)
select tb_1.*
from tb_1 , prm
where
( case when prm.filter_A = 'ALL' then 1 = 1
else prm.filter_A in (tb_1.col_A )
end )
AND ( case when prm.filter_B = 'ALL' then 1 = 1
else prm.filter_B in (tb_1.col_B )
end )
AND ( case when prm.filter_ALL = 'ALL' then 1 = 1
else prm.filter_ALL in (tb_1.col_A, tb_1.col_B, tb_1.col_C )
end )
全てALLならば全件出力できました。
では、filter_ALLを巨峰に変えてみます。
with tb_1 as (
select *
from
( values ('ミカン', 'ブドウ', 'イチゴ')
, ('オレンジ', 'グレープ', 'ストロベリー')
, ('不知火', '巨峰', 'あまおう')
) as t(col_A, col_B, col_C)
)
, prm as (
select * from (values ( 'ALL' , 'ALL', '巨峰')) as t (filter_A , filter_B,filter_ALL)
)
select tb_1.*
from tb_1 , prm
where
prm.filter_A in (tb_1.col_A , 'ALL')
AND prm.filter_B in (tb_1.col_B , 'ALL' )
AND prm.filter_ALL in (tb_1.col_A , tb_1.col_B , tb_1.col_C ,'ALL' )
巨峰が含まれているレコードのみが出力されるようになりました。。
このように、inの中に複数項目を入れることで、その複数項目を条件に抽出することが
することができます。
マジックナンバーはwith句で分離しよう
※パフォーマンス劣化を起こす場合があるので、実際の要件に沿ってテストは実施してください。
例えば、こんなユーザーの名前を管理しているテーブルがあるとします。
はい、全角スペースや半角スペースの混在しています。
（わかりやすさのために、半角スペースは'△'で表現しています。）
こんなの「姓と名前に分割してください。」とか言われたら発狂したくなります。
with tb_1 as (
select * from (values ( '山田△太郎') , ('川田 花子') ,( '緑川薫') ) as t (user_name)
)
select
(case when user_name like '%'||'△'||'%' then split_part(user_name,'△',1)
when user_name like '%'||' '||'%' then split_part(user_name,' ',1)
else user_name
end ) as first_name
, (case when user_name like '%'||'△'||'%' then split_part(user_name,'△',2)
when user_name like '%'||' '||'%' then split_part(user_name,' ',2)
else ''
end ) as last_name
from tb_1
めんどくさい！！！
直書き箇所が4か所もあるんですよ！！
区切り文字の追加や修正するたびにミスしてないか怖くなります。
（……'△'が('ω')みたいでかわいいな……）
SQLなんですから、joinで書いた方が楽です。
with tb_1 as (
select * from (values ( '山田△太郎') , ('川田 花子') ,( '緑川薫') ) as t (user_name)
), delimiter as (
select * from (values ( '△') , (' ') ) as t (delimiter)
), position as (
-- 氏名の出力する位置を設定
select * from (values (1,2)) as t (first_name, last_name)
)
select
coalesce( split_part(user_name, delimiter , p.first_name )
, user_name ) as first_name
,coalesce( split_part(user_name, delimiter , p.last_name)
, '' ) as last_name
from tb_1
cross join position as p
left join delimiter as d
on tb_1.user_name like '%'||d.delimiter ||'%'
はい、マジックナンバーはすべて分離できました！！( ´∀｀)bｸﾞｯ!
これで、区切り文字が追加になっても問題なく追加しやすいでしょう。
実際、マジックナンバーは最初にwith句でくくった方がグッと楽になります。
SQLを開いたときに、マジックナンバーに使いたい内容も一目で確認もできますし……
Cross joinも便利なんじゃよ……（覚えなくていいとかいわれるけど……）
※パフォーマンス劣化を起こす場合があるので、実際の要件に沿ってテストは実施してください。
例えば、こんな感じでユーザーと購入額がまとめてあるようなテーブルがあるとします。
合計金額が3000円を超えている人を出したい場合ってありますよね。
（あるかな……ないかも……）
select user_id
from tb_1
group by user_id
having sum(purchase_amount) >= 3000
havingの説明にも出る感じの基本形ですね。
……でもよく考えてください。
キャンペーンっていくつもある可能性がありますよね。
もちろんコレくらいならコメントでも残せます。
select user_id
from tb_1
group by user_id
having sum(purchase_amount) >= 3000 -- キャンペーンA
OR sum(purchase_amount) >= 1500 -- キャンペーンB
それを直書きで書こうモノなら、今後の修正時に影響が出る可能性があります。
数個所に書こうモノなら、今回の条件では
置換は効かないしコメントを検索するしかありません。
「ﾃﾍﾍﾟﾛ、コメント……書き忘れてた」なんてこともあり得ます！
数字で書いたら、エディタの補完機能も使えません！！！！！！
同じく、with句にまとめちゃいましょう！！！
……条件が合計金額だけなので結合条件がありませんね。同じようにはjoinできないですね。
そんなの知るか！くっつけたいんじゃ！！！とそこでCross joinです。
with tb_1 as (
-- 使用明細（ユーザー、購入額）
select *
from
( values ('A', 1500) , ('A', 2000)
, ('B', 1000) , ('B', 1000)
) as t (user_id, purchase_amount)
), campaign_A as (
-- キャンペーンAの対象開始金額
select 3000 as start_campaign
), campaign_B as (
-- キャンペーンBの対象開始金額
select 1500 as start_campaign
)
select user_id
from tb_1
, campaign_A as c_A -- Cross joinの省略形
, campaign_B as c_B
group by user_id
having sum(purchase_amount) >= MAX(c_A.start_campaign)
OR sum(purchase_amount) >= MAX(c_B.start_campaign)
はい、両方のキャンペーン対象者が抽出できました！！( ´∀｀)bｸﾞｯ!
（……たぶん、普通のお作法的に考えると、副問い合わせを書くのかな……）
campaign_A as (
-- キャンペーンAの対象開始金額
select 3000 as start_campaign
), campaign_B as (
-- キャンペーンBの対象開始金額
select 1500 as start_campaign
)
select user_id
from tb_1
group by user_id
having sum(purchase_amount) >= (select * from campaign_A) -- キャンペーンA
OR sum(purchase_amount) >= (select * from campaign_B) -- キャンペーンB
ただ、私個人はCross joinの方を多用することが多いですね。
（だって、使う場所全部でselectを複数回書くの面倒だし……）
複数個所で使う条件はwith句でつくる！
※パフォーマンス劣化を起こす場合があるので、実際の要件に沿ってテストは実施してください。
case式は便利ですけど、偶にめんどうを作る原因にもなってしまいます！！
例えば、こんなテーブルがあって、
「松」、「竹」では誕生日にプレゼントがあるみたいです。（お寿司屋さんとかですかね？）
SQLで書くとこんな感じですかね。
with tb_1 as (
select *
from
( values ('A', '梅', '2023-12-16')
, ('B', '竹', '2023-12-16')
, ('C', '竹', '2023-09-17')
, ('D', '松', '2023-12-16')
, ('E', '松', '2023-09-17')
) as t(user_name, plan, birthday))
select
*
, case
when plan = '竹' and to_char(now(),'yyyy-mm-dd') = birthday then 'プレゼントがあるよ'
when plan = '松' and to_char(now(),'yyyy-mm-dd') = birthday then 'とても豪華なプレゼントがあるよ'
end as message
, case
when plan = '竹' and to_char(now(),'yyyy-mm-dd') = birthday then '割引券'
when plan = '松' and to_char(now(),'yyyy-mm-dd') = birthday then '特上割引券'
end as present
from
tb_1
また、同じです。
birthdayの判定が複数個所に書いてあります。
これでは、プランを出力するだけのSQLだと思っている人が「なんでto_char？？」となってしまいます。
誕生日の判定をwith句にまとめてみましょう！
with tb_1 as (
select *
from
( values ('A', '梅', '2023-12-16')
, ('B', '竹', '2023-12-16')
, ('C', '竹', '2023-09-17')
, ('D', '松', '2023-12-16')
, ('E', '松', '2023-09-17')
) as t(user_name, plan, birthday)
) , tmp as (
select *
-- 誕生日プレゼントがあるかどうかの判定フラグを追加
, to_char(now(),'yyyy-mm-dd') = birthday as birthday_flag
from tb_1
)
select
*
, case
when plan = '竹' and birthday_flag then 'プレゼントがあるよ'
when plan = '松' and birthday_flag then 'とても豪華なプレゼントがあるよ'
end as message
, case
when plan = '竹' and birthday_flag then '割引券'
when plan = '松' and birthday_flag then '特上割引券'
end as present
from
tmp
これで誕生日判定箇所が一か所に集まりました！
読んだ人も誕生かどうかでプレゼントがあるんだなとわかってくれることでしょう！
こんな感じで、with句で呼び出す際に、複数の箇所で使う判定条件は追加してしまいましょう！！！
でもcase式を直書きで書くのもめんどくさいですね。
文言修正するのに、SQLをいじりたくないですし……これもwith句に書いちゃいましょう！
with tb_1 as (
select
*
from
(values ('A', '梅', '2023-12-16')
, ('B', '竹', '2023-12-16')
, ('C', '竹', '2023-09-17')
, ('D', '松', '2023-12-16')
, ('E', '松', '2023-09-17')
) as t(user_name, plan, birthday)
)
, tmp as (
select
*
-- 誕生日プレゼントがあるかどうかの判定フラグを追加
, to_char(now(), 'yyyy-mm-dd') = birthday as birthday_flag
from
tb_1
)
, birthday_present as (
select *
from (
values ('竹', 'プレゼントがあるよ!', '割引券')
, ('松', 'とても豪華なプレゼントがあるよ!', '特上割引券')
) as t(plan, message, present)
)
, message as (
select *
from
(values ('いつもご利用ありがとうございます!')) as message(message)
)
select
tmp.*
, (m.message || coalesce(birthday.message, '')) as message
, birthday.present as birthday_present
from
tmp
left join message as m
on 1 = 1
left join birthday_present birthday
on tmp.birthday_flag
AND tmp.plan = birthday.plan
これで抽出SQLからはcase式がなくなりました！
定型文的な文章を入れてもそんなに複雑ではなくなりましたね！！
with句の行が増えているので、行が増えていますが、
実際はwith句ではなく、
- メッセージテーブルに格納する
- on条件に誕生日の判定処理を記載する
とかすると実際に行数も減って、ソースもだいぶ圧縮できます。
（逆に圧縮しすぎて何を出しているか忘れてしまいそう……(;^ω^)）
select
tb_1.*
, (m.message || coalesce(birthday.message, '')) as message
, birthday.present as birthday_present
from
tb_1 as tb_1
left join message as m
on 1 = 1
left join birthday_present birthday
on to_char(now(), 'yyyy-mm-dd') = birthday
AND tb_1.plan = birthday.plan
（messageのCTEテーブルですか………なんで、さっき力説していた、cross joinじゃなくてleft joinの1=1とかいう意味不明な結合条件かですって？？
いや、なんかjoinのあとにon条件がないとテーブルごとのインデントの区切りがなくて収まり悪くって……♪～<(ﾟεﾟ；)>）
##あとがき
なんか他にもSQL書いてるときに気にしていることある気はするけど、なんか思い出せない。
思い出せたら加筆します。