MySQL のユーザー定義変数と RDB のココロ

第11回 中国地方DB勉強会 in 広島の資料です

Transcript

1. MySQLのユーザー定義変数と RDBのココロ @tsuda_ahr 第11回 中国DB勉強会 LT 2015/9/20
2. こんな表があるとします。 日付 借入額 返済額 2月1日 20000 0 2月2日 0 5000 2月3日 0 4000 2月4日 0 3000 2月5日 10000 2000
3. この表を元に、こんな結果を得たい 差引 利息 残額 20000 200 20200 15200 152 15352 11352 114 11466 8466 85 8551 16551 166 16717 日付 借入額 返済額 2月1日 20000 0 2月2日 0 5000 2月3日 0 4000 2月4日 0 3000 2月5日 10000 2000 利息を含めた、 日々の残額を表示する
4. 規則 • 1日1レコード • 毎日記録される • 利息は 残額に対して 1%/日 • 一円以下の利息は切り上げ
5. Excel なら瞬殺な問題だが… = H6 + (C7 – D7)
6. RDB側で解決する方法 • たとえば Oracle での解決方法を考えてみる。
7. まず表を作る create table 借入金 ( 日付 date, 借入額 number(6), 返済額 number(6) ); insert into 借入金 values ('2015/2/1', 20000, 0); insert into 借入金 values ('2015/2/2', 0, 5000); insert into 借入金 values ('2015/2/3', 0, 4000); insert into 借入金 values ('2015/2/4', 0, 3000); insert into 借入金 values ('2015/2/5', 10000, 2000); commit;
8. 計算結果を戻すための構造体とかコレクションを定義 create type 残債行 as object ( 日付 date, 借入額 number(6), 返済額 number(6), 差引 number(6), 利息 number(6), 残額 number(6) ); / create type 残債表 as table of 残債行; /
9. コード本体 create or replace function 残債 return 残債表 pipelined is 差引 number(6); 利息 number(6); 残額 number(6); cursor cur1 is select * from 借入金; begin 残額 := 0; for row1 in cur1 loop 差引 := 残額 + row1.借入額 - row1.返済額; 利息 := ceil(差引 * 0.01); 残額 := 差引 + 利息; pipe row(残債行(row1.日付, row1.借入額, row1.返済額, 差引, 利息, 残額)); end loop; return; end; /
10. 結果 select * from table(残債()); 日付 借入額 返済額 差引 利息 残額 -------- ---------- ---------- ---------- ---------- ---------- 15-02-01 20000 0 20000 200 20200 15-02-02 0 5000 15200 152 15352 15-02-03 0 4000 11352 114 11466 15-02-04 0 3000 8466 85 8551 15-02-05 10000 2000 16551 166 16717
11. ストアドかよ(汗
12. SQL 単独で実現する場合は？ あきらめた（汗
13. SQL では難しい なぜか？ • 前の行にアクセスする必要がある。 • しかもアクセスしたい列は計算列。 • 固定値なら、OLAP 関数(LAGとか)で取ってこれないことはないが、 計算列なので一筋縄では取ってこれない。 • やれる方法はあるかもしれないが、素直に思いつかない段階で白旗(汗
14. 具体的には何が問題か? 16551 = 8551 + (10000 – 2000) • 「当日の」借入額と返済額の差と、「前日の」残額を足す必要がある。 差引 利息 残額 20000 200 20200 15200 152 15352 11352 114 11466 8466 85 8551 16551 166 16717 日付 借入額 返済額 2月1日 20000 0 2月2日 0 5000 2月3日 0 4000 2月4日 0 3000 2月5日 10000 2000
15. SQL (というか関係データベース) の特性 • SQL のモデルは、集合としてのデータであって、物理的なファイルではない。 • SQL における「作業単位」はスキーマ全体であって、個々のテーブルではない。 • 集合というのは、学校で習ったあの数学的な抽象概念だ。 • 集合に含まれる要素は、どれも同じタイプに属し、そして重要な性質は、 順序を持たないことだ。 「プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに」より https://www.shoeisha.co.jp/book/detail/9784798128023
16. つまり • SQL は順序を扱う処理は苦手
17. そこで MySQL の出番ですよ！
18. MySQL には ユーザー定義変数 という、超強力な機能がある！
19. MySQL のユーザー定義変数による解決 mysql> set @残額 := 0; mysql> select 日付, 借入額, 返済額, -> @差引 := @残額 + 借入額 - 返済額 as 差引, -> @利息 := ceiling(@差引 * 0.01) as 利息, -> @残額 := @差引 + @利息 as 残額 -> from 借入金 -> order by 日付;
20. 結果 +------------+-----------+-----------+--------+--------+--------+ | 日付 | 借入額 | 返済額 | 差引 | 利息 | 残額 | +------------+-----------+-----------+--------+--------+--------+ | 2015-02-01 | 20000 | 0 | 20000 | 200 | 20200 | | 2015-02-02 | 0 | 5000 | 15200 | 152 | 15352 | | 2015-02-03 | 0 | 4000 | 11352 | 114 | 11466 | | 2015-02-04 | 0 | 3000 | 8466 | 85 | 8551 | | 2015-02-05 | 10000 | 2000 | 16551 | 166 | 16717 | +------------+-----------+-----------+--------+--------+--------+
21. すごい！
22. でも邪道 (汗
23. なぜ邪道か？ • SQLに見えて、やっていることはストアドと変わらない。 • ストアドが嫌われる理由 • 順序制御 • 環境依存 • ユーザー定義変数の特性は、ストアドが嫌われる理由がそのまま当てはまる。 • 特に、SQL に見えて SQL の特性(=非順序性) を逸脱している点が凶悪。
24. ところで • このユーザー定義変数、使い方を間違えるとひどい目に合う可能性があるので 注意が必要。
25. 例) ユーザー定義変数を含むソート set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金; set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金 order by 日付 desc, 累積; set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金 order by 日付 desc;
26. 例1) ソートなし set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金; +------------+-----------+-----------+--------+ | 日付 | 借入額 | 返済額 | 累積 | +------------+-----------+-----------+--------+ | 2015-02-01 | 20000 | 0 | 0 | | 2015-02-02 | 0 | 5000 | 5000 | | 2015-02-03 | 0 | 4000 | 9000 | | 2015-02-04 | 0 | 3000 | 12000 | | 2015-02-05 | 10000 | 2000 | 14000 | +------------+-----------+-----------+--------+
27. 例2) 日付と、ユーザー定義変数でソート set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金 order by 日付 desc, 累積; +------------+-----------+-----------+--------+ | 日付 | 借入額 | 返済額 | 累積 | +------------+-----------+-----------+--------+ | 2015-02-05 | 10000 | 2000 | 14000 | | 2015-02-04 | 0 | 3000 | 12000 | | 2015-02-03 | 0 | 4000 | 9000 | | 2015-02-02 | 0 | 5000 | 5000 | | 2015-02-01 | 20000 | 0 | 0 | +------------+-----------+-----------+--------+
28. 例3) 日付だけでソート set @sum := 0; select 日付, 借入額, 返済額, @sum := @sum + 返済額 as 累積 from 借入金 order by 日付 desc; +------------+-----------+-----------+--------+ | 日付 | 借入額 | 返済額 | 累積 | +------------+-----------+-----------+--------+ | 2015-02-05 | 10000 | 2000 | 2000 | | 2015-02-04 | 0 | 3000 | 5000 | | 2015-02-03 | 0 | 4000 | 9000 | | 2015-02-02 | 0 | 5000 | 14000 | | 2015-02-01 | 20000 | 0 | 14000 | +------------+-----------+-----------+--------+
29. 分かったこと • ソートの指定にユーザー定義変数を含む場合と、そうでない場合とで、 ソートが実行される位置が違うように見える。 ユーザー定義変数をソートに含む場合 ユーザー定義変数の計算を行ってから、ソートを行う。 ユーザー定義変数をソートに含まない場合 ソートを行ってから、ユーザー定義変数の計算を行う。
30. なんか未定義動作のようで怖い…（汗 (将来的にも、いつ挙動が変わってもおかしくないかのような動き…)
31. 結局どうすれば、一番良いのか？ • 前日の残額が確定したとき、当日のレコード内に記録してやれば、 あとは当日のレコードを参照するだけで対処できる。 差引 利息 残額 20000 200 20200 15200 152 15352 11352 114 11466 8466 85 8551 16551 166 16717 日付 借入額 返済額 2月1日 20000 0 2月2日 0 5000 2月3日 0 4000 2月4日 0 3000 2月5日 10000 2000 前日の残額 0 20200 15352 11466 8551 この列の記録を追加する
32. 結局バッチ処理かよ(汗
33. まとめ • 処理系の特性に逆らわない。 • 順序を SQL で操作しようとしたときは、「何かがおかしい」と一度立ち止まったほうがよい。 • 無理に SQL だけで解決しようとせず、ストアドや外部言語系で行ったほうが適していないかを検討 する。 • 計算するか、記録するかのバランスが重要。 • 計算で求まる列だからといって、計算だけに頼らない。 • 都度の計算したほうがいいのか、それともバッチ処理で行ったほうが良いのかを検討する。 • しかし強力な機能を使わないのは損 • ストアドやユーザー定義変数が有効な場面では積極的に使うべき。 • その場合、移植性(=他のDBとの互換)や後方互換性なども検討する。 • とはいえ、頼りすぎは危険。
34. ご清聴ありがとうございました