Объединить строки в BigQuery, игнорируя нули

Как это работает?

select a.id, max(c.col_1)col_1, a.col_2,  a.updated  from yourtable a 
 left join (
    select id, col_1, max(updated) updated from yourtable
    where col_1 is not null
    group by id, col_1)b
on a.id=b.id and a.updated=b.updated
left join  yourtable c on a.id=c.id and a.updated<>c.updated
where a.col_2 is not null and c.col_1 is not null
group by a.id,   a.col_2,  a.updated 
order by updated  

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

#standardSQL
SELECT id, 
  IFNULL(col_1, FIRST_VALUE(col_1 IGNORE NULLS) OVER(win)) col_1, 
  IFNULL(col_2, FIRST_VALUE(col_2 IGNORE NULLS) OVER(win)) col_2, 
  updated
FROM `project.dataset.your_table`
WINDOW win AS (PARTITION BY id ORDER BY updated DESC 
               ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
-- ORDER BY id, updated

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

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 1 id, 'first_data' col_1, NULL col_2,  '4/22' updated UNION ALL
  SELECT 1,     NULL,             'old',        '4/23'         UNION ALL
  SELECT 1,     NULL,             'correct',    '4/24'         UNION ALL
  SELECT 1,    'next_data',       NULL,         '4/25'         UNION ALL
  SELECT 1,     NULL,             NULL,         '4/26'         
)
SELECT id, 
  IFNULL(col_1, FIRST_VALUE(col_1 IGNORE NULLS) OVER(win)) col_1, 
  IFNULL(col_2, FIRST_VALUE(col_2 IGNORE NULLS) OVER(win)) col_2, 
  updated
FROM `project.dataset.your_table`
WINDOW win AS (PARTITION BY id ORDER BY updated DESC 
               ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
ORDER BY id, updated

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

Row id  col_1       col_2   updated  
1   1   first_data  null    4/22     
2   1   first_data  old     4/23     
3   1   first_data  correct 4/24     
4   1   next_data   correct 4/25     
5   1   next_data   correct 4/26