表計算 (Table Calc) の窓関数 (Windows function) を使う方法

  • 11 November 2021
  • 0 replies
  • 48 views

Userlevel 2

みなさんこんにちは!

表計算による窓関数は非常に便利なものでぜひ記事をアップしたいと思いました。

下記の画像をぜひご覧ください。極端な例になりますが、およそ300行ほどのデータを2つの行に圧縮することができました。

男子・女子の合計売り上げ


こちらのメソッドは割とシンプル、しかし魔法的と言えるでしょう。

 

まずはGroup集計(窓関数)かけたいコラムを指定し、(今回はProducts Department)値がはじまる行と次の値がはじまる行を変数にする表計算を作ります
→ 表計算1
名前:group_start_row:
コード:match(${products.department}, ${products.department})
そして
→ 表計算2
名前:next_group_start_row:
コード:count(${products.department}) - match(${products.department}, offset(${products.department}, count(${products.department}) - row()*2 + 1)) + 2

上記のデータ変数があれば、様々な窓関数が開放されます。
例えば:
名前:group_count:
コード:${next_group_start_row} - ${group_start_row}


又は、上の画像で使っている

名前:group_sum:
コード:sum(offset_list(${inventory_items.total_cost}, -1 * (row() - ${group_start_row}), ${next_group_start_row} - ${group_start_row}))

 

他にはRunning_totalの窓関数も

 

名前:group_running_total:
コード:sum(offset_list(${inventory_items.total_cost}, -1 * (row() - ${group_start_row}), row() - ${group_start_row} + 1))

 

上記の方法で簡単な集計が容易くなりますが、MAX()とか、複数の数字を比べるような器用な関数はどうしたらいいのか?となった時は今度はListの関数を使う出番です。

例えば、ミッションは毎日のそれぞれの商品の種類の売り上げを出し、その日のトップアイテムと比べなさいと言われたどうしますか?

日付をGroupにし、それぞれのCategoryの売り上げをList化しMaxをかけるとその日付のMax売り上げが取れます。


この場合は上記で使っていたgroup_start_rownext_group_start_rowを使い、売り上げをリスト化します。

当値から次の値までの、当行の数字を取得。
名前:row_offset_till_group_start:
コード:if(row()=${group_start_row},0,0-1*(${group_start_row}-row()))

Groupの数字を全てリスト化します。
名前:group_list:
コード:offset_list(${order_items.total_sale_price},-${row_offset_till_group_start},${next_group_start_row})

 

最後にgroup_listのMaxやAverage等を取得。
名前:max_in_group:
コード:max(${group_list})

このように、Exploreから複雑かつ、器用な集計ができます!

質問ありましたらぜひコメントで聞いてください!


0 replies

Be the first to reply!

Reply