Блог

Главная - Вопросы по программированию - группировать по дням и времени в sql BigQuery

группировать по дням и времени в sql BigQuery

#sql #time #group-by #google-bigquery #timestamp

#sql #время #группировать по #google-bigquery #временная метка

Вопрос:

Данные должны быть сгруппированы по времени с интервалом в 1 минуту. Данные:

 id            time                  
111      2020-09-01-09:19:00         
111      2020-09-01-09:19:04        
111      2020-09-01-09:19:08     
112      2020-09-01-10:12:05       
111      2020-09-02-12:36:54     
111      2020-09-02-12:36:58     
111      2020-09-02-12:37:03     
111      2020-09-02-12:37:09

Желаемый результат:

 id            time                         
111      2020-09-01-09:19:08     
112      2020-09-01-10:12:05           
111      2020-09-02-12:37:09

Я пытался:

 SELECT id, TIMESTAMP(time) 
FROM my_table
GROUP BY id, (DATEPART(MINUTE, [time]) / 1)

Function not found: DATEPART

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

1. у вас действительно есть столбец времени, отформатированный таким образом 2020-09-01-09:19:00 ? пожалуйста, подтвердите / уточните

2. На самом деле, это строка… Итак, вы правы. Я только что отредактировал свой вопрос.

Ответ №1:

Данные должны быть сгруппированы по времени с интервалом в 1 минуту.

Для этого вопроса вы можете использовать timestamp_trunc() (или datetime_trunc() ):

 select max(timestamp_trunc(time, minute)), id
from t
group by timestamp_trunc(time, minute), id;

Однако это не возвращает указанные вами результаты.

Ответ №2:

Ниже приведен стандартный SQL для BigQuery

 #standardSQL
SELECT AS VALUE 
  ARRAY_AGG(t ORDER BY time DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.my_table` t
GROUP BY id, 
  TIMESTAMP_SUB(
    PARSE_TIMESTAMP('%Y-%m-%d-%H:%M:%S', time), 
    INTERVAL MOD(UNIX_SECONDS(PARSE_TIMESTAMP('%Y-%m-%d-%H:%M:%S', time)), 2 * 60) 
    SECOND
  )

Вы можете протестировать, поиграть с вышеизложенным, используя образец данных из нашего вопроса, как в примере ниже

 #standardSQL
WITH `project.dataset.my_table` AS (
  SELECT 111 id, '2020-09-01-09:19:00' time UNION ALL
  SELECT 111, '2020-09-01-09:19:04' UNION ALL
  SELECT 111, '2020-09-01-09:19:08' UNION ALL
  SELECT 112, '2020-09-01-10:12:05' UNION ALL
  SELECT 111, '2020-09-01-12:36:54' UNION ALL
  SELECT 111, '2020-09-01-12:36:58' UNION ALL
  SELECT 111, '2020-09-01-12:37:03' UNION ALL
  SELECT 111, '2020-09-01-12:37:09' 
)
SELECT AS VALUE 
  ARRAY_AGG(t ORDER BY time DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.my_table` t
GROUP BY id, 
  TIMESTAMP_SUB(
    PARSE_TIMESTAMP('%Y-%m-%d-%H:%M:%S', time), 
    INTERVAL MOD(UNIX_SECONDS(PARSE_TIMESTAMP('%Y-%m-%d-%H:%M:%S', time)), 2 * 60) 
    SECOND
  )

с выводом

 Row     id      time     
1       111     2020-09-01-09:19:08  
2       112     2020-09-01-10:12:05  
3       111     2020-09-01-12:37:09

Примечание: чтобы соответствовать ожидаемому результату, вы должны использовать интервал в 2 минуты вместо интервала в 1 минуту