如何选择第一个非空日期BigQuery

Question

我想选择第一个非空日期时间，但使用不起作用

"row_number((over partition((">

在我的代码中，我现在只排除了空行，现在我只需要选择最低日期。Pls.help如何修复：

left join (
select deal, type, assigned_to_user, marked_as_done_time,
first_value(marked_as_done_time ignore nulls) over (partition by deal
order by marked_as_done_time asc ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as first_value,

from data_marts.pipedrive_activity
where done = 'Done' 
group by 1,2,3,4
) act on act.deal = d.id

Answer 1

您可以尝试下面的-

left join (
select deal, type, assigned_to_user, marked_as_done_time,
row_number() over (partition by deal
order by marked_as_done_time) as first_value,

from data_marts.pipedrive_activity
where done = 'Done' and marked_as_done_time is not null
group by 1,2,3,4
) act on act.deal = d.id where first_value=1