がねこさん（@ritz_Tableau）の記事Tableau 表計算 TOTAL関数と、合計表示機能についてをリリース前に査読させていただく機会がありました。
確かにTOTALは表計算の中でも特殊というか、シート上の集計値を無視した集計値（後述します）を取得する挙動をしているように見えます。 この点をちゃんと理解するために、査読に際してPerformance Recordingの確認やSQLでの対応クエリ記述を実施して検証していました。その辺りの話を書いていきます。
今回の記事はSQLを多用します。文字が多く並びますがご了承下さい。
TOTAL関数の概要やWINDOW関数との違いについては、是非がねこさんの記事をご参照ください。
というわけで、一緒にTOTAL関数のベールをはがしていきましょう。
検証環境/道具について
SQLを書きながら内容を検証していきたいので、今回はGoogle BigQueryサンドボックス（要は無料枠）を使用していきます。
自分のサンドボックス内プロジェクトにTableau 2021.1版のSample Super StoreのOrdersデータを格納してあります。
今回はこちらのテーブルにTabelau DesktopからLIVE接続します。
またBQに流れるクエリの確認のため、Tableau DesktopのPerformance Recording機能を使用します。
またクエリを見るうえで不必要な項目を無くしておきたいので、今回は以下のカスタムSQLを使用します。
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`最初に：TOTALと総計/小計について
まず今回扱うTOTALと総計の振る舞いについて簡単に説明します。
以下はRegionとCategoryの合計Salesについて、総計(Grand Total)とRegion小計(Total)を表示したものです。
よくある普通の総計の使い方ですね。
ところでこの総計に対応する表計算関数があり、それがTOTAL関数です。
TOTAL(SUM(Sales))
TOTAL(SUM([Sales]))
上図はTOTALを使用した計算式を、CategoryのみまたはRegionとCategory両方を使用して計算した結果です。
２列目は各Regionの小計、３列目は総計に対応した値を返していることが分かります。
基本的に総計/小計は、このTOTAL関数を利用しているものと考えて下さい。
（「基本的に」と言いましたが、この点についてはがねこさんの記事の結論まわりを確認ください）
今回はこのTOTAL関数に焦点を当てます。
TOTAL関数について
さて、もう少しTOTAL関数について見てみましょう。
次はAVG(Sales)を使用した時を見てみます。
TOTAL(AVG(Sales))
TOTAL(AVG([Sales]))
AVG(Sales)は各Viz-LODの、つまりRegionとCategoryの各組合せの中にあるデータについて、Salesの平均値を取得しています。
TOTALに関する本題に入る前に、少し総計と小計の値を見ておきましょう。
例えばRegion = Centralのデータについて、各AVG(Sales)を足して平均を取る：(340.5 + 117.5 + 405.8)/3は約287.9ですが、これはそのRegionの小計：215.8と一致していません。
これは、この小計はそのディメンジョン以下のマークにおけるAVG(Sales)の平均値をとっているわけではなく、Region = Centralに含まれるすべてのSalesの平均値をとった値が小計に使用されているからです。
要はRegion小計は以下と一致します。
総計も同様に、Viz-LODを無視してデータを集計した時のSalesの平均値を示しています。
ちなみに各マークのAVG(Sales)の平均値を取得したい場合、WINDOW_AVG()を使用します。
参考のために載せておきますね。上述の通り、小計と値が異なることに注意してください。
WINDOW_AVG(AVG(Sales))
WINDOW_AVG(AVG([Sales]))
前置きが長くなりましたが、ここから本題です。
先ほどから「総計/小計とTOTALは対応している」ことをお話してきましたが、
Categoryに沿ったTOTALは、これはRegion小計と一致しているので、どうやら「それぞれのRegionについて、Categoryを無視してデータを集計し、Salesの平均値を出している」ように見えます。
同様に「CategoryとRegionに沿ったTOTALは、総計と一致するので、全ディメンションを無視してSalesの平均値を出している」ように見えます。
ところでTOTAL関数は表計算関数でした。
表計算関数は基本的に「Tableauが取得した（Viz-LODで集計される）集計データについて、更に集計を行う関数」です。
見かけ上たしかにそう見えるものの、表計算関数でありながら「TOTAL関数は集計データを無視する実行のされ方をする」という理解は正しいのでしょうか？
本記事はこの点についてPerformance Recordで分かる実行クエリを見て、また対応するSQLを書いて検証してみます。
検証
総計/小計とTOTAL両方を走らせる代わりに、以下ではTOTALのみ使用した、３つのシートを使用します。
（本当は全集計関数について行っても良いのですが、今回はSUM, AVG, COUNTDについて実施します）
それぞれについてPerformance Recordを実施し、実行クエリを確認します。
まずはSUM(Sales)についての結果です。
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`,
SUM(`Custom SQL Query`.`Sales`) AS `TEMP_TC___3018240649__0_`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,2まあシンプルにSUM(Sales)だけ取得し、TOTALの表計算はTableau側で単に足し合わせていることが想像できます。
次にAVG(Sales)についての結果です。
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`,
COUNT(`Custom SQL Query`.`Sales`) AS `TEMP_TEMP_TC___2215921567__0___2199760863__0_`,
SUM(`Custom SQL Query`.`Sales`) AS `TEMP_TEMP_TC___2215921567__0___3018240649__0_`,
AVG(`Custom SQL Query`.`Sales`) AS `avg_Sales_ok`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,2こちらが気になるのが、ビュー中で使われていないCOUNT(Sales)とSUM(Sales)が取得されていることが分かります。
最後にCOUNTD(Sales)についての結果です。
こちらは面白いことに、３つのクエリが流れました。
--Query 1
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`,
COUNT(DISTINCT `Custom SQL Query`.`Sales`) AS `TEMP_TC___1096538070__0_`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,2--Query 2
SELECT
`Custom SQL Query`.`Region` AS `Region`,
COUNT(DISTINCT `Custom SQL Query`.`Sales`) AS `TEMP_TC___1096538070__0_`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1--Query 3
SELECT
COUNT(DISTINCT `Custom SQL Query`.`Sales`) AS `TEMP_TC___1096538070__0_`,
1.1000000000000001 AS `X__alias__0`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 2Query1はCategory、RegionごとのCOUNTD(Sales)を、
Query2はRegionごとのCOUNTD(Sales)を、
Query3はデータ全体のCOUNTD(Sales)を取得しています。
まずここまでで「表計算はビュー上の集計値に対して実行される」という基本性質について、TOTALはCOUNTD()については明らかにその性質を満たしていません。
それぞれの粒度についてCOUNTD走らせて、それをTableau内で最終的に結合しているように見えます。
一方でSUMとAVGについてはどうでしょうか。
SUMについてのTOTALは、SUM(Sales)をViz-LODに沿って取得し、その後足し合わせる動作になることが容易に予想できるので、これは表計算の性質を満たしています。
AVGについては、COUNTとSUMがなぜ取得されているかについては後述しますが、こちらの集計値を元にしてTOTALを計算していそうです。
少なくともViz-LODを無視したクエリが（COUNTDと異なり）走っていません。
ということで、TOTALは厳密には表計算でないケースがあるのでは・・・？という思いを持ちつつ、AVGのケースについてもう少し見てみます。
TOTAL(AVG)について
さて、TOTAL(AVG)に関して掘り下げるわけですが、TableauがGoogle BQに流したクエリをもう一度見てみましょう。
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`,
COUNT(`Custom SQL Query`.`Sales`) AS `TEMP_TEMP_TC___2215921567__0___2199760863__0_`,
SUM(`Custom SQL Query`.`Sales`) AS `TEMP_TEMP_TC___2215921567__0___3018240649__0_`,
AVG(`Custom SQL Query`.`Sales`) AS `avg_Sales_ok`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,2「Viz-LODを無視したAVGを取る」という動作は、SQL的には以下のような記述となるはずです。
SELECT DISTINCT
Region
, Category
, AVG(Sales) OVER(partition by Region) AS total_avg_by_region
, AVG(Sales) OVER() AS total_avg
FROM `my-first-project-309112.sample_super_store.sample_super_store_2021_1` 実行結果を見ると、先ほどのビューと一致します。
一方で、このようなクエリはPerformance Recordでは確認できませんでした。
したがって別のアプローチで、先ほどのCOUNT(Sales)とSUM(Sales)から平均値を算出していることが分かります。
そしてどういうクエリが流れていそうかと言うと…VizQLで動いているので厳密に同じではないと思いますが、以下のような形でTOTAL(AVG)を求めているのではと思います。
WITH agg_data AS ( --output from DB
SELECT
Region
, Category
, AVG(Sales) AS avg_sales
, COUNT(Sales) AS ct_sales
, SUM(Sales) AS sum_sales
FROM `my-first-project-309112.sample_super_store.sample_super_store_2021_1`
GROUP BY 1,2
)
SELECT
Region
, Category
, avg_sales
, SUM(sum_sales) OVER(PARTITION BY Region)
/ SUM(ct_sales) OVER(PARTITION BY Region) AS total_avg_by_region
, SUM(sum_sales) OVER() / SUM(ct_sales) OVER() AS total_avg
FROM agg_data実行結果は以下です。
確かにTableauのビューと一致しつつ、Viz-LODによる集計値を元にしてTOTAL(AVG)が計算できています。
ということで、TOTAL(AVG)も表計算らしく集計値を元にしつつ「疑似的に」「ディメンションを無視した集計値」を表示させていることが分かります。
ここまでのまとめ
TOTALって表計算にしては表計算っぽくない挙動するよね、というのが最初の疑問だったわけですが、実際表計算っぽく動く場合とそうでない場合が混在しているようです。
特にTOTAL(COUNTD)は、TOTALを求める数だけクエリ走らせるようなので、中々パフォーマンスへの懸念を与えてくれますね。
一方で今回試さなかったMIN/MAXはViz-LODにおける集計値から、さらに最小／最大を抜き出すだけなので、もちろんTOTALは表計算的にふるまいます。
ちょっと気になるのは標準偏差や分散ですが…Performance Record見た限りCOUNTDのように複数クエリ投げていなかったので、AVGのようにうまくやる方法があるのでしょう。
ちなみにMEDIANとPERCENTILEはライブ接続で使えないケースがあるので今回は未検証です。
ここまでで今回の大筋は終わりです。
以降、少し細かい検証をしていきます。
LOD計算とTOTAL
LOD計算を使用した時のTOTALの挙動について確認します。
面白いことに、LOD計算を挟むと総計/小計はWINDOW_xxx関数の結果と一致しているように見えます。
これがクエリ的にどうなっているか見てみます。
SELECT
`t0`.`Category` AS `Category`,
`t0`.`Region` AS `Region`,
AVG(`t0`.`X_measure__3`) AS `TEMP_TC___1538059710__0_`,
COUNT(`t0`.`X_measure__3`) AS `cnt_Calculation_711005831878778880_ok`,
SUM(`t0`.`X_measure__3`) AS `sum_Calculation_711005831878778880_ok`
FROM (
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`,
SUM(`Custom SQL Query`.`Sales`) AS `X_measure__3`,
`Custom SQL Query`.`Segment` AS `Segment`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,2,4
) `t0`
GROUP BY 1,2それなりにサブクエリが入っていますが、要はTOTAL用のCOUNTとSUMが、LOD計算によって作成される中間テーブルに対して取得されているようです。
今回は{INCLUDE [Segment]: SUM(Sales)}を使用しているので、まずCategory, Region, Segmentでの集計テーブルが作成され、それに対してCOUNTとSUMを実施しています。
したがって非LOD計算とLOD計算ではTOTALが走る対象が異なることが分かります。特にCOUNTの結果が異なってきますね。
ところで今回の例では偶然にもWINDOW_AVGの結果とTOTALの結果が一致しましたが、これは偶然「Category, Region, Segmentの集計テーブルのレコード件数が一致しているから」であり、一般的に最終的な集計値の平均を取るWINDOW_xxx関数とTOTALの結果が一致するわけではないことにご留意ください。
TOTAL(COUNTD)のメジャーフィルター
ところでTOTAL(COUNTD)はViz-LODによる集計値を使わず、別のクエリを投げていることが分かりました。
この場合、集計値に関するフィルターであるメジャーフィルターがどのように適用されるか、ちょっとイメージがつきにくいところがあるのではと。
ということで、以下のシートによるクエリを見てみましょう。
SELECT
COUNT(DISTINCT `Custom SQL Query`.`Sales`) AS `TEMP_TC___1096538070__0_`,
1.1000000000000001 AS `X__alias__0`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
INNER JOIN (
SELECT
`Custom SQL Query`.`Category` AS `Category`,
`Custom SQL Query`.`Region` AS `Region`
FROM (
SELECT
Row_ID
,Segment
,Region
,Category
,Sales
FROM `my-first-project-309112.sample_super_store`.`sample_super_store_2021_1`
) `Custom SQL Query`
GROUP BY 1,
2
HAVING (COUNT(DISTINCT `Custom SQL Query`.`Sales`) >= 300)
) `t0`
ON ((IFNULL(`Custom SQL Query`.`Category`, '') = IFNULL(`t0`.`Category`, ''))
AND (IF(`Custom SQL Query`.`Category` IS NULL, 0, 1) = IF(`t0`.`Category` IS NULL, 0, 1))
AND (IFNULL(`Custom SQL Query`.`Region`, '') = IFNULL(`t0`.`Region`, ''))
AND (IF(`Custom SQL Query`.`Region` IS NULL, 0, 1) = IF(`t0`.`Region` IS NULL, 0, 1)))
GROUP BY 2赤字でハイライトしましたが、元データにINNER JOINで「メジャーフィルターの条件を満たすディメンションの値」を結合し、データをフィルターしていることが分かります。
ただでさえ面倒なTOTAL(DISTINCT)に、更にサブクエリが増えるわけですね。
結構な力業に見えますが、こういうクエリが走りパフォーマンスの影響がありそうですね、ということで。
最後に
冒頭のがねこさんの記事に刺激を受け、細かく調べて書いてみました。
TOTAL関数だけ今まで何となく良く分からなかったと言いますか、正直WINDOW_xxx関数を意図的に使うようにしていたので、今回色々と調べる切欠を頂きました。ありがとうございます。
先日こういうマニアックなことに時間を割けなくなってきているなと悩んでいましたが、色々と捨てて全集中でこういうことをするのは気持ちが良いですね。幸福度が爆上がりしました。
ご質問等はTwitterかLinkedinに頂ければと思います。それでは。
Comments