sql - 有没有更好的方法来查找表中的条件最后值？

Question

我有下表t:

<表类="s-表"><头>user_id值时间戳<正文>1一个2021-01-01 10:00:001b2021-01-01 10:01:001X2021-01-01 10:02:001X2021-01-01 10:04:001一个2021-01-01 10:05:001X2021-01-01 10:06:002X2021-01-01 10:07:002X2021-01-01 10:08:003一个2021-01-01 10:07:003一个2021-01-01 10:08:0042021-01-01 10:08:005c2021-01-01 10:09:0052021-01-01 10:10:0062021-01-01 10:09:006X2021-01-01 10:10:007d2021-01-01 10:09:007X2021-01-01 10:10:00

对于不是 X 的每个用户，我需要来自 val 的最新(通过 tstamp )非空值。如果用户除了 X 没有其他值，它应该只是 X(如用户 2)。如果对用户没有任何值(value)，它应该保持 NULL(就像用户 4)。上表的预期结果如下:

<表类="s-表"><头>user_idlast_val<正文>1一个2X3一个45c6X7d

我主要是在寻找一种用最少的 CTE 来做到这一点的方法，因为我已经有了解决方案，如下所示:

with user_values as ( 
    select 
        user_id, 
        count(distinct val) as types_of_vals, 
        coalesce(boolor_agg(val = 'X'),false) as has_value_x 
    from t 
    group by 1
),
user_last_value as ( 
    select distinct 
        user_id, 
        last_value(val) over (partition by user_id order by tstamp) as last_value_without_x 
    from t 
    where val!='X'
)
select distinct 
    user_id, 
    case 
        when types_of_vals=0 then null 
        when (types_of_vals=1 and has_value_x) then 'X' 
    else 
        last_value_without_x 
    end as last_val
from t
left join user_values using (user_id)
left join user_last_value using (user_id)
order by user_id;

这似乎太复杂了，但它产生了预期的结果。我很确定有更好的方法可以做到这一点。

另一种方法是通过

select 
    *, 
    last_value(val) ignore 'X' over (partition by user_id order by tstamp) as last_value_without_x, -- this won't work 
    last_value(val) ignore nulls over (partition by user_id order by tstamp) as last_value_without_nulls
from t

然后在稍后的 CTE 中，我可以合并 last_value_without_x 和 last_value_without_nulls 来获得所需的内容，但不幸的是，last_value( )。

如果有帮助，我正在使用 Snowflake。

Answer 1

我会为此使用 row_number():

select user_id, val as last_val
from (select t.*,
             row_number() over (partition by user_id
                                order by (val is not null) desc,
                                         (val = 'X') desc,
                                         timestamp desc)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

如果您想明确类型转换，则可以对 order by 使用显式转换(即 (val = 'x')::int desc) .