Объединять несколько столбцов в массив только тогда, когда столбцы имеют ненулевое значение в Bigquery
У меня есть таблица, которая выглядит так:
+----+------+------+------+------+------+
| id | col1 | col2 | col3 | col4 | col5 |
+----+------+------+------+------+------+
| a | 1 | null | null | null | null |
| b | 1 | 2 | 3 | 4 | null |
| c | 1 | 2 | 3 | 4 | 5 |
| d | 2 | 1 | 7 | null | 4 |
+----+------+------+------+------+------+
Я хочу создать агрегированную таблицу, где для каждого идентификатора я хочу массив, который содержит ненулевое значение из всех других столбцов. Вывод должен выглядеть так:
+-----+-------------+
| id | agg_col |
+-----+-------------+
| a | [1] |
| b | [1,2,3,4] |
| c | [1,2,3,4,5] |
| d | [2,1,7,4] |
+-----+-------------+
Можно ли произвести вывод, используя стандарт BigQuery SQL?
1 ответ
Ниже не является универсальным решением, но оно работает для вашего конкретного примера, который вы предоставили - id представлен буквенно-цифровым (не начинается с цифры), а остальные столбцы являются числами - целыми числами
#standardSQL
SELECT id,
ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != '') AS agg_col_as_array,
CONCAT('[', ARRAY_TO_STRING(ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != ''), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
Вы можете проверить, поиграть с выше, используя пример данных из вашего вопроса, как показано ниже
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id,
ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != '') AS agg_col_as_array,
CONCAT('[', ARRAY_TO_STRING(ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != ''), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id
с результатом как
Row id agg_col_as_array agg_col_as_string
1 a 1 [1]
2 b 1 [1,2,3,4]
2
3
4
3 c 1 [1,2,3,4,5]
2
3
4
5
4 d 2 [2,1,7,4]
1
7
4
Как вы думаете, возможно ли сделать это, упомянув конкретные столбцы, а затем связав их в массив?
Конечно, это выполнимо - см. Ниже
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id,
ARRAY(
SELECT col
FROM UNNEST([col1, col2, col3, col4, col5]) col
WHERE NOT col IS NULL
) AS agg_col_as_array,
CONCAT('[', ARRAY_TO_STRING(
ARRAY(
SELECT CAST(col AS STRING)
FROM UNNEST([col1, col2, col3, col4, col5]) col
WHERE NOT col IS NULL
), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id
НО... это не лучший вариант, который у вас есть, так как вам нужно управлять и настраивать количество и названия столбцов в каждом случае для разных целей
Ниже приводится исправленная версия моего оригинального ответа на ваш последний комментарий - Actually the sample was too simple. Both of my id and other columns have alphanumeric and special characters.
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id,
ARRAY(
SELECT col
FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(.*?)(?:,|})')) col WITH OFFSET
WHERE col != 'null' AND OFFSET > 0
) AS agg_col_as_array,
CONCAT('[', ARRAY_TO_STRING(
ARRAY(
SELECT col
FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(.*?)(?:,|})')) col WITH OFFSET
WHERE col != 'null' AND OFFSET > 0
), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id
оба с тем же результатом, что и раньше
Row id agg_col_as_array agg_col_as_string
1 a 1 [1]
2 b 1 [1,2,3,4]
2
3
4
3 c 1 [1,2,3,4,5]
2
3
4
5
4 d 2 [2,1,7,4]
1
7
4