Блог

Главная - Вопросы по программированию - BigQuery — выбор минимального значения столбца, зависящего от значения в другом столбце

BigQuery — выбор минимального значения столбца, зависящего от значения в другом столбце

#sql #google-bigquery #pivot #min

# #sql #google-bigquery #сводная #min

Вопрос:

Допустим, у меня есть такая таблица

 userId  eventType   timing
647     'jump'      32.7
123     'skip'      13.1
647     'skip'      24.4
433     'jump'      12.7 
433     'skip'      53.6 
647     'jump'      2.4 
647     'jump'      64.4 
123     'skip'      14.0
433     'jump'      4.3 
123     'jump'      18.6

Я хотел бы вывести таблицу с одной строкой на идентификатор пользователя со столбцами в качестве идентификатора пользователя, минимальным временем, когда EventType был ‘skip’ для этого идентификатора пользователя, и минимальным временем, когда EventType был ‘jump’ для того же идентификатора пользователя. Вот так.

 userID  first_skip  first_jump  
647     24.4        2.4
123     13.1        18.6
433     53.6        4.3

Я понимаю, что могу сделать это с помощью объединений.

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 647 userId, 'jump' eventType, 32.7 timing UNION ALL
  SELECT 123, 'skip', 13.1 UNION ALL
  SELECT 647, 'skip', 24.4 UNION ALL
  SELECT 433, 'jump', 12.7 UNION ALL
  SELECT 433, 'skip', 53.6 UNION ALL
  SELECT 647, 'jump', 2.4 UNION ALL
  SELECT 647, 'jump', 64.4 UNION ALL
  SELECT 123, 'skip', 14.0 UNION ALL
  SELECT 433, 'jump', 4.3 UNION ALL
  SELECT 123, 'jump', 18.6
)

SELECT 
  raw.userID, 
  MIN(skips.timing) AS first_skip,
  MIN(jumps.timing) AS first_jump,
FROM `project.dataset.table` AS raw
LEFT JOIN `project.dataset.table` AS skips ON raw.userId = skips.userId
LEFT JOIN `project.dataset.table` AS jumps ON raw.userId = jumps.userId
WHERE skips.eventType = 'skip' AND jumps.eventType = 'jump'
GROUP BY userId

Однако мои фактические данные довольно большие, и есть еще несколько категорий типов событий, что означает, что запрос обрабатывается вечно. Мне интересно, есть ли более приятный и эффективный способ сделать это, который не использует объединения. Может быть, используя window или partition ?

Ответ №1:

Используйте условную агрегацию:

 select userid,
    min(case when eventtype = 'skip' then timing end) first_skip,
    min(case when eventtype = 'jump' then timing end) first_jump
from mytable
group by userid

Ответ №2:

Вы можете использовать условное агрегирование:

 select user_id, 
       min(case when eventtype = 'skip' then timing end) as skip,
       min(case when eventtype = 'jump' then timing end) as jump
from t
group by user_id;
Метки: