日付の羅列からSQLで継続日数を割り出してみた

日の列があれば、継続日数 を動的に割り出したい。 データはだいたいデータベース内に入っている為、SQLで出したい。どうやる？を書きました。 あんまりどこにも書いてないんだよねぇ。ほぼSQLパズル。

※ Redshiftを対象としています。

元テーブルと参考データ

create

create table continued ( date Date );
insert into continued values ('2017-01-01'),('2017-01-02'),('2017-01-03'),('2017-01-05'), ('2017-01-06'),('2017-01-07'), ('2017-01-09'), ('2017-01-11');  

ここから割り出す

    date    
------------
 2017-01-01
 2017-01-02
 2017-01-03
 2017-01-05
 2017-01-06
 2017-01-07
 2017-01-09
 2017-01-11

開始日を割り出す

開始日を割り出すには、対象日の前の日に居なかったら開始とみなせる。 その為、開始日対象日をFで継続中をTと出す。

create table continued_tf as 
select a.date, case when ( select 'T' as TF  from continued b where b.date = (a.date - 1)) = 'T' then 'T' else 'F' end  as TF from continued a;

select 内でサブクエリってパワー使いそうであまり使いたくないけど、仕方ないね。 この時点で地獄感ある。

    date    | tf 
------------+----
 2017-01-01 | F
 2017-01-02 | T
 2017-01-03 | T
 2017-01-05 | F
 2017-01-06 | T
 2017-01-07 | T
 2017-01-09 | F
 2017-01-11 | F

最も近い開始日を各日付に割り当てる

あんまりこういういSQLは好きじゃないけど仕方ないかな・・。 ユーザー毎に作りたかったらユーザー毎のIDを指定すればよろし。

with aaa as (
  select a.date, a.tf,  
  (
    select
      b.date start_date 
    from continued_tf b
    where b.date <= a.date 
      and b.tf = 'F'
    order by b.date desc
    limit 1
  ) start_date
  from continued_tf a
)
select f.start_date, f.date, f.tf, datediff('days', f.start_date, f.date ) as continue 
from aaa f

postgresql の場合はcontinueを以下に書き換え。

( f.date - f.start_date ) as continue 

↓ こんな感じで完了

 start_date |    date    | tf | continue 
------------+------------+----+----------
 2017-01-01 | 2017-01-01 | F  |        0
 2017-01-01 | 2017-01-02 | T  |        1
 2017-01-01 | 2017-01-03 | T  |        2
 2017-01-05 | 2017-01-05 | F  |        0
 2017-01-05 | 2017-01-06 | T  |        1
 2017-01-05 | 2017-01-07 | T  |        2
 2017-01-09 | 2017-01-09 | F  |        0
 2017-01-11 | 2017-01-11 | F  |        0

♡SQL