SQLによるデータ分析のテクニック

# SQLによるデータ分析のテクニック

## 概要

* 前処理大全を読み、以下の理由から感銘を受けたものを記載する。
   * データ分析で、これに近いことを頼まれた(このような面倒くさい処理は、データ分析ではよくある)
   * こんなふうにSQLを書けるんだ。めっちゃ楽やんと知った。

* 詳しくは[前処理大全](https://www.amazon.co.jp/%E5%89%8D%E5%87%A6%E7%90%86%E5%A4%A7%E5%85%A8-%E3%83%87%E3%83%BC%E3%82%BF%E5%88%86%E6%9E%90%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AESQL-Python%E5%AE%9F%E8%B7%B5%E3%83%86%E3%82%AF%E3%83%8B%E3%83%83%E3%82%AF-%E6%9C%AC%E6%A9%8B-%E6%99%BA%E5%85%89/dp/4774196479/ref=sr_1_1?ie=UTF8&qid=1551975861&sr=8-1&keywords=%E3%81%BE%E3%81%88%E3%81%97%E3%82%87%E3%82%8A%E3%81%9F%E3%81%84%E3%81%9C%E3%82%93)を読むことをおすすめします。
   * データなども「前処理大全」のgithubにあります。

* 前提
   * 以下のSQLはPostgreSQLのものです。(最下部の日付の計算以外はほかでも動きそう)

## 内容

### 最も多い価格帯(最頻値)の取得

   * 予約を格納したテーブルから、最も頻出する価格帯を取得する。
   * 正確な価格ではなく、ROUND関数を使って千円の桁で四捨五入をしたものを対象にする。

   * SQL

      ```sql
      SELECT
      ROUND(total_price, -3) as total_price_round
      FROM reserve_tb
      GROUP BY total_price_round
      LIMIT 1;
      ```

   * 対象データ例

| reserve_id | hotel_id | customer_id | total_price|
|------------|----------|-------------|------------|
| r1         | h_75     | c_1         |       97200|
| r2         | h_219    | c_1         |       20600|
| r3         | h_179    | c_1         |       33600|
| r4         | h_214    | c_1         |      194400|
| r5         | h_16     | c_1         |       68100|
| r6         | h_241    | c_1         |       97500|
| r7         | h_256    | c_1         |      103500|

   * 結果例
      * 97000

### 時系列に番号を付与

   * 顧客ごとに予約日時の古い順に番号を振る。
   * ROW_NUMBER(Window関数)を知らないとけっこう長くなる。

   * SQL

      ```sql
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY reserve_datetime) as log_no
      FROM
        reserve_tb;
      ```

   * 対象データ例

| reserve_id | hotel_id | customer_id |  reserve_datetime   |
|------------|----------|-------------|---------------------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |
| r9         | h_217    | c_2         | 2016-03-05 13:31:06 |
| r10        | h_240    | c_2         | 2016-06-25 09:12:22 |
| r11        | h_183    | c_2         | 2016-11-19 12:49:10 |
| r12        | h_268    | c_2         | 2017-05-24 10:06:21 |

   * 結果例

| reserve_id | hotel_id | customer_id |  reserve_datetime   |  log_no  |
|------------|----------|-------------|---------------------|----------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |    1     |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |    2     |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |    3     |
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |    4     |
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |    5     |
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |    6     |
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |    7     |
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |    8     |
| r9         | h_217    | c_2         | 2016-03-05 13:31:06 |    1     |
| r10        | h_240    | c_2         | 2016-06-25 09:12:22 |    2     |
| r11        | h_183    | c_2         | 2016-11-19 12:49:10 |    3     |
| r12        | h_268    | c_2         | 2017-05-24 10:06:21 |    4     |

### ランキング

   * ホテルごとに予約件数でランキングをつける。
   * hotel_id をキーに GROUP BY して COUNT(*) を取るところまでは思いつくが、RANK(Window関数)を知らないと、その後が長い。

   * SQL

      ```sql
      SELECT
        hoteL_id,
      RANK() OVER (ORDER BY COUNT(*) DESC) AS rsv_cnt_rank
      FROM reserve_tb
      GROUP BY hotel_id;
      ```

   * 対象データ例
      * 上記データと同じ

   * 結果例

| hotel_id | rsv_cnt_rank|
|----------|-------------|
| h_241    |            1|
| h_144    |            2|
| h_37     |            3|
| h_142    |            3|
| h_178    |            5|
| h_82     |            5|
| h_215    |            7|

### n件前のデータの取得

   * 予約テーブルの各レコードに、同じ顧客の2回前の予約金額を付与する。
   * LAG関数(Window関数)を知らないと、けっこう長くなる。
   * LAG関数は、n件前のデータを取得する。対象となるグループは、PARTITION BYで指定し、並び方はORDER BYで指定する。

   * SQL

      ```sql
      SELECT
        *,
        LAG(total_price, 2) OVER (PARTITION BY customer_id ORDER BY reserve_datetime) AS before_price
      FROM reserve_tb;
      ```

   * 対象データ例

| reserve_id | hotel_id | customer_id |  reserve_datetime   | total_price |
|------------|----------|-------------|---------------------|-------------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |       97200 |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |       20600 |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |       33600 |
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |      194400 |
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |       68100 |
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |       36000 |
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |      103500 |
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |        6000 |
| r59        | h_245    | c_10        | 2016-06-20 11:10:52 |      110400 |
| r60        | h_102    | c_10        | 2016-10-23 01:56:42 |       36000 |
| r61        | h_62     | c_10        | 2017-03-12 00:45:50 |      187200 |
| r62        | h_21     | c_10        | 2017-07-29 17:48:41 |       89600 |
| r63        | h_142    | c_10        | 2017-09-11 19:41:28 |       37200 |
| r64        | h_112    | c_10        | 2018-03-07 00:43:22 |      117000 |

   * 結果例

| reserve_id | hotel_id | customer_id |  reserve_datetime   | total_price | before_price|
|------------|----------|-------------|---------------------|-------------|-------------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |       97200 |             |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |       20600 |             |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |       33600 |        97200|
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |      194400 |        20600|
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |       68100 |        33600|
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |       36000 |       194400|
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |      103500 |        68100|
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |        6000 |        36000|
| r59        | h_245    | c_10        | 2016-06-20 11:10:52 |      110400 |             |
| r60        | h_102    | c_10        | 2016-10-23 01:56:42 |       36000 |             |
| r61        | h_62     | c_10        | 2017-03-12 00:45:50 |      187200 |       110400|
| r62        | h_21     | c_10        | 2017-07-29 17:48:41 |       89600 |        36000|
| r63        | h_142    | c_10        | 2017-09-11 19:41:28 |       37200 |       187200|
| r64        | h_112    | c_10        | 2018-03-07 00:43:22 |      117000 |        89600|

### 過去n件の合計値
   * 顧客ごとに各レコードから過去の2件前までの予約金額の合計を付与する。(過去のレコードが2件に満たないときは値なし)
   * Window関数を知らないと「顧客ごとの集計ではだめですか？」と妥協案を探ってしまう。
   * BETWEENでカウントや合計の対象を設定する。2 PRECEDING は2件前、CURRENT ROWは現在の行を表す。

   * SQL

      ```sql
      SELECT
        *,
        CASE WHEN
          COUNT(total_price) OVER (PARTITION BY customer_id ORDER BY reserve_datetime ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3
        then
          SUM(total_price) OVER
          (PARTITION BY customer_id ORDER BY reserve_datetime ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
        ELSE
          NULL
        END AS price_sum
      FROM reserve_tb;
      ```

   * 対象データ例

| reserve_id | hotel_id | customer_id |  reserve_datetime   | total_price |
|------------|----------|-------------|---------------------|-------------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |       97200 |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |       20600 |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |       33600 |
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |      194400 |
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |       68100 |
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |       36000 |
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |      103500 |
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |        6000 |
| r59        | h_245    | c_10        | 2016-06-20 11:10:52 |      110400 |
| r60        | h_102    | c_10        | 2016-10-23 01:56:42 |       36000 |
| r61        | h_62     | c_10        | 2017-03-12 00:45:50 |      187200 |
| r62        | h_21     | c_10        | 2017-07-29 17:48:41 |       89600 |
| r63        | h_142    | c_10        | 2017-09-11 19:41:28 |       37200 |
| r64        | h_112    | c_10        | 2018-03-07 00:43:22 |      117000 |

   * 結果例

| reserve_id | hotel_id | customer_id |  reserve_datetime   | total_price | price_sum|
|------------|----------|-------------|---------------------|-------------|----------|
| r1         | h_75     | c_1         | 2016-03-06 13:09:42 |       97200 |          |
| r2         | h_219    | c_1         | 2016-07-16 23:39:55 |       20600 |          |
| r3         | h_179    | c_1         | 2016-09-24 10:03:17 |       33600 |    151400|
| r4         | h_214    | c_1         | 2017-03-08 03:20:10 |      194400 |    248600|
| r5         | h_16     | c_1         | 2017-09-05 19:50:37 |       68100 |    296100|
| r6         | h_241    | c_1         | 2017-11-27 18:47:05 |       36000 |    298500|
| r7         | h_256    | c_1         | 2017-12-29 10:38:36 |      103500 |    207600|
| r8         | h_241    | c_1         | 2018-05-26 08:42:51 |        6000 |    145500|
| r59        | h_245    | c_10        | 2016-06-20 11:10:52 |      110400 |          |
| r60        | h_102    | c_10        | 2016-10-23 01:56:42 |       36000 |          |
| r61        | h_62     | c_10        | 2017-03-12 00:45:50 |      187200 |    333600|
| r62        | h_21     | c_10        | 2017-07-29 17:48:41 |       89600 |    312800|
| r63        | h_142    | c_10        | 2017-09-11 19:41:28 |       37200 |    314000|
| r64        | h_112    | c_10        | 2018-03-07 00:43:22 |      117000 |    243800|

### 過去n件の平均値
   * 上記と同じ。

   * SQL

      ```sql
      SELECT
        *,
        AVG(total_price) OVER (PARTITION BY customer_id ORDER BY checkin_date ROWS between 3 PRECEDING AND 1 PRECEDING) AS price_avg
      FROM reserve_tb;
      ```

   * 対象データ例

| reserve_id | hotel_id | customer_id | checkin_date | total_price |
|------------|----------|-------------|--------------|-------------|
| r1         | h_75     | c_1         | 2016-03-26   |       97200 |
| r2         | h_219    | c_1         | 2016-07-20   |       20600 |
| r3         | h_179    | c_1         | 2016-10-19   |       33600 |
| r4         | h_214    | c_1         | 2017-03-29   |      194400 |
| r5         | h_16     | c_1         | 2017-09-22   |       68100 |
| r6         | h_241    | c_1         | 2017-12-04   |       36000 |
| r7         | h_256    | c_1         | 2018-01-25   |      103500 |
| r8         | h_241    | c_1         | 2018-06-08   |        6000 |
| r59        | h_245    | c_10        | 2016-07-08   |      110400 |
| r60        | h_102    | c_10        | 2016-11-01   |       36000 |
| r61        | h_62     | c_10        | 2017-04-08   |      187200 |
| r62        | h_21     | c_10        | 2017-08-06   |       89600 |
| r63        | h_142    | c_10        | 2017-09-13   |       37200 |
| r64        | h_112    | c_10        | 2018-03-13   |      117000 |

   * 結果例

| reserve_id | hotel_id | customer_id | checkin_date | total_price |       price_avg       |
|------------|----------|-------------|--------------|-------------|-----------------------|
| r1         | h_75     | c_1         | 2016-03-26   |       97200 |                       |
| r2         | h_219    | c_1         | 2016-07-20   |       20600 |     97200.000000000000|
| r3         | h_179    | c_1         | 2016-10-19   |       33600 |     58900.000000000000|
| r4         | h_214    | c_1         | 2017-03-29   |      194400 |     50466.666666666667|
| r5         | h_16     | c_1         | 2017-09-22   |       68100 |     82866.666666666667|
| r6         | h_241    | c_1         | 2017-12-04   |       36000 |     98700.000000000000|
| r7         | h_256    | c_1         | 2018-01-25   |      103500 |     99500.000000000000|
| r8         | h_241    | c_1         | 2018-06-08   |        6000 |     69200.000000000000|
| r59        | h_245    | c_10        | 2016-07-08   |      110400 |                       |
| r60        | h_102    | c_10        | 2016-11-01   |       36000 |    110400.000000000000|
| r61        | h_62     | c_10        | 2017-04-08   |      187200 |     73200.000000000000|
| r62        | h_21     | c_10        | 2017-08-06   |       89600 |    111200.000000000000|
| r63        | h_142    | c_10        | 2017-09-13   |       37200 |    104266.666666666667|
| r64        | h_112    | c_10        | 2018-03-13   |      117000 |    104666.666666666667|

### 過去n日間の合計値
   * 顧客ごとに過去n日間の売上の合計を算出する。
      * NULLのときはゼロにする。
   * 上記でやった「過去n件の〇〇」とは違ったアプローチになる。
      * 顧客IDと日付を検索条件に、FROMで指定したテーブルをLEFT JOINする。その上で、FROMで指定したテーブルのカラム全てでGROUP BYする。

   * SQL

      ```sql
      SELECT
         t1.*,
         COALESCE(SUM(t2.total_price), 0) AS price_sum
      FROM reserve_tb AS t1
      LEFT JOIN reserve_tb AS t2
        ON t1.customer_id = t2.customer_id
        AND t1.reserve_datetime > t2.reserve_datetime
        AND (t1.reserve_datetime - interval '90 day') <= t2.reserve_datetime
      GROUP BY t1.reserve_id, t1.hotel_id, t1.customer_id, t1.reserve_datetime, t1.checkin_date, t1.checkin_time, t1.checkout_date, t1.people_num, t1.total_price;
      ```

   * データ例

| reserve_id | hotel_id | customer_id |  reserve_datetime   | checkin_date | checkin_time | checkout_date | people_num | total_price |
|------------|----------|-------------|---------------------|--------------|--------------|---------------|------------|-------------|
| r2824      | h_5      | c_710       | 2016-11-15 20:28:48 | 2016-12-10   | 09:00:00     | 2016-12-11    |          3 |       40500 |
| r1370      | h_234    | c_336       | 2017-10-22 00:21:07 | 2017-11-20   | 11:30:00     | 2017-11-21    |          3 |       16800 |
| r1488      | h_191    | c_366       | 2016-03-27 19:14:10 | 2016-04-19   | 11:00:00     | 2016-04-20    |          1 |       41300 |
| r3085      | h_80     | c_775       | 2018-01-18 18:27:10 | 2018-02-05   | 12:30:00     | 2018-02-06    |          3 |      110100 |
| r865       | h_61     | c_210       | 2016-07-29 10:12:03 | 2016-08-05   | 09:30:00     | 2016-08-07    |          3 |      448800 |
| r2488      | h_285    | c_622       | 2016-04-01 15:26:37 | 2016-04-11   | 12:30:00     | 2016-04-14    |          2 |       71400 |
| r3205      | h_65     | c_806       | 2016-01-02 01:07:26 | 2016-01-16   | 10:30:00     | 2016-01-17    |          3 |       25200 |
| r1096      | h_54     | c_267       | 2016-06-04 11:49:37 | 2016-06-20   | 10:00:00     | 2016-06-21    |          2 |       25600 |
| r3509      | h_270    | c_871       | 2017-02-03 15:27:41 | 2017-03-02   | 12:00:00     | 2017-03-04    |          4 |       66400 |
| r2432      | h_255    | c_608       | 2017-02-13 15:34:55 | 2017-03-15   | 11:00:00     | 2017-03-18    |          3 |      346500 |
| r44        | h_145    | c_7         | 2017-06-23 23:56:10 | 2017-07-18   | 09:30:00     | 2017-07-21    |          2 |      112800 |
| r3428      | h_11     | c_855       | 2016-04-22 05:12:06 | 2016-04-24   | 12:00:00     | 2016-04-27    |          4 |      448800 |
| r2188      | h_55     | c_545       | 2016-05-28 05:28:22 | 2016-06-16   | 12:00:00     | 2016-06-19    |          3 |      127800 |
| r1835      | h_225    | c_454       | 2016-07-11 23:53:54 | 2016-07-29   | 09:00:00     | 2016-07-31    |          1 |       14400 |
| r917       | h_19     | c_224       | 2016-10-03 14:03:33 | 2016-10-23   | 10:30:00     | 2016-10-25    |          4 |       96000 |
| r3212      | h_73     | c_806       | 2017-07-26 04:45:47 | 2017-07-30   | 10:00:00     | 2017-08-01    |          2 |       79600 |
| r2477      | h_65     | c_620       | 2017-02-07 11:08:26 | 2017-02-07   | 11:00:00     | 2017-02-08    |          2 |       16800 |
| r2457      | h_206    | c_614       | 2017-01-18 08:29:43 | 2017-01-31   | 09:00:00     | 2017-02-02    |          1 |       48400 |


   * 結果例

|  reserve_id | hotel_id | customer_id |  reserve_datetime   | checkin_date | checkin_time | checkout_date | people_num | total_price | price_sum|
|------------|----------|-------------|---------------------|--------------|--------------|---------------|------------|-------------|-----------|
| r2824      | h_5      | c_710       | 2016-11-15 20:28:48 | 2016-12-10   | 09:00:00     | 2016-12-11    |          3 |       40500 |         0|
| r1370      | h_234    | c_336       | 2017-10-22 00:21:07 | 2017-11-20   | 11:30:00     | 2017-11-21    |          3 |       16800 |         0|
| r1488      | h_191    | c_366       | 2016-03-27 19:14:10 | 2016-04-19   | 11:00:00     | 2016-04-20    |          1 |       41300 |         0|
| r3085      | h_80     | c_775       | 2018-01-18 18:27:10 | 2018-02-05   | 12:30:00     | 2018-02-06    |          3 |      110100 |         0|
| r865       | h_61     | c_210       | 2016-07-29 10:12:03 | 2016-08-05   | 09:30:00     | 2016-08-07    |          3 |      448800 |     15400|
| r2488      | h_285    | c_622       | 2016-04-01 15:26:37 | 2016-04-11   | 12:30:00     | 2016-04-14    |          2 |       71400 |     86400|
| r3205      | h_65     | c_806       | 2016-01-02 01:07:26 | 2016-01-16   | 10:30:00     | 2016-01-17    |          3 |       25200 |         0|
| r1096      | h_54     | c_267       | 2016-06-04 11:49:37 | 2016-06-20   | 10:00:00     | 2016-06-21    |          2 |       25600 |     29100|
| r3509      | h_270    | c_871       | 2017-02-03 15:27:41 | 2017-03-02   | 12:00:00     | 2017-03-04    |          4 |       66400 |    231600|
| r2432      | h_255    | c_608       | 2017-02-13 15:34:55 | 2017-03-15   | 11:00:00     | 2017-03-18    |          3 |      346500 |     14800|
| r44        | h_145    | c_7         | 2017-06-23 23:56:10 | 2017-07-18   | 09:30:00     | 2017-07-21    |          2 |      112800 |     36000|
| r3428      | h_11     | c_855       | 2016-04-22 05:12:06 | 2016-04-24   | 12:00:00     | 2016-04-27    |          4 |      448800 |         0|
| r2188      | h_55     | c_545       | 2016-05-28 05:28:22 | 2016-06-16   | 12:00:00     | 2016-06-19    |          3 |      127800 |         0|
| r1835      | h_225    | c_454       | 2016-07-11 23:53:54 | 2016-07-29   | 09:00:00     | 2016-07-31    |          1 |       14400 |         0|
| r917       | h_19     | c_224       | 2016-10-03 14:03:33 | 2016-10-23   | 10:30:00     | 2016-10-25    |          4 |       96000 |         0|
| r3212      | h_73     | c_806       | 2017-07-26 04:45:47 | 2017-07-30   | 10:00:00     | 2017-08-01    |          2 |       79600 |         0|
| r2477      | h_65     | c_620       | 2017-02-07 11:08:26 | 2017-02-07   | 11:00:00     | 2017-02-08    |          2 |       16800 |         0|
| r2457      | h_206    | c_614       | 2017-01-18 08:29:43 | 2017-01-31   | 09:00:00     | 2017-02-02    |          1 |       48400 |    108600|