履歴テーブルからデータを取得する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を紹介しました。
