woshidan's blog

あいとゆうきとITと、とっておきの話。

状態や数値を集約する列の取り扱いについて

途中まで読んでいて放置してしまっていたSQL実践入門をようやく読み終わりました。

9章の難しい問題を難しいままの状態で解きたがるスーパーソルジャー病という部分にはっと来たり、ウィンドウ関数の記述の一部で、そこはさすがにアプリケーションレイヤーの方が得意な領域ではないかなと思ったり、データベースエンジニアの人の考え方を妄想してみたりできて面白かったです。

その中で、11章の例としてあげられていた「注文表と注文明細表があって、注文表の中に注文明細表の集約列を作ることでクエリを簡単にする」、という話の部分について、もう少し確認しておきたいな、ということがあったので、簡単に確認しておきます。

注文明細の集約値を管理する列

注文に対して品物が何件あったとか、合計金額がいくらあったか、ということを注文一覧の画面を出すときに、クエリを簡単にしたり軽くしたりすることを考えます。

そういうとき、その都度クエリを飛ばして集計するクエリをチューニングするアプローチだけでなくて、注文表に、合計金額や合計点数みたいな列を設けて注文表に単純なクエリを飛ばせば済むようにする、というアプローチが紹介されていました*1

後者のアプローチをとる場合、合計金額や合計点数の列は、注文が作成された時やキャンセルされたときに動的に更新したり、バッチで定時に一気に更新する(大体深夜)ことが考えられます。

バッチで行う場合、注文が確定する時間と、その注文が閲覧されるまでの時間を意識する必要があります。バッチ処理をその2つの時間の間に終わらせる必要があるからです*2

注文件数がかなり多くて、集計に時間がかかるという場合に有効だと思います*3

ただ、一部は瞬時値が欲しい場合もあり、その場合は

  • 合計金額や合計点数の列は、注文が作成された時やキャンセルされたときに動的に更新
  • クエリをその都度走らせる

を状況に応じて併用すれば良いのかな、と思います。

また、今日の分のブログのPVはその都度クエリで最新の値をとって、昨日以前の分は、毎日のバッチで確定した値のレコードを用意しておこう、みたいな場合もありそうです。

注文の特定のフラグや状態を管理する列

注文された商品のうち、1つでも配送が遅くなっていたら商品ごとではなく注文ごとに遅配メールを送るといった場合を考えます。

メールを送る条件をSQLで抽出して...とするより、商品の方から注文の受付日と自身の配送日を比較して、注文表の遅配フラグを更新する、といった処理を書いたほうが事が簡単かもしれません。

ただ、フラグと言っても、抽出条件がややこしい、というよりは、 キャンセルされた、納品された、といった操作によって生まれる新しい事実みたいなことを扱う場合は、 注文に関するテーブルを使って管理するのもいいかもしれません。

この辺りは、なぜキャンセルしたのか、いつキャンセルしたのか、どこの業者が納品したか、いつ発送されたか、といった付随する他の情報を扱いたい事も多そうですし。

また、上の例も、遅配フラグ、というより注文の中の品物の1つの配送日が注文受付日より一定以上遅い、という事実ですね。

なんていうか、その状態であると判断するSQLが比較的簡単であれば、その状態である、そのフラグがONである、という判断のもとの事実で管理していたほうがよくて、クエリが複雑であったり時間がかかるのであれば、判断結果の列も必要悪としておいておく、みたいな感じに捉えた方がよさそうです。

判断結果の列は、パフォーマンス向上のために置くのであって、元から管理している事実は外してはいけない予感がします。

支払済だったらpaid, 出荷済みだったらshippedみたいな状態の列って事実というより事実を元にした判断が集約された列、と考えていた方が安心そうです。

xx_stateがあったら、いつ出荷されたか、いつ支払いされたかはおまけの情報、みたいな感じ方をしてしまうときがあるのですが、 売り上げをいつ立てるか、いつ提携元と金銭や労働力のやり取りが発生したの辿れないの怖いな、という感じです*4

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

*1:割といろんな本で紹介されているし書いてしまっていいでしょう

*2:こういうのをバッチウィンドウという

*3:アプリケーションの画面上の表示はアプリケーションの制限で回避出来る場合もある。どちらかというと不可避なのはcsvなどのファイルで集計した一覧をDLしたかったり、特定目的で調査してデータをこねくり回したかったときに途端にTEMP落ちしてしまうような状況の回避とか

*4:売上が立つタイミングが必ずしもすべての納品工程が終わった後かどうかみたいなのは、業態に依る、みたいなところを感じていて、元々その事実を扱う表があったとして、みたいな一番正規化された形を想定しておくのが安全かな、という考え方が最近自分の中であります。もっと他業種とコミュニケーションとろうよ、という話ではありますが、自分でも気づきやすい基準を持っておいた方が良さそう