TableauのLOD表現をSQLで理解する

最近再びTableauを使い始めました。
Tableauを使うのは2年ぶりでしたが、操作方法の大枠は変わらないのでブランクがあっても難なく利用できています。
※テーブルの結合周りでやや仕様が変わっていて、JOINしたと見せかけてリレーションになっていたことがありましたが…。

さて、だいぶ昔にTableauでのアドホック分析をSQLと対応づけて説明する記事を書きましたが、今回はもう一歩踏み込んで、LOD表現とSQLとの対応を説明してみます。

キヨシの命題

 

1 Pocket

Tableauで始めるアドホック分析~SQLと関連づけてTableauの仕組みを理解する

https://yolo-kiyoshi.com/2018/12/09/post-923

今回はTableauの仕組みを理解する上で本質的と思われる項目に焦点を当て、SQLと対比しながらTableauの仕組みを説明していきます。ちなみに筆者はTableau歴1年で、Jedi(ジェダイ)には及ばないものの不自由なくTableauを使いこなせるようになりました。(ダッシュボード関連では細かいバグを見つけたりもしています)しかし、Tableauを使い始めたころは仕組みを理解せずになんとなーく使っていました。そのため複数条件を掛け合わせた集計だったり、粒度が異なるディメンションを集計する際はかなり四苦八苦した覚えがあります。ですが、そ...

TableauでのLOD表現を理解するために重要なことはこちらで説明されている通り、FIXED、INCLUDE、EXCLUDEともに中間テーブルを作成して粒度が異なる集計をしていると考えることです。

エクスチュア株式会社ブログ

 

4 Users

35 Pockets

Tableau：分かりやすいLOD-FIXED編

https://ex-ture.com/blog/2017/12/04/分かりやすいlod-fixed編/

こんにちは、エクスチュアの渡部です。 LODブログ第二弾です。 今回は、FIXED関数について説明します。 前 …

今回はFIXEDのみを説明しますが、FIXEDの仕組みを理解できればINCLUDEもEXCLUDEも理解できます。
※FIXEDとINCLUDE、EXCLUDEの違いはビュー内に存在しているディメンションを対象にするか否かだけなので。

準備

今回は無料で利用できるTableau Publicで説明していきます。
集計対象のデータは、Tableau Publicでサンプルデータとして紹介されている、「Airbnbのニューヨーク市におけるlistingデータセット」とします。

通常の集計とSQLの関係(復習)

まず、通常の集計とSQLとの関係を確認します。
「Neighbourhood」、「Property_Name」、「Room_Type」の3つのディメンションごとのPriceの平均値を集計したい場合、Tableauでは以下のように操作します。

この集計をSQLと対応させると以下のようになります。

-- 通常
select
	Neighbourhood
	,Property_Name
	,Room_Type
	,avg(Price)
from
	AirBnB_NYC
group by
	Neighbourhood
	,Property_Name
	,Room_Type

ディメンションでgroup byして、メジャーを集計関数の引数にするというのが通常の集計とSQLの対応になります。

FIXEDとSQLの関係

しかし、FIXEDを使った場合は上で説明した「ディメンションでgroup byして、メジャーを集計関数の引数にする」SQLとは対応しません。
これは、冒頭で説明したようにFIXEDは中間テーブルを作成し、粒度が異なる集計結果を出力する関数だからです。

以降、FIXEDとSQLの対応を説明していきますが、まず次の計算フィールドを作成します。

{ FIXED [Neighbourhood]: AVG([Price]) }

このFIXEDで指定したディメンション「Neighbourhood」がビューに存在するか否かでSQLとの対応付けが異なります。
それぞれ見ていきます。

ビューにFIXEDで指定したディメンションが存在する場合

まず、「Neighbourhood」、「Property_Name」、「Room_Type」の3つをディメンションに設定して、Priceの平均値と作成した計算フィールドの平均値を集計します。

一番右の作成した計算フィールドの平均値を見てみると、同じ「Neighbourhood」であれば値も同じになっていることが分かります。
これは、計算フィールドでFIXEDに「Neighbourhood」を指定したためです。
他のディメンションは考慮せずに「Neighbourhood」だけをFIXED(固定)して集計するというのがFIXED関数なのです。

上記のFIXED関数による集計をSQLで対応させるならば、「Neighbourhood」でgroup byした中間テーブルをビューに結合していると言えます。
以下がSQLで対応させた例になります。

-- fixed(中間テーブルを作るようなもの)
select
	origin.Neighbourhood
	,origin.Property_Name
	,origin.Room_Type
	,origin.Price_avg
	,fixed.Price_fixed_avg
from
	-- 通常の集計
	(
	select
		Neighbourhood
		,Property_Name
		,Room_Type
		,avg(Price) as Price_avg
	from
		AirBnB_NYC
	group by
		Neighbourhood
		,Property_Name
		,Room_Type
	) origin
	-- FIXEDとSQLとの対応
	-- FIXEDで指定したディメンションで集計した結果を結合
	join
		(
		select
			Neighbourhood
			,avg(Price) as Price_fixed_avg
		from
			AirBnB_NYC
		group by
			Neighbourhood
		) fixed
		on origin.Neighbourhood = fixed.Neighbourhood

ビューにFIXEDで指定したディメンションが存在しない場合

最後に、FIXEDで指定した「Neighbourhood」がビューに存在しない場合を説明します。
「Property_Name」、「Room_Type」の2つをディメンションに設定して、Priceの平均値と作成した計算フィールドの平均値を集計します。

FIXEDの場合、ビューに存在しないディメンションを指定しても集計結果が反映されます。
※これがINCLUDE、EXCLUDEとの違いになります。
上記のFIXED関数による集計をSQLで対応させると以下のようになります。
ビューでは「Neighbourhood」ディメンションが存在しないためにFIXED関数が集計結果に与える影響がわかりませんが、SQLで対応付けて考えるとその仕組みが良くわかります。

-- fixedで指定したディメンションがフィールドにない場合
select
	origin.Property_Name
	,origin.Room_Type
	,origin.Price_avg
	,fixed.Price_fixed_avg
from
	-- 通常の集計
	(
	select
		Property_Name
		,Room_Type
		,avg(Price) as Price_avg
	from
		AirBnB_NYC
	group by
		Property_Name
		,Room_Type
	) origin
	-- FIXEDとSQLとの対応
	-- FIXEDで指定したディメンションで集計した結果を結合
	join
		-- FIXEDで指定したディメンションの集計とビューのディメンションと揃える
		(
		select
			fixed_tmp1.Property_Name
			,fixed_tmp1.Room_Type
			,avg(fixed_tmp2.Price_fixed_avg) as Price_fixed_avg
		from
			-- ビューのディメンション
			(
			select
				Property_Name
				,Room_Type
				,Neighbourhood
			from
				AirBnB_NYC
			group by
				Property_Name
				,Room_Type
				,Neighbourhood
			) fixed_tmp1
			join
				(
				select
					Neighbourhood
					,avg(Price) as Price_fixed_avg
				from
					AirBnB_NYC
				group by
					Neighbourhood
				) fixed_tmp2
				on fixed_tmp1.Neighbourhood = fixed_tmp2.Neighbourhood
		group by
			fixed_tmp1.Property_Name
			,fixed_tmp1.Room_Type
		) fixed
		on
			origin.Property_Name = fixed.Property_Name
			and origin.Room_Type= fixed.Room_Type

少々長くなってしまいましたが、結局はFIXEDで指定した「Neighbourhood」でgroup byした中間テーブルをビューに結合しているだけです。

最後に

SQLに慣れている方であれば、本記事のようにSQLとTableauの操作を対応付けることで、より深く、素早くTableauを理解できる気がします。
また、集計関数の中でも特に「AVG」は集計対象のレコードがどういう構造になっているのかを理解せずに使うと誤った結果を招くので注意が必要です。