More than 1 year has passed since last update.

SQL：日付や数値などの大小比較で表を結合する

Last updated at 2022-11-15Posted at 2022-11-14

2つの表にある日付や数値を大小比較で結合する場合のクエリ例です。
ここでの例は、「結合元の日付未満で結合先の日付の最大の行を結合する」パターンで作成しました。
例えば、結合元の日付データが「2022-01-31」で、結合先に「2022-01-20」「2022-01-25」「2022-01-26」の日付データがあった場合に、「2022-01-31」に「2022-01-26」を結合します。
DB FiddleではMySQLのサンプルを載せていますが、特定のDBMSに依存した機能は使っていないので同様のクエリでできるはずです。

サンプルデータ

t1テーブル

key1	date1	val1
1	2022-01-02	30
1	2022-01-05	20
1	2022-01-20	10
1	2022-01-26	30
2	2022-01-08	10
2	2022-01-10	40
2	2022-01-19	10
3	2022-01-01	40
3	2022-01-08	20
3	2022-01-25	10

t2テーブル

key2	date2	val2
1	2022-01-02	10
1	2022-01-05	11
1	2022-01-05	12
1	2022-01-09	13
1	2022-01-10	14
1	2022-01-11	15
1	2022-01-14	16
1	2022-01-16	17
1	2022-01-17	18
1	2022-01-25	19
1	2022-01-27	20
1	2022-01-30	21
2	2022-01-06	22
2	2022-01-14	23
2	2022-01-19	24
2	2022-01-20	25
2	2022-01-20	26
2	2022-01-21	27
2	2022-01-22	28
2	2022-01-23	29
2	2022-01-28	30
2	2022-01-28	31
3	2022-01-03	32
3	2022-01-03	33
3	2022-01-03	34
3	2022-01-04	35
3	2022-01-19	36
3	2022-01-19	37
3	2022-01-22	38
3	2022-01-28	39

期待する結果

key1	date1	val1	key2	date2	val2
1	2022-01-02	30
1	2022-01-05	20	1	2022-01-02	10
1	2022-01-20	10	1	2022-01-17	18
1	2022-01-26	30	1	2022-01-25	19
2	2022-01-08	10	2	2022-01-06	22
2	2022-01-10	40	※	※	※
2	2022-01-19	10	2	2022-01-14	23
3	2022-01-01	40
3	2022-01-08	20	3	2022-01-04	35
3	2022-01-25	10	3	2022-01-22	38
※は「2022-01-06」をその前の行に結合されているので結合しない場合と、無関係に結合する場合が考えられます。

結合条件でサブクエリを使用したクエリ

結合条件で最大の日付に一致する行を抽出する方法です。
＜期待する結果＞の※の部分は「2022-01-06」になります。

SELECT * FROM t1 LEFT JOIN t2
ON t1.key1 = t2.key2 AND
   t2.date2 = (
     SELECT MAX(s.date2) FROM t2 s
       WHERE t1.key1 = s.key2 AND
             t1.date1 > s.date2)
ORDER BY t1.key1, t1.date1, t2.date2;

選択式でサブクエリを使用したクエリ

選択式で条件に合う行の中から取り出した最大の日付を付加する方法です。
＜期待する結果＞の※の部分は「2022-01-06」になります。

SELECT * FROM
(
  SELECT *,
    (SELECT MAX(t2.date2) FROM t2
     WHERE t1.key1 = t2.key2 AND
           t1.date1 > t2.date2) date2
  FROM t1
) s
LEFT JOIN t2
ON s.key1 = t2.key2 AND
   s.date2 = t2.date2
ORDER BY s.key1, s.date1, s.date2;

t1に対するクエリで、日付未満t1.date1 > t2.date2の最大MAX(t2.date2)を取得します。
t1にt2と結合可能なdate2の列を追加できたので、LEFT JOINによりs.date2=t2.date2で結合します。

UNIONを使用したクエリ

結合といいつつ、JOINではなくUNIONでの方法です。
この方法のよいところは、中間的に作成される行数が2つの表の総数を超えず、その中だけで処理していることです。
＜期待する結果＞の※の部分はNULLになります。

SELECT * FROM
(
  SELECT *,
    CASE
    WHEN LAG(s1.val1) OVER(PARTITION BY s1.key1 ORDER BY s1.date1) IS NULL
    THEN LAG(s1.date1) OVER(PARTITION BY s1.key1 ORDER BY s1.date1)
    ELSE NULL END date2
  FROM
  (
    SELECT key1,date1,val1 FROM t1
    UNION ALL
    SELECT key2,date2,NULL FROM t2
  ) s1
) s
LEFT JOIN t2
ON s.key1 = t2.key2 AND
   s.date2 = t2.date2
WHERE val1 IS NOT NULL
ORDER BY s.key1, s.date1, s.date2;

t1とt2をkey1(key2)とdate1(date2)のカラムを揃えてUNION ALLで統合します。 → s1のサブクエリ
CASE式でt1の行に対応したdate2を決定します。 → sのサブクエリ
1. LAG関数を使用して、key1でパーティショニング、date1でソートした1行前のval1を参照します。
  （UNION ALLする際にテーブル種別を表す項目を追加すればもっと直感的な判定ができます。）
2. NULLならt2テーブル側の行なので日付項目(t2のdate2)とします。
3. NULLでなければt1テーブル側の行なのでNULL(一致するデータなし)とします。
t1にt2と結合可能なdate2の列を追加できたので、LEFT JOINによりs.date2=t2.date2で結合します。
WHERE句でval1がNULLの行はt2の行なので除外します。

ROW_NUMBERを使用したクエリ

「日付の比較で結合」で一番イメージしやすい結合条件であるt1.date1 > t2.date2で結合します。
この場合、条件に合致する最大以外の行も結合されるため、ROW_NUMBERで降順に行番号をつけ、1行目だけ抽出することで最大行だけに絞り込みます。
ただし、絞込前の中間的に作成された行数は多くなります。
＜期待する結果＞の※の部分は「2022-01-06」になります。

SELECT * FROM
(
  SELECT *,
    ROW_NUMBER() OVER(
      PARTITION BY t1.key1,t1.date1
      ORDER BY t2.date2 DESC) N
  FROM t1 LEFT JOIN t2
  ON t1.key1 = t2.key2 AND
     t1.date1 > t2.date2
) s
WHERE s.N = 1
ORDER BY s.key1, s.date1, s.date2;

日付の比較t1.date1 > t2.date2で結合します。
ROW_NUMBERで結合元のkey1とdate1でパーティション、date2で降順ソートすることで、行番号1が最大の行となります。
WHERE句でs.N = 1の条件により最大の行だけに絞り込みます。

最大以外の行も結合するクエリ

上の3つとは異なり、最大行以外も含める場合のクエリです。
LAG関数を使用して1行前の日付(開始日)を持たせ、開始日date0～終了日date1の範囲を使用した結合を行うことで、最大行以外も結合した結果を得られます。
＜期待する結果＞の※の部分はNULLになります。

SELECT * FROM
(
  SELECT *,
    LAG(t1.date1) OVER(
      PARTITION BY t1.key1
      ORDER BY t1.date1) date0
  FROM t1
) s
LEFT JOIN t2
ON s.key1 = t2.key2 AND
   (s.date0 IS NULL OR s.date0 <= t2.date2) AND
   t2.date2 < s.date1
ORDER BY s.key1, s.date1, t2.date2;

LAG関数で1つ前の行の日付をdate0として追加します。 → sのサブクエリ
s.date0～s.date1の間に含まれるt2.date2を結合します。最初の行はNULLになるので、IS NULL条件も加えます。

実行結果

| key1 | date1      | val1 | date0      | key2 | date2      | val2 |
| ---- | ---------- | ---- | ---------- | ---- | ---------- | ---- |
| 1    | 2022-01-02 | 30   |            |      |            |      |
| 1    | 2022-01-05 | 20   | 2022-01-02 | 1    | 2022-01-02 | 10   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-05 | 12   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-05 | 11   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-09 | 13   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-10 | 14   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-11 | 15   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-14 | 16   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-16 | 17   |
| 1    | 2022-01-20 | 10   | 2022-01-05 | 1    | 2022-01-17 | 18   |
| 1    | 2022-01-26 | 30   | 2022-01-20 | 1    | 2022-01-25 | 19   |
| 2    | 2022-01-08 | 10   |            | 2    | 2022-01-06 | 22   |
| 2    | 2022-01-10 | 40   | 2022-01-08 |      |            |      |
| 2    | 2022-01-19 | 10   | 2022-01-10 | 2    | 2022-01-14 | 23   |
| 3    | 2022-01-01 | 40   |            |      |            |      |
| 3    | 2022-01-08 | 20   | 2022-01-01 | 3    | 2022-01-03 | 34   |
| 3    | 2022-01-08 | 20   | 2022-01-01 | 3    | 2022-01-03 | 33   |
| 3    | 2022-01-08 | 20   | 2022-01-01 | 3    | 2022-01-03 | 32   |
| 3    | 2022-01-08 | 20   | 2022-01-01 | 3    | 2022-01-04 | 35   |
| 3    | 2022-01-25 | 10   | 2022-01-08 | 3    | 2022-01-19 | 37   |
| 3    | 2022-01-25 | 10   | 2022-01-08 | 3    | 2022-01-19 | 36   |
| 3    | 2022-01-25 | 10   | 2022-01-08 | 3    | 2022-01-22 | 38   |

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up