Объединение двух столбцов и создание новых строк

Пытаясь объединить две колонки в одну в BQ,

моя текущая таблица выглядит так:

+-------+--------------------------------------+------+-----+
| id    | time                        | color1 |color2|type |
+-------+--------------------------------------+------+-----+
| 10954 | 2018-09-09 23:20:01.074 UTC | yellow | blue | 1   |
+-------+--------------------------------------+------+-----+
| 10954 | 2018-10-09 20:38:61.151 UTC | red    | blue | 1   |
+-------+--------------------------------------+------+-----+
| 20562 | 2018-08-09 19:49:14.391 UTC | green  | red  | 0   |
+-------+--------------------------------------+------+-----+
| 20562 | 2017-09-09 17:02:22.903 UTC | green  | red  | 1   |
+-------+--------------------------------------+------+-----+

И моя таблица целей будет:

+-------+--------------------------------------+------+
| id    | time                        | color  | type |     
+-------+--------------------------------------+------+
| 10954 | 2018-09-09 23:20:01.074 UTC | yellow |  1   |     
+-------+--------------------------------------+------+
| 10954 | 2018-10-09 20:38:61.151 UTC | red    |  1   |     
+-------+--------------------------------------+------+
| 10954 | 2018-09-09 23:20:01.074 UTC | blue   |  0   |     
+-------+--------------------------------------+------+
| 20562 | 2018-08-09 19:49:14.391 UTC | green  |  0   |     
+-------+--------------------------------------+------+
| 20562 | 2017-09-09 17:02:22.903 UTC | green  |  1   |     
+-------+--------------------------------------+------+
| 20562 | 2017-09-09 17:02:22.903 UTC | red    |  0   |     
+-------+--------------------------------------+------+

Таким образом, будут созданы новые строки для color2, где id будет дублироваться, time будет временем мин для группы id и type = 0. Можно ли сделать это в операторе case when при создании столбца нового цвета или с помощью CTE?

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
0
507
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

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

#standardSQL
SELECT id, time, color1 AS color, type
FROM `project.dataset.table`
UNION ALL
SELECT id, MIN(time) AS time, color2 AS color, 0 type
FROM `project.dataset.table`
GROUP BY id, color2

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

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 10954 id, '2018-09-09 23:20:01.074 UTC' time, 'yellow' color1, 'blue' color2, 1 type UNION ALL
  SELECT 10954, '2018-10-09 20:38:61.151 UTC', 'red', 'blue', 1 UNION ALL
  SELECT 20562, '2018-08-09 19:49:14.391 UTC', 'green', 'red', 0 UNION ALL
  SELECT 20562, '2017-09-09 17:02:22.903 UTC', 'green', 'red', 1
)
SELECT id, time, color1 AS color, type
FROM `project.dataset.table`
UNION ALL
SELECT id, MIN(time) AS time, color2 AS color, 0 type
FROM `project.dataset.table`
GROUP BY id, color2
-- ORDER BY id 

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

Row id      time                        color   type     
1   10954   2018-09-09 23:20:01.074 UTC yellow  1    
2   10954   2018-10-09 20:38:61.151 UTC red     1    
3   10954   2018-09-09 23:20:01.074 UTC blue    0    
4   20562   2018-08-09 19:49:14.391 UTC green   0    
5   20562   2017-09-09 17:02:22.903 UTC green   1    
6   20562   2017-09-09 17:02:22.903 UTC red     0    

Другие вопросы по теме