BigQueryを用いたデータ分析で頻繁に出てくるSQL文をここにまとめます。
各自のデータベースに合わせてお使いください。
##前提
今回用いるテーブルのスキーマとデータ例です。
テーブル名：items
| フィールド名 | データ型 | 説明 |
|---|---|---|
| user_id | STRING | ユーザ ID |
| order_id | STRING | 受注番号 |
| total_price | INTEGER | 購入合計金額 |
| item_id | STRING | 商品 ID |
| item_name | STRING | 商品名 |
| category_name | STRING | カテゴリ名 |
| purchased_at | DATE | 購入日 |
| 行 | user_id | order_id | total_price | item_id | item_name | category_name | purchased_at |
|---|---|---|---|---|---|---|---|
| 1 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-01 |
| 2 | 62178 | 272324 | 3830 | 423852 | ピカチュウの帽子 | 帽子 | 2021-11-01 |
| 3 | 43532 | 272325 | 3830 | 940234 | コダックのぬいぐるみ | ぬいぐるみ | 2021-11-01 |
| 4 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-03 |
| 5 | 28564 | 272327 | 1200 | 435666 | ミュウのキーホルダー | キーホルダー | 2021-11-04 |
| ・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ |
##RFM分析
###R値
欲しい結果：今日の日付から購入日までの日数を算出
R値算出
DATE_DIFF(DATE (CURRENT_DATE("Asia/Tokyo")), CAST (購入日 AS DATE), DAY) AS Recently
例文
SELECT
*,
DATE_DIFF(DATE (CURRENT_DATE("Asia/Tokyo")), CAST (purchased_at AS DATE), DAY) AS Recently
FROM
items
結果（本日が2021/11/11と仮定）
| 行 | user_id | order_id | total_price | item_id | item_name | category_name | purchased_at | Recently |
|---|---|---|---|---|---|---|---|---|
| 1 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 10 |
| 2 | 62178 | 272324 | 3830 | 423852 | ピカチュウの帽子 | 帽子 | 2021-11-01 | 10 |
| 3 | 43532 | 272325 | 3830 | 940234 | コダックのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 10 |
| 4 | 62178 | 272326 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-03 | 7 |
| 5 | 28564 | 272327 | 1200 | 435666 | ミュウのキーホルダー | キーホルダー | 2021-11-04 | 6 |
| ・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ |
###F値
欲しい結果：今日までの購入回数を算出
F値算出
DENSE_RANK() OVER (PARTITION BY ユーザーID ORDER BY 購入日 ASC ) AS Frequency
例文
SELECT
*,
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY purchased_at ASC ) AS Frequency
FROM
items
結果
| 行 | user_id | order_id | total_price | item_id | item_name | category_name | purchased_at | Frequency |
|---|---|---|---|---|---|---|---|---|
| 1 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 1 |
| 2 | 62178 | 272324 | 3830 | 423852 | ピカチュウの帽子 | 帽子 | 2021-11-01 | 1 |
| 3 | 43532 | 272325 | 3830 | 940234 | コダックのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 1 |
| 4 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-03 | 2 |
| 5 | 28564 | 272327 | 1200 | 435666 | ミュウのキーホルダー | キーホルダー | 2021-11-04 | 1 |
| ・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ |
###M値
例：今日の日付から購入日までの日数を算出
F値算出
SUM(購入額) OVER(PARTITION BY ユーザーID ORDER BY 注文ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Monetary
例文
SELECT
*,
SUM(total_price) OVER(PARTITION BY user_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Monetary
FROM
items
結果
| 行 | user_id | order_id | total_price | item_id | item_name | category_name | purchased_at | Monetary |
|---|---|---|---|---|---|---|---|---|
| 1 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 2780 |
| 2 | 62178 | 272324 | 3830 | 423852 | ピカチュウの帽子 | 帽子 | 2021-11-01 | 6610 |
| 3 | 43532 | 272325 | 3830 | 940234 | コダックのぬいぐるみ | ぬいぐるみ | 2021-11-01 | 3830 |
| 4 | 62178 | 272324 | 2780 | 702671 | ピカチュウのぬいぐるみ | ぬいぐるみ | 2021-11-03 | 9390 |
| ・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ | ・・・ |