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

手続き型プログラマの為のSQL

t.honda

2015.08.30

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

はじめに

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

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

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

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

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

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

今回行いたいこと

サンプルデータ

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

抽出するデータについて

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

予めrantalテーブルの全レコードを顧客IDでソートしておく
一件ずつループし、顧客IDが前のレコードと同じ場合はレンタルした期間を比較する
これを繰り返すことで、顧客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に置き換えてしまうのも手かもしれません。