Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL のユーザー定義変数と RDB のココロ
Search
tsuda.a
September 20, 2015
Programming
0
210
MySQL のユーザー定義変数と RDB のココロ
第11回 中国地方DB勉強会 in 広島の資料です
tsuda.a
September 20, 2015
Tweet
Share
More Decks by tsuda.a
See All by tsuda.a
マジカルインクリメントと指数表記
tsudaahr
0
190
バックアップしていますか?
tsudaahr
0
120
RDB以前のファイル設計の話でもしようか(ぇ
tsudaahr
0
120
NPUわからん
tsudaahr
0
180
計算量オーダーの話
tsudaahr
1
390
クラウド初学者が抱える不安について
tsudaahr
0
270
キューとは何か
tsudaahr
0
230
等幅は死んだ(ぇ
tsudaahr
0
99
いくら眺めてもエラーの理由がわからないコードについて
tsudaahr
0
180
Other Decks in Programming
See All in Programming
品質ワークショップをやってみた
nealle
0
590
組込みだけじゃない!TinyGo で始める無料クラウド開発入門
otakakot
1
340
大規模アプリのDIフレームワーク刷新戦略 ~過去最大規模の並行開発を止めずにアプリ全体に導入するまで~
mot_techtalk
1
470
kiroとCodexで最高のSpec駆動開発を!!数時間で web3ネイティブなミニゲームを作ってみたよ!
mashharuki
0
790
SwiftDataを使って10万件のデータを読み書きする
akidon0000
0
230
なぜあの開発者はDevRelに伴走し続けるのか / Why Does That Developer Keep Running Alongside DevRel?
nrslib
3
410
Google Opalで使える37のライブラリ
mickey_kubo
2
120
モテるデスク環境
mozumasu
3
230
Software Architecture
hschwentner
6
2.3k
AI駆動で0→1をやって見えた光と伸びしろ
passion0102
1
660
その面倒な作業、「Dart」にやらせませんか? Flutter開発者のための業務効率化
yordgenome03
1
140
bootcamp2025_バックエンド研修_WebAPIサーバ作成.pdf
geniee_inc
0
120
Featured
See All Featured
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
Product Roadmaps are Hard
iamctodd
PRO
55
11k
We Have a Design System, Now What?
morganepeng
53
7.8k
Docker and Python
trallard
46
3.6k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
333
22k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
The Pragmatic Product Professional
lauravandoore
36
7k
The Illustrated Children's Guide to Kubernetes
chrisshort
49
51k
Building Flexible Design Systems
yeseniaperezcruz
329
39k
Leading Effective Engineering Teams in the AI Era
addyosmani
7
540
The MySQL Ecosystem @ GitHub 2015
samlambert
251
13k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
52
5.6k
Transcript
MySQLのユーザー定義変数と RDBのココロ @tsuda_ahr 第11回 中国DB勉強会 LT 2015/9/20
こんな表があるとします。 日付 借入額 返済額 2月1日 20000 0 2月2日 0 5000
2月3日 0 4000 2月4日 0 3000 2月5日 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 利息を含めた、 日々の残額を表示する
規則 • 1日1レコード • 毎日記録される • 利息は 残額に対して 1%/日 •
一円以下の利息は切り上げ
Excel なら瞬殺な問題だが… = H6 + (C7 – D7)
RDB側で解決する方法 • たとえば Oracle での解決方法を考えてみる。
まず表を作る 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;
計算結果を戻すための構造体とかコレクションを定義 create type 残債行 as object ( 日付 date, 借入額
number(6), 返済額 number(6), 差引 number(6), 利息 number(6), 残額 number(6) ); / create type 残債表 as table of 残債行; /
コード本体 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; /
結果 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
ストアドかよ(汗
SQL 単独で実現する場合は? あきらめた(汗
SQL では難しい なぜか? • 前の行にアクセスする必要がある。 • しかもアクセスしたい列は計算列。 • 固定値なら、OLAP 関数(LAGとか)で取ってこれないことはないが、
計算列なので一筋縄では取ってこれない。 • やれる方法はあるかもしれないが、素直に思いつかない段階で白旗(汗
具体的には何が問題か? 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
SQL (というか関係データベース) の特性 • SQL のモデルは、集合としてのデータであって、物理的なファイルではない。 • SQL における「作業単位」はスキーマ全体であって、個々のテーブルではない。 •
集合というのは、学校で習ったあの数学的な抽象概念だ。 • 集合に含まれる要素は、どれも同じタイプに属し、そして重要な性質は、 順序を持たないことだ。 「プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに」より https://www.shoeisha.co.jp/book/detail/9784798128023
つまり • SQL は順序を扱う処理は苦手
そこで MySQL の出番ですよ!
MySQL には ユーザー定義変数 という、超強力な機能がある!
MySQL のユーザー定義変数による解決 mysql> set @残額 := 0; mysql> select 日付,
借入額, 返済額, -> @差引 := @残額 + 借入額 - 返済額 as 差引, -> @利息 := ceiling(@差引 * 0.01) as 利息, -> @残額 := @差引 + @利息 as 残額 -> from 借入金 -> order by 日付;
結果 +------------+-----------+-----------+--------+--------+--------+ | 日付 | 借入額 | 返済額 | 差引
| 利息 | 残額 | +------------+-----------+-----------+--------+--------+--------+ | 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 | +------------+-----------+-----------+--------+--------+--------+
すごい!
でも邪道 (汗
なぜ邪道か? • SQLに見えて、やっていることはストアドと変わらない。 • ストアドが嫌われる理由 • 順序制御 • 環境依存 •
ユーザー定義変数の特性は、ストアドが嫌われる理由がそのまま当てはまる。 • 特に、SQL に見えて SQL の特性(=非順序性) を逸脱している点が凶悪。
ところで • このユーザー定義変数、使い方を間違えるとひどい目に合う可能性があるので 注意が必要。
例) ユーザー定義変数を含むソート 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;
例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 | +------------+-----------+-----------+--------+
例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 | +------------+-----------+-----------+--------+
例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 | +------------+-----------+-----------+--------+
分かったこと • ソートの指定にユーザー定義変数を含む場合と、そうでない場合とで、 ソートが実行される位置が違うように見える。 ユーザー定義変数をソートに含む場合 ユーザー定義変数の計算を行ってから、ソートを行う。 ユーザー定義変数をソートに含まない場合 ソートを行ってから、ユーザー定義変数の計算を行う。
なんか未定義動作のようで怖い…(汗 (将来的にも、いつ挙動が変わってもおかしくないかのような動き…)
結局どうすれば、一番良いのか? • 前日の残額が確定したとき、当日のレコード内に記録してやれば、 あとは当日のレコードを参照するだけで対処できる。 差引 利息 残額 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 この列の記録を追加する
結局バッチ処理かよ(汗
まとめ • 処理系の特性に逆らわない。 • 順序を SQL で操作しようとしたときは、「何かがおかしい」と一度立ち止まったほうがよい。 • 無理に SQL
だけで解決しようとせず、ストアドや外部言語系で行ったほうが適していないかを検討 する。 • 計算するか、記録するかのバランスが重要。 • 計算で求まる列だからといって、計算だけに頼らない。 • 都度の計算したほうがいいのか、それともバッチ処理で行ったほうが良いのかを検討する。 • しかし強力な機能を使わないのは損 • ストアドやユーザー定義変数が有効な場面では積極的に使うべき。 • その場合、移植性(=他のDBとの互換)や後方互換性なども検討する。 • とはいえ、頼りすぎは危険。
ご清聴ありがとうございました