管理人Kのひとりごと

デジモノレビューやプログラミングや写真など

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

from句内にlagを使ったSQLの結果を入れて、それに対して絞り込みを入れました。
実行結果は以下の通り。想定通りの結果が得られました。

packet_date prev_date date_diff
20201202 20201130 2 days

参考にしました