sql - 每个特定间隔的 SQLite 组

Question

假设我有一个包含条目的表，并且这些条目包含时间戳列（如 Long），它告诉我们该条目何时到达表中。

现在，我想做一个 SELECT 查询，我想知道有多少条目以具体的频率出现在选定的时间间隔内。

例如：间隔是从 27.10.2020 到 30.10.2020，频率是 6 小时。查询的结果将告诉我在 6 小时组中此间隔中有多少条目。

喜欢：

• 27.10.2020 00:00:00 - 27.10.2020 06:00:00：2 个条目
• 27.10.2020 06:00:00 - 27.10.2020 12:00:00 : 5 个条目
• 27.10.2020 12:00:00 - 27.10.2020 18:00:00 : 0 个条目
• 27.10.2020 18:00:00 - 28.10.2020 00:00:00 : 11 个条目
• 28.10.2020 00:00:00 - 28.10.2020 06:00:00：8 个条目等...

频率参数可以插入小时、天、周...

谢谢大家的帮助！

标签： sqldatabasesqlite

Answer 1

首先，您需要一个CTE返回时间间隔的递归：

with cte as (
  select '2020-10-27 00:00:00' datestart,
         datetime('2020-10-27 00:00:00', '+6 hour') dateend
  union all
  select dateend,
         min('2020-10-30 00:00:00', datetime(dateend, '+6 hour'))
  from cte 
  where dateend < '2020-10-30 00:00:00'
)

然后你必须把它LEFT加入CTE到表中并聚合：

with cte as (
  select '2020-10-27 00:00:00' datestart,
         datetime('2020-10-27 00:00:00', '+6 hour') dateend
  union all
  select dateend,
         min('2020-10-30 00:00:00', datetime(dateend, '+6 hour'))
  from cte 
  where dateend < '2020-10-30 00:00:00'
)
select c.datestart, c.dateend, count(t.datecol) entries
from cte c left join tablename t
on datetime(t.datecol, 'unixepoch') >= c.datestart and datetime(t.datecol, 'unixepoch') < c.dateend
group by c.datestart, c.dateend

将tablename和替换datecol为表和日期列的名称。
如果您的日期列包含毫秒，则将ON子句更改为：

on datetime(t.datecol / 1000, 'unixepoch') >= c.datestart 
and datetime(t.datecol / 1000, 'unixepoch') < c.dateend