SHOYAN BLOG

相関サブクエリを使って次回契約を取得する

相関サブクエリを使って次回契約を取得します。

Contract table

| id | account_id | start_date | end_date |
| — | — | — | — |
| 1 | 1 | 20140101 | 20141231 |
| 2 | 1 | 20150101 | 20151231 |
| 3 | 1 | 20160101 | 20161231 |
| 4 | 1 | 20170101 | 20171231 |
| 5 | 2 | 20150101 | 20151231 |
| 6 | 2 | 20160101 | 20161231 |

上記のようなaccount_idと開始日、終了日の登録してあるテーブルがあるとします。
現在の契約を取得するのは簡単ですね。

1
2

# 現在契約を取得する
SELECT * FROM contracts WHERE start_date >= 現在日付 AND end_date <= 現在日付

現在契約を取得するのは簡単ですが、その次の契約を取得するとなるとそう単純にはいきません。

そこで、相関サブクエリを使います。
相関サブクエリを使うことで次回契約を取得できます。

1
2
3
4
5
6
7
8
9
10
11
12

# 次回契約を取得する
SELECT *
  FROM contracts As cont
 WHERE start_date = (SELECT MIN(start_date)
                       FROM contracts as c1
                     WHERE c1.start_date > ( SELECT end_date
                                               FROM contracts as c2
                                             WHERE c2.start_date <= '20150528'
                                               AND c2.end_date >= '20150528'
                                               AND c1.account_id = c2.account_id)
                       AND cont.account_id = c1.account_id
                     GROUP BY c1.account_id);

結果

| id | account_id | start_date | end_date |
| — | — | — | — |
| 3 | 1 | 20160101 | 20161231 |
| 6 | 2 | 20160101 | 20161231 |

クエリの説明

クエリの説明をします。

クエリは内側からみていきます。
まずは、一番内側にある、 SELECT end_date ... AND c1.account_id = c2.account_idのクエリです。
このクエリでは現在の契約(ここでは2015/5/28とします)を取得します。

2つめのクエリで、次回以降の契約を取得します。
SELECT MIN(start_date) を使うことで、次回契約のなかで直近の契約を取得できます。
アカウントごとに直近の次回契約を取得したいので、GROUP BY account_id をしています。

c1.account_id = c2.account_id と cont.account_id = c1.account_id は行と行を比較するために必要です。

3つめのクエリ(SELECT * ... WHERE start_date =)で直近の次回契約を条件として、データを取得します。

手元で試したい方は以下のクエリでデータをつくれます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE `contracts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `start_date` int(11) DEFAULT NULL,
  `end_date` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `contracts` (`id`, `account_id`, `start_date`, `end_date`)
VALUES
    (1, 1, 20140101, 20141231),
    (2, 1, 20150101, 20151231),
    (3, 1, 20160101, 20161231),
    (4, 1, 20170101, 20171231),
    (5, 2, 20150101, 20151231),
    (6, 2, 20160101, 20161231);

よく読まれている記事

Comments