DWHで使い勝手を向上するため、粒度の異なる複数のファクトを加工して１つのデータマートを作成する時があります。
例えば、次のようなデータマートを作成します。
インプットデータ
- 商品別、年月別の見込みデータがある。
- 商品別、年月日別で実績データがある。
アウトプットデータ
- 商品別、年月別で見込み・実績データマートを作る。
（見込み・実績データマートは、商品、年月に対して見込値、実績値を持つ）
こういった時のデータマートを作るときは次のような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先の項目の型と桁が優先されます。
