SQL Python 前処理

Standard SQL CookBook - 5/N

ユーザーごとの開始日, 終了日が記録されたレコードから開始日から終了日までのDate sequenceを作成する

公開日: 2022-07-15
更新日: 2022-08-01

  概要
目的 ユーザーごとの開始日, 終了日が記録されたレコードから開始日から終了日までのDate sequenceを作成する
分類 SQL Cookbook
SQL Standard SQL
環境 BigQuery

Table of Contents

問題設定

(user_id, 開始日, 終了日)のレコードからなるデータが以下のように与えられたとします.

1
2
3
4

(1,'2020-09-01','2020-11-01'),
(2,'2020-09-01','2021-01-01'),
(3,'2020-10-01','2021-02-01'),
(4,'2020-11-01','2021-01-01')

このとき,以下のようにユーザーごとに開始日から終了日までの１ヶ月ごとのDate sequenceを作成したいとします.

user_id observed_month
1 2020-09-01
1 2020-10-01
1 2020-11-01
2 2020-09-01
2 2020-10-01
2 2020-11-01
2 2020-12-01
2 2021-01-01
3 2020-10-01
3 2020-11-01
3 2020-12-01
3 2021-01-01
3 2021-02-01
4 2020-11-01
4 2020-12-01
4 2021-01-01

SQL実行例の紹介

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

WITH 
    raw_data AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<user_id INT64, min_date DATE, max_date DATE>
                (1,'2020-09-01','2020-11-01'),
                (2,'2020-09-01','2021-01-01'),
                (3,'2020-10-01','2021-02-01'),
                (4,'2020-11-01','2021-01-01')
                ])     
    ),
    user_date_table AS(
    SELECT
        user_id,
        GENERATE_DATE_ARRAY(min_date, max_date, INTERVAL 1 MONTH) AS date_range
    FROM
        raw_data
    )
SELECT
  user_id,
  date_range_parsed AS observed_month
FROM
    user_date_table,
    UNNEST(date_range) AS date_range_parsed

UNNESTのなかでarrayを作成し,explodeするという観点から以下のクエリも実行可能です(Special Thanks to SGSKさん)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WITH 
    raw_data AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<user_id INT64, min_date DATE, max_date DATE>
                (1,'2020-09-01','2020-11-01'),
                (2,'2020-09-01','2021-01-01'),
                (3,'2020-10-01','2021-02-01'),
                (4,'2020-11-01','2021-01-01')
                ])     
    )
SELECT
    user_id,
    date_range_parsed AS observed_month
FROM
    raw_data,
    UNNEST(GENERATE_DATE_ARRAY(min_date, max_date, INTERVAL 1 MONTH)) AS date_range_parsed

Python実行例の紹介

パターン1: .apply(pd.Series).stack()を活用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

## import
import pandas as pd

## Data作成
col_names = ['user_id', 'min_date', 'max_date']
record = [(1,'2020-09-01','2020-11-01'),
          (2,'2020-09-01','2021-01-01'),
          (3,'2020-10-01','2021-02-01'),
          (4,'2020-11-01','2021-01-01')]

df = pd.DataFrame(record, columns=col_names)

## ユーザーごとのDate sequence作成
df['date_range'] = df.loc[:, ['min_date','max_date']].apply(
                    lambda x:pd.date_range(x.min_date, x.max_date , freq='MS'),
                    axis=1
                    )
res = df.set_index(['user_id'])['date_range'].apply(pd.Series).stack()
res = res.reset_index(level=0)
res.columns = ['user_id','observed_month']
res

パターン2: pd.Dataframe().explode()を活用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

## import
import pandas as pd

## Data作成
col_names = ['user_id', 'min_date', 'max_date']
record = [(1,'2020-09-01','2020-11-01'),
          (2,'2020-09-01','2021-01-01'),
          (3,'2020-10-01','2021-02-01'),
          (4,'2020-11-01','2021-01-01')]

df = pd.DataFrame(record, columns=col_names)

## ユーザーごとのDate sequence作成
df['date_range'] = df.loc[:, ['min_date','max_date']].apply(
                    lambda x:pd.date_range(x.min_date, x.max_date , freq='MS'),
                    axis=1
                    )
df.explode('date_range').reset_index(drop=True)

Then,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

	user_id	min_date	max_date	date_range
0	1	2020-09-01	2020-11-01	2020-09-01
1	1	2020-09-01	2020-11-01	2020-10-01
2	1	2020-09-01	2020-11-01	2020-11-01
3	2	2020-09-01	2021-01-01	2020-09-01
4	2	2020-09-01	2021-01-01	2020-10-01
5	2	2020-09-01	2021-01-01	2020-11-01
6	2	2020-09-01	2021-01-01	2020-12-01
7	2	2020-09-01	2021-01-01	2021-01-01
8	3	2020-10-01	2021-02-01	2020-10-01
9	3	2020-10-01	2021-02-01	2020-11-01
10	3	2020-10-01	2021-02-01	2020-12-01
11	3	2020-10-01	2021-02-01	2021-01-01
12	3	2020-10-01	2021-02-01	2021-02-01
13	4	2020-11-01	2021-01-01	2020-11-01
14	4	2020-11-01	2021-01-01	2020-12-01
15	4	2020-11-01	2021-01-01	2021-01-01

References

オンラインマテリアル

Share Buttons
 Share on:
Feature Tags
Leave a Comment
(注意：GitHub Accountが必要となります）
CONTENTS