【SQL】過去直近データを取得するには

SQL

■ はじめに

SQLで、過去直近データを取りたい時って多々あるのでメモ。

目次

【１】方法１
　１）構文
　２）欠点
【２】方法２
　１）構文
　２）サンプル
【３】方法３：ROW_NUMBER + PARTITION BY

 【１】方法１

* ただし、この方法には欠点がある

１）構文

SELECT
  * 
FROM
  テーブル名
WHERE
  カラム名 = (SELECT MAX(カラム名) FROM テーブル名 WHERE カラム名 <= 対象日付)

 ２）欠点

 * メインのSQLに他に条件を加えると「(SELECT MAX(カラム名)・・・」の方にも
   同条件を追加しなくてはならない
  => ただ、逆に言うと、同条件を追加すればいいのだが...

ダメな例 

 * 以下のサンプルのデータにおいて、
  「id = 'I0000001'(apple)」の過去「2016-01-03」の直近データを取得しようと思った場合、
   以下のSQL文では0件データとなる

 ダメなサンプル 

-- For SQL Server2008 R2

DECLARE @target_date datetime;
SET @target_date =  '2016-01-03';

SELECT
  i.id,
  i.name,
  i.price,
  i.updatedate
FROM
 item AS i 
WHERE
 updatedate = 
  (SELECT MAX(updatedate) AS latest FROM item WHERE updatedate <= @target_date)
  AND id = 'I0000001'

 【２】方法２

 * 以下の関連記事「最新日付データを取得する」を応用するだけ

最新日付データを取得するには
https://dk521123.hatenablog.com/entry/2014/10/09/004105

１）構文

SELECT
  * 
FROM
  テーブル名 AS A1
INNER JOIN
 (SELECT
    主キー,
    MAX(カラム名) AS latest
  FROM
    テーブル名
  WHERE
    カラム名 <= 対象日付
  GROUP BY
    主キー)) AS A2
ON
 A1.主キー = A2.主キー
 AND A1.カラム名 = A2.latest

２）サンプル

* 以下のサンプルは、SQL Server2008 R2で試したもの

サンプルデータ 

-- テーブル定義
CREATE TABLE item
(
  id char(8),
  name varchar(100),
  price int,
  updatedate datetime
);

-- データ
INSERT INTO
 item(id, name, price, updatedate)
VALUES 
 ('I0000001', 'apple', 100, '2014-01-02 12:20:22'),
 ('I0000001', 'apple', 100, '2014-01-02 13:20:22'),
 ('I0000001', 'apple', 101, '2014-11-22 12:23:22'),
 ('I0000001', 'apple', 99, '2015-10-02 12:10:23'),
 ('I0000001', 'apple', 120, '2015-12-02 12:23:22'),
 ('I0000002', 'Orange', 62, '2014-01-01 12:20:22'),
 ('I0000002', 'Orange', 76, '2014-03-02 13:20:22'),
 ('I0000002', 'Orange', 92, '2014-11-22 12:23:22'),
 ('I0000002', 'Orange', 101, '2015-02-02 12:10:23'),
 ('I0000002', 'Orange', 67, '2015-12-02 12:23:22'),
 ('I0000002', 'Orange', 67, '2016-01-02 12:23:22');

SQL文 

DECLARE @target_date datetime;
SET @target_date =  '2016-01-03';

SELECT
 i.id,
 i.name,
 i.price,
 i.updatedate
FROM
 item AS i 
INNER JOIN
 (SELECT
  id,
  MAX(updatedate) AS latest
 FROM
  item
 WHERE
 id = 'I0000001' AND updatedate <= @target_date
 GROUP BY
  id) AS m
ON
 m.id=i.id
 AND m.latest = i.updatedate
ORDER BY
 id;

出力結果 

id   name    price   updatedate
I0000001    apple   120 2015-12-02 12:23:22.000

【３】方法３：ROW_NUMBER + PARTITION BY

* 以下の関連記事を参照のこと。

https://dk521123.hatenablog.com/entry/2021/03/11/210937

関連記事

サブクエリ / 副問合せ
https://dk521123.hatenablog.com/entry/2015/12/21/002727
ROW_NUMBER + PARTITION BY
https://dk521123.hatenablog.com/entry/2021/03/11/210937
最新日付データを取得するには
https://dk521123.hatenablog.com/entry/2014/10/09/004105