DX（デジタルトランスフォーメーション）事業本部の吉倉です。
Excelに「ピボットテーブル」という機能があります。
便利な機能です。集計とかグループ化とかフィルタとかできるので、重宝します。
重宝ぶりを表すのに、例を出します。
過去、保守で対応していたシステムに、売上数量のデータが有りました。
データのカラムは以下のとおりです。
- 日付
- 商品コード
- 拠点コード
- 予測実績区分 （1:予測、2:実績）
- 数量
Excelに貼るとこんな感じです。
このデータの特性は以下の通りです。
- 1週間に1回、翌週の予測データが登録される
- 毎日、前日の実績データが登録される
- 数量ゼロのレコードは登録されない。
- 売れる予定がない場合は、予測データのレコードが登録されない
- 売れなかった場合は、実績データのレコードが登録されない
さて、このシステムの保守をしている時に、こんな依頼がありました。
- 日毎の予測と実績を集計してほしい
- 対象の商品コードは’a’のデータのみ
- 期間は2021/1/1～2021/1/7
- 予測と実績は並べて見たい
やりようはいくつかあります。列挙していきましょう。
ピボットテーブル（Excel)
データをExcelに持ってきて、ピボットテーブルを使えば一発です。こんな感じです。
ピボットテーブルは、クロス集計するのに便利な機能です。
商品コード単位のフィルタもかけれますし、行のフィルタもかけられます。
左外部結合（SQL)
この依頼が来た当時、「SQLで書かなきゃ！」と思っていたこともあり、こんなSQLを書いたことを記憶しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT yosoku.日付, yosoku.売上予測数量合計, jisseki.売上実績数量合計 FROM ( SELECT 日付, SUM(数量) AS 売上予測数量合計 FROM 売上数量 WHERE 商品コード ='a' AND 予測実績区分 = '1' AND 日付 BETWEEN '2021-01-01' AND '2021-01-07' GROUP BY 日付 ) yosoku LEFT OUTER JOIN ( SELECT 日付, SUM(数量) AS 売上実績数量合計 FROM 売上数量 WHERE 商品コード ='a' AND 予測実績区分 = '2' AND 日付 BETWEEN '2021-01-01' AND '2021-01-07' GROUP BY 日付 ) jisseki ON yosoku.日付 = jisseki.日付 |
このSQLには致命的な問題があります。どこに問題があるでしょうか。
何はなくとも実行してみましょう。
こんな結果になりました。
予測が無いけど、実績がある日のレコード（2021年1月3日)が抽出されていません。
左外部結合の左側（駆動表）に居ないレコードなので、必然です。
このSQLは「予測の数字を日毎に集計した結果」と「実績の数字を日毎に集計した結果」を結合しています。
左外部結合・右外部結合を使っている限りは想定通りの結果は出ません。
縦横変換してから集計（SQL)
そもそも欲しいのは売上予測、売上実績ですが、このカラムはテーブルにありません。
先にカラムの値を展開して、欲しい結果のカラムを作ってから集計すると良さそうです。
縦横変換は、カラムの値を一意にした上で、列方向に展開して出力することです。
ここでは、変換したいカラムは予測実績区分で、予測実績区分は1:予測、2:実績のみです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT t.日付, SUM(t.売上予測数量) AS 売上予測数量合計, SUM(t.売上実績数量) AS 売上実績数量合計 FROM ( SELECT 日付 ,CASE WHEN 予測実績区分 = '1' THEN 数量 ELSE 0 END AS 売上予測数量 ,CASE WHEN 予測実績区分 = '2' THEN 数量 ELSE 0 END AS 売上実績数量 FROM 売上数量 WHERE 商品コード ='a' AND 日付 BETWEEN '2021-01-01' AND '2021-01-07' ) t GROUP BY t.日付 ORDER BY t.日付 |
結果はこうなります。
予測実績区分が1のレコードは、売上予測数量が数量、売上実績数量がゼロ。
予測実績区分が2のレコードは、逆に、売上予測数量がゼロで、売上実績数量が数量。
というサブクエリtを作り、それを集計しました。
想定通り（ピボットテーブルと同様）になりました！
先程Nullだった箇所が0になっていますが、こっちが見やすいですね。
これでおしまい。
、としようと思いましたが、前回のプロジェクトではO/Rマッパで全部データアクセス層は書ききりました。
いわゆる「生のSQL」というやつは使っていません。
「SQLで書けるのはいいけど、それってO/Rマッパで書けるの？」と言われて「できない」となると、
解決策にしづらくなります。
LINQ to SQL
じゃ、LINQ to SQLで書いてみましょう。
SampleContextがDbContextを継承したクラス、SmapleContextには売上数量テーブルのモデルがあるとして、
コードで書くとこうなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
var context = new SampleContext(options); var table = context.売上数量; var query = table .Where(x => x.商品コード == "a") .Where(x => x.日付 >= new DateTime(2021, 1, 1)) .Where(x => x.日付 <= new DateTime(2021, 1, 7)) .Select(x => new { x.日付, 売上予測数量 = x.予測実績区分 == "1" ? x.数量 : 0, 売上実績数量 = x.予測実績区分 == "2" ? x.数量 : 0, }) .GroupBy(x => x.日付) .Select(x => new { 日付 = x.Key, 売上予測数量合計 = x.Sum(y => y.売上予測数量), 売上実績数量合計 = x.Sum(y => y.売上実績数量) }) .OrderBy(x => x.日付); |
書けました。結果も問題なしでした。
PIVOT句(SQL)
そういえば、SQLにはPIVOT句があるのでした。
行列変換してくれる演算子があるのなら使わないともったいない。
PIVOT句で書いてみるとどうなるでしょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT 日付, [1] AS 売上予測数量合計, [2] AS 売上実績数量合計 FROM ( SELECT 日付, 予測実績区分, 数量 FROM 売上数量 WHERE 商品コード ='a' AND 日付 BETWEEN '2021-01-01' AND '2021-01-07' ) AS s PIVOT ( SUM(数量) FOR 予測実績区分 IN ([1], [2]) ) AS p |
書けました。結果はこう。
Excelのピボットテーブルと同じことをしたい、ということであれば、これがシンプルかもしれませんね。
さいごに
もともとのデータがデータベースにありますし、SQLに慣れているので、SQLで書くことが多いですが、
SQLの場合は、「予測実績区分に3（速報ベースの実績）を増やすよ」とか言われた場合に泣きを見ます。
ピボットテーブルを使っていると、Excelにデータをダウンロードしてこないといけない、という手間はあれども、
列に予測実績区分を指定しておけば、カラムに含まれている値を全部列方向に展開してくれます。
SQLとExcel、適切に使い分けていきたいものです。
最近はPower Queryが結構便利に思っているので、別途ブログにできればと思います。
ここまで読んでいただき、ありがとうございました。