縦持ちを横持ちにして集計する

DX(デジタルトランスフォーメーション)事業本部の吉倉です。

Excelに「ピボットテーブル」という機能があります。
便利な機能です。集計とかグループ化とかフィルタとかできるので、重宝します。

重宝ぶりを表すのに、例を出します。
過去、保守で対応していたシステムに、売上数量のデータが有りました。
データのカラムは以下のとおりです。

  • 日付
  • 商品コード
  • 拠点コード
  • 予測実績区分 (1:予測、2:実績)
  • 数量

Excelに貼るとこんな感じです。

data

このデータの特性は以下の通りです。

  • 1週間に1回、翌週の予測データが登録される
  • 毎日、前日の実績データが登録される
  • 数量ゼロのレコードは登録されない。
    • 売れる予定がない場合は、予測データのレコードが登録されない
    • 売れなかった場合は、実績データのレコードが登録されない

さて、このシステムの保守をしている時に、こんな依頼がありました。

  • 日毎の予測と実績を集計してほしい
  • 対象の商品コードは’a’のデータのみ
  • 期間は2021/1/1~2021/1/7
  • 予測と実績は並べて見たい

やりようはいくつかあります。列挙していきましょう。

ピボットテーブル(Excel)

データをExcelに持ってきて、ピボットテーブルを使えば一発です。こんな感じです。

excel-pivot

ピボットテーブルは、クロス集計するのに便利な機能です。
商品コード単位のフィルタもかけれますし、行のフィルタもかけられます。

左外部結合(SQL)

この依頼が来た当時、「SQLで書かなきゃ!」と思っていたこともあり、こんなSQLを書いたことを記憶しています。

このSQLには致命的な問題があります。どこに問題があるでしょうか。
何はなくとも実行してみましょう。

 

こんな結果になりました。

ng-result

予測が無いけど、実績がある日のレコード(2021年1月3日)が抽出されていません。
左外部結合の左側(駆動表)に居ないレコードなので、必然です。

このSQLは「予測の数字を日毎に集計した結果」と「実績の数字を日毎に集計した結果」を結合しています。
左外部結合・右外部結合を使っている限りは想定通りの結果は出ません。

 

縦横変換してから集計(SQL)

そもそも欲しいのは売上予測、売上実績ですが、このカラムはテーブルにありません。
先にカラムの値を展開して、欲しい結果のカラムを作ってから集計すると良さそうです。
縦横変換は、カラムの値を一意にした上で、列方向に展開して出力することです。
ここでは、変換したいカラムは予測実績区分で、予測実績区分は1:予測、2:実績のみです。

結果はこうなります。

予測実績区分が1のレコードは、売上予測数量が数量、売上実績数量がゼロ。
予測実績区分が2のレコードは、逆に、売上予測数量がゼロで、売上実績数量が数量。
というサブクエリtを作り、それを集計しました。

ok-result

想定通り(ピボットテーブルと同様)になりました!
先程Nullだった箇所が0になっていますが、こっちが見やすいですね。

これでおしまい。

、としようと思いましたが、前回のプロジェクトではO/Rマッパで全部データアクセス層は書ききりました。
いわゆる「生のSQL」というやつは使っていません。

「SQLで書けるのはいいけど、それってO/Rマッパで書けるの?」と言われて「できない」となると、
解決策にしづらくなります。

LINQ to SQL

じゃ、LINQ to SQLで書いてみましょう。
SampleContextがDbContextを継承したクラス、SmapleContextには売上数量テーブルのモデルがあるとして、
コードで書くとこうなります。

書けました。結果も問題なしでした。

PIVOT句(SQL)

そういえば、SQLにはPIVOT句があるのでした。
行列変換してくれる演算子があるのなら使わないともったいない。
PIVOT句で書いてみるとどうなるでしょう。

書けました。結果はこう。

pivot-sql

Excelのピボットテーブルと同じことをしたい、ということであれば、これがシンプルかもしれませんね。

さいごに

もともとのデータがデータベースにありますし、SQLに慣れているので、SQLで書くことが多いですが、
SQLの場合は、「予測実績区分に3(速報ベースの実績)を増やすよ」とか言われた場合に泣きを見ます。
ピボットテーブルを使っていると、Excelにデータをダウンロードしてこないといけない、という手間はあれども、
列に予測実績区分を指定しておけば、カラムに含まれている値を全部列方向に展開してくれます。
SQLとExcel、適切に使い分けていきたいものです。

最近はPower Queryが結構便利に思っているので、別途ブログにできればと思います。
ここまで読んでいただき、ありがとうございました。

最近の記事

  • 関連記事
  • おすすめ記事
  • 特集記事

アーカイブ

カテゴリー

PAGE TOP