LoginSignup
52
53

More than 5 years have passed since last update.

@9ryuuuuu(クリュウ)

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

Posted at

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

概要

  • 前処理大全を読み、以下の理由から感銘を受けたものを記載する。

    • データ分析で、これに近いことを頼まれた(このような面倒くさい処理は、データ分析ではよくある)
    • こんなふうにSQLを書けるんだ。めっちゃ楽やんと知った。

  • 詳しくは前処理大全を読むことをおすすめします。

    • データなども「前処理大全」のgithubにあります。

  • 前提

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

内容

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

  • 予約を格納したテーブルから、最も頻出する価格帯を取得する。

  • 正確な価格ではなく、ROUND関数を使って千円の桁で四捨五入をしたものを対象にする。

  • 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

      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

      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

      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

      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

      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

      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
52
53
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
52
53