SQLにおける時間の分割と移動


データマイニングのためや、もっと単純な集計でも良いのですが、月や年で区切って集計するということがしばしば必要となります。今回はSQLでの実現方法について書きたいと思います。

ただし、単純にgroup byするという話ではありません。ここではある期間・区間において(以下区間とは1か月間や1年間などの集計単位、期間とは過去5年分など集計範囲を指すことにします)

  • 事象の発生回数(例:購買回数)
  • その期間、正確には期間中のある時点、における状態(例:年齢や顧客ランク)
  • 数値の合計(例:売り上げ合計)

といったものを計算することを考えます。
期間と区間

まず気をつけたいのは、データにはおおまかにいって時間情報が付与されてレコードが追加されていくものと、都度更新されていくものの2種類あるということです。
前者の例は購買履歴、後者の例は顧客名簿が挙げられます。顧客名簿には生年月日あるいは年齢、住所などが登録されています。住所は時期によって異なる可能性があるのですが、集計時には最新情報しかないという点が問題です。この点については誤差とあきらめるしかないのでしょうか。一方、年齢は生年月日が分かっていれば任意の時点の年齢を計算することもできます。

期間と区間

最初に、集計の期間と区間をどう実装するかを検討します。方法は大きく2種類考えられます。
一つは時間を表す項目から年や月など特定のフィールドを取り出してグループ化する方法で、例えば売上テーブルに対しgroup by extract(year from "購買日")を行うようなことです。単純で良いのですが、その区間までの累計を求めようとすると面倒です。

私のおすすめは、区間を表すテーブルを定義し、開始時点と終了時点等を登録することです。例えば

create table "集計区間" (
  label varchar, -- 例:2014年Q1
  from_date date,-- 例:2014-1-1
  to_date date   -- 例:2014-3-31
);

というテーブルを作り、以下のように集計します。

select
     t1.label
     , sum(t2."金額") as "期間中売上"
     , sum(t3."金額") as "累積売上"
from
     "集計区間" t1
left join
     "売上明細" t2 on t2."購買日" between t1.from_date and t1.to_date
left join
     "売上明細" t3 on t3."購買日" <= t1.to_date
group by
     t1.label
order by
     t1.to_date desc;

"集計区間"テーブルの作成にはPostgreSQLのgenerate_series関数を使うと便利かも知れません。
to_dateの指定は冗長かも知れませんが、結合条件を書きやすいのと、'2014年Q1'と'2014年通期'のように区間に重なりを持たせる場合に有効です。

また、時系列データを用いた予測の問題では、「ある時点から1か月後の状態を、2か月前までのデータを用いて予測する」ということがあり得ます。このように集計の基準となる時点が複数ある場合でもテーブルにカラムを足して対応できるという柔軟性があります。

マスタデータの時間展開

次に、期間中のある時点における状態を計算する方法について考えます。

年齢とか会員/非会員といった情報は集計時の状態を考えなければいけません。例えば2010年1月1日から2014年12月31日のデータから、毎月1日時点での会員の退会を予測しようとするモデルの学習データの一部は以下のようなクエリで計算されるかも知れません。これは、毎月1日時点での各会員の年齢と退会有無を求めています。

select
	t1."会員番号"
	, calc_age(t1."生年月日", t3."集計月初") as "年齢" -- 年齢計算関数を仮定
	, case when t2."退会日" <= t3."集計月初" then 1 else 0 end as "退会フラグ"
          -- 退会情報が無い("退会日" IS NULL)場合false、
          -- 退会が集計月以後であればまだ退会していないのでやはりfalse
from
	"会員マスタ" t1
left join -- これまで退会した会員があれば退会日を調べる
	"退会ログ" t2 using ("会員番号")
cross join -- 人と月の直積を取る
	generate_series('2010-1-1', '2014-12-1', interval '1 month') t3("集計月初")
where
	t1."入会日" >= t3."集計起点" -- まだ会員になってない月のデータは除く
;

マスタ表のスナップショットを持つ

マスタ表が都度更新されており更新履歴も記録されていない場合は、過去の状態を知ることはできません。論理的あるいは物理的なスナップショットを定期的に取得することが必要となります。この時それぞれのスナップショットを別表にしてしまうと扱いにくいので、取得日時とセットで記録しておくと良いと思います。ストレージ容量を気にする場合、変更があった場合のみレコードを残すようにすると良いでしょう。

以上です。


This entry was posted in データマイニング. Bookmark the permalink.