时间戳

首页 > 解决方案 > 如何选择第一个非空日期 BigQuery

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

问题描述

我想选择第一个非空日期时间，但它不适用于我使用

“row_number() 超过 partition()”

现在在我的代码中，我只排除了空行，现在我只需要选择最低日期。请帮助如何解决它：

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

标签： sqlgoogle-bigquerypartitionrow-number

解决方案

你可以试试下面的 -

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

推荐阅读