Блог

Главная - Вопросы по программированию - Как заполнить пропущенные значения за определенный интервал времени

Как заполнить пропущенные значения за определенный интервал времени

#sql #google-bigquery

# #sql #google-bigquery

Вопрос:

У меня есть таблица в формате ниже

 user  timestamp              count  total_count

xyz   01-01-2020 00:12:00    45        45
xyz   01-01-2020 00:27:00    12        57
xyz   01-01-2020 00:29:00    11        68
xyz   01-01-2020 00:53:00    32        100

Я хочу, чтобы данные были с интервалом в 5 минут, как показано ниже (ожидаемый результат)

 user  timestamp              count  total_count

xyz   01-01-2020 00:05:00    0         0
xyz   01-01-2020 00:10:00    0         0
xyz   01-01-2020 00:15:00    45        45
xyz   01-01-2020 00:20:00    0         45
xyz   01-01-2020 00:25:00    0         45
xyz   01-01-2020 00:30:00    23        68
xyz   01-01-2020 00:35:00    0         68
xyz   01-01-2020 00:40:00    0         68
xyz   01-01-2020 00:45:00    0         68
xyz   01-01-2020 00:50:00    0         68
xyz   01-01-2020 00:55:00    32        100

Я пытался

    SELECT
        TIMESTAMP_SECONDS(5*60 * DIV(UNIX_SECONDS(timestamp), 5*60)) timekey,
        SUM(count) AS count,
        MAX(total_count) as total_count
   FROM db.table
   WHERE
        timestamp BETWEEN {{ start_date }}
        AND {{ end_date }}
        AND user = {{ user_id }}
   GROUP BY
        timekey
   ORDER BY
        timekey

Результат приведенного выше запроса:

 user  timestamp              count  total_count

xyz   01-01-2020 00:15:00    45        45
xyz   01-01-2020 00:30:00    23        68
xyz   01-01-2020 00:55:00    32        100

Как я могу заполнить эти недостающие временные метки в приведенном выше запросе и заполнить значения count(с нулями) и total_count(предыдущее ненулевое значение)?

Ответ №1:

Используется generate_timestamp_array() для заполнения пропущенных значений:

 SELECT ts,
       SUM(t.count) AS count,
       MAX(t.total_count) as total_count
FROM UNNEST(GENERATE_TIMESTAMP_ARRAY( {{start_date}}, {{end_date}}, INTERVAL 5 minute)) ts LEFT JOIN
     db.table t
     ON t.timestamp >= ts AND
        t.timestamp < TIMESTAMP_ADD(ts, INTERVAL 5 minute) AND
        t.user = {{ user_id }}
GROUP BY ts
ORDER BY ts;

Если вам нужно разбить по таблице, вы можете немного изменить запрос:

 SELECT ts,
       SUM(t.count) AS count,
       MAX(t.total_count) as total_count
FROM UNNEST(GENERATE_TIMESTAMP_ARRAY( {{start_date}}, {{end_date}}, INTERVAL 5 minute)) ts LEFT JOIN
     (SELECT t.*
      FROM db.table t
      WHERE timestamp BETWEEN {{ start_date }} AND {{ end_date }}
     ) t
     ON t.timestamp >= ts AND
        t.timestamp < TIMESTAMP_ADD(ts, INTERVAL 5 minute) AND
        t.user = {{ user_id }}
GROUP BY ts
ORDER BY ts;

Комментарии:

1. У меня есть timestamp ключ раздела as, поэтому я получаю сообщение об ошибке Cannot query over table 'db.table' without a filter over column(s) 'timestamp' that can be used for partition elimination

2. @Sociopath . , , Затем добавьте where предложение, например . where timestamp >= {{start_date]} and timestamp <= {{end_date}}