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
260
キューとは何か
tsudaahr
0
230
等幅は死んだ(ぇ
tsudaahr
0
99
いくら眺めてもエラーの理由がわからないコードについて
tsudaahr
0
180
Other Decks in Programming
See All in Programming
GitHub Actions × AWS OIDC連携の仕組みと経緯を理解する
ota1022
0
250
(Extension DC 2025) Actor境界を越える技術
teamhimeh
1
250
Advance Your Career with Open Source
ivargrimstad
0
470
バッチ処理を「状態の記録」から「事実の記録」へ
panda728
PRO
0
140
iOSエンジニア向けの英語学習アプリを作る!
yukawashouhei
0
190
組込みだけじゃない!TinyGo で始める無料クラウド開発入門
otakakot
0
210
Back to the Future: Let me tell you about the ACP protocol
terhechte
0
140
ソフトウェア設計の実践的な考え方
masuda220
PRO
4
550
Web Components で実現する Hotwire とフロントエンドフレームワークの橋渡し / Bridging with Web Components
da1chi
3
2k
タスクの特性や不確実性に応じた最適な作業スタイルの選択(ペアプロ・モブプロ・ソロプロ)と実践 / Optimal Work Style Selection: Pair, Mob, or Solo Programming.
honyanya
3
160
オープンソースソフトウェアへの解像度🔬
utam0k
12
2.5k
どの様にAIエージェントと 協業すべきだったのか?
takefumiyoshii
2
640
Featured
See All Featured
The Illustrated Children's Guide to Kubernetes
chrisshort
48
51k
StorybookのUI Testing Handbookを読んだ
zakiyama
31
6.2k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
54
3k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
657
61k
For a Future-Friendly Web
brad_frost
180
9.9k
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
YesSQL, Process and Tooling at Scale
rocio
173
14k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
114
20k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
52
5.6k
Faster Mobile Websites
deanohume
310
31k
Producing Creativity
orderedlist
PRO
347
40k
The Cost Of JavaScript in 2023
addyosmani
53
9k
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との互換)や後方互換性なども検討する。 • とはいえ、頼りすぎは危険。
ご清聴ありがとうございました