SQL データを学ぶ

【初心者向けSQL】中間テーブルを使ってみよう【CREATE TABLE/WITH句/副参照 】

この記事では、下記の部分まで理解することを目指しましょう。

この記事の目標

  • 中間テーブルの概念を理解する
  • 中間テーブルの作り方(CREATE TABLE / WITH句 / 副参照)を覚える
  • 状況によって作り方の使い分けができるようになる

一度で集計できないような複雑な集計をお願いされたの!

一発で集計できるようなタスクは
データアナリストにはあまりこないよ笑
中間テーブルというものを作って、セーブポイントを
作りながら書くんだ。
今日はその作り方を解説するね。

ahilog

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

このシリーズの記事はこちらです。

中間テーブルの概念を理解しよう

中間テーブルというのは、最終的に作りたいデータを作るための
セーブポイントのようなもの
です。

今あるデータから一撃で求めたデータ構造を作れることは稀で
一度途中まで集計したパーツを作って
そのパーツを組み合わせたり、再度集計する というステップを踏みます。

中間テーブルの作り方には大きく2パターンあります。

ゆっくり見ていきましょう。

パターン1:本当に作ってしまう(CREATE TABLE)

DWHによって微妙に書き方が異なるのですが、
CRATE TABLEというフレーズを使って、実際にテーブルを作り保存するやり方があります。

前回のJOINの説明の時に、
ahilogさんが使っていたやつだね!

その通り!よく覚えていたね。
CREATE TABLE をSELECT前に書くことで、
その集計結果にテーブル名をつけて保存することができるんだ。

ahilog

CREATE TABLEには以下の3パターンの書き方があります。

書き方 意味
CREATE TABLE IF NOT EXISTS 【テーブル名】AS その【テーブル名】が存在しない場合に新たに作成
CREATE OR REPLACE TABLE 【テーブル名】AS 【テーブル名】が存在していれば上書き・していなければ新たに作成

実際にCREATE TABLEで作ってしまうことには
以下のメリット・デメリットがあるので覚えておきましょう。

  • メリット:テーブルがすでにあるので他のメンバーと共有・編集できる
  • デメリット:無駄にテーブルを量産すると容量を圧迫する / DWHが散らかる

DWHはお家で言えば共有スペースにあたります。

自分の都合で勝手に家具を増やしたり、散らかしたら怒られるのは当然ですよね。
テーブルの作成はチームのルールを遵守してやりましょう。

ahilog

命名規則をつける(日付+作成者名+テーブル名)や
フォルダ分けなど、ローカルルールがあるはずです。

例題

下記会員データをgold_membersというテーブル名で保存するケースを考えましょう。

顧客ID顧客氏名年齢会員種別
A山田くん30ゴールド
B佐藤さん44シルバー
C高橋さん28ブロンズ
仮にこのテーブル名を「user_info」としましょう。

CREATE TABLE IF NOT EXISTS

こちらのパターンはテーブルが存在しない場合にのみ
新しくテーブルを作ってくれるので、間違って上書きするのを防止できます。

ahilog

すでに他のメンバーが作ったテーブルを書き換えたら
大惨事ですからね。。。

こちらで書く場合は、gold_membersという名称のテーブルがすでにある場合
ちゃんとストップがかかるようになります。

実際に書くとこんな感じです。先頭に書くだけです。

CREATE TABLE IF NOT EXISTS gold_members AS
select
*
from
user_info
where
会員種別 = "ゴールド"

CREATE OR REPLACE TABLE

こちらのパターンは、既存のテーブルがあっても上書きされます。

自分で作っている中間テーブルにバグがあって
それを修正したい時などはこちらを使っています。

ahilog

SQLを書き間違えるたびに、table_1 table_2・・・と
増えていったらテーブルが増えすぎてぐちゃぐちゃになります。

実際に書くとこんな感じになります。

CREATE OR REPLACE TABLE gold_members AS
select
*
from
user_info
where
会員種別 = "ゴールド"

CREATE OR REPLACE TABLEでSQLを回すと、
もともとあったテーブルが書き換わってしまいます!
注意して使うようにしましょうね。

パターン2:SQL內に仮で作成する

2つ目の方法はSQL內部に仮のセーブポイントを作る方法です。

こちらのパターンは実際に中間テーブルが作られることは有りません。
あくまでそのテーブル名で一時保存できるだけです。

SQL內で仮の中間テーブルを作る方法でよく使われるのが
WITH句と副参照です。

WITH句のほうがわかりやすいので、こちらから說明しますね。

WITH句の使い方

WITH句の使い方はこのような感じになります。

with 〇〇 as () で、()内の集計結果を〇〇という名称で一時保存できます。
※有効期限はそのSQLが完了するまでです。

with gold_members AS (
select
*
from
user_info
where
会員種別 = "ゴールド"
)

ちなみにこちらのクエリを回すとエラーになります。

SQLくん

中間テーブル作って結局何がしたかったん?
出力するもんがないからエラーにしたで!

with句で定義したものは
そのテーブル名で一時保存してほしい という命令でしかないので
その後にちゃんと出力したい集計を書きましょう。

実際に書くとこんな感じです。

with gold_members AS (
select
*
from
user_info
where
会員種別 = "ゴールド"
)

select
*
from
gold_members
SQLくん

gold_membersという中間テーブルを作って
それを全行表示してほしいんやな!
お茶の子さいさいやで!

ahilog

このSQLだと中間テーブルの内容を出力しているだけなので
あまり意味がありません笑
実際はこの次にgold_membersをもう一発別の集計加工をすることで
複雑な課題に対応しています。

副参照の使い方

個人的には推奨しない書き方ですが、副参照という書き方があります。

副参照というのは、本来テーブル名を書く部分(FROMやJOINの後)に
さらにSQLを重ねて書く方法
です。

見たほうがわかりやすいので、下記SQLを見てください。

select
*
from (
select
*
from
user_info
where
会員種別 = "ゴールド"
)

FROMの後に()を書くことで、その中に別のSQLを書くことが出来ます。
()內の集計結果を使って、別の集計をする時に使います。

なんだか読みにくいような・・・

副参照を乱用しすぎると、本当に読みにくいコードになってしまうんだ。。
他の人と共有するときはあまりオススメできない書き方かな。
ざっと書くときには便利なんだけどね。

ahilog

まとめ:中間テーブルの作り方と使い分けについて

以上のように、中間テーブルには2種類の作り方があり、
使い分けが重要になってきます。

もう一度下記の通りまとめておきますね。

  • 実際にテーブルを保存して活用:CREATE TABLE構文(保存でき他のメンバーと共有できるが、乱用すると散らかる)
  • SQL内に仮で作って再利用:WITH句もしくは副参照(その集計限りで使える構文。クエリを読みやすくするために使い分けよう)

中間テーブルが使えるようになると
一気に複雑な集計ができるようになってきます!

是非マスターしてみてください!

BigQueryでやってみよう

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが
本記事ではBigQuery環境を想定して執筆します。

使用するデータについて

BigQueryにtitanicのデータを格納しており、
こちらをもとに分析します。

データはtitanic_sample というフォルダの中に
titanic_logという名前で格納してあります。

ahilog

この場合、FROM titanic_sample.titanic_log と「.」をつけて
フォルダ名を書く必要があるので注意しましょう。
大抵の場合、データは何かしらのフォルダにまとめて置かれています。

乗船者の家族の合計人数が4人以上のデータのみをtitanic_logから抜き出してみよう

今回は前回のJOINの回と同じ問題を解いてみましょう。
CREATE TABLEを使った方法は前回の記事でやっているので除外します。

前回の記事のリンクを再掲しておきますね。

参考【初心者向けSQL】テーブル同士を結合しよう【JOIN / LEFT JOIN】

続きを見る

今回はSQL一発で取り組んでみます。

WITH句を使った場合

前回の記事の【課題1】をそのままWITH 〇〇 as() の中身に書くだけです。
その後に【課題2】のコードを書きましょう。

with big_family_list as (

#課題1の内容
SELECT 
SibSp+Parch as num_of_family,
Passengerid
from 
`ahilog-analysis.titanic_sample.titanic_log`
where SibSp+Parch >= 4
order by Passengerid

)
#課題2の内容

select 
t1.*,
t2.num_of_family
from 
`ahilog-analysis.titanic_sample.titanic_log` t1
join 
`ahilog-analysis.titanic_sample.big_family_list` t2
on
t1.Passengerid = t2.Passengerid
ahilog

SQLも長くなってきて、
かなり本格的になってきましたね・・!
難しかったらもう一度前の記事に戻って勉強しましょう!

副参照を使った場合

副参照を使う場合は、FROMやJOINの中身に【課題1】のコードを書きます。

JOINする対象が【課題1】なので、
JOINのあとに()を書いて、コードを乗せるだけです。

select 
t1.*,
t2.num_of_family
from 
`ahilog-analysis.titanic_sample.titanic_log` t1
join (

#課題1の内容
SELECT 
SibSp+Parch as num_of_family,
Passengerid
from 
`ahilog-analysis.titanic_sample.titanic_log`
where SibSp+Parch >= 4
order by Passengerid

) t2
on
t1.Passengerid = t2.Passengerid

いかがでしたでしょうか。

中間テーブルの作成くらいまでできるようになると
基本的なテーブル操作はできるレベルになってきていると思います。

次回からはデータの細かな操作について
データアナリストがよく使うものをまとめていきたいと思います。

皆様のSQLライフに幸あれ!

より体系的にガッツリと勉強したい方にはUdemyがおすすめです!
リッチな動画コンテンツで豊富な知識を手に入れましょう。

キャリアアップに必要な知識を身につけよう | Udemyオンライン動画コース

  • この記事を書いた人

yutasukun

京都橘高校からの史上初東大受験・現役合格を成し遂げ 「リアルドラゴン桜」として横断幕が出たことアリ。 そんな過去の栄光も虚しく10年が経過。 今はデータアナリストとして汐留の広告代理店で バリバリはたらく毎日。

-SQL, データを学ぶ

© 2021 ahilog Powered by AFFINGER5