skillup

技術ブログ

Database

サブクエリ 移動平均など

投稿日:

前回の応用編です。

日付、入出金、残高はできましたが、ここからさらに、現在のレコードから3行以内(3行あれば3行、なければそれ以内でできるだけ)のレコードの合計値を出す計算を考えます。

結果だけ先に書くと下記のようになります。

prc_date | prc_amt | sum
————+———+——-
2006-10-26 | 12000 | 12000
2006-10-28 | 2500 | 14500
2006-10-31 | -15000 | -500
2006-11-03 | 34000 | 21500
2006-11-04 | -5000 | 14000
2006-11-06 | 7200 | 36200
2006-11-11 | 11000 | 13200

SQL自体ですが、これは前回の下記のSQLに制限をかけるとわかりやすいです。

4行目の合計値に制限を付けます。

要はすべての合計値ではなく、a1.prc_dateとa2.prc_dateの間が常に3行以内になるデータが存在するということをSQLで表現します。

イメージでいうとEXISTSみたいなかんじに近いですかね。

要は1.prc_dateとa2.prc_dateの間に3行以内のレコードが存在すればいいので、下記のようなSQLを追加することになります。

といっても私は自力では思いつけなかったです。達人に学ぶSQL徹底指南書の模範解答をみて、具体値を入れた後、図に書き出して初めてイメージがつかめました。

こういうところはプログラムって数学に近いですね。本には非常にわかりやすい説明が載っていますが・・・・

時間の重複

以前、予約管理システムをつくったときにもやりましたが、宿泊のシステムなどを作った時に重複しているか否かをみるロジックをSQLで書いてみます。

以下のようなタイムテーブルがあるとします。

例によってデータはすべてここにあります。

reserver | start_date | end_date
———-+————+————
木村 | 2006-10-26 | 2006-10-27
荒木 | 2006-10-28 | 2006-10-31
堀 | 2006-10-31 | 2006-11-01
山本 | 2006-11-03 | 2006-11-04
内田 | 2006-11-03 | 2006-11-05
水谷 | 2006-11-06 | 2006-11-06

ここから重複を導くSQLは下記のようになります。基本的に数直線を書けばすぐわかるかと思います。

以前にこのエントリーで紹介しましたが、こういった書き方もできます。ただBetweenを使ったほうが直感的でわかりやすいかな・・・

これまた数直線を書いていろいろイメージしてみるとすぐにわかるかと思います。

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

アンチパターン トランザクションスコープ+大量データのリアルタイム集計+接続が詰まる

本日は主にインフラの設計的なことに関して。 Contents1 トランザクションスコープの設定1.1 デメリット1.2 対策2 大量データのリアルタイム集計2.1 デメリット2.2 対策3 詰まると接 …

no image

joinとeager loading

フレームワークでデータをORMがらみでjoinするときのネタ。 自分の場合はLaravel。他のフレームワークでも考え方は通じるものあるかと・・ Contents1 通常のjoin2 ループの中で取得 …

no image

SQL 更新系に関して

SQL実践入門、残り2章になりましたが、いやーむずいっす。 今回は9章を進めていますがSQLはもともと検索を主な用途として発展したため、SELECT文の使用がメインになります。 ですが、UPDATE文 …

no image

オプティマイザと実行計画

データベースがSQLを受け取って処理を実行する前には下記のような段階があります。 Contents1 パーサー2 オプティマイザ3 カタログマネージャー4 参考リンク パーサー SQL構文のチェックし …

no image

HAVING句について

本日はHAVINGについて。 かろうじて用法はしっており、たまに使うこともありますが、あまりしっかり理解しているとはいえない状況ですので、掘り下げてみようと思います。 WHEREとは違い、抽出した結果 …

アーカイブ