BigQuery, Получить массив номеров строк по условию в группе по

Question

Учитывая таблицу

id column1 timestamp
1  no
1  yes
1  yes
2  no
2  yes
2  no

Мне нужен список строк столбца 1 по условию "да" в группе как

   id arr
    1 [2,3]
    2 [2]

Благодарность

Answer 1

Пример ниже для BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 pos, 'no' col1 UNION ALL
  SELECT 1, 2, 'yes' UNION ALL
  SELECT 1, 3, 'yes' UNION ALL
  SELECT 2, 4, 'no' UNION ALL
  SELECT 2, 5, 'yes' UNION ALL
  SELECT 2, 6, 'no' 
)
SELECT id, ARRAY_AGG(num ORDER BY num) arr
FROM (
  SELECT id, col1, ROW_NUMBER() OVER(PARTITION BY id ORDER BY pos) num
  FROM `project.dataset.table`
)
WHERE col1 = 'yes'
GROUP BY id   

с выходом

Rowid  arr  
1   1   2    
        3    
2   2   2     

Примечание: у вас ДОЛЖЕН быть дополнительный столбец, который будет определять порядок ваших строк, поэтому вывод будет детерминированным - в этом случае я добавил столбец pos. Но это может быть что-то вроде даты или времени и т. Д.

Очевидно, формально вы можете сделать что-то вроде ниже без лишних столбцов, как в вашем вопросе

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'no' col1 UNION ALL
  SELECT 1, 'yes' UNION ALL
  SELECT 1, 'yes' UNION ALL
  SELECT 2, 'no' UNION ALL
  SELECT 2, 'yes' UNION ALL
  SELECT 2, 'no' 
)
SELECT id, ARRAY_AGG(num ORDER BY num) arr
FROM (
  SELECT id, col1, ROW_NUMBER() OVER(PARTITION BY id) num
  FROM `project.dataset.table`
)
WHERE col1 = 'yes'
GROUP BY id   

но вывод не будет детерминированным, поэтому не будет иметь практической ценности

Примечание: в приведенном выше примере у вас все равно будет тот же результат, что и в первом запросе, но в реальной жизни он будет случайным - если, как в первом запросе, у вас есть что-то для определения порядка