Блог

У меня есть данные, которые имеют идентификатор, а затем различное количество состояний, которые связаны через строки с одинаковым идентификатором. Я хочу создать новый столбец, который показывает значение TRUE / FALSE, если данный идентификатор когда-либо был состоянием, и чтобы эта информация была прикреплена ко всем строкам с соответствующим идентификатором.

Пример исходных данных:

 '''''''''''''''''
| ID | status   |
'''''''''''''''''
| 1  | state1   |
| 1  | state2   |
| 1  | state2   |
| 1  | state3   |
| 2  | state2   |
| 2  | state3   |
| 3  | state2   |
| 3  | state3   |
| 3  | state1   |
| ...| ...      |  

и я хочу, чтобы это выглядело так:

 ''''''''''''''''''''''''''''''''''
| ID | status   | is_ever_state1 |
''''''''''''''''''''''''''''''''''
| 1  | state1   | TRUE           |
| 1  | state2   | TRUE           |
| 1  | state2   | TRUE           |
| 1  | state3   | TRUE           |
| 2  | state2   | FALSE          |
| 2  | state3   | FALSE          |
| 3  | state2   | TRUE           |
| 3  | state3   | TRUE           |
| 3  | state1   | TRUE           |
| ...| ...      | ...            |

<!-- begin snippet: js hide: false console: true babel: false -->  

Вот код для создания исходной таблицы:

 WITH table AS
 (SELECT 1 as ID, 'state1' as status UNION ALL
  SELECT 1, 'state2' UNION ALL
  SELECT 1, 'state2' UNION ALL
  SELECT 1, 'state3' UNION ALL
  SELECT 2, 'state2' UNION ALL
  SELECT 2, 'state3' UNION ALL
  SELECT 3, 'state2' UNION ALL
  SELECT 3, 'state3' UNION ALL
  SELECT 3, 'state1')
SELECT * FROM table
  

Я немного застрял в том, как это сделать. Одна из моих идей заключалась в том, чтобы поместить его в структуру, затем использовать функцию case, чтобы разделить его на основе родительского идентификатора (а затем отменить его после этого) однако, похоже, я не могу этого сделать, поскольку это не позволит мне получить доступ к дочерним элементам в формате структуры, и поэтому я не думаю, что это способ сделать это.

 SELECT
ID,
status,
case
  when status_struct.status = 'state1' then TRUE
  when status_struct.status != 'state1' then FALSE
end as is_ever_state1
FROM(
  SELECT 
  ID,
  status,
  ARRAY_AGG(STRUCT( status))AS status_struct,
  FROM table
  GROUP BY ID,status)  

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

 #standardSQL
SELECT *, 
  MAX(status = 'state1') OVER(PARTITION BY ID) AS is_ever_state1
FROM table    
  

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

 Row ID  status  is_ever_state1   
1   1   state1  true     
2   1   state2  true     
3   1   state2  true     
4   1   state3  true     
5   2   state2  false    
6   2   state3  false    
7   3   state2  true     
8   3   state3  true     
9   3   state1  true     
  

Просто используйте функцию окна:

 select t.*,
       (countif(state = 'state1') over (partition by id) > 0) as flag
from t;