How to get the first not null value from a column

问题:

I am trying to extract the first not null value from a column of values based on timestamp. Can somebody share your thoughts on this. Thank you.

What have i tried so far?

FIRST_VALUE( column ) OVER ( PARTITION BY id ORDER BY timestamp) 

Input :-

id,column,timestamp
1,NULL,10:30 am
1,NULL,10:31 am
1,'xyz',10:32 am
1,'def',10:33 am
2,NULL,11:30 am
2,'abc',11:31 am

Output(expected) :-
1,'xyz',10:30 am
1,'xyz',10:31 am
1,'xyz',10:32 am
1,'xyz',10:33 am
2,'abc',11:30 am
2,'abc',11:31 am

回答1:

Try this old trick of string manipulation:

Select 
ID,
  Column,
  ttimestamp,
  LTRIM(Right(CColumn,20)) as CColumn,
  FROM
(SELECT
  ID,
  Column,
  ttimestamp,
  MIN(Concat(RPAD(IF(Column is null, '9999999999999999',STRING(ttimestamp)),20,'0'),LPAD(Column,20,' '))) OVER (Partition by ID) CColumn
FROM (

  SELECT
    *
  FROM (Select 1 as ID, STRING(NULL) as Column, 0.4375 as ttimestamp),
        (Select 1 as ID, STRING(NULL) as Column, 0.438194444444444 as ttimestamp),
        (Select 1 as ID, 'xyz' as Column, 0.438888888888889 as ttimestamp),
        (Select 1 as ID, 'def' as Column, 0.439583333333333 as ttimestamp),
        (Select 2 as ID, STRING(NULL) as Column, 0.479166666666667 as ttimestamp),
        (Select 2 as ID, 'abc' as Column, 0.479861111111111 as ttimestamp)
))

回答2:

You can modify your sql like this to get the data you want.

FIRST_VALUE( column )
  OVER ( 
    PARTITION BY id
    ORDER BY
      CASE WHEN column IS NULL then 0 ELSE 1 END DESC,
      timestamp
  )

回答3:

As far as I know, Big Query has no options like 'IGNORE NULLS' or 'NULLS LAST'. Given that, this is the simplest solution I could come up with. I would like to see even simpler solutions. Assuming the input data is in table "original_data",

select w2.id, w1.column, w2.timestamp
from
(select id,column,timestamp
   from
     (select id,column,timestamp, row_number() 
                   over (partition BY id ORDER BY timestamp) position
       FROM original_data
       where column is not null
    )
   where position=1 
) w1
right outer join
 original_data as w2
on w1.id = w2.id 

回答4:

SELECT id,
(SELECT top(1) column FROM test1 where id=1 and column is not null order by autoID desc) as name ,timestamp FROM yourTable

Output :- 1,'xyz',10:30 am 1,'xyz',10:31 am 1,'xyz',10:32 am 1,'xyz',10:33 am 2,'abc',11:30 am 2,'abc',11:31 am