Athenaを使ってデータ集計をバッチ処理しているのですが、土日の扱いに困るときがあります。
平日しか稼働させないような日次のバッチ処理の場合、月曜の処理では直前の金曜まで取得したいが、火曜～金曜は前日まで取得できれば良い、という状況です。（こういう状況はあまりないかもしれませんが）
素直に、月曜用のクエリと火曜～金曜用のクエリを分ける・Lambdaなどで動的にクエリを書き換える、といった方法もあると思いますが、管理するリソースをなるべく減らしたいという思いで汎用的なクエリが書けないかと思った次第です。
このような期間指定の課題をAthena（SQL）でどうするかを備忘を兼ねて書きます。
前提
以下のようなデータがあるとします。
テーブル名：sample
| timestamp | item |
|---|---|
| 2022-09-27 19:39:00 | A |
| 2022-09-27 19:40:00 | B |
| 2022-09-28 20:40:00 | C |
| … | … |
このようなデータに対して、クエリを実行する当日から、直前の平日までの期間のデータを取得することが目標です。
クエリの方針
- 日時を表すデータをtimestamp型として扱う
- timestamp型のデータから日付を抽出する
- 日付から曜日を抽出する
- 曜日を、平日=1、土日=0と置き換える
- window関数を使い、現在日付から何日目の平日かを集計する
実際のクエリ
まずは上記のsampleテーブルを読み込みます。
ここで、dayとしてtimestampから日付を抽出します。
with input as (
select
timestamp
, date(cast(timestamp as timestamp)) as day
, item
from sample
),
次に、日付を曜日に変換します。
day_of_week関数は、日付を月曜=1～日曜=7として曜日に変換してくれます。
つまり、day_of_weekが1~5の場合は平日になるので、後段での集計のために平日を1、土日を0に変換します。
weekdays as (
select distinct
day
, if(day_of_week(day) < 6, 1, 0) as weekday
from input
),
このweekdaysという句は、日付のマスタ的な位置づけとして、後段のクエリでinput句とjoinするために利用します。
ここまでで、平日を1、土日を0とできたので、あとはカウントするだけです。
今回は集計当日からそれぞれの日までの平日の数を数えたいので、window関数を利用します。
valid_day_count as (
select
day
-- 集計当日からその日までの平日の数
, sum(weekday) over(
order by day desc
rows between unbounded preceding and current row
) as valid_day
from weekdays
),
window関数は見慣れないと圧倒されてしまいますが、上記のクエリの場合over()の中で指定した条件に応じでsum()を実行する、という気持ちです。
ここでは
order by dey descで日付の降順に並べたデータを、rows between unbound preceding and current rowでその行から一番上の行まで（つまり当日まで）を範囲として、その範囲のweekdayの値をsum()するという形です。
weekdayの値は、平日=1、土日=0としているので、valid_dayには集計当日からその日までのあいだの平日の日数が入ります。
ここまできたら、元のデータinputに対してvalid_day_countをjoinしてあげます。
aggregation as (
select i.*, v.valid_day
from input as i
left join valid_day_count as v
on i.day = v.day
),
こうすることで、valid_dayに対して平日のカウント数が設定されているので、必要な平日数を指定すれば土日を考慮した期間のデータを抽出できます。
select *
from aggregation
where
valid_day <= 2
クエリをまとめると以下の通りです。
with input as (
select
timestamp
, date(cast(timestamp as timestamp)) as day
, item
from sample
),
weekdays as (
select distinct
day
, if(day_of_week(day) < 6, 1, 0) as weekday
from input
),
valid_day_count as (
select
day
-- 集計当日からその日までの平日の数
, sum(weekday) over(
order by day desc
rows between unbounded preceding and current row
) as valid_day
from weekdays
),
aggregation as (
select
i.*
, v.valid_day
from input as i
left join valid_day_count as v
on i.day = v.day
)
select *
from aggregation
where
valid_day <= 2