集計する前やその後に必要な処理があったりするわけで、Power Query で集計処理をしなければならないこともあるいうことだ。なら、可能な限り集計処理も素早く終えるような工夫が必要だ。
Excel ワークシートに テーブル:Sales (約 1,000,000 行 / 4列) がある。わずか1,000,000 行。大事なことなので2回。
で、Sales には 社員ID列が含まれているので、別途用意したテーブルとマージし社員IDごとに数量列の合計を得たいとする。
let
Source = Table.NestedJoin(
Table1, {"社員ID"},
Sales, {"社員ID"},
"Sales", JoinKind.LeftOuter
),
AggregatedSales = Table.AggregateTableColumn(
Source,
"Sales",
{
{"数量", List.Sum, "Sum of 数量"}
}
)
in
AggregatedSales
おおむね 50 sec 程度で結果を得られた。遅いよね。
せっかくの Excel なのだからピボットテーブルでいいんじゃね？って話ではある。もしくは、Sales テーブルを参照する構造化参照 SUM( FILTER( Sales[数量], Sales[社員ID] = [@社員ID] ) ) で刹那で処理終わるよね。
まずは、なぜ遅いのかを探るとよいんだぜ
遅くなるのは仕方がないにしても、わずか 1,000,000 行(3回目)で遅くなっては困るのだ。なので、実際にどのような動作をしているのか見当つけるとよい。
例えば、仕訳けて集計したいとする社員IDをすべてではなく、1つづ増やしたときどうなるか。ここでは、社員ID が1 行の時、約 5 sec、2 行の時 約 10 sec、3 行の時 約 15 sec と行数に比例した時間を要していた。これは効率的な集計がされてないなと。おそらく、
let
AddedSumOf数量 = Table.AddColumn(
Table1,
"Sum of 数量",
each
let
Id = [社員ID],
FilteredSales = Table.SelectRows(
Sales,
each [社員ID] = Id
),
SumOfValue = List.Sum( FilteredSales[数量] )
in
SumOfValue
)
in
AddedSumOf数量
このクエリ同様の行ごと処理になってしまっているのだろうと。マージを示すステップを MashUp engine に課したとしてが妥当なものとして、行ごとの処理をしているのでしょう。
なぜ？だって、Sales テーブルの社員ID と Sales テーブルに関係なく社員IDと名付けた列を勝手に用意しただけだもの。
なので、テーブルの集計に使われるように情報を追加するとよいかなと。
できること
集計に使うキーを Mashup engine に伝える
集計用のテーブル各行がユニークであることを Mashup engine に伝えると、マージの内部処理が最適化され期待する集計が行われる。5 ~ 6 sec で結果が得られた。50 sec よりかなり改善である。
let
Source = Table.NestedJoin(
Table.AddKey( Table2, { "社員ID" }, true ), // 👈ココ
{"社員ID"},
Sales, {"社員ID"},
"Sales", JoinKind.LeftOuter
),
AggregatedSales = Table.AggregateTableColumn(
Source,
"Sales",
{
{"数量", List.Sum, "Sum of 数量"}
}
)
in
AggregatedSales
let
Source = Table.NestedJoin(
Table.Distinct( Table2 ), // 👈ココ
{"社員ID"},
Sales, {"社員ID"},
"Sales", JoinKind.LeftOuter
),
AggregatedSales = Table.AggregateTableColumn(
Source,
"Sales",
{
{"数量", List.Sum, "Sum of 数量"}
}
)
in
AggregatedSales
// Table.Distinct でキーが設定される
グループ化で集計
そもそもマージで集計する必要はなかったのでは？これも、5 ~ 6 sec で結果が得られる。
let
GroupedRows = Table.Group(
Sales,
{"社員ID"},
{
{"Sum of 数量", each List.Sum([数量]), Int64.Type}
}
)
in
GroupedRows
集計される社員IDをフィルタすればよいし、場合によってはマージしてもよい。グループ化のあとにソートしても影響は少ないはずだし。
ソートを素早くできる Excel だからできること
もっと素早く結果が必要なら ワークシート上で Sales テーブルにソートを仕掛けておけばよいのだ。
社員ID ごとで集計するから、社員ID が昇順もしくは降順であることを保証し、Table.Group 関数のロジックを変更する。これだと 2 ~ 3 sec でクエリの評価は完了する。
let
GroupedRows = Table.Group(
Sales, {"社員ID"},
{
{"Sum of 数量", each List.Sum([数量]), Int64.Type}
},
GroupKind.Local // 👈ココ
)
in
GroupedRows
If the data is already sorted by the key columns, then a groupKind of GroupKind.Local can be provided. This may improve the performance of grouping in certain cases, since all the rows with a given set of key values are assumed to be contiguous.
ソート済みならパフォーマンスが向上する可能性があるって書いてあるよね。これに該当するということだ。
集計をサポートするデータソースでも都合がよい時がある
集計をサポートしクエリフォールディングによる効率的な処理が可能なデータソースであってもグループ化を使うことがある。
データソースに定義されたプライバシーレベルにより効率的な集計ができないことがあって、例えば、Sales テーブルが Azure SQL Database に配置されていて、異なるデータソースに集計のキーとなる社員IDを含むテーブルがある。このとき、マージで集計しようとすると Sales テーブル全行を取得することがある。すると、Mashup engine が集計処理を行うことになりパフォーマンスが超悪化する。せっかくAzure SQL Database 使ってるのにね。なので、Table.Goup 関数で集計済み結果を得てから処理をするめることがある。
Table.Group 関数は Azure SQL Database に対し GROUP BY を使用した集計済みの結果を要求するので結構使えるのです。
思ったこと🙄
マージはよく使われる処理だけど、クエリフォールディングが期待できないデータソースであるとき、とてもコストが高い処理なんですよ。