如何从Big Query中的一列值中获取第一个非空值? [英] How to get the first not null value from a column of values in Big Query?
本文介绍了如何从Big Query中的一列值中获取第一个非空值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图从基于时间戳的值列中提取第一个非空值。有人可以分享你的想法。
FIRST_VALUE (列)OVER(PARTITION BY ID ORDER BY timestamp)
输入:
id,列,时间戳
1,NULL ,上午10点30分
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
输出(预计): -
1,'xyz',上午10:30 b $ b 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
解决
选择
ID,
Column,
ttimestamp,
LTRIM(Right(CColumn,20))as CCcolumn,
FROM
(SELECT
ID,
Column,
ttimestamp,
MIN(Concat(RPAD(IF(Column is null,'9999999999999999',STRING(ttimestamp)),20,'0'),LPAD (Column,20,'')))OVER(按ID划分)CC列
FROM(
SELECT
*
FROM(选择1作为ID,STRING( NULL)as Column,0.4375 as ttimestamp),
(选择1作为ID,STRING(NULL)作为列,0.438194444444444作为ttimestamp),
(选择1作为ID,'xyz'作为列,0.438888888888889作为ttimestamp),
(选择1作为ID,'def'作为列,0.439583333333333作为ttimestamp),
(选择2作为ID,STRING(NULL)作为列,0.479166666666667作为ttimestamp),
(选择2作为ID,'abc'作为列,0.479861111111111作为ttimestamp)
))
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
解决方案
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)
))
这篇关于如何从Big Query中的一列值中获取第一个非空值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文