Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

【SQL腕試し問題!】締め切り日が近いイベントの一覧の取得するSQLを書いて下さい

More than 5 years have passed since last update.

はじめに

先日、簡単そうに見えて意外と難しい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ができたらgistSQL FiddleのURLをこの記事のコメントに 貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。

Screen Shot 2014-01-24 at 5.58.31.png

その他

  • 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
  • この課題はとあるRailsの案件で必要になったSQLをベースとしています。Rails開発者の人はActiveRecordを使うならどうするか、というのを考えてみると良いかもしれません。

僕の解答例

僕はこんな感じで作ってみました。うーん、そこそこ複雑ですね~。
あ、自力で答えを考えたい人はまだ見ないで下さいね!!

SQLだけ見たい人はこちらをどうぞ。

最後に

それではみなさん、Let's try!!

jnchito
SIer、社内SEを経て、ソニックガーデンに合流したプログラマ。 「プロを目指す人のためのRuby入門」の著者。 http://gihyo.jp/book/2017/978-4-7741-9397-7 および「Everyday Rails - RSpecによるRailsテスト入門」の翻訳者。 https://leanpub.com/everydayrailsrspec-jp
https://blog.jnito.com/
sonicgarden
「お客様に無駄遣いをさせない受託開発」と「習慣を変えるソフトウェアのサービス」に取り組んでいるソフトウェア企業
http://www.sonicgarden.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away