ふと気がつくと少数派になっていることがある。
いくつかあるテーブルからデータを持ち寄ってリストを出力する時、ってリレーショナルデータベースなら持ち寄るのが前提だから一々断る必要もないんだけどさ。 俺はそんな時に、まず1回のSQL実行でデータ取得をやりきるにはどうするかを考える。 テーブルが多かったり条件が複雑だったりで、最終的に分割実行しないと厳しいだろうと最初から想像できたとしても、それでもまずは1回でできないかを考える。 無理矢理にでも。
でもこれ少数派らしい。
まずどのテーブルから何を持って来て… 次に… なんて順序立てて考えるのは同じだが、それを動的にSQLを作ったり中間テーブルを使ったりDBの外にデータを持ったり、要はSQLとその他の何かをどう組み合わせるかを考えるところから始めるらしい。 一つのSQLで実行してみようなんて基本的に考えないらしい。 O/Rマッピングのフレームワークを使わない環境でさえも。
もうしばらく前のことだが、ちょっとした検索条件に対応する処理が動的にSQLを作るようになっているのを見て 「このぐらいなら、わざわざ動的に作らなくてもパラメーター埋め込んだ固定のSQLを一つ用意すりゃいいのに」 と言ったら 「いや、それを一撃で持ってこようなんて考えるの渡邊さんぐらいですよ」 と返された。 それがちょっと気になって改めて色々ソースを見た結果、判ったのは、自社内ですら少数派という現実だった。
例えば、こんなテーブルがあるとする。
| 書籍ID |
| 納品先ID |
| 納品日 |
| 納品数 |
| 書籍ID |
| 納品先ID |
| 返品日 |
| 返品数 |
| 書籍ID |
| 書籍名 |
| 価格 |
| 著者ID |
| 著者名 |
| 書籍ID |
| 著者ID |
| 納品先ID |
| 納品先名 |
納品と返品がいわゆるトランザクションで、残りがマスター。
これらから、日々の書籍の動きを調査する為にリスト出力したいという要望を受けたとする。
検索条件と入力画面はこんな感じで。
出力項目はこれで。
これを受けて、俺が最初に考えるのはこんな感じ。
with
納返品 as (
select
書籍ID
, 納品先ID
, 納品日 as 取扱日
, 納品数 as 取扱数
, '納品' as 取扱種別
from
納品
union all
select
書籍ID
, 納品先ID
, 返品日 as 取扱日
, 返品数 as 取扱数
, '返品' as 取扱種別
from
返品
)
, 書籍別納品先別返品率 as (
select
d.書籍ID
, d.納品先ID
, case
when r.返品数 is null then 0
else trunc( r.返品数 / d.納品数 * 100 )
end as 返品率
from
( select
書籍ID
, 納品先ID
, 納品数
from
( select
書籍ID
, 納品先ID
, sum( 納品数 ) over ( partition by 書籍ID, 納品先ID ) as 納品数
, row_number() over ( partition by 書籍ID, 納品先ID order by 1 ) as rn
from
納品 )
where
rn = 1 ) d
left join ( select
書籍ID
, 納品先ID
, 返品数
from
( select
書籍ID
, 納品先ID
, sum( 返品数 ) over ( partition by 書籍ID, 納品先ID ) as 返品数
, row_number() over ( partition by 書籍ID, 納品先ID order by 1 ) as rn
from
返品 )
where
rn = 1 ) r
on d.書籍ID = r.書籍ID
and d.納品先ID = r.納品先ID
)
, 著者列化著書 as (
select
w.書籍ID
, b.書籍名
, listagg( a.著者名, '/' ) within group ( order by a.著者名 ) as 著者名
from
著書 w
inner join 書籍 b
on w.書籍ID = b.書籍ID
inner join 著者 a
on w.著者ID = a.著者ID
group by
w.書籍ID
, b.書籍名
)
select
a.書籍ID
, b.書籍名
, b.著者名
, a.納品先ID
, c.納品先名
, a.取扱日
, a.取扱数
, a.取扱種別
, d.返品率
from
納返品 a
inner join 著者列化著書 b
on a.書籍ID = b.書籍ID
inner join 納品先 c
on a.納品先ID = c.納品先ID
left join 書籍別納品先別返品率 d
on a.書籍ID = d.書籍ID
and a.納品先ID = d.納品先ID
where
1 = 1
and ( b.書籍名 like '%' || :書籍名 || '%' and :書籍名 is not null or :書籍名 is null )
and ( b.著者名 like '%' || :著者名 || '%' and :著者名 is not null or :著者名 is null )
and ( a.納品先ID = :納品先ID and :納品先ID is not null or :納品先ID is null )
and ( a.取扱日 >= :始点日 and :始点日 is not null or :始点日 is null )
and ( a.取扱日 <= :終点日 and :終点日 is not null or :終点日 is null )
and ( d.返品率 >= :下限率 and :下限率 is not null or :下限率 is null )
and ( d.返品率 <= :上限率 and :上限率 is not null or :上限率 is null )
and ( a.取扱種別 = '納品' and :種別 <> '返品のみ'
or
a.取扱種別 = '返品' and :種別 <> '納品のみ' )
;
出力項目から結合や集約が違うものを分割してwithに列記し、それらを再度結合して検索の母集団を作り、これに検索条件を適用する。 個々の検索条件は、設定されていれば使い、設定されなかった場合は無視するように、有効無効も含めたパラメーターとしてSQLに埋め込む。
で、このままだとだいたい遅いので、これを出発点に検索条件をwithの中に移動させて検索の母集団を早期に減らすとか、インデックスを作ったりヒントをつけたりすることを考える。
が、検索条件をこんな形で静的にSQLに埋め込みたいのは本当に少数派の模様。
「この and なんとか is not null or なんとか is null ってなんですか？」
「なんとかが入力されてたら検索条件として使って、入力されなかったら無視」
「え、これでそうなるんですか？」
「なるでしょ。 or より and の方が強いからね」
「えーと…んー…あ、本当だ」
「どう？」
「いや、でもまだなんか騙されてる気がします」
最初に見せた時はだいたいこんな反応が返ってくるよ。