CODEWITHSUNDEEP
sqlitecommon-table-expression
Daniel
Daniel

Reputation: 2592

SQLite: create equal date ranges and query data based on them?

I have datas in a table with schema:

Id INTEGER,
date DATETIME,
value REAL

id is primary key, and I have an index on date column to speed up querying values within a specific date range.

What should I do if I need N equal date ranges between specific start and end dates, and query aggregated datas for each date range?

For example:

In this case equal date intervals should be:

And the query should aggregate all values (AVG) in between those intervals, so I would like to have 5 total rows after the execution.

Maybe something with CTE?

Upvotes: 0

Views: 112

Answers (1)

forpas
forpas

Reputation: 164099

There are 2 ways to do it. They both use recursive ctes but return different results.

The 1st one with NTILE():

with 
  dates as (select '2015-01-01' mindate, '2019-12-31' maxdate),
  alldates as (
    select mindate date from dates
    union all
    select date(a.date, '1 day') 
    from alldates a cross join dates d
    where a.date < d.maxdate
  ),
  groups as (
    select *, ntile(5) over (order by date) grp
    from alldates
  ),
  cte as (
    select min(date) date1, max(date) date2
    from groups
    group by grp
  )
select * from cte;

Results:

| date1      | date2      |
| ---------- | ---------- |
| 2015-01-01 | 2016-01-01 |
| 2016-01-02 | 2016-12-31 |
| 2017-01-01 | 2017-12-31 |
| 2018-01-01 | 2018-12-31 |
| 2019-01-01 | 2019-12-31 |

And the 2nd builds the groups with math:

with 
  dates as (select '2015-01-01' mindate, '2019-12-31' maxdate),
  cte1 as (
    select mindate date from dates
    union all
    select date(
      c.date, 
      ((strftime('%s', d.maxdate) - strftime('%s', d.mindate)) / 5) || ' second'
    )
    from cte1 c inner join dates d
    on c.date < d.maxdate
  ),
  cte2 as (
    select date date1, lead(date) over (order by date) date2
    from cte1
  ),
  cte as (
    select date1, 
      case 
        when date2 = (select maxdate from dates) then date2
        else date(date2, '-1 day')
      end date2
    from cte2
    where date2 is not null
  )  
select * from cte

Results:

| date1      | date2      |
| ---------- | ---------- |
| 2015-01-01 | 2015-12-31 |
| 2016-01-01 | 2016-12-30 |
| 2016-12-31 | 2017-12-30 |
| 2017-12-31 | 2018-12-30 |
| 2018-12-31 | 2019-12-31 |

In both cases you can get the averages by joining the table to the cte:

select c.date1, c.date2, avg(t.value) avg_value 
from cte c inner join tablename t
on t.date between c.date1 and c.date2
group by c.date1, c.date2

Upvotes: 1

Related Questions