データ分析のためのSQL

はじめに

こんにちは！今回は、データ分析のために活用できるSQLの機能をまとめます。近年のデータ分析では、単なる抽出だけでなく、複雑な条件分岐や集計、行の順序に基づいた計算が必要になることが多いです。SQLでは、ウィンドウ関数やWITH句などの豊富な機能を用いて、こうした処理を柔軟かつ効率的に実装できます。

本文

1. データ処理・加工のためのSQL

分析やレポートで使いやすい形に加工するため、欠損値の処理や複数テーブルの結合などが必要です。以下の関数は、よく使われるデータ前処理の例です。

1. CASE 関数
   条件分岐を行い、複数の条件に応じて値を切り替えます

   -- 構文
   CASE 
       WHEN <条件式1> THEN <結果1>
       WHEN <条件式2> THEN <結果2>
       ...
       ELSE <それ以外のときの結果>
   END
   

   -- 例
   SELECT 商品名,
          CASE WHEN 単価 >= 2000 THEN '高級品' 
               ELSE '標準品'
          END AS 商品区分
   FROM 商品;
   

2. 日付/時刻関数
   日付や時刻を扱うための関数で、CURRENT_DATE, CURRENT_TIMESTAMPなどを用いれば、システム時刻を取得可能。ユーザ定義関数で日付の切り出しや加減算を行い、経過日数や月単位集計を作るのにも使えます

3. COALESCE 関数
   NULL値を別の値（デフォルト値）に置き換える関数

   -- 例
   SELECT COALESCE(売上高, 0) AS 売上高0補完
   FROM 売上;
   

4. CONCAT 関数
   文字列同士を連結する関数

   -- 例　
   SELECT CONCAT(姓, 名) AS 氏名
   FROM 顧客;
   

2. ウィンドウ関数

ウィンドウ関数（Window Function） は、SQL:2003以降で本格的に導入された機能で、「行に順序付け」や「ある範囲だけの集計」を簡単に表現できます。従来のSQLでは相関副問合せや複雑なJOINが必要だった操作が、ウィンドウ関数を使うとシンプルに書けるようになります。

-- 構文（例：AVG 関数を使う場合）
AVG(<列名>) OVER (
    [PARTITION BY 列名リスト]
    [ORDER BY 列名リスト]
    [フレーム句]
)

1. PARTITION BY
   グループ化の対象列を指定。GROUP BYと似ていますが、行をまとめてしまうのではなく、ウィンドウ（論理的な区切り）を作った上で計算しつつ、元行をすべて表示できます

2. ORDER BY
   ウィンドウ内での行の順序を定義します。並び順があることで、前行や後行との比較ができるようになります

3. フレーム句
   ウィンドウ内のさらに細かい範囲（フレーム）を指定します。

2.1 ウィンドウ関数専用の関数

• ROW_NUMBER() : 連番を振る
• RANK(), DENSE_RANK() : 順位付け（同値があった場合の処理が少し異なる）
• LAG(列名 [,n]) : 現在行からn行前の値を参照
• LEAD(列名 [,n]) : 現在行から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 行番号;

2.1.1 サンプルテーブル

下記のような 商品 テーブルを用意し、価格 列をソートの基準にしています。

商品ID	商品名	価格
1	A商品	100
2	B商品	100
3	C商品	200
4	D商品	300
5	E商品	300
6	F商品	400

2.1.2 実行結果

このデータで上記クエリを実行すると、概ね次のような結果が得られます（DBMSによって一部差がある場合もあります）。

商品名	価格	行番号	ランク	DENSEランク	前の行の価格	次の行の価格
A商品	100	1	1	1	NULL	100
B商品	100	2	1	1	100	200
C商品	200	3	3	2	100	300
D商品	300	4	4	3	200	300
E商品	300	5	4	3	300	400
F商品	400	6	6	4	300	NULL

2.2 既存の集約関数をウィンドウとして使う

SUM()、COUNT()、AVG() など、通常のGROUP BYで使う集約関数をウィンドウ関数として利用し、個々の行を保持しながら集計結果だけ追加できます。

-- 商品区分ごとに平均単価を求めつつ、個々の商品も表示
SELECT 商品名, 商品区分,
       AVG(単価) OVER (PARTITION BY 商品区分) AS 区分別平均
FROM 商品;

2.2.1 サンプルテーブル

仮に「商品」テーブルが以下のような構成・データを持つとします。

商品ID	商品名	商品区分	単価
1	りんご	フルーツ	100
2	みかん	フルーツ	200
3	にんじん	野菜	150
4	レタス	野菜	120
5	ステーキ	肉	500
6	ひき肉	肉	350

2.2.2 実行結果

上記クエリを実行した際、概ね次のような結果が得られます（DBMSによって一部差がある場合もあります）。

商品名	商品区分	区分別平均
りんご	フルーツ	150.0
みかん	フルーツ	150.0
にんじん	野菜	135.0
レタス	野菜	135.0
ステーキ	肉	425.0
ひき肉	肉	425.0

従来のGROUP BYでは、1行にまとめられてしまう情報が、ウィンドウ関数なら「元の行＋集計結果」の形で表示可能です。

2.3 フレーム句の開始点・終了点に使える句

ウィンドウ関数のフレーム句では、行単位または値単位で「どこからどこまでを計算対象にするか」を指定できます。例えば、ROWS BETWEEN 5 PRECEDING AND CURRENT ROW のように記述し、ウィンドウ内の「現在行の5行前」から「現在行」までを集計対象にするなどの操作が可能です。

-- 構文
[ROWS|RANGE] BETWEEN 開始点 AND 終了点

以下は、開始点・終了点に使える句の一覧です。

句	説明
CURRENT ROW	現在の行だけ。ここを始点／終点にすると「現在行を含む集計」を表現できる
n PRECEDING	現在の行から n行前。例えば 3 PRECEDING は「3行前」
n FOLLOWING	現在の行から n行後。例えば 2 FOLLOWING は「2行後」
UNBOUNDED PRECEDING	ウィンドウの先頭（最初の行）から現在の行まで。範囲を「開始から現在行」までに限定したいとき
UNBOUNDED FOLLOWING	現在の行からウィンドウの末尾（最後の行）まで。範囲を「現在行から終わり」までに限定したいとき

補足

• ROWSを使うと、あくまで「行数」に注目したフレーム指定を行います
• RANGEを使う場合、列値（ソートキー）の差分に注目して範囲を指定することができます

3. WITH句（共通テーブル式：CTE）

WITH句は、副問合せを使いやすくし、クエリを整理するための仕組みです。

-- 構文
WITH <テンポラリテーブル名> AS (
    SELECT ...
)
SELECT ...

複雑なサブクエリを何度も書く代わりに、WITH句内で仮のテーブル（CTE: Common Table Expressions）を定義し、それをメインクエリで参照します。可読性や保守性が向上し、必要に応じて再帰的に利用することも可能です（WITH RECURSIVE）。

3.1 シンプルな例

-- 例
WITH TEMP (商品数) AS (
    SELECT COUNT(*) FROM 商品
)
SELECT 商品区分, COUNT(*) * 100.0 / 商品数 AS 割合
  FROM 商品 CROSS JOIN TEMP
  GROUP BY 商品区分, 商品数;

ここでは仮想テーブルTEMPを先に作っておき、そこから全商品の個数を参照して比率を算出しています。

3.2 再帰的な利用

WITH RECURSIVEを使うと、自己参照テーブルなどを階層的にたどるクエリを書けます。例えば、ログの親子関係を辿る場合などに有用です。

-- 例
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;

まとめ

データ分析のためのSQLでは、単純な検索や集計だけでなく、条件分岐（CASE）や欠損値処理（COALESCE）、文字列操作（CONCAT） などを組み合わせて前処理を行うことが多くあります。そして、ウィンドウ関数を用いることで、行の順序や特定の区間を意識した集計・ランキング・差分計算などをシンプルに表現できます。

さらに、WITH句（CTE）を使うことで副問合せを見やすくまとめ、複雑な分析クエリも整理しやすくなります。これらの機能を上手に使いこなすと、従来のSQLでは困難だった高度なデータ分析ロジックを少ないクエリで実現できるようになります。

今後のデータ分析業務を効率化するために、ぜひウィンドウ関数やWITH句などのSQL機能を活用してみてください。