BigQuery get row above empty column

Data:

SELECT 'aa' column_a, 'asdf' column_b UNION ALL
  SELECT 'bbb', '' UNION ALL
  SELECT 'ccc', 'asdf' UNION ALL
  SELECT 'ddd', '' UNION ALL
  SELECT 'eee', 'asdf' UNION ALL
  SELECT 'fff', 'asdf' 

What would be the best way to get the row right above the empty cell? For example, I’d like to filter out row 1 and 3 based on the fact that column_b in row 2 and 4 are empty (in this example). A simple where statement does not work unfortunately, but how would it be possible to get the row above an empty cell?

Thank you

Answer

I think you want lead() – but you need a column that defines the ordering of the rows, I assumed id:

select t.* except(lead_column_b)
from (
    select t.*, lead(column_b) over(order by id) lead_column_b
    from mytable t
) t
where lead_column_b is not null

Note that this will filter out the last row in the table (because it has no “next” row). If you don’t want that, we can add a little more logic:

select t.* except(lead_column_b, rn)
from (
    select t.*, 
        lead(column_b) over(order by id) lead_column_b,
        row_number() over(order by id desc) rn
    from mytable t
) t
where lead_column_b is not null or rn = 1