こんにちは、まさかめです。
今回はRedShiftでleadやlagを使って次月の値を正確に取得する方法について解説していきます。
やりたいこととデータ例チェック
やりたいこと
月ごとのデータを抽出する際に、次月の値というカラムを作成することを目指します。
最終的なアウトプットイメージは以下のようなものを想定しています。
| month | value | next_value |
| 2022-01 | 10 | 20 |
| 2022-02 | 20 | 30 |
| 2022-03 | 30 | 40 |
| 2022-04 | 40 | 50 |
| 2022-05 | 50 | 60 |
| 2022-06 | 60 | 70 |
| 2022-07 | 70 | 80 |
| 2022-08 | 80 | 90 |
| 2022-09 | 90 | 100 |
| 2022-10 | 100 | 110 |
| 2022-11 | 110 | 120 |
| 2022-12 | 120 | null |
テーブル確認
sample_tableというテーブルに月と値が入っている状態だとします。
単純に2022年分のデータを抽出すると以下のようなイメージです。
select
month
, value
from sample_table
where month between '2022-01' and '2022-12'
ここで、sample_tableが以下①②の2つの場合を考えます。
- 毎月レコードが存在する場合
- レコードの存在しない月がある場合
毎月レコードが存在する場合
毎月レコードが存在するので、2022年12ヶ月分のデータとなっています。
| month | value |
| 2022-01 | 10 |
| 2022-02 | 20 |
| 2022-03 | 30 |
| 2022-04 | 40 |
| 2022-05 | 50 |
| 2022-06 | 60 |
| 2022-07 | 70 |
| 2022-08 | 80 |
| 2022-09 | 90 |
| 2022-10 | 100 |
| 2022-11 | 110 |
| 2022-12 | 120 |
レコードの存在しない月がある場合
レコードが存在しない月があり、4月と10月のレコードがないため10レコードのデータとなっています。
| month | value |
| 2022-01 | 10 |
| 2022-02 | 20 |
| 2022-03 | 30 |
| 2022-05 | 50 |
| 2022-06 | 60 |
| 2022-07 | 70 |
| 2022-08 | 80 |
| 2022-09 | 90 |
| 2022-11 | 110 |
| 2022-12 | 120 |
Lead関数を用いて次月の値を取得する方法
テーブルの中身次第でやり方が分かれます。
- 毎月レコードが存在する場合
- レコードの存在しない月がある場合
毎月レコードが存在する場合
毎月レコードが存在する場合、
つまり1月から12月まですべてのレコードがヌケモレなく存在している場合は、
Lead関数を用いて以下のように抽出可能です。
select
month
, value
, lead(value, 1) over (order by month) as next_value
from sample_table
where month between '2022-01' and '2022-12'
| month | value | next_value |
| 2022-01 | 10 | 20 |
| 2022-02 | 20 | 30 |
| 2022-03 | 30 | 40 |
| 2022-04 | 40 | 50 |
| 2022-05 | 50 | 60 |
| 2022-06 | 60 | 70 |
| 2022-07 | 70 | 80 |
| 2022-08 | 80 | 90 |
| 2022-09 | 90 | 100 |
| 2022-10 | 100 | 110 |
| 2022-11 | 110 | 120 |
| 2022-12 | 120 | null |
レコードの存在しない月がある場合
先程紹介した4月と10月のレコードが存在しないテーブルに対して、
単純にLead関数を用いると以下のようになります。
select
month
, value
, lead(value, 1) over (order by month) as next_value
from sample_table
where month between '2022-01' and '2022-12'
| month | value | next_value |
| 2022-01 | 10 | 20 |
| 2022-02 | 20 | 30 |
| 2022-03 | 30 | 50 |
| 2022-05 | 50 | 60 |
| 2022-06 | 60 | 70 |
| 2022-07 | 70 | 80 |
| 2022-08 | 80 | 90 |
| 2022-09 | 90 | 120 |
| 2022-11 | 110 | 120 |
| 2022-12 | 120 | null |
一見ちゃんと月ごとにデータが取得できていますが、
3月における次月の値が5月の値、
9月における次月の値が11月の値になってしまっています。
これは、4月と10月がレコードとして存在しないため、Lead関数が3月や9月の次の値を5月や11月と認識したため発生しています。
そこで、Lead関数で次のmonthを取得する際に、ひと月先のmonthかどうかを判定するようにします。
select
month
, value
, case when datediff(month, month, lead(month, 1) over (order by month)) = 1 then lead(value, 1) over (order by month) else null end as next_value
from sample_table
where month between '2022-01' and '2022-12'この場合は3月のレコードにおいて、次月が5月で差分がふた月となり、次月の値がNULLになります。
まとめ
Lead関数を使って次月の値を取得する際は、
datediffと組み合わせることで正確に次の月の値を取得できます。
