SageMakerからRedshiftやSnowflakeに接続してクエリを投げたり、月単位のデータマートに対して集計を行ったりということが最近多いので備忘録です。
状況としては
データマートのテーブル名やcsvファイルのファイル名
に対して複数の期間をfor文で集計を回すことを想定しています。
集計コードの例としては、以下のようなトランザクションのテーブルが
purchase_id | user_id | purchase_price |
---|---|---|
P0001 | USER001 | 1000 |
P0002 | USER002 | 1500 |
P0003 | USER003 | 2000 |
P0004 | USER004 | 1000 |
P0005 | USER001 | 3000 |
P0006 | USER003 | 5000 |
transaction_20210401
transaction_20210501
transaction_20210601
...
のように月単位で存在する状況で
output = pd.DataFrame()
for TARGET_DATE in TARGET_DATE_LIST:
TARGET_DATE = TARGET_DATE.strftime('%Y%m%d')
sql = f"""
select
count(purchase_id) as 購入回数合計
, count(distinct user_id) as 購入人数合計
, sum(purchase_price) as 購入金額合計
from
transaction_{TARGET_DATE}
"""
df = pd.read_sql(sql, con)
df = df.T
df.rename(columns={0:f'{TARGET_DATE}'}, inplace=True)
output = pd.concat([output, df], axis=1)
上記のような集計を行うときにfor文で回す日付のリストを簡単に作成する関数を月初日パターンと月末日パターンで作成しました。
# ライブラリのインポート
from datetime import date
from dateutil.relativedelta import relativedelta
指定した期間の月初日のリストを作成する関数は以下で作成できます。
def get_first_day_list(start_date: date, end_date: date):
"""指定した期間の月初日のリストを作成する関数"""
first_day_list = []
while start_date <= end_date:
first_day_list.append(start_date)
start_date = start_date + relativedelta(months=1)
return first_day_list
# 実行
start_date = date(2021, 4, 1)
end_date = date(2022, 3, 1)
TARGET_DATE_LIST = get_first_day_list(start_date, end_date)
TARGET_DATE_LIST
# 実行結果
[datetime.date(2021, 4, 1),
datetime.date(2021, 5, 1),
datetime.date(2021, 6, 1),
datetime.date(2021, 7, 1),
datetime.date(2021, 8, 1),
datetime.date(2021, 9, 1),
datetime.date(2021, 10, 1),
datetime.date(2021, 11, 1),
datetime.date(2021, 12, 1),
datetime.date(2022, 1, 1),
datetime.date(2022, 2, 1),
datetime.date(2022, 3, 1)]
指定した期間の月末日のリストを作成する関数は以下で作成できます。
def get_last_day_list(start_date: date, end_date: date):
"""指定した期間の月末日のリストを作成する関数"""
last_day_list = []
while start_date <= end_date:
last_day_list.append(start_date)
start_date = start_date + relativedelta(days=1) + relativedelta(months=1) - relativedelta(days=1)
return last_day_list
# 実行
start_date = date(2021, 4, 30)
end_date = date(2022, 3, 31)
TARGET_DATE_LIST = get_last_day_list(start_date, end_date)
TARGET_DATE_LIST
# 実行結果
[datetime.date(2021, 4, 30),
datetime.date(2021, 5, 31),
datetime.date(2021, 6, 30),
datetime.date(2021, 7, 31),
datetime.date(2021, 8, 31),
datetime.date(2021, 9, 30),
datetime.date(2021, 10, 31),
datetime.date(2021, 11, 30),
datetime.date(2021, 12, 31),
datetime.date(2022, 1, 31),
datetime.date(2022, 2, 28),
datetime.date(2022, 3, 31)]