履歴テーブルからデータを取得するSQL

DI部の大矢です。こんにちは。

現在私は、Amazon Redshiftを使ったデータ分析システムの開発プロジェクトに参画しておりますが、
今回は、データ分析システムではよく出てくる「履歴テーブル」を扱うSQLについて書いてみます。

記載したSQLは、Amazon Redshift(1.0.1086)、PostgreSQL(9.5.3)で動作確認しました。

履歴テーブルとは

履歴テーブルとは、データを更新する際、元のレコードはそのまま残して、新たなレコードをinsertしていくようにしているテーブルです。 こうしておくと、過去の任意の時点でデータがどのような状態であったのか、調べることができます。

今回は以下のような「顧客テーブル」を例に使用します。
A社は社名を頻繁に変え、同時に引っ越しもしているようですね。

create table customer (
    customer_id     smallint
    , customer_name varchar(256)    --会社名
    , address       varchar(256)    --住所
    , updated_at    timestamp       --更新日時
);

 customer_id | customer_name |   address   |     updated_at
-------------+---------------+-------------+---------------------
           1 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
           1 | A株式会社     | 新宿区...   | 2015-03-01 12:34:56
           1 | A.com         | 千代田区... | 2016-07-01 12:34:56
           2 | B社           | 千葉市...   | 2012-07-01 12:34:56
           2 | B社           | 市川市...   | 2016-02-01 12:34:56
           3 | C社           | 名古屋市... | 2015-02-01 12:34:56

履歴テーブルから最新レコードを取得する

顧客テーブルから、customer_idごとの最新のレコードのみを抽出するクエリーは以下のようになります。

select customer_id, customer_name, address, updated_at from (
    select
        customer_id
        , customer_name
        , address
        , updated_at
        , row_number() over (
            partition by customer_id
            order by updated_at desc
        ) as row_num
    from customer
) as r
where row_num = 1

 customer_id | customer_name |   address   |     updated_at
-------------+---------------+-------------+---------------------
           1 | A.com         | 千代田区... | 2016-07-01 12:34:56
           2 | B社           | 市川市...   | 2016-02-01 12:34:56
           3 | C社           | 名古屋市... | 2015-02-01 12:34:56

ウィンドウ関数「row_number」を使って、customer_idごとに更新日時の降順でレコードに順位をつけ、1位のレコードのみに絞ります。

履歴テーブルから、ある時点のレコードを取得

最新ではなく、過去のある時点のレコードを取得する場合は、先ほどのクエリーにwhere句で更新日時の条件を加えることでできます。 例えば2015年初時点を条件とすると、以下のようになります。

select customer_id, customer_name, address, updated_at from (
    select
        customer_id
        , customer_name
        , address
        , updated_at
        , row_number() over (
            partition by customer_id
            order by updated_at desc
        ) as row_num
    from customer
    where updated_at < timestamp '2015-01-01 00:00:00'
) as r
where row_num = 1

 customer_id | customer_name |   address   |     updated_at
-------------+---------------+-------------+---------------------
           1 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
           2 | B社           | 千葉市...   | 2012-07-01 12:34:56

別のテーブルとJOINする際、適切な履歴レコードとJOINさせる

以下のような「売上テーブル」があるとします。

create table sales (
    sales_id        smallint
    , customer_id   smallint
    , sales_date    date        --売上日
    , sales_amount  bigint      --売上額
);

 sales_id | customer_id | sales_date | sales_amount
----------+-------------+------------+--------------
        1 |           1 | 2014-03-15 |      5000000
        2 |           1 | 2015-03-15 |     10000000
        3 |           1 | 2016-03-15 |     20000000
        4 |           2 | 2016-01-15 |      1000000
        5 |           2 | 2016-01-15 |      2000000
        6 |           3 | 2015-03-15 |     30000000

売上テーブルと顧客テーブルをJOINすることを考えます。 せっかく顧客テーブルが「履歴テーブル」なので、売上日の時点の会社名、住所を表示したいですね。
※前提として、売り上げを登録する前に顧客テーブルに顧客を登録している、顧客の会社名や住所が変更されたら即座に顧客テーブルに反映している、とします。

ということで、以下のようなクエリーを考えてみました。

select 
    sales_id
    , sales_date
    , sales_amount
    , customer_name
    , address
    , updated_at
from (
    select
        *
        , row_number() over (
            partition by sales_id
            order by updated_at desc
        ) as row_num
    from (
        select 
            s.sales_id
            , s.sales_date
            , s.sales_amount
            , c.customer_id
            , c.customer_name
            , c.address
            , c.updated_at
        from sales as s
        inner join customer as c
            on  s.customer_id = c.customer_id
            and s.sales_date >= c.updated_at
    ) as s_c
) as r
where row_num = 1

 sales_id | sales_date | sales_amount | customer_name |   address   |     updated_at
----------+------------+--------------+---------------+-------------+---------------------
        1 | 2014-03-15 |      5000000 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
        2 | 2015-03-15 |     10000000 | A株式会社     | 新宿区...   | 2015-03-01 12:34:56
        3 | 2016-03-15 |     20000000 | A株式会社     | 新宿区...   | 2015-03-01 12:34:56
        4 | 2016-01-15 |      1000000 | B社           | 千葉市...   | 2012-07-01 12:34:56
        5 | 2016-01-15 |      2000000 | B社           | 千葉市...   | 2012-07-01 12:34:56
        6 | 2015-03-15 |     30000000 | C社           | 名古屋市... | 2015-02-01 12:34:56

一応できました。

解説しますと、一番下のサブクエリーで、売り上げテーブルと顧客テーブルをJOINしています。JOINの条件として、顧客レコードは更新日時が売上日時より前であることとしています。 こうすることで、売り上げ後の顧客レコードは排除できましたが、売上日よりも古いレコードが複数ある場合、まだ絞り切れてません。 一番下のサブクエリーだけを実行した結果は以下のようになります。

 sales_id | sales_date | sales_amount | customer_id | customer_name |   address   |     updated_at
----------+------------+--------------+-------------+---------------+-------------+---------------------
        1 | 2014-03-15 |      5000000 |           1 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
        2 | 2015-03-15 |     10000000 |           1 | A株式会社     | 新宿区...   | 2015-03-01 12:34:56
        2 | 2015-03-15 |     10000000 |           1 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
        3 | 2016-03-15 |     20000000 |           1 | A株式会社     | 新宿区...   | 2015-03-01 12:34:56
        3 | 2016-03-15 |     20000000 |           1 | 有限会社A     | 世田谷区... | 2012-04-01 12:34:56
        4 | 2016-01-15 |      1000000 |           2 | B社           | 千葉市...   | 2012-07-01 12:34:56
        5 | 2016-01-15 |      2000000 |           2 | B社           | 千葉市...   | 2012-07-01 12:34:56
        6 | 2015-03-15 |     30000000 |           3 | C社           | 名古屋市... | 2015-02-01 12:34:56

このサブクエリーの結果を、row_number関数を使ってsales_idごとに更新日時の降順でレコードに順位をつけ、1位のみに絞ることで、ようやく望んだ結果が得られました。

まとめ

今回は、分析システムのDBでよく登場する「履歴テーブル」を扱うSQLを紹介しました。