Блог

давайте предположим, что у меня есть таблица с записями, и эти записи содержат столбец timestamp (as 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 записи и т.д…

Параметр частоты может быть вставлен в часах, днях, неделях…

Спасибо всем за помощь!

Сначала вам нужен рекурсивный 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 именами вашей таблицы и столбца даты.
Если ваш столбец date содержит миллисекунды, измените ON предложение на это:

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

Вот один из вариантов:

 select 
    datetime((strftime('%s', ts) / (6 * 60 * 60)) * 6 * 60 * 60, 'unixepoch') newts,
    count(*) cnt
from mytable
where ts >= '2020-10-27' and ts < '2020-10-30'
group by newts
order by newts
  

ts представляет столбец datetime в вашей таблице. У SQLite нет long типа данных, поэтому предполагается, что у вас есть законная дата, сохраненная как text .

Логика запроса состоит в том, чтобы превратить дату в временную метку эпохи, а затем округлить ее до 6 часов, что представлено 6 * 60 * 60 .