I want to compile data with consecutive dates in SQLite.

I am using SQLite3 to assume the following data, but would it be possible to extract (type_id, start date, end date) for data with the same type_id and serial number?

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

The actual data is
| 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)|
...
is shown in .

In the example above, the expected response is

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

and so on.

I look forward to your kind cooperation.

sql sqlite

2022-09-30 19:29

1 Answers

Find entries that do not have entries after 1 day (*1)
For each entry in (*1), look for past and recent entries and find the number of days difference.
(*1) with the original table and summarize by past and within the above number of days difference

Then you need to issue a high-cost query that requires multiple self-coupling operations

Using the common table recursive, I think it can be more concise.
https://sqlite.org/lang_with.html

SELECT
  MIN(T2.id)ASid,
  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(
    /* ----Same ----*/
    SELECT
      MIN(T1.id)ASid,
      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
    /* ----Same ----*/
  )T1
  LEFT JOIN(
    /* ----Same ----*/
    SELECT
      MIN(T1.id)ASid,
      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
    /* ----Same ----*/
  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
;

2022-09-30 19:29

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656