

SQLite3を使用し、以下のようなデータを想定しているのですが、「type_idが同一で、日付が連番となっているデータについて、(type_id、開始日、終了日）を抽出する」ことはできますでしょうか。

◇data  
・id : Integer(primary key)  
・type_id : Integer  
・date : Date

実際のデータは、  
| id | type_id | date |  
| 1 | 1 | date(2017, 1, 1) |  
| 2 | 1 | date(2017, 1, 1) |  
| 3 | 1 | date(2017, 1, 2) |  
| 4 | 1 | date(2017, 1, 2) |  
| 5 | 1 | date(2017, 1, 3) |  
| 6 | 1 | date(2017, 1, 3) |  
| 7 | 1 | date(2017, 1,17) |  
| 8 | 1 | date(2017, 1,18) |  
| 9 | 1 | date(2017, 1,19) |  
| 10 | 1 | date(2017, 2, 1) |  
...  
| ? | 2 | date(2017, 1, 1) |  
...  
のようになっています。

上の例で、期待するレスポンスは、

(1, date(2017, 1, 1), date(2017, 1, 3))  
(1, date(2017, 1,17), date(2017, 1,19))  
...

といった具合です。

どうぞよろしくお願いいたします。


CC BY-SA 3.0
2017-11-28T08:43:37.537
39904
2017-11-30T08:31:13.473
26359
question
sql sqlite
SQLiteで日付が連続になっているデータをまとめたい


  * 1日後のエントリがないエントリを探す(*1)
  * (*1)のエントリそれぞれについて、過去、かつ最近のエントリを探し、その日数差を求める
  * (*1)と元のテーブルを結合し、過去、かつ上記日数差以内のエントリごとにまとめる

とすれば求まりますが、多数回の自己結合操作をしなければならない高コストなクエリを発行する必要があります

共通テーブル式の再帰を用いれば、より簡潔な記述ができると思います  
<https://sqlite.org/lang_with.html>

[code]

    SELECT
      MIN(T2.id) AS id,
      MIN(T2.date) AS date_min,
      MAX(T2.date) AS date_max
    FROM (
      SELECT
        T1.id,
        T1.type_id,
        T1.date,
        COALESCE(T1.date - MAX(T2.date), 365) AS diff
      FROM (
        /* ---- 同じ ---- */
        SELECT
          MIN(T1.id) AS id,
          MIN(T1.type_id) AS type_id,
          MIN(T1.date) AS date
        FROM
          my_table T1
        LEFT JOIN
          my_table T2 ON (T1.type_id = T2.type_id AND T1.date + 1 = T2.date)
        GROUP BY
          T1.type_id, T1.date
        HAVING
          MIN(T2.date) IS NULL
        /* ---- 同じ ---- */
      ) T1
      LEFT JOIN (
        /* ---- 同じ ---- */
        SELECT
          MIN(T1.id) AS id,
          MIN(T1.type_id) AS type_id,
          MIN(T1.date) AS date
        FROM
          my_table T1
        LEFT JOIN
          my_table T2 ON (T1.type_id = T2.type_id AND T1.date + 1 = T2.date)
        GROUP BY
          T1.type_id, T1.date
        HAVING
          MIN(T2.date) IS NULL
        /* ---- 同じ ---- */
      ) T2 ON (T1.type_id = T2.type_id AND T1.date > T2.date)
      GROUP BY
        T1.id,
        T1.type_id,
        T1.date
    ) T1
    LEFT JOIN
      my_table T2 ON (T1.type_id = T2.type_id AND T1.date >= T2.date AND T1.date - diff < T2.date)
    GROUP BY
      T1.id
    ORDER BY
      T1.id
    ;
    
[/code]


CC BY-SA 3.0
2017-11-30T08:31:13.473
39948
2017-11-30T08:31:13.473
9796
39904
answer