🦮

【Tableau】LOD計算を使った集計をSQLに落とし込んでみる

2024/02/05に公開

Tableau

tech

FIXEDやINCLUDEの理解を深めるためにTableauで集計した結果をSQLに落とし込んでみます。

使用したデータ

データは以下のリンクから取得したSample - Superstore.xlsのOrdersシートです。

同様のデータにSQLを発行するため、xlsファイルからエクスポートしたCSVをPostgreSQLにインポートしています。
PostgreSQLのバージョンはPostgreSQL 16.1です。

INCLUDEを使用したカテゴリ別平均顧客毎注文回数の集計

Tableauで顧客毎の注文回数を計算する場合、以下の数式を使います。

{INCLUDE [Customer ID]:COUNTD([Order ID])}

カテゴリ別にこれの平均を出したい場合、右クリックメニューから[メジャー] - [平均]を選択します。

この計算をSQLで書くと以下になります。

WITH order_counts AS (
	SELECT 
		category, -- 行で指定
		customer_id, -- INCLUDEで指定

		COUNT(DISTINCT order_id) AS order_count
	FROM orders
	GROUP BY category, customer_id
) 
SELECT 
    category,
    AVG(order_count) AS avg_orders_per_customer
FROM order_counts
GROUP BY category;

INCLUDEをFIXEDに変えてみる

INCLUDEとFIXEDの違いを理解するためにINCLUDEをFIXEDに変えてみたいと思います。

{FIXED [Customer ID]:COUNTD([Order ID])}

この計算と同じ結果をSQLで書くとつぎのようになります。

WITH order_counts AS (
	SELECT 
		customer_id,
		COUNT(DISTINCT order_id) AS order_count
	FROM orders
	GROUP BY customer_id
)
,orders_per_cat_cust AS (
	SELECT
		DISTINCT
		category
		,ordrs.customer_id
		,oc.order_count
	FROM orders AS ordrs
		INNER JOIN order_counts AS oc
			ON ordrs.customer_id = oc.customer_id
) 
SELECT
    category
    ,AVG(order_count) AS avg_orders_per_customer
FROM orders_per_cat_cust
GROUP BY category;

customer_id毎にorder_idの数を重複を除去してカウントした上で、カテゴリごとにAVGをとっているので、概ねこのような形になることは理解できますが、orders_per_cat_cust内でのDISTINCT指定になっているところが直感的には理解しにくいところです。

ここからTableauでAVG{FIXED [Customer ID]:COUNTD([Order ID])}と記述したとき、内部的にどのような順序で処理をしているか推測してみます。

[Customer ID]毎に[Order ID]の数を重複を除外して集計した結果を列として元の表に加える
Category毎のAVGを計算するのに必要な列だけを抽出し、重複を省く
Category毎のAVGを計算する

SQLで書くとかなり長くなる処理がTableauだと数ステップで済む

SQLで書くとかなり長くなる処理がTableauだとたった1行の記述と数ステップのドラッグ＆ドロップで実現できてしまうことが改めて実感できました。これは探索的に分析をしているシーンでは非常に強力なメリットです。

一方で、Tableauが内部的にどんな処理をしているかについては直感的には把握しづらい部分があり、Tableauの挙動が意図したものになっているか都度確認する必要があると感じました。