【Snowflake】lead/lag関数を使用して歯抜けのチェックをしよう

歯抜けのチェックをしたい

日ごとの集計テーブルにおいて、歯抜けとなっている日がどれくらいあるか調べる機会がありました。

シンプルにdistinctをかけるだけでは一目でわかりづらいので、少し工夫してみました。

lead/lag関数についての説明は割愛いたします。

https://docs.snowflake.com/ja/sql-reference/functions/lead

https://docs.snowflake.com/ja/sql-reference/functions/lag

出来上がったクエリ

previous_day：lag関数によって呼び出した1つ前の日付

not_enough：previous_dayとの差（歯抜け日数）

を表しています。

上の例では、

・2023-04-04と2023-04-02の間が1日歯抜け（2023-04-03）

・2023-04-16と2023-04-10の間が5日歯抜け（2023-04-11~2023-04-15）

・2023-04-30と2023-04-21の間が8日歯抜け（2023-04-22~2023-04-29）

ということがわかります。

調査する日付けの範囲がもっと広い場合は、上記クエリをサブクエリ化し「where not_enough > 0」を加えると歯抜けのあるレコードのみ表示することができます。

lead関数を使用するときは（order by $1 desc）とすると同じ結果になります。

上記のクエリの良い点は、大体どのくらい歯抜けがあるか数字で分かるところです。

これからもどんどんリカバリ業務の効率化をしていきたいですね。