Агрегирование BigQuery в массив на основе id и id_type

У меня есть таблица, которая выглядит примерно так:

WITH
  table AS (
  SELECT 1 object_id, 234 type_id, 2 type_level UNION ALL
  SELECT 1, 23, 1 UNION ALL
  SELECT 1, 24, 1 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 2, 34, 1 UNION ALL
  SELECT 2, 46, 1 UNION ALL
  SELECT 2, 465, 2 UNION ALL
  SELECT 2, 349, 2 UNION ALL
  SELECT 2, 4, 0 UNION ALL
  SELECT 2, 3, 0 )
SELECT
  object_id,
  type_id,
  type_level
FROM
  table

Теперь я пытаюсь создать три новых столбца _2 _, _ 3 _, _ 4_ для каждого объекта и объединить type_id соответствующего уровня типов в этот массив (я не ищу строку, разделенную запятыми).

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

+----+--------------------+--------------------+--------------------+
| id | type_level_0_array | type_level_1_array | type_level_2_array |
+----+--------------------+--------------------+--------------------+
| 1  | 2                  | 24,23              | 234                |
+----+--------------------+--------------------+--------------------+
| 2  | 3,4                | 34,46              | 465,349            |
+----+--------------------+--------------------+--------------------+

Есть ли способ добиться этого?

Обновлять:

Хотя кажется, что мой type_id имеет определенный шаблон, например. типы уровня 0 имеют длину 1, типы уровня 1 имеют длину 2 и так далее, в моем реальном наборе данных такого шаблона нет. Идентификация уровня возможна только при просмотре type_level любой строки.

google-bigquery bigquery-standard-sql

Syed Arefinul Haque 25.01.2019 источник

Ответы (2)

arrow_upward
2
arrow_downward

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

#standardSQL
SELECT object_id,
  ARRAY_AGG(DISTINCT IF(type_level = 0, type_id, NULL) IGNORE NULLS) AS type_level_0_array,
  ARRAY_AGG(DISTINCT IF(type_level = 1, type_id, NULL) IGNORE NULLS) AS type_level_1_array,
  ARRAY_AGG(DISTINCT IF(type_level = 2, type_id, NULL) IGNORE NULLS) AS type_level#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 object_id, 234 type_id, 2 type_level UNION ALL
  SELECT 1, 23, 1 UNION ALL
  SELECT 1, 24, 1 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 2, 34, 1 UNION ALL
  SELECT 2, 46, 1 UNION ALL
  SELECT 2, 465, 2 UNION ALL
  SELECT 2, 349, 2 UNION ALL
  SELECT 2, 4, 0 UNION ALL
  SELECT 2, 3, 0 )
SELECT object_id,
  ARRAY_AGG(DISTINCT IF(type_level = 0, type_id, NULL) IGNORE NULLS) AS type_level_0_array,
  ARRAY_AGG(DISTINCT IF(type_level = 1, type_id, NULL) IGNORE NULLS) AS type_level_1_array,
  ARRAY_AGG(DISTINCT IF(type_level = 2, type_id, NULL) IGNORE NULLS) AS type_level_2_array
FROM `project.dataset.table`
GROUP BY object_id   
array
FROM `project.dataset.table`
GROUP BY object_id

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

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 object_id, 234 type_id, 2 type_level UNION ALL
  SELECT 1, 23, 1 UNION ALL
  SELECT 1, 24, 1 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 2, 34, 1 UNION ALL
  SELECT 2, 46, 1 UNION ALL
  SELECT 2, 465, 2 UNION ALL
  SELECT 2, 349, 2 UNION ALL
  SELECT 2, 4, 0 UNION ALL
  SELECT 2, 3, 0 )
SELECT object_id,
  ARRAY_AGG(DISTINCT IF(type_level = 0, type_id, NULL) IGNORE NULLS) AS type_level_0_array,
  ARRAY_AGG(DISTINCT IF(type_level = 1, type_id, NULL) IGNORE NULLS) AS type_level_1_array,
  ARRAY_AGG(DISTINCT IF(type_level = 2, type_id, NULL) IGNORE NULLS) AS type_level_2_array
FROM `project.dataset.table`
GROUP BY object_id

с результатом

Row     object_id   type_level_0_array  type_level_1_array  type_level_2_array   
1       1           2                   24                  234  
                                        23       
2       2           4                   34                  349  
                    3                   46                  465

Mikhail Berlyant 25.01.2019

arrow_upward
0
arrow_downward

Попробуй это. Работает на меня.

Bigquery не позволит вам создать массив с пустыми значениями в них, поэтому требуется IGNORE NULLS.

РЕДАКТИРОВАТЬ: я обновил код, основанный на столбце type_level

WITH table
 AS (
  SELECT 1 object_id, 234 type_id, 2 type_level UNION ALL
  SELECT 1, 23, 1 UNION ALL
  SELECT 1, 24, 1 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 1, 2, 0 UNION ALL
  SELECT 2, 34, 1 UNION ALL
  SELECT 2, 46, 1 UNION ALL
  SELECT 2, 465, 2 UNION ALL
  SELECT 2, 349, 2 UNION ALL
  SELECT 2, 4, 0 UNION ALL
  SELECT 2, 3, 0 )
SELECT
  ARRAY_AGG(CASE WHEN type_level = 0 THEN type_id ELSE NULL END IGNORE NULLS) AS type_level_0_array
  , ARRAY_AGG(CASE WHEN type_level = 1 THEN type_id ELSE NULL END IGNORE NULLS) AS type_level_1_array
  , ARRAY_AGG(CASE WHEN type_level = 2 THEN type_id ELSE NULL END IGNORE NULLS) AS type_level_2_array
FROM
  table

Paul H 25.01.2019

comment

Привет, Пол, хотя кажется, что все уровни 0 - это 1 цифра, а уровень 1 - 2 цифры и так далее, в моем реальном наборе данных все они имеют одинаковый идентификатор длины. - Syed Arefinul Haque; 25.01.2019

Агрегирование BigQuery в массив на основе id и id_type

Ответы (2)

Вопросы по теме