时间戳

首页 > 解决方案 > SQLite：创建相等的日期范围并根据它们查询数据？

sqlite - SQLite：创建相等的日期范围并根据它们查询数据？

问题描述

我在带有架构的表中有数据：

Id INTEGER,
date DATETIME,
value REAL

id 是主键，我在日期列上有一个索引，以加快查询特定日期范围内的值。

如果我需要在特定的开始日期和结束日期之间有 N 个相等的日期范围，并查询每个日期范围的聚合数据，我该怎么办？

例如：

在这种情况下，相等的日期间隔应该是：

并且查询应该聚合这些间隔之间的所有值（AVG），所以我希望在执行后总共有 5 行。

也许与CTE有关？

标签： sqlitecommon-table-expression

解决方案

有两种方法可以做到这一点。它们都使用递归 ctes，但返回不同的结果。

第一个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;

结果：

| 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 |

第二个用数学建立组：

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

结果：

| 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 |

在这两种情况下，您都可以通过将表连接到 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

推荐阅读