Window関数を使って、日付が含まれるテーブルの、日付の”飛び”をチェックする
検証環境
PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
今回集計対象のデータ
日ごとの電話番号別のパケット使用量です。日付の"飛び"とは、20210205→20210203の様に、日付が連続していないことです。なお、以下のデータには"飛び"はありません。
servicecode |
packet_date |
packet_withcoupon |
packet_withoutcoupon |
AAAAAAAAAAA |
20210205 |
0 |
1 |
CCCCCCCCCCC |
20210205 |
1 |
0 |
DDDDDDDDDDD |
20210205 |
0 |
0 |
BBBBBBBBBBB |
20210205 |
273 |
1 |
AAAAAAAAAAA |
20210204 |
0 |
1 |
CCCCCCCCCCC |
20210204 |
1 |
1 |
DDDDDDDDDDD |
20210204 |
0 |
0 |
BBBBBBBBBBB |
20210204 |
467 |
1 |
AAAAAAAAAAA |
20210203 |
1 |
1 |
CCCCCCCCCCC |
20210203 |
1 |
1 |
DDDDDDDDDDD |
20210203 |
1 |
0 |
BBBBBBBBBBB |
20210203 |
250 |
1 |
AAAAAAAAAAA |
20210202 |
1 |
1 |
CCCCCCCCCCC |
20210202 |
1 |
1 |
DDDDDDDDDDD |
20210202 |
1 |
1 |
BBBBBBBBBBB |
20210202 |
273 |
1 |
AAAAAAAAAAA |
20210201 |
1 |
0 |
CCCCCCCCCCC |
20210201 |
1 |
0 |
DDDDDDDDDDD |
20210201 |
1 |
0 |
BBBBBBBBBBB |
20210201 |
110 |
0 |
テーブルデータを集計し、レコードの日付の1つ前の日付と、その差分を表示する
Window関数(lag)を利用して、1つ前のレコードを取得します。
select
packet_date
,lag(packet_date, 1) over(order by packet_date) prev_date
,TO_TIMESTAMP(packet_date, 'YYYYMMDD') - TO_TIMESTAMP(lag(packet_date, 1) over(order by packet_date), 'YYYYMMDD') date_diff
from
(
select
packet_date
from
t_packet_volumes
group by
packet_date
order by
packet_date
) a
order by
packet_date desc
実行結果はこんな感じです。
packet_date |
prev_date |
date_diff |
20210205 |
20210204 |
1 day |
20210204 |
20210203 |
1 day |
20210203 |
20210202 |
1 day |
20210202 |
20210201 |
1 day |
20210201 |
20210131 |
1 day |
上記のSQLを応用して、2日以上間隔が開いている日付を求める
上記のwhere句に
where
lag(packet_date, 1) over(order by packet_date) prev_date > '1days'
としたら、where句にはWindow関数は使えん、と怒られたので、美しくないけど以下のように書いてみた
select
*
from
(
select
packet_date
,lag(packet_date, 1) over(order by packet_date) prev_date
,TO_TIMESTAMP(packet_date, 'YYYYMMDD') - TO_TIMESTAMP(lag(packet_date, 1) over(order by packet_date), 'YYYYMMDD') date_diff
from
(
select
packet_date
from
t_packet_volumes
group by
packet_date
order by
packet_date
) a
order by
packet_date desc
) b
where
date_diff > '1days'
order by
packet_date desc
from句内にlagを使ったSQLの結果を入れて、それに対して絞り込みを入れました。
実行結果は以下の通り。想定通りの結果が得られました。
packet_date |
prev_date |
date_diff |
20201202 |
20201130 |
2 days |