TableauのカスタムSQLでデータを取得する際、WITH句を使ったSQLだとエラーが出てしまいます。
カスタムSQLではWITH句のような共通テーブル式(CTE)はサポートしていません。
代わりに「初期SQL」で一時テーブルを作成し、カスタムSQLで作成した一時テーブルを参照することができます。
このやり方はTableau公式にも紹介されていますが、今回は実際に画面で試した結果をお見せしたいと思います。
DBはSQL Serverを使用しています。
サンプルSQL
こちらが元となるSQLです。これをそのままカスタムSQLに設定しても最初にお見せしたエラーが出ます。
WITH basedata AS (
SELECT
YEAR([年月日]) AS [年],
[都道府県],
[売上]
FROM [test].[SalesData]
WHERE [年月日] >= '2016-01-01'
)
SELECT
t.[年],
t.[都道府県],
t.[売上],
a.[全国売上]
FROM ( --都道府県別年間売上
SELECT
[年],
[都道府県],
SUM([売上]) AS [売上]
FROM basedata
GROUP BY [年], [都道府県]
) t
LEFT JOIN ( --全国年間売上
SELECT
[年],
SUM([売上]) AS [全国売上]
FROM basedata
GROUP BY [年]
) a
ON t.[年] = a.[年]
ORDER BY [年], [都道府県]
basedataに必要なデータのみ抽出し、basedataから都道府県別集計と全国集計を求めJoinしています。
初期SQLの設定
初期SQLをクリックします。
初期SQLでローカル一時テーブルを作成します。
WITH句はそのままで、クエリの最後でSELECT INTO [一時テーブル名] を使用してローカル一時テーブルを作成します。
ローカル一時テーブルはテーブル名の先頭に#をつけます。
SELECT * INTO #basedata from basedata
今回は#basedataという名称にしました。
設定ができたらOKを押下します。何も起きていないように見えますが、一時テーブル#basedataが作成されています。
カスタムSQLの設定
カスタムSQLで、初期SQLで作成した一時テーブル#basedataに対してSELECTをかけます。
OKを押下します。エラーが出ることなく、カスタムSQLの設定ができました。
結果の確認
「今すぐ更新」をするとエラーが出ることなくデータが取れました！！
今回はシンプルなSQLだったので、WITH句をわざわざ使わなくても、サブクエリを書けばすむと思いますが、
実際の業務ではもっと複雑なSQLになる場合も多いと思います。
このような場合にWITHを使うと可読性が上がると思います。
また今回の例では、サブクエリの結果を使いまわしており、そのような場合にもWITH句は有効かと思います。
（使えない場合は何度も同じことを書かなくてはなりません。）
共通テーブル式をどうしても使いたい！という方は初期SQLとカスタムSQLの合わせ技でお試しください！
