プログラマ38の日記

主にプログラムメモです。

【DWH/SQL】データマートの作り方メモ

DWHで使い勝手を向上するため、粒度の異なる複数のファクトを加工して1つのデータマートを作成する時があります。

例えば、次のようなデータマートを作成します。

 

インプットデータ

  • 商品別、年月別の見込みデータがある。
  • 商品別、年月日別で実績データがある。

アウトプットデータ

  • 商品別、年月別で見込み・実績データマートを作る。
    (見込み・実績データマートは、商品、年月に対して見込値、実績値を持つ)

こういった時のデータマートを作るときは次のようなSQLになります。

select
  商品          as 商品
 ,年月          as 年月
 ,sum(見込値)   as 見込値
 ,sum(実績値)   as 実績値  
(
  select
    商品                       as 商品
   ,年月                       as 年月
   ,見込値                     as 見込値
   ,cast(null as decimal(18))  as 実績値
  from 
    見込みデータ
  union all
  select
    商品
   ,to_char(年月日,'YYYYMM')
   ,null
   ,実績値
  from 
    実績データ
) ユニオンデータ
group by
  商品          as 商品
 ,年月          as 年月

考え方としては、まずユニオンを使って縦にデータを並べて、並べた後に集計を行います。
縦に並べる際に、数値項目は、列を分けておくことで集計後に別項目として扱います。

使うのはunion all と group by なので全てのDBで可能なやり方です。

 

このやり方を使うと、見込データにはあるけど実績データにない「商品、年月の組み合わせ」やその逆で、実績データにはあるけど、見込みデータにはない「商品、年月の組み合わせ」も正しくデータを作成することができます。


実際のデータマートでは集計軸となる項目はもっとたくさんあって、時にはその項目がnullの場合もあると思いますが、このSQLは結合ではなく、集計を使うので、nullは集約できます。

 

他のやり方としては、見込みデータと実績データをそれぞれデータの粒度を揃えた後、full outer結合というやり方もなくはないですが、結合条件の値がnullだった時は結合できないのと、full outer結合ができないDBもあるのでお勧めしません。

 

次のサンプルデータでデータマートを作ってみます。

[見込みデータ]

商品 年月 見込値
P001 201001 960
P002 201001 274
P003 201001 181
P001 201002 276
P002 201002 499
P003 201002 3
P001 201003 141
P002 201003 993
P003 201003 757
P001 201004 610
P002 201004 455
P003 201004 903

 [実績データ]

商品 年月日 実績値
P001 2010/01/06 19
P003 2010/01/06 19
P001 2010/01/10 27
P003 2010/01/10 95
P004 2010/01/11 1
P003 2010/01/12 43
P001 2010/01/13 50
P003 2010/01/14 31
P001 2010/01/15 18
P004 2010/01/16 39
P001 2010/01/17 41
P003 2010/01/18 83

 [データマート]

商品 年月 見込値 実績値
P001 201001 960 155
P002 201001 274  
P003 201001 181 271
P004 201001   40
P001 201002 276  
P002 201002 499  
P003 201002 3  
P001 201003 141  
P002 201003 993  
P003 201003 757  
P001 201004 610  
P002 201004 455  
P003 201004 903  

 上のようにデータマートが作れます。

 

補足
TERADATAでユニオンを指定したselectを行うと、先頭の項目の型と桁が使われます。
(なので先頭の項目がchar(10)の場合、2番目の項目がchar(100)だとしてもchar(10)で切り取られたりします)
ただし、そのselect文を使ったinsert文ではinsert先の項目の型と桁が優先されます。