はじめに
先日、簡単そうに見えて意外と難しいSQLに遭遇したので、SQLの腕試し問題として出題してみます。
みなさん奮って答えを考えてみて下さい！！
要件
- イベントとスケジュールという二つのモデル(テーブル)がある。
- イベントは複数のスケジュールを持つことができる。
- スケジュールを一件も持たないイベントもある（スケジュール未定のイベント）。
- スケジュールには締め切り日が設定されている。
- イベント単位で同じ締め切り日は複数存在しないものとする。
- 次のような条件でイベントとスケジュールの一覧を出したい。
- システム日付以降に締め切り日がある、またはスケジュール未定のイベントのみを表示する。
- スケジュールが複数あるイベントはシステム日付に最も近い締め切り日のスケジュールのみを表示する。
- 締め切り日順に並び替える（締め切り日が近いほど上）。ただしスケジュール未定であれば一番下に表示する。
- 締め切り日が同じであればイベントID順に並び替える（IDが小さいものほど上）。
例
イベントテーブル
| id | name |
|---|---|
| 1 | Completed event |
| 2 | No schedule event |
| 3 | Continuing event |
| 4 | Future event 1 |
| 5 | Future event 2 |
スケジュールテーブル
| id | event_id | due_date |
|---|---|---|
| 1 | 1 | 2014/01/23 |
| 2 | 3 | 2014/01/01 |
| 3 | 3 | 2014/02/01 |
| 4 | 3 | 2014/03/01 |
| 5 | 4 | 2014/01/24 |
| 6 | 4 | 2014/01/25 |
| 7 | 5 | 2014/01/24 |
期待する出力結果(システム日付が 2014/01/24 の場合)
| event_id | name | schedule_id | due_date |
|---|---|---|---|
| 4 | Future event 1 | 5 | 2014/01/24 |
| 5 | Future event 2 | 7 | 2014/01/24 |
| 3 | Continuing event | 3 | 2014/02/01 |
| 2 | No schedule event |
制約等
- RDBMSはPostgreSQL 9.2.4とする(推奨)。
- 解答例を比較しやすいようにRDBMSを固定しようと思いましたが、必須条件ではなく推奨条件とします。他のRDBMSを使ってもOKです。SQL方言やRDBMS固有の関数も自由に使って下さい。
- システム日付は便宜的に以下のSQLで取得できる値とする。
SELECT MAX(sysdate) FROM sysdate_dummy
スキーマ作成用のSQL
CREATE TABLE events (
id integer PRIMARY KEY
,name varchar(50)
);
INSERT INTO events VALUES (1,'Completed event');
INSERT INTO events VALUES (2,'No schedule event');
INSERT INTO events VALUES (3,'Continuing event');
INSERT INTO events VALUES (4,'Future event 1');
INSERT INTO events VALUES (5,'Future event 2');
CREATE TABLE schedules (
id integer PRIMARY KEY
,event_id integer
,due_date date
);
INSERT INTO schedules VALUES (1,1,'2014/01/23');
INSERT INTO schedules VALUES (2,3,'2014/01/01');
INSERT INTO schedules VALUES (3,3,'2014/02/01');
INSERT INTO schedules VALUES (4,3,'2014/03/01');
INSERT INTO schedules VALUES (5,4,'2014/01/24');
INSERT INTO schedules VALUES (6,4,'2014/01/25');
INSERT INTO schedules VALUES (7,5,'2014/01/24');
CREATE TABLE sysdate_dummy (
id integer PRIMARY KEY
,sysdate date
);
INSERT INTO sysdate_dummy VALUES (1,'2014/01/24');
解答方法
- 実行方法は自由ですが、SQL Fiddleを使うとお手軽かもしれません。
- SQLができたらgistやSQL FiddleのURLをこの記事のコメントに 貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。
その他
- 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
- この課題はとあるRailsの案件で必要になったSQLをベースとしています。Rails開発者の人はActiveRecordを使うならどうするか、というのを考えてみると良いかもしれません。
僕の解答例
僕はこんな感じで作ってみました。うーん、そこそこ複雑ですね～。
あ、自力で答えを考えたい人はまだ見ないで下さいね！！
SQLだけ見たい人はこちらをどうぞ。
最後に
それではみなさん、Let's try!!