SQLite:基于它们创建相同的日期范围和查询数据？
SQLite:基于它们创建相同的日期范围和查询数据？
提问于 2020-07-11 07:07:45
我在一个具有模式的表中有数据：
Id INTEGER,
date DATETIME,
value REALid是主键，我有一个日期索引列，以加快在特定日期范围内查询值的速度。
如果在特定的开始日期和结束日期之间需要N个相等的日期范围，并查询每个日期范围的聚合数据，我应该做什么？
例如：
- 开始日期:2015-01-01
- 结束日期: 2019-12-31
- N: 5
在这种情况下，相等的日期间隔应该是：
2015年-01~ 2015-12-31
- 2016-01-01 ~ 2016-12-31
- 2017-01-01 ~ 2017-12-31
- 2018-01-01 ~ 2018-12-31
- 2019-01-01 ~ 2019-12-31
并且查询应该在这些间隔之间聚合所有值(AVG)，所以我希望在执行之后有5行。
也许和CTE有什么关系？
Stack Overflow用户
回答已采纳
发布于 2020-07-11 09:46:28
有两种方法可以做到这一点。它们都使用递归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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62846208
复制相关文章
相似问题
腾讯云开发者
