はじめに
こんにちは。今日はデータ分析のためのSQLについてまとめます。
データ分析を行う場合は、複雑で順序が定められているデータに対応する必要があります。SQLには、ウィンドウ関数をはじめとした、データ分析のための関数が取り入れられています。
本文
データ処理、加工のためのSQL
データベースのデータを分析に適した形で前処理する必要があります。
具体的には、欠損値や誤りのあるデータを削除したり、NULL値を0に変換したり、複数のテーブルをまとめて結合したりします。
データ処理や加工でよく用いられるSQL文には、次のようなものがあります。
1. CASE関数
CASE関数は、条件分岐処理を行う場合に使用するものです。
SELECT文やUPDATE文で特定の条件のリストを評価して、いくつかの取り得る値のうちから1つを選択します。
-- 構文
CASE <列名> WHEN <値> THEN <列名>と<値>が一致するときの結果
[ELSE <列名>が<値>と異なるときの結果] END
CASE関数は、複数の列名や複雑な条件にも対応できます。
-- 構文
CASE WHEN <条件式>
THEN <条件式>に当てはまったときの結果
[ELSE <条件式>に当てはまらなかったときの結果]
END
2. 日付/時刻関数
日付や時刻を表す関数です。
現在日付を表すCURRENT_DATEや現在時刻を表すCURRENT_TIMESTAMPなどを用いて、DATE型で現在の時刻を使用することができます。また、ユーザ定義関数を用いて、指定されたDATE型から年、月、日をそれぞれ取り出したり、経過日数などを算出したりすることができます。
3. COALESCE 関数
データの欠損値をデフォルト値に置き換える関数です。
-- 例
COALESCE(A, 0) # Aは列名、0はデフォルト値
4. CONCAT関数
文字列を連結する関数です。
-- 例
CONCAT(A, B) # A, Bは列名
ウィンドウ関数
ウィンドウ関数（Window Function）は、標準SQL（SQL:2003以降）で本格導入された関数です。
関係データベースでは、行に順序はなく、集合として演算を行います。
データ分析では、データの順序を取り扱ったり、集計する範囲を指定したりする必要があります。こうした操作を従来のSQLで行う場合には、相関副問合せなど、問い合わせを複数組み合わせる複雑な処理が必要です。
そこで導入されたのが、ウィンドウ関数です。OVER句を用いて表される、順序や範囲に応じた集計を簡単に行うための関数群です。
ウィンドウ関数の構文を以下に示します。
ウィンドウ関数の1つである平均を求めるAVG関数を例として挙げます。
-- 構文
AVG ( <列名> ) OVER (
[ PARTITION BY ウィンドウを分割する列名リスト ]
[ ORDER BY 整列列名リスト ]
[ フレーム句 ]
)
OVER句以下がウィンドウを指定する部分です。
PARTITION BY句で、ウィンドウの範囲を指定します。GROUP BY句の列名と同様、列名の値が同じ行をウィンドウとしてまとめます。
ORDER BY句で、順序を指定します。通常のSQL文のORDER BY句とほぼ同様ですが、整列される範囲はウィンドウの範囲内に限られます。
フレーム句では、フレームの範囲を指定します。フレームとは、ウィンドウ内で作成する枠です。例えば、「7行前から現在の行まで」というように指定し、フレームごとの集計結果を得ることができます。
- ウィンドウ関数の種類
ウィンドウ関数には、順序を取り扱うためのウィンドウ関数専用の関数と従来の集約関数（集計関数）を発展させたものの2種類があります。
[例：順序を扱うウィンドウ関数専用の関数]
ROW_NUMBER()
RANK()
DENSE_RANK()
LAG(列名[,n])
LEAD(列名[,n])
これらの関数を使えば、行番号やランキングを得ることができます。
-- 例
SELECT 商品名, 価格,
ROW_NUMBER() OVER(ORDER BY 価格) AS 行番号,
RANK() OVER(ORDER BY 価格) AS ランク,
DENSE_RANK() OVER(ORDER BY 価格) AS DENSEランク,
LAG(価格) OVER(ORDER BY 価格) AS 前の行の価格,
LEAD(価格) OVER(ORDER BY 価格) AS 次の行の価格,
FROM 商品
ORDER BY 行番号
[例：実行結果]
RANK関数、DENSE_RANK関数ともに、同じ値であれば同じランク隣ります。RANK関数では、同じランクとなるデータの数をカウントして、次のランクではその数分を飛ばした番号を付与します。DENSE_RANK関数では、同じランクとなるデータの数が2つ以上でもカウントせずに次の番号を付与します。
LAG関数、LEAD関数はテーブル内の順序を扱うときに使用する関数です。直前の行の値を取得して差分を求めることなどができます。
[例：従来の集約関数がウィンドウ関数となる関数]
SUM(列名)
MAX(列名)
MIN(列名)
COUNT(列名)
-- 例
SELECT 商品区分, AVG(単価) AS 平均単価
FROM 商品
GROUP BY 商品区分
この記述では、商品区分ごとに集約され、元の商品の行は表示させることができません。
元の商品の行を表示させつつ、部署ごとの平均単価を求める場合には、ウィンドウ関数を用いて次のように表します。
-- 列
SELECT 商品名, 商品区分, AVG(単価) OVER (PARTITION BY 商品区分)
FROM 商品
- フレーム句
フレーム句はウィンドウ関数の中でのみ使用できる範囲指定です。フレーム句の構文を以下に示します。
-- 構文
[ROWS|RANGE] BETWEEN 開始点 AND 終了点
開始点と終了点でフレームの範囲を指定します。
ROWS区で始まる場合は、行単位で指定を行います。RANGE句で始まる場合には、列の値単位での指定が可能隣ります。
[例：開始点、終了点に使える句]
CURRENT ROW
n PRECEDING
n FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
例えば、「7行前から現在の行まで」という指定は、次のように行います。
-- 例
ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
WITH句
副問合せとは、SQL文の問合せ中に入れ子型で使用する問合せです。副問合せ内で仮想テーブルを作成し、そのテーブルを元に問い合わせを実行します。
副問合せを読みやすくするための方法として、あらかじめ問合せに使用する仮想テーブルを作成するWITH句があります。
WITH句は、共通テーブル式、またはCTE(Common Table Expressions)とも呼ばれます。
-- 構文
WITH [RECURSIVE] <問合せ名>(<列名リスト>) AS (<問合せ内容>)
--例1
WITH TEMP ( TOTAL ) AS ( SELECT COUNT(*) FROM 物件 )
--例2: 作成したTEMPを利用して、全物件数に占める割合を客分率で算出
SELECT 沿線, FLOOR( COUNT(*) * 100 / TOTAL )
FROM 物件 CROSS JOIN TEMP
WHERE エアコン='Y' AND オートロック='Y'
ORDER BY 沿線
再起的な問合せの場合には、RECURSIVEを指定することでSQL文で再起的な処理を行うことができます。
-- 例
WITH RECURSIVE TEMP(ログID) AS
(SELECT ログID
FROM ログ関連
WHERE ログID = '101'
UNION ALL
SELECT A.ログID
FROM ログ関連 A, TEMP B
WHERE A.親ログID = B.ログID)
SELECT ログID FROM TEMP
最初のSELECT文で、ログIDが'101'の行を抽出し、2番目のSELECT文で抽出したログIDを親ログIDとする行を抽出します。これを再起的に繰り返すことで、親から順にログを表示させることができます。
まとめ
いかがでしたか？