Window関数で日付の”飛び”をチェックする(PostgreSQL)

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で1つ前のレコードの値(1つ前の日付)を取得 */
   ,lag(packet_date, 1) over(order by packet_date) prev_date
　/* packet_dateは文字列型のため、Timestamp型にキャストの上差分を計算 */
   ,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