Как сформулировать запрос?

Question

[Стас Заварухин]

Добрый день! Есть таблица (tokens) в ней следующие колонки: ts(datetime), symbol(varchar), id. Выглядит следующим образом:
1 | 2019.01.01 21:14:00.000 | ADABTC
2 | 2019.01.01 21:15:00.000 | ADABTC
3 | 2019.01.01 21:16:00.000 | ADABTC
4 | 2019.01.01 21:18:00.000 | ADABTC
5 | 2019.01.01 21:19:00.000 | ADABTC
6 | 2019.03.02 20:11:00.000 | ETHBTC
7 | 2019.01.01 20:12:00.000 | ETHBTC
8 | 2019.01.01 20:13:00.000 | ETHBTC
9 | 2019.01.01 20:15:00.000 | ETHBTC

Как вы можете видеть, дата идет раз в минуту с некоторыми исключениями. То есть в бд существуют пропуски в несколько минут. Мне нужно каким-то образом получить все пропущенные даты\времена. Второй день голову ломаю

Comments

[EVGENY T.]

Сдаётся мне, тут запросом не отделаешься, а если и можно, то лучше не надо - заковыристо выйдет. Писать надо процедурно. Или в хранимке с курсором. Или в обычном языке запихнуть данные в структуру и анализировать в цикле.

Answer 1

[Hedy]

Для SQLSERVER можно так (идея построить таблицу возможных значений и сджоинить с вашей таблицу на предмет пропусков):

--строим все возможные значения даты-времени через рекурсивную CTE
with dates as (
	select convert(datetime, '20190101') AS cdate --начальная дата
	UNION ALL
	SELECT DATEADD(minute, 1, cdate) AS cdate --прибавляем по минуте
	FROM dates
	WHERE cdate <'20190102' --конечная дата
) 
select * from dates d 
left join tokens t
on t.ts = d.cdate
where t.ts is null --смотрим пропуски
option (maxrecursion 0)

Comments

[Стас Заварухин]

Все работает, за исключением одного но. Он не показывает symbol. Как это сделать?

[Станислав Валсинатс]

select * from dates d
Тут только из таблицы dates выводит.

попробуйте
select dates.cdate , tokens.ts, tokens.symbol, tokens.id from dates d, tokens t

Answer 2

[antares4045]

Маркировать начала пропусков можно при помощи выбора всех дат, для которых дата + минута не существует. Затем оберните это в подзапрос для запроса ищущего ближайшую минимальную дату, большую для каждой даты из подзапроса, так вы найдёте концы интервалов пропусков.

UPD:

Признаться честно, полностью воссоздавать тест мне в лом, но для базы

CREATE TABLE nums (num INTEGER)
INSERT INTO nums (num) VALUES (1),(2),(4),(5),(10),(11)

запрос отработал верно

SELECT mins.num + 1 startInterval, MIN(maxs.num) -1 endInterval
FROM (SELECT num
      FROM nums outerRequest 
      WHERE NOT EXISTS (SELECT num 
                        FROM nums innerRequest 
                        WHERE innerRequest.num = outerRequest.num + 1)) mins,
     (SELECT num 
      FROM nums maxs) maxs
WHERE maxs.num > mins.num
GROUP BY mins.num