だれでもわかるデータ分析に必要なSQL（入門編）とTableau（タブロー）

敏也村山

6年前

-PR-

SQL（SELECT）スキルアップ講座リニューアル

だれでもわかるデータ分析に必要なSQL（入門編）とTableau（タブロー）

はじめに

本記事の対象ユーザーについて）

対象ユーザーは、Tableau（タブロー）にはそれなりに馴れてきたが、SQLに不慣れなウェブデータアナリストまたはそれに近しいユーザーを対象としております。

今回のテーマ）

ウェブデータをデータベース上で分析する上で避けて通れない「集計・ウィンドウ関数」系SQLの操作について考えてみたいと思います。初学者が、SQLの原理原則を理解するためにもなるべくオーソドックスな（少し拙い）SQLで整理してみます。なお、SQL自体は、今回、PostgreSQLで検証します。＊データベース環境構築方法は説明いたしません。

対象外のユーザ）

SQLにすでに慣れ親しんだユーザーには、本記事はまったく役に立ちません。ぜひ、読み飛ばしてください。

プロフェッショナルなユーザーは、こちらの書籍の精読をおすすめいたします。

（ビッグデータ分析・活用のためのSQLレシピ）

※ なお、上記書籍はSQLの、特にデータ分析系の「集大成」となります。これからデータ分析をSQLで試してみようとするウェブデータアナリストには一足飛びとなります。ただ、そこに至るまでの過程は一度、試行錯誤する必要があります。

ウェブデータアナリストにSQLは本当に必要か？

問答無用でYesです。

まず、ウェブデータアナリストはアナリティクスツールやマーケティングツールといった、ツールだけにあらず、です。Googleアナリティクス、Adobe Analyticsまたはその他周辺マーケティングツールを単体で扱えれば良い時期はもうまもなく終了いたします。顧客データ、店舗データ、在庫データまたは実際のサーバーログデータなどのオフラインデータを併用できなければ大局的な示唆はなかなか出せません。

そこで、本当に示唆が出せるかどうかを検証するため、コストをかけずに仮の環境をサンプルとしてすぐに構築することが必要となります。大企業でもない限り、その仮の環境構築はウェブデータアナリスト自身で推進することを今後、否が応でも求められるようになります。その場合、自身もしくはせいぜいアシスタントと２人で遂行しなければなりません。そこで役に立つスキル要件の一つがまさに「SQL」です。

こちらの記事の考え方はもちろん理解できます。

しかしながら、私がみてきた現場での数々の経験からいうと、データ分析に明るく、かつ、協力いただけるインフラエンジニア、データベースエンジニアは、大企業でデータ専門部署でもない限り、ほとんどいません。その場合、人材不足を補うために外注するケースがほとんどです。外注に頼る場合、データ分析の知見まで期待することはほぼ無理です。もし、ウェブデータアナリスト自身のオフラインデータに対する表現力があれば、ディレクションできます。オフラインデータを活用するためになんとか協力して推進することができます。「SQL」という共通言語があればさらに心強いです。

ウェブデータアナリストとして自身のやりたいこととゴールを明確にして、主に集計用の中間データベース・テーブルを設計しながら自身で最終的に「SQL」という共通言語で会話できれば、それもやはり賢い選択です。そのような理由からも、エンジニア背景のないウェブデータアナリストもあきらめず、ぜひ、SQLの習得を強く推奨します。

そこで設計したものをもとに、内外のインフラエンジニア・データベースエンジニアに分析環境を実際に構築していただくことがベストプラクティスと考えます。

どの程度のSQLの知識が必要なのか？

最初の段階では、更新系のSQLはまだ気にしなくて良いと思います。

まず、SQLの参照系（SELECT）構文をしっかりと理解しておく必要があります。

その中でも、「集計・ウィンドウ関数」を使った集計系SQLを理解して操作できれば十分に「共通言語」となります。

まずは、そこを理解することからスタートしてください。そのあとは、副問い合わせを理解すると良いでしょう。

Tableau（タブロー）などのBIツールで結果を確認しながらSQLを確認してみよう

初学者がSQLを習得するにあたり、すでにBIツールのスキルがあれば理解は早いです。

例えば、Tableau（タブロー）などで、馴染み深いディメンションやメジャーを使って集計表をいろいろ作ってみます。そこから、まったく同じデータソースを使って、それをSQLで引き直したときにどう書けば良いかを考えると、「SQL」のさまざまなことが見えてきます。すでに正解はTableau（タブロー）などで可視化できておりますので答え合わせも楽だと思います。

本記事では、Tableau（タブロー）を使いがなら、このような、少し変わったアプローチを取りたいと思います。

今回の学習範囲

前提知識を確認する
SQL「句」の評価順序を理解する
Tableau（タブロー）のフィルター評価順序を理解する
集計系関数を使ってできること…
ウィンドウ関数を使ってできること…

前提知識

（１）ディメンションとメジャー（指標・メトリクス）の違い

SQLを理解する上でも、このディメンションとメジャーの違いを理解しておくことはとても有用です。

Tableau（タブロー）などのツールを使い倒している方にはさすがに説明は不要ですが、念のため、おさらいしておきます。

　ディメンション　：切り口・軸または、セグメント

　メジャー　　　　：測れるもの、つまり、数値や率

例）店舗別の売上

　ディメンション　：店舗

　メジャー　　　　：売上

例）店舗別顧客別の売上

　ディメンション　：店舗、顧客（２つ以上のディメンションを複数ディメンションという）

　メジャー　　　　：売上

ポイント）

メジャーは測れるもの。今回は売上が該当します。

つぎに、ディメンションを考えます。〇〇別に該当するのがディメンションとなります。

この〇〇別が複数ある場合を、複数ディメンションと呼びます（店舗別顧客別）。

そして、前方のディメンションからプライマリディメンション、後方のディメンションをセカンダリディメンションと呼びます。また、SQLでこれを実現するには、GROUP BY句を利用します。こちらは後述しますが、「SQL」でも、この識別はとても役立つ概念です。しっかりと理解してください。

（２）SQL「句」との対応関係

Tableau（タブロー）でいうところの、ディメンションフィルターやメジャーフィルターは、SQLでは、一般的に下記のように捉えることができます。

（参考サイト　フィルターと詳細レベルの式）

　ディメンションのフィルター：　WHERE句　に相当

　メジャーのフィルター　　　：　HAVING句　に相当

そのほか、Tableau（タブロー）でよく使う下記の機能は、SQL句では下記に該当します。

　ディメンション指定　　　　　：　GROUP BY句　に相当

　昇順・降順（並び替え）　　　：　ORDER BY句　に相当

　上位N件　　　　　　　　　　：　LIMIT句　に相当

（３）SQL「句」の評価順序

SELECT構文の各句の評価順序を理解することは、SQLを理解するのに欠かせません。

PostgreSQLの場合、下記の通りとなります。

SELECT構文の句の評価順序

参考サイト）PostgreSQL9.6.5文書

WITH句
FROM句
ON / JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

WHERE句とHAVING句の違い

引用　『スッキリわかる SQL 入門ドリル215問付き! (スッキリシリーズ)』P189

評価順序がそもそも違いますが、それよりも大事なことは、絞り込み対象がそもそも違うという点。

WHERE句　　：もとのテーブルに対する絞り込み

HAVING句　：集計結果に対する絞り込み

ここからいえることは、例えば、WHERE句に集計関数は使えない、ということです。

なぜなら、WHERE句が評価されるタイミングでは、集計対象となるグループがまだ生成されておりません。

なお、パフォーマンスを改善するために、HAVING句で指定するディメンションフィルターに関する条件を、WHERE句で先にフィルターをしておけば負荷を抑えることも可能です。

（４）Tableau（タブロー）のフィルター評価順序も理解する

引用　『フィルターと詳細レベルの式』

Tableauのフィルター評価順序もしっかりと抑えておきましょう。

抽出時フィルター
データソースフィルター
コンテキストフィルター
LOD: FIXED
ディメンションフィルター（WHERE句に相当）
LOD: INCLUDE , EXCLUDE
メジャーフィルター（HAVING句に相当）
表計算フィルター

ここからいえることは、前述した通り、評価順序は、

ディメンションフィルター（WHERE句に相当）　→　メジャーフィルター（HAVING句に相当）の順番です。

なお、LOD: FIXEDがディメンションの配置や入れ替えに影響されないのは、フィルターの適用順序を理解できれば納得できると思います。

（５）その他の前提知識

・SQL集計関数を使った基本構文は下記の通り。

　SELECT　GROUP BYで指定したディメンション , 集計関数

　FROM　対象テーブル

　WHERE　ディメンションフィルタ条件（もと表に対する絞り込み）

　GROUP BY　集計単位となるディメンション（複数ディメンションはカンマ区切りでつなぐ）

　HAVING　メジャーフィルター条件（集計結果への絞り込み）

　ORDER BY　ソート条件

　LIMIT　　N件

・SQL集計関数とは　　SUM(), AVG(), MIN(), MAX(), COUNT()のこと

・SQL集計関数は、検索結果に対して利用するもの。

したがってそもそもSELECT文でしか利用できない。

・SQL集計関数は、SELECT文の中でも、列指定部分とHAVING句でのみ利用可能。

WHERE句では前述の通り、SQL集計関数を利用できない。

集計結果に対して絞り込みたい場合は、HAVING句を使う。

・集計関数の引数は、基本、数値型を取る。ただし、MIN(), MAX()または、COUNT()に関しては、文字列型や日付型を取ることができる。

・NULLの取り扱いについて

SUM() ：無視

AVG()　：無視

MIN()　：無視

MAX() ：無視

COUNT()　：列名指定の場合は無視。* 指定の場合はNULLもカウント

・〇〇別あるいは、〇〇別〇〇別の売上など、ディメンション単位（もしくは複数ディメンション単位）で集計したい場合は、GROUP BY句を使う。複数ディメンションは、カンマ区切りでつなぐこと。

PR
弊社オリジナル講座
SQL（SELECT）スキルアップ講座リニューアル

それではさっそく、SQLを学習していきましょう！

前置きが長くなりましたが、それではSQLを学習していきます。

（１）集計関数を全部、試してみる

まずは、Tableau（タブロー）で結果を先に確認しておきます。下記の通りです。

ここでは、テーブル全体の各集計関数の集計結果が表示されております。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

SELECT

　SUM(“Sales”),

　AVG(“Sales”),

　MIN(“Sales”),

　MAX(“Sales”)

FROM “ORDER”

WHERE句、GROUP BY句は特に指定されておりません。

そのため、結果はテーブル全体に対する集計結果となります。

（２）集計関数のCOUNT()を試してみる

ここでは、Customer Nameをカウントしてみます。重複カウントする場合としない場合とでそれぞれ試してみます。

下図の793が、ユニークカウント結果となります。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

通常カウント

SELECT COUNT(“Customer Name”) FROM “ORDER”;

ユニークカウント（DISTINCTを併用）
SELECT COUNT(DISTINCT(“Customer Name”)) FROM “ORDER”;

（３）地域別売上の合計を集計関数で試してみる

ここではじめて、〇〇別が出てきました。

〇〇別に該当するのは、ディメンションです。

今回は、地域別となりますので、SQLでは、GROUP BY句で、地域ディメンションを指定する必要があります。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

SELECT
　”Region”,
　SUM(“Sales”) as sa
FROM “ORDER”
GROUP BY “Region”
ORDER BY sa DESC

地域別の売上を集計しますので、GROUP BYに”Region”を指定します。

また、SELECTの列指定で、GROUP BY句で指定した”Region”を指定する必要があります。

ORDER BY句でソート基準を指定しております。

saとは、SUM(“Sales”)のエイリアスです。

DESCは降順指定です。

（４）地域別カテゴリ別売上の合計を集計関数で試してみる

今回は、〇〇別が複数重なった、複数ディメンションで集計します。

SQLでは、この複数ディメンションを、GROUP BY句でカンマ区切りで指定します。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

SELECT
　”Region”,
　”Category”,
　SUM(“Sales”) as sa
FROM “ORDER”
GROUP BY “Region”,”Category”
ORDER BY “Region” DESC, sa DESC, “Category” DESC

ORDER BY句で、ソート基準を複数指定することが可能です。

（５）ディメンションフィルタ（2015年を指定）

ディメンションフィルタは、WHERE句に相当します。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

SELECT
　date_part(‘year’,”Order Date”) as dp_year,
　date_part(‘month’,”Order Date”) as dp_month,
　SUM(“Sales”)
FROM “ORDER”
WHERE to_char(“Order Date”,’YYYY’) = ‘2015’
GROUP BY dp_year, dp_month
ORDER BY dp_year, dp_month ASC

（６）メジャーフィルター（サブカテゴリ別売上 2000万以上）

メジャーフィルタは、HAVING句に相当します。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

SELECT
　”Sub-Category”,
　SUM(“Sales”) as sa
FROM “ORDER”
GROUP BY “Sub-Category”
HAVING SUM(“Sales”) >= 20000000
ORDER BY sa DESC

（７）ランク（売上）

地域別サブカテゴリ別の売上上位３件をそれぞれ表示しております。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

ポイント）

ランクを作成するには、ウィンドウ関数のRANK()を使用します。

あわせて、OVER句を使って、集計範囲を指定します。

OVER句内のPARTITIONで、実際のウィンドウ範囲を制御します。

OVER句内のORDER BYで処理される行の順序を制御します。

as 01とは、FROM句で利用可能な副問い合わせのテーブル名（任意）を付与しております。

SELECT *
FROM
　(SELECT
　　”Region”,
　　RANK() OVER (PARTITION BY “Region” ORDER BY SUM(“Sales”) DESC) AS RK,
　　”Sub-Category”,
　　SUM(“Sales”)
　　FROM “ORDER”
　　Group by “Region”, “Sub-Category”
　　ORDER BY “Region” Desc, “Sub-Category” asc
　) as o1
WHERE RK <= 3
ORDER BY “Region” desc, RK asc,”Sub-Category”

（８）移動平均

年別月別の売上移動平均を作成します。

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

ポイント）

ここでは、集計関数であるAVG関数を使いますが、OVER句と併用することで、ウィンドウ関数として扱えます。

OVER句内のPARTITIONで、実際のウィンドウ範囲を制御します。

累計処理は、OVER句のPRECEDING AND CURRENT ROW（それ以前の行から現在行まで）　で定義します。BETWEEN 4　は前４つのデータという意味です。

SELECT
　date_part(‘year’,”Order Date”) as year,
　date_part(‘month’,”Order Date”) as month,
　avg(sum(“Sales”))
　over(order by date_part(‘year’,”Order Date”),date_part(‘month’,”Order Date”) asc
　　rows between 5 preceding and current row)
FROM public.order
group by date_part(‘year’,”Order Date”),date_part(‘month’,”Order Date”)
order by year,month

（９）売上累計

それでは、上記の結果をSQLで得るにはどうしたら良いでしょうか？

下記のSQL文で取得可能となります。

ポイント）

ここでも集計関数SUM()とOVER句を併用しております。

ROWS UNBOUNDED PRECEDING　は、前にある行すべてという意味です。

SELECT
　”Category”,
　date_part(‘year’,”Order Date”) AS order_year,
　SUM(SUM(“Sales”))
　OVER (
　　PARTITION BY “Category”
　　ORDER BY “Category”,date_part(‘year’,”Order Date”)
　　ROWS UNBOUNDED PRECEDING
　)
FROM “ORDER”
GROUP BY “Category”,order_year
ORDER BY “Category”,order_year

まとめ）

今回は、Tableau（タブロー）の集計結果をもとに、SQLで集計系・ウィンドウ関数を用いて、SQLで再現することを試してみました。

基本的な構文は決まっておりますので、その中で、集計関数のみで対応できるのか、ウィンドウ関数を用いるのかを判断すればどのパターンでも実現できるはずです。あとは、（７）ランクで使用した、福問い合わせを併用できるようになれば問題ないでしょう。

この基本概念を理解しながら、トレーニングを繰り返すことでSQLを書くスピードや精度はどんどん上がるでしょう。そして、SQLを使ってオフラインデータを含めた環境構築と実装イメージがどんどん湧いてくると思います。

これを機に、ぜひ、SQLをものにしてください。そして、ツールでしか語れないウェブデータアナリストからオフライン環境実装までイメージできる人材をめざして頑張って下さい。

-PR-

だれでもわかるデータ分析に必要なSQL（入門編）とTableau（タブロー）

はじめに

本記事の対象ユーザーについて）

今回のテーマ）

対象外のユーザ）

ウェブデータアナリストにSQLは本当に必要か？

どの程度のSQLの知識が必要なのか？

Tableau（タブロー）などのBIツールで結果を確認しながらSQLを確認してみよう

今回の学習範囲

前提知識

（１） ディメンションとメジャー（指標・メトリクス）の違い

（２）SQL「句」との対応関係

（３）SQL「句」の評価順序

SELECT構文の句の評価順序

WHERE句とHAVING句の違い

（４）Tableau（タブロー）のフィルター評価順序も理解する

（５）その他の前提知識

それではさっそく、SQLを学習していきましょう！

（１）集計関数を全部、試してみる

（２）集計関数のCOUNT()を試してみる

（３）地域別売上の合計を集計関数で試してみる

（４）地域別カテゴリ別売上の合計を集計関数で試してみる

（５）ディメンションフィルタ（2015年を指定）

（６）メジャーフィルター（サブカテゴリ別売上 2000万以上）

（７）ランク（売上）

（８）移動平均

（９）売上累計

まとめ）

BigQueryで学ぶSQL

共有:

（１）ディメンションとメジャー（指標・メトリクス）の違い