SQL分析データ準備と基礎統計#10:取引履歴の集計

売上などの発生レコードを時系列で順に保持していくデータに対して、一定期間で集計をかける場合、ある期間においてはデータが発生しておらず、集計データが作成されないケースがあります。例えば顧客、取引年月日、売上金額といったカラムで構成されるテーブルから、顧客月別の売上金額を集計するとき、ある月にその顧客の売上がなければ、集計対象となる行すら存在しないため、歯抜けになってしまいます。時系列トレンドデータとしてみたいときは、この発生しない月の売上金額をゼロとしてみたいのですが、歯抜けが発生する.. このようなときに使うのが以下のような処理です。

ここでsrc内においては、単純に顧客別月別で集計しています。この段階では歯抜けが起こったままです。契約の開始と終了が合わせてテーブルに乗っているケースはまれですが、結合をサンプルに加えて難しくするのを避けるために乗せています。

次に、stem内において、顧客のユニークと、カレンダーテーブルから取得した年月のユニークをクロスジョインさせています。さらに契約開始から終了までにその期間を顧客ごとに絞っています。この組み合わせは直積のため、件数によっては非常に大きくなるので注意が必要です。

stemによって得られた顧客別月別の歯抜けなしのデータに対して、srcで得られた実際の集計値をleft outer joinしています。そして歯抜け部分に関してはnullとなるためゼロで埋めています。

///

#sql #teradata #analytics