SQLアタマ養成講座

第9回 SQL流集合操作(2) 合わせ技1本

この記事を読むのに必要な時間:およそ 1 分

合わせ技1本

引き続き,集約操作の練習をもう1問やっておきましょう。問題は,『SQLパズル 第2版』「パズル65 製品の対象年齢の範囲」を使います。表3のような,複数の製品の対象年齢ごとの値段を管理するテーブルがあるとします。同じ製品IDでも値段の異なる製品があるのは,対象年齢によって設定や難易度を変えたバージョンの違いによるもの,くらいに考えてください。また1つの製品について,年齢範囲の重複するレコードはないものと仮定します。

表3 PriceByAge

product_id
(製品ID)
low_age
(対象年齢の下限)
high_age
(対象年齢の上限)
price
(値段)
製品10502000
製品1511003000
製品201004200
製品3020500
製品33170800
製品3711001000
製品40998900

すると,このテーブルにおいては,(製品ID, 対象年齢の下限)で,レコードが一意に定まります(下限の代わりに上限を使ってもかまいません)。考えてもらう問題は,これらの製品の中から,0~100歳までのすべての年齢で遊べる製品を求めるというものです。もちろん,バージョンの相違は無視して,製品ID単位で考えます。

図5のように図示してみると,問題の意図がよりわかりやすくなるでしょう。

図5 製品の対象年齢の範囲

図5 製品の対象年齢の範囲

製品1の場合,2レコードを使って0~100までの整数の全範囲をカバーできています。したがって,製品1は今回の条件を確かに満たします。一方,製品3の数直線を見ると,3レコードも使っているにもかかわらず,21~30の間が断絶していることが見て取れます。こちらは残念ながらNGです。

このように,たとえ1レコードで全年齢範囲をカバーできなかったとしても,複数のレコードを組み合わせてカバーできたなら,「合わせ技1本」とみなす,というのがこの問題の主旨です。

そうとわかれば,あとの話は先ほどの問題と同じです。まず,集約する単位は製品ですから,集約キーは製品IDに決まります。あとは,各レコードの範囲の大きさをすべて足しこんだ合計が101に到達している製品を探し出せば任務完了です(0から100までなので,値の個数は101個であることに注意)。

答えはリスト6のようになります。

リスト6 正解

SELECT product_id
  FROM PriceByAge
 GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;

HAVING句の「high_age - low_age + 1」で,各行の年齢範囲が含む値の個数が算出されます。あとは,それを同じ製品内で足し合わせればよいわけです。

今は,サンプルとして「年齢」という数値型のデータを用いましたが,より一般的に日付や時刻に拡張することもできます。たとえば,応用問題としてこんなのはどうでしょう。ホテルの部屋ごとに,投宿日と出発日の履歴を記録するテーブルを使います表4)。

表4 HotelRooms

room_nbr
(部屋番号)
start_date
(投宿日)
end_date(出発日)
1012008-02-012008-02-06
1012008-02-062008-02-08
1012008-02-102008-02-12
2022008-02-052008-02-06
2022008-02-082008-02-09
2022008-02-092008-02-10
3032008-02-032008-02-17

このテーブルから,稼働日数が10日を超える部屋を選択してください。稼働日数の定義は,宿泊日数で計ることとします。だから,投宿日が2月1日,出発日が2月6日の場合は,5泊なので5日です。これは演習問題として,宿題にしておきましょう(解答は,筆者のWebサイト注4に掲載しています)。

注4)
リレーショナル・データベースの世界

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入