SQLite _ 連続日付view作成

SQLite : 日付処理　：　連続日付view作成 = カレンダーテーブル

日付でデータを集計すると、通常は、データの存在する日付しか表示出来ないが、 連続日付で表にしたい場合、以下のような view を作っておくと、便利です。
以下は、SQLite 3.7 で検証しています
参考：日付関数の使い方　Modifiers(修飾子)の指定、SQLite.org Document: datetime function

• まずは連続数値table

  SQLiteにおいては、カラム型名は、あんまり意味はないけど、一応、データの意図がわかりやすいように入れておきます。
  日付を直接持つ必要はなく、初日に、連続整数を足していけばいいので、まず、数値を保持するテーブルを作ります。 数値も、0 から 9 があれば、 cross join で 100, 1000 と増やせるので、これもview にしてしまいます。

  CREATE TABLE  seq10 (num INT);
  INSERT INTO  seq10 values (0);
  INSERT INTO  seq10 values (1);
    --   sqlite は １行ずつ insert なので、 transaction 使って、 0 ～9 の10 行 insert
  INSERT INTO  seq10 values (9);
  
  --  0 ～ 99 の連続数
  create view  seq100
   as  select  n1.num*10 + n2.num  as num 
       from seq10 n1 , seq10 n2 ;
  --  0 ～ 999 の連続数
  create view  seq1000
   as  select  n1.num*100 + n2.num*10 + n3.num  as num 
       from seq10 n1 , seq10 n2 , seq10 n3 ;
  

• 日付作成

  SQLiteの日付計算関数を駆使します。

  • まずは、2014-09-01 特定日付から１ヶ月分。
    0 日から足すので 30日未満まで。
    date関数の日付足し算は '+1 day' という文字列にしないとならないので、文字列連結 || を使っています。

    select  date( '2014-09-01' , '+' || num || ' day' ) as seqdate
      from  seq100  
      where num < 30 ;
    

  • 今週日曜から 7日間

    10日未満なので、seq10テーブルから数値をとることにします。
    strftime の書式 %w は 日曜 0 開始なので、本日の曜日数を引いた日付がstart 、これにさらに連番を足していきます。

     select date('now', '-' || strftime('%w','now') || ' day' , '+'|| num ||' day') as seqdate
      from  seq10 
      where num < 7 ;
    

  • 今週月曜から 7日間

    日曜 の場合 6を引いて、それ以外の日は 本日の曜日数-1 を引いた日付がstart、 ということで、case 文も駆使

     select date('now'
       , '-' || (case when cast(strftime('%w','now') as integer )<1 then 6 else strftime('%w','now')-1 end ) || ' day' 
       , '+'|| num ||' day'
       ) as seqdate
      from  seq10  
      where num < 7 ;
    

    strftime の返値は文字列型で、式の結果を比較に使う場合は型変換はおこなわれず、数値と文字列の比較になり、数値の方が常に小さいと判定されてしまうので、castが必要です。 上記は日曜にならないと発覚しないが、次の月間データで、cast 必要なことが発覚。
    ウィークディ集計は、月曜からが多いと思うので、これも view にしておきましょう。

    create view  calendar_weeklymonday
     as select date('now'
       , '-' || (case when cast(strftime('%w','now') as integer )<1 then 6 else strftime('%w','now')-1 end ) || ' day' 
       , '+'|| num ||' day'
       ) as seqdate
      from  seq10  
      where num < 7 ;
    

• 月間、年間カレンダー view

  開始日固定ではなく、今月や今年であれば、現在日付 'now' を使ってviewに出来ます。

  • 月間カレンダー

    月間の計算は、date('now','start of month') で初日が得られ、初日に１ヶ月足してから１日引くと月末日付けを得て、月間日数が得られます

    create view  calendar_thismonth
     as  select  date( 'now','start of month' , '+' || num || ' day' ) as seqdate
         from seq100  
         where num < cast( strftime('%d' , 'now','start of month', '+1 month','-1 day') as integer ) ;
    

    strftime の返値を比較に使うにはcastが必要です。結構落とし穴ですね、whereの条件節が無効になってると思ったらcastの方でした。SQLite Document読みあさって漸く解る。 SQLite Document Cast

  • 年間カレンダー

    開始日 date('now','start of year') で今年の元旦
    今年の日数は、来年元旦の前日における通年日（元旦からの日数）を %j の書式で取り出します。このように計算すると閏年をいちいち気にする必要がなくなります。

    create view  calendar_thisyear
     as  select  date( 'now','start of year' , '+' || num || ' day' ) as seqdate
         from seq1000  
         where num < cast(strftime('%j' , 'now','start of year', '+1 year','-1 day')  as integer );
    

  • 年間カレンダーからの抽出
    まずは、between で期間指定, ４月から６月末までの四半期抽出
    １月に３ヶ月足すと４月なので、date('now','start year','+3 month')　で今年の４月１日が得られます。

    select seqdate from  calendar_thisyear
      where seqdate between date('now','start year','+3 month')  and date('now','start year','+6 month','-1 day')  ;
    

• 先月今月来月

  年をまたぐ可能性があり、上述の年間カレンダーでは、不足することがあるので、日付関数を使って別途算出します。
  日数計算も、年間通日では、年をまたぐとき計算が面倒になるので、Julian day number を使ってみました。小数部があるため、時刻誤差でちょっと嵌りましたが、roundで回避。

  create view  calendar_prenext 
   as  select  date( 'now','start of month','-1 month' , '+' || num || ' day' ) as seqdate
       from seq100 
       where num < (round( strftime('%J' , 'now','start of month', '+2 month','-1 day') )
                 -  round( strftime('%J' , 'now','start of month', '-1 month' ,'-1 day') )
                   ) ;
  

• さて、当初の目的、別テーブルの集計結果を日付連続にしたい。

  まず、これまでに作った seq10テーブルと他のview を一つのdatabaseファイルにまとめておくと、目的集計databaseへ attach することで、いつでも使えるようになります。

  • 以下、ファイル名を 'calendarview.sqlite3' とした場合の attach構文

    ATTACH DATABASE 'calendarview.sqlite3' AS calendar ;
    --  なるべくなら、ファイル名は、プレースホルダーで絶対パス指定するほうがよい。
    

    以降viewには、database名 calendar を付けて参照します。
  • 集計文例 テーブル名 test_tbl (testdate date , data string )
    今週月曜から７日分
    期間指定にも calendar_weeklymonday で作った物を利用

    select testdate , count(*) as cnt from  test_tbl
        where testdate between ( select min(seqdate) from calendar.calendar_weeklymonday )
                       and ( select max(seqdate) from calendar.calendar_weeklymonday  )
        group by testdate  ;
    

  • testdateの集計で日付に抜けがあっても、７日分の行を出力するには、calendar_weeklymonday に上記サブクエリテーブルをleft join
    coalesceで、gather側に行が無い時を 0　表示。

    select cal.seqdate ,coalesce( gather.cnt, 0)
      from calendar.calendar_weeklymonday as cal
      left join ( select testdate , count(*) as cnt from  test_tbl
             where testdate between ( select min(seqdate) from calendar.calendar_weeklymonday ) 
                       and ( select max(seqdate) from calendar.calendar_weeklymonday )
             group by testdate
      ) as gather
      on cal.seqdate = gather.testdate  ;