Заполнение пропусков в данных временных рядов

При работе с данными временных рядов могут возникать пропуски в данных из-за отсутствия данных или бездействия. Обычно мы не хотим, чтобы такие пропуски существовали, когда выполняем запрос к данным. В этом случае клаузула WITH FILL может оказаться очень полезной. Этот гид обсуждает, как использовать WITH FILL, чтобы заполнить пропуски в ваших данных временных рядов.

Настройка

Представим, что у нас есть следующая таблица, которая хранит метаданные об изображениях, сгенерированных сервисом изображений GenAI:

Давайте импортируем некоторые записи:

Запрос по интервалу

Мы собираемся исследовать изображения, созданные между 00:24:03 и 00:24:04 24 марта 2023 года, поэтому давайте создадим некоторые параметры для этих точек времени:

Затем мы напишем запрос, который группирует данные в интервалы по 100 мс и возвращает количество изображений, созданных в этом интервале:

Набор результатов включает только те интервалы, в которых было создано изображение, но для анализа временных рядов мы, возможно, захотим вернуть каждый 100 мс интервал, даже если у него нет записей.

WITH FILL

Мы можем использовать клаузулу WITH FILL, чтобы заполнить эти пробелы. Мы также укажем STEP, который обозначает размер заполняемых интервалов. По умолчанию это 1 секунда для типов DateTime, но мы хотим заполнять пробелы длиной 100 мс, поэтому давайте установим интервал в 100 мс в качестве значения шага:

Мы видим, что промежутки были заполнены нулевыми значениями в колонке count.

WITH FILL...FROM

Тем не менее, по-прежнему существует пропуск в начале временного диапазона, который мы можем исправить, указав FROM:

Как видно из результатов, теперь все интервалы от 00:24:03.000 до 00:24:03.500 также отображаются.

WITH FILL...TO

Однако мы все еще пропускаем некоторые интервалы в конце временного диапазона, которые мы можем заполнить, указав значение TO. TO не включается, поэтому мы добавим небольшое значение к времени окончания, чтобы убедиться, что оно будет включено:

Все пропуски теперь заполнены, и у нас есть записи для каждых 100 мс с 00:24:03.000 до 00:24:05.000.

Кумулятивная статистика

Предположим, теперь мы хотим сохранить кумулятивное количество созданных изображений по интервалам. Мы можем сделать это, добавив колонку cumulative, как показано ниже:

Значения в кумулятивной колонке не работают так, как нам хотелось бы.

WITH FILL...INTERPOLATE

Любые строки, у которых в колонке count стояло значение 0, также имеют 0 в колонке кумулятивной статистики, в то время как мы бы предпочли использовать предыдущее значение в колонке кумулятивной статистики. Мы можем сделать это, используя клаузулу INTERPOLATE, как показано ниже:

Теперь это выглядит гораздо лучше. И чтобы завершить, давайте добавим гистограмму, используя функцию bar, не забывая добавить нашу новую колонку в клаузулу INTERPOLATE.