BigQuery:检查其中一个重复ID的值，并将所有重复ID存储在新列中

Question

我的数据有一个ID，然后是通过具有相同ID的行链接的不同数量的状态。如果给定ID曾经是一个状态，我想创建一个显示TRUE/FALSE的新列，并将此信息附加到具有关联ID的所有行。

初始数据示例：

'''''''''''''''''
| 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函数在每个父ID的基础上对其进行分区(然后再进行UNNEST(，但我似乎无法做到这一点，因为它不允许我访问结构格式中的子项，因此我不认为这是实现这一目标的方法。

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)

Answer 1

下面是BigQuery标准SQL

#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     

Answer 2

只需使用一个窗口函数：

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