blog

SQLite3で再帰クエリを使ってログを調べる

Published:

By nob

Category: Posts

Tags: SQLite3

動機

前回 、アクセスログをSQLiteのデータベースに保存してみたのだが、ログローテーション( newsyslog.8 )があるので欠損がないか気になった。

特定の間隔の日時とデータベースに格納されているアクセスログの日時を比較して欠損を調べたい。

前提

software version
SQLite3 3.44.2

手順

以下の手順はどうかと考えた。

  1. 調査対象の日時を列挙する
  2. アクセスログの日時を列挙する
  3. Outer JoinまたはNot Existsで欠損を調べる

調査対象の日時を列挙する

日と時をCross Joinすれば良さそうであるので 

with day as (
    select '01' as m
    union
    select '02'
    union
    -- ...
),
hour as (
    -- ...
)
select * from day, hour;

とすれば良さそうである。だが入力が大変面倒である。 map ・・・。

一時表でも作れたら、しかし権限が。あるいはそういう用途を見越して本番環境にそれっぽく使えるマスタを仕込んでおくというアイデアが無い訳では無い。が、そんなことを言っても今無いものは仕方ない。

とか何とか、あれこれ想像して検索してみる。まあ、無いだろうけど、「階層問い合わせ」かな・・・と。

あった。

まさに求めていたものである。 

with recursive hours_to_check(hour) as (
    select
        datetime('2024-06-26 00:00:00') as hour
    union all
    select
        datetime(hour, '+1 hour') as hour
    from
        hours_to_check
    where
        hour < datetime('now', 'localtime')
)
select * from hours_to_check;

ところで 

datetime()

と入力して結果が変だなと思ったら、 SQLiteのdatetimeの日時のデフォルトはUTC だった。

そういえば昔 FreeRADIUS ( raddb/mods-config/sql/main/sqlite/process-radacct-new-data-usage-period.sh )を見てそんなことを考えたような記憶がある。

アクセスログの日時を列挙する

これはDISTINCTすれば良さそうである。

欠損を調べる

日付を日時に変換して結合する。 

with recursive hours_to_check(hour) as (
    select
        datetime('2024-06-26 00:00:00') as hour
    union all
    select
        datetime(hour, '+1 hour') as hour
    from
        hours_to_check
    where
        hour < datetime('now', 'localtime')
),
hour_check as (
    select
        strftime('%Y%m%d%H', hour) as h
    from
        hours_to_check
),
hour_access as (
    select
        distinct(strftime('%Y%m%d%H', time)) as h
    from
        access
)
select
    *
 from
    hour_check c
 left outer join
    hour_access a
 on
    c.h = a.h
order by
    c.h;