合わせ技1本
引き続き、集約操作の練習をもう1問やっておきましょう。問題は、『SQLパズル 第2版』の「パズル65 製品の対象年齢の範囲」を使います。表3のような、複数の製品の対象年齢ごとの値段を管理するテーブルがあるとします。同じ製品IDでも値段の異なる製品があるのは、対象年齢によって設定や難易度を変えたバージョンの違いによるもの、くらいに考えてください。また1つの製品について、年齢範囲の重複するレコードはないものと仮定します。
表3 PriceByAge
product_id (製品ID) | low_age (対象年齢の下限) | high_age (対象年齢の上限) | price (値段) |
製品1 | 0 | 50 | 2000 |
製品1 | 51 | 100 | 3000 |
製品2 | 0 | 100 | 4200 |
製品3 | 0 | 20 | 500 |
製品3 | 31 | 70 | 800 |
製品3 | 71 | 100 | 1000 |
製品4 | 0 | 99 | 8900 |
すると、このテーブルにおいては、(製品ID, 対象年齢の下限)で、レコードが一意に定まります(下限の代わりに上限を使ってもかまいません)。考えてもらう問題は、これらの製品の中から、0~100歳までのすべての年齢で遊べる製品を求めるというものです。もちろん、バージョンの相違は無視して、製品ID単位で考えます。
図5のように図示してみると、問題の意図がよりわかりやすくなるでしょう。
製品1の場合、2レコードを使って0~100までの整数の全範囲をカバーできています。したがって、製品1は今回の条件を確かに満たします。一方、製品3の数直線を見ると、3レコードも使っているにもかかわらず、21~30の間が断絶していることが見て取れます。こちらは残念ながらNGです。
このように、たとえ1レコードで全年齢範囲をカバーできなかったとしても、複数のレコードを組み合わせてカバーできたなら、「合わせ技1本」とみなす、というのがこの問題の主旨です。
そうとわかれば、あとの話は先ほどの問題と同じです。まず、集約する単位は製品ですから、集約キーは製品IDに決まります。あとは、各レコードの範囲の大きさをすべて足しこんだ合計が101に到達している製品を探し出せば任務完了です(0から100までなので、値の個数は101個であることに注意)。
答えはリスト6のようになります。
HAVING句の「high_age - low_age + 1」で、各行の年齢範囲が含む値の個数が算出されます。あとは、それを同じ製品内で足し合わせればよいわけです。
今は、サンプルとして「年齢」という数値型のデータを用いましたが、より一般的に日付や時刻に拡張することもできます。たとえば、応用問題としてこんなのはどうでしょう。ホテルの部屋ごとに、投宿日と出発日の履歴を記録するテーブルを使います(表4)。
表4 HotelRooms
room_nbr (部屋番号) | start_date (投宿日) | end_date(出発日) |
101 | 2008-02-01 | 2008-02-06 |
101 | 2008-02-06 | 2008-02-08 |
101 | 2008-02-10 | 2008-02-12 |
202 | 2008-02-05 | 2008-02-06 |
202 | 2008-02-08 | 2008-02-09 |
202 | 2008-02-09 | 2008-02-10 |
303 | 2008-02-03 | 2008-02-17 |
このテーブルから、稼働日数が10日を超える部屋を選択してください。稼働日数の定義は、宿泊日数で計ることとします。だから、投宿日が2月1日、出発日が2月6日の場合は、5泊なので5日です。これは演習問題として、宿題にしておきましょう(解答は、筆者のWebサイト注4に掲載しています)。