[SQL]ループをカット、ウィンドウ関数で解決する

特集

t.honda

2015.08.30

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

はじめに

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

  • ループを書くことが出来ない

ことが挙げられます。とはいってもロジックを考えていると、ループさせたくなることがあることも事実です。

このような場合、SQLで解決するために考えてられるのが

  • カットを使う
  • ウィンドウ関数を使う

ことです。以下に実例を挙げてみたいと思います。

今回行いたいこと

サンプルデータ

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

抽出するデータについて

rantalテーブルのレンタル履歴データを参照し、顧客ID(customer_id)ごとに最も長い期間借りたレコードを抽出しました。
これを手続き型のロジックで行うと「顧客IDごと」の部分がループになるかと思います。例えば以下の様なロジックになるかと思います。

  1. 予めrantalテーブルの全レコードを顧客IDでソートしておく
  2. 一件ずつループし、顧客IDが前のレコードと同じ場合はレンタルした期間を比較する
  3. これを繰り返すことで、顧客IDごとに最もレンタル期間が長いレコードを特定する


これらと同じことをSQLにて実現してみたいと思います。

SQLによるループの実現

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

各レコードのレンタルした期間を取得する必要があるのですが、rentalテーブルにはそのようなカラムはありません。レンタル日(rental_date)・返却日(return_date)から算出する必要がありますが、SQLを分かりやすくするため、予め必要な項目を抽出してテンポラリテーブルに登録することにします。以下、テンポラリテーブル(tmp_rental_customer)を作成するSQLです。

CREATE TEMPORARY TABLE tmp_rental AS
SELECT
     rental_id
    ,customer_id
    ,rental_date
    ,return_date
    ,date_part('day', return_date - rental_date) as rental_days
FROM
    rental
;

レンタル期間(rental_days)を含めて必要な項目取得しています。この結果作成されるテンポラリテーブルは以下の通りです。

# tmp_rentalテーブル

rental_id    customer_id    rental_date                     return_date                     rental_days
11299        1              "2005-08-02 15:36:52.000000"    "2005-08-10 16:40:52.000000"    8
6163         1              "2005-07-11 10:13:46.000000"    "2005-07-19 13:15:46.000000"    8
10437        1              "2005-08-01 08:51:04.000000"    "2005-08-10 12:12:04.000000"    9
76           1              "2005-05-25 11:30:37.000000"    "2005-06-03 12:00:37.000000"    9
1476         1              "2005-06-15 21:08:46.000000"    "2005-06-25 02:26:46.000000"    9
9100         4              "2005-07-30 08:46:09.000000"    "2005-08-05 10:34:09.000000"    6
15147        4              "2005-08-22 13:58:23.000000"    "2005-08-28 14:33:23.000000"    6
13807        4              "2005-08-20 12:55:40.000000"    "2005-08-28 09:06:40.000000"    7
12294        4              "2005-08-18 05:14:44.000000"    "2005-08-27 01:32:44.000000"    8
6042         5              "2005-07-11 03:17:04.000000"    "2005-07-19 07:08:04.000000"    8
11001        5              "2005-08-02 04:56:45.000000"    "2005-08-11 08:04:45.000000"    9
6663         5              "2005-07-12 11:27:35.000000"    "2005-07-21 16:35:35.000000"    9
13209        5              "2006-02-14 15:16:03.000000"    NULL                            NULL

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

余談ですが、SQLが長く複雑になる場合は、テンポラリテーブルを使って複数のSQLに分割したほうが可読性の上でもいいのではないか、と最近は思っています。

カットによる抽出

「顧客IDごと」に最も長い期間借りたレコードを取得するため

  • 顧客IDでのカットを行い
  • それぞれの顧客IDでレンタル期間が最大のデータを抽出

します。SQLにすると以下の通りです。

SELECT
     tmp1.rental_id
    ,tmp1.customer_id
    ,tmp1.rental_days
FROM
    tmp_rental tmp1
WHERE
    tmp1.rental_days =
    (
        SELECT
            MAX(tmp2.rental_days)
        FROM
            tmp_rental tmp2
        WHERE
            tmp1.customer_id = tmp2.customer_id
    )
ORDER BY
      tmp1.customer_id
     ,tmp1.rental_id
;

実行結果は以下の通りです。

rental_id    customer_id    rental_days
76           1              9
1476         1              9
10437        1              9
12294        4              8
6663         5              9
11001        5              9

こちらも上記のテンポラリテーブルのデータに該当するレコードのみ抜粋しています。顧客IDごとにレンタル期間が最大のレコードが抽出されていることが分かるかと思います。

ウィンドウ関数による実現

こちらも「顧客IDごと」に最も長い期間借りたレコードを取得するという目的は同じですが、SQLでのアプローチがカットとは微妙に違います。やることを列挙すると

  • ウィンドウ関数rank()を使い、顧客IDごとにレンタル期間の長さでランキングを付け
  • ランキングが1位(つまりレンタル期間が最も長い)のレコードを抽出

となります。SQLにすると以下の通りです。

SELECT
     t.rental_id
    ,t.customer_id
    ,t.rental_days
FROM
    (
        SELECT
            *,
            rank() OVER (
                PARTITION BY customer_id
                ORDER BY rental_days DESC
            ) AS rental_rank
        FROM
            tmp_rental
        WHERE
            rental_days IS NOT NULL
    ) t
WHERE
    t.rental_rank = 1
ORDER BY
     t.customer_id
    ,t.rental_id
;

実行結果は以下の通りです。

rental_id    customer_id    rental_days
76           1              9
1476         1              9
10437        1              9
12294        4              8
6663         5              9
11001        5              9

こちらも上記のテンポラリテーブルのデータに該当するレコードのみ抜粋しています。カットと同様、顧客IDごとにレンタル期間が最大のレコードが抽出されていることが分かるかと思います。

ウィンドウ関数を用いたSQLはパフォーマンスがいいのですが、いくつか注意点があります。

注意点1. 使用するウィンドウ関数に気をつける

まあ当たり前ですが(笑)、ウィンドウ関数が違うと結果も変わってきます。以下がrank()の代わりにrow_number()を使用する場合と結果です。

SELECT
     t.rental_id
    ,t.customer_id
    ,t.rental_days
FROM
    (
        SELECT
            *,
            row_number() OVER (
                PARTITION BY customer_id
                ORDER BY rental_days DESC
            ) AS rental_rank
        FROM
            tmp_rental
        WHERE
            rental_days IS NOT NULL
    ) t
WHERE
    t.rental_rank = 1
ORDER BY
     t.customer_id
    ,t.rental_id
;

実行結果は以下の通りです。

rental_id    customer_id    rental_days
76           1              9
12294        4              8
6663         5              9

row_number()を使うことで、レンタル期間が重複したレコードがある場合、先頭の1件のみを取得してしまっています。

注意点2. NULLに気をつける

レンタル期間がNULLのレコードの存在にも気をつける必要があります。というのはレンタル期間で逆ソートしてランキングを付けていますが、そのレンタル期間がNULLを含むかどうかで結果が変わってくるからです。最初のSQLではレンタル期間がNULLのレコードを除外していたのですが(WHERE rental_days IS NOT NULL)、その条件を外してみました。

SELECT
     t.rental_id
    ,t.customer_id
    ,t.rental_days
FROM
    (
        SELECT
            *,
            rank() OVER (
                PARTITION BY customer_id
                ORDER BY rental_days DESC
            ) AS rental_rank
        FROM
            tmp_rental
    ) t
WHERE
    t.rental_rank = 1
ORDER BY
     t.customer_id
    ,t.rental_id
;

実行結果は以下の通りです。

rental_id    customer_id    rental_days
76           1              9
1476         1              9
10437        1              9
12294        4              8
13209        5              NULL

顧客IDが"5"のレコードについて、レンタル期間がNULLのレコードが抽出されてしまっています。これでは間違いですね(笑)。今回はWHERE句でレンタル期間がNULLのレコードは除外しましたが、もしレンタル期間がNULLのレコードも含めて最も長いデータを抽出したいという要件なら、テンポラリテーブルを作る際にレンタル期間 = NULLを0に置き換えてしまうのも手かもしれません。

まとめ

ループをカット・ウィンドウ関数で解決する例について見てきました。SQLにてループを使いたくなったら、カットやウィンドウ関数を使用できないかを検討してみてもいいかもしれません。

参考文献

今回の内容については、以下の本やサイトを参考にさせて頂きました。ありがとうございました。
SQL実践入門
第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（1）サブクエリ

customer-story-analytics