[SQL]クロス集計と、テンポラリテーブルを使用したクエリの分割

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

前回にも書きましたが、SQLの特徴として(PL/SQLなどは除いて)

  • 変数に代入して次の処理に行くことができない
  • メソッドやクラスに処理を分割できない

ことが挙げられます。なのでサブクエリなどを使っていくと、一つのクエリが長くなりがちです。これについては「テンポラリテーブル」を使用して一時的なテーブルにデータを保持させることで、一つ一つのクエリを短く・複数に分割することが可能です。

またデータの表示方法として以下の様な2次元の表というのは非常に分かりやすいかと思います。

2005年05月 2005年06月 2005年07月
タイトル1 10人 20人 30人
タイトル2 40人 50人 60人
タイトル3 70人 80人 90人

このような縦・横方向に項目を持つ集計を「クロス集計」と言います。が、SQLで単純にSELECTした場合、取得した項目を縦方向に保持してしまいます。

今回は「テンポラリテーブル」を使用してクエリを短くすることと、上記のような「クロス集計」をSQLで実現する方法について書きたいと思います。

今回行いたいこと

サンプルデータ

今回は以下のPostgreSQLのサンプルデータベースを使いました。DVDのレンタルショップを題材としたデータのようです。
PostgreSQL Sample Database
リンクを開いてもらうとER図が載っているので参照してください。

抽出するデータについて

rentalテーブルに保持しているレンタル履歴データとinventoryテーブル・filmテーブルを結合し、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しました。そして抽出したデータを、縦方向にはフィルムID(film_id)・タイトル(title)を、横方向には年月を持つクロス集計とします。注意点としては、各月のユニークユーザ数は月毎・フィルム毎のユニークユーザ数の合計とならないことです。

では、これらをSQLにて実現する手順についてです。

テンポラリテーブルの作成

まずはテンポラリテーブルの作成についてです。上記にも書きましたが、これを行う目的は一時的なテーブルにデータを保持することでクエリを短く・単純にすることです。今回はデータの抽出に必要なテーブルの結合、及び項目の絞り込みを予め行い、テンポラリテーブルに格納したいと思います。では、先に書いたrentalテーブル・inventoryテーブル・filmテーブルを結合し、テンポラリテーブルに格納します。

CREATE TEMPORARY TABLE tmp_rantal_film AS
SELECT
     r.rental_id
    ,to_char(r.rental_date, 'YYYY-MM') rental_month
    ,r.inventory_id
    ,r.customer_id
    ,i.film_id
    ,f.title
FROM
    rental r
INNER JOIN
    inventory i
ON
    r.inventory_id = i.inventory_id
INNER JOIN
    film f
ON
    i.film_id = f.film_id
;

上記の3テーブルを結合し、必要な項目を抽出しています。また月毎のデータが必要であるため、「rental_month」という列名で予めレンタルした年月を取得するようにしています。この結果作成されるテンポラリテーブルは以下の通りです。

# tmp_rantal_filmテーブル
rental_id       rental_month    inventory_id    customer_id     film_id     title
361             2005-05         6               587             1           "Academy Dinosaur"
972             2005-05         2               411             1           "Academy Dinosaur"
1033            2005-05         14              25              3           "Adaptation Holes"
465             2005-05         20              261             4           "Affair Prejudice"
995             2005-05         17              150             4           "Affair Prejudice"
552             2005-05         23              106             5           "African Egg"
(中略)                                        
2117            2005-06         2               170             1           "Academy Dinosaur"
1210            2005-06         7               345             1           "Academy Dinosaur"
3201            2005-06         6               597             1           "Academy Dinosaur"
1427            2005-06         14              100             3           "Adaptation Holes"
2090            2005-06         17              197             4           "Affair Prejudice"
1716            2005-06         20              24              4           "Affair Prejudice"
(以降略)

実際はもっと件数が多いのですが、分かりやすくするため一部を抜粋しております。このテンポラリテーブルからデータを抽出します。

SELECT
    sub.*
FROM
(
    SELECT
         rental_month
        ,film_id
        ,title
        ,count(DISTINCT customer_id)
    FROM
        tmp_rantal_film
    GROUP BY
         rental_month
        ,film_id
        ,title
    UNION
    SELECT
        rental_month
        ,NULL as film_id
        ,NULL as title
        ,count(DISTINCT customer_id)
    FROM
        tmp_rantal_film
    GROUP BY
        rental_month
) sub
ORDER BY
     sub.rental_month
    ,sub.film_id
;

真ん中のサブクエリで、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しています。このサブクエリに外側でORDER BYを掛けて、年月・film_idで並び替えています。この結果は以下の通りです。

rental_month    film_id    title                    count
2005-05         1          "Academy Dinosaur"       2
2005-05         3          "Adaptation Holes"       1
2005-05         4          "Affair Prejudice"       2
2005-05         5          "African Egg"            1
2005-05         NULL       NULL                     520
(中略)
2005-06         1          "Academy Dinosaur"       3
2005-06         3          "Adaptation Holes"       1
2005-06         4          "Affair Prejudice"       2
2005-06         5          "African Egg"            2
2005-06         NULL       NULL                     590
(以降略)

このままでも求めたい値は算出できているのですが、今回はこの結果を元にクロス集計を行います。なので値を算出するクエリとクロス集計を作るクエリを分けるため、この結果もテンポラリテーブルに登録します。以下がテンポラリテーブルを作るSQLとなります。

CREATE TEMPORARY TABLE tmp_rantal_unique_user AS
SELECT
    sub.*
FROM
(
    SELECT
         rental_month
        ,film_id
        ,title
        ,count(DISTINCT customer_id)
    FROM
        tmp_rantal_film
    GROUP BY
         rental_month
        ,film_id
        ,title
    UNION
    SELECT
        rental_month
        ,NULL as film_id
        ,NULL as title
        ,count(DISTINCT customer_id)
    FROM
        tmp_rantal_film
    GROUP BY
        rental_month
) sub
ORDER BY
     sub.rental_month
    ,sub.film_id
;

先に紹介したSELECT文に「CREATE TEMPORARY TABLE・・・」を追加しただけですね。

クロス集計

最後にクロス集計です。実はクロス集計は以前の記事で書いた「縦持ちのデータを横持ちに入れ替える」と同じやり方となります。以下の様なSQLとなります。

SELECT
     film_id
    ,COALESCE(title, title, '月次ユニークユーザ') AS title
    ,max(case rental_month when '2005-05' then count else null end) AS 2005/05
    ,max(case rental_month when '2005-06' then count else null end) AS 2005/06
    ,max(case rental_month when '2005-07' then count else null end) AS 2005/07
    ,max(case rental_month when '2005-08' then count else null end) AS 2005/08
    ,max(case rental_month when '2006-02' then count else null end) AS 2006/02
FROM
    tmp_rantal_unique_user
GROUP BY
     film_id
    ,title
ORDER BY
    film_id
;

結果は以下の通りです。

film_id    title                2005/05    2005/06    2005/07    2005/08    2006/02
1          "Academy Dinosaur"   2                3                9                9                NULL
2          "Ace Goldfinger"     NULL             NULL             2                4                1
3          "Adaptation Holes"   1                1                4                6                NULL
4          "Affair Prejudice"   2                2                12               6                1
5          "African Egg"        1                2                5                3                1
NULL       月次ユニークユーザ     520             590              599              599               158

一番最初に示した2次元の表に近づいたかと思います。ただしこの方法の欠点としては、年月を動的に増やすことができないことです。また年月の表現に全角文字を使用していますが(使用したデータベースが半角数値からカラム名を始めることができないため)、ここも個人的には気になるところです(私がやったことですが・・・)。

まとめ

テンポラリテーブルを使用してSQLを分割し、クロス集計を行う例について見てきました。バッチ処理等では長いSQLが多くなりがちだと思いますが、その場合はテンポラリテーブルの導入を考えてもいいかもしれません。またクロス集計についても、以前書いたSQLによる縦持ち・横持ちを入れ替えることの実例として見て頂ければ幸いです。

参考文献

今回の内容については、以下の本を参考にさせて頂きました。ありがとうございました。
10年戦えるデータ分析入門