Is there a better way to find conditional last values in a table?

user_11313791 0
Viewed 116

I have the following table called t:

user_id val tstamp
1 a 2021-01-01 10:00:00
1 b 2021-01-01 10:01:00
1 X 2021-01-01 10:02:00
1 X 2021-01-01 10:04:00
1 a 2021-01-01 10:05:00
1 X 2021-01-01 10:06:00
2 X 2021-01-01 10:07:00
2 X 2021-01-01 10:08:00
3 a 2021-01-01 10:07:00
3 a 2021-01-01 10:08:00
4 2021-01-01 10:08:00
5 c 2021-01-01 10:09:00
5 2021-01-01 10:10:00
6 2021-01-01 10:09:00
6 X 2021-01-01 10:10:00
7 d 2021-01-01 10:09:00
7 X 2021-01-01 10:10:00

from which I need the latest (by tstamp ) non-null value from val for each user that is not X. If there is no other value for a user other than X, it should just be X (like user 2). If there was no value for a user whatsoever, it should remain NULL (like for user 4). The intended outcome for the above table is the following:

user_id last_val
1 a
2 X
3 a
4
5 c
6 X
7 d

I'm mainly looking for a way to do this with the least amount of CTEs necessary as I already have a solution for this, which goes as follows:

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;

This seems to be too complicated, but it produces the intended outcome. I'm pretty sure there is a better way to do this. 

An alternative way for it could be by 

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

and then in a later CTE I can coalesce the last_value_without_x and last_value_without_nulls to arrive at what's needed, but unfortunately, arbitrary values cannot be ignored by last_value().

I'm using Snowflake if that helps.

snowflake-cloud-data-platform sql
4 Answers

Using NULLIF:

WITH cte AS (
   SELECT *, NULLIF(val, 'X') AS val_without_x FROM t
)
select *, 
    last_value(val_without_x) ignore nulls 
          over (partition by user_id order by tstamp) as last_value_without_x, 
    last_value(val) ignore nulls 
          over (partition by user_id order by tstamp) as last_value_without_nulls
from cte
user_50708790

Try this

select user_id, val last_val from (
        select user_Id, val, row_number() over (partition by user_id order by tstamp desc) rn
        from t
        where val !='x' or (select count(*) from t t2 where t2.user_id=t.user_id and t2.val !='X' )=0
    ) x
where rn=1
user_153326500

I would just use row_number() for this:

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;

You can use explicit conversions for the order by (i.e. (val = 'x')::int desc) if you want to be explicit about the type conversion.

user_11440350

with the data:

WITH data(user_id, val, tstamp) AS (
    SELECT * FROM VALUES
        (1, 'a', '2021-01-01 10:00:00'),
        (1, 'b', '2021-01-01 10:01:00'),
        (1, 'X', '2021-01-01 10:02:00'),
        (1, 'X', '2021-01-01 10:04:00'),
        (1, 'a', '2021-01-01 10:05:00'),
        (1, 'X', '2021-01-01 10:06:00'),
        (2, 'X', '2021-01-01 10:07:00'),
        (2, 'X', '2021-01-01 10:08:00'),
        (3, 'a', '2021-01-01 10:07:00'),
        (3, 'a', '2021-01-01 10:08:00'),
        (4, null,'2021-01-01 10:08:00'),
        (5, 'c', '2021-01-01 10:09:00'),
        (5, null,'2021-01-01 10:10:00'),
        (6, null,'2021-01-01 10:09:00'),
        (6, 'X', '2021-01-01 10:10:00'),
        (7, 'd', '2021-01-01 10:09:00'),
        (7, 'X', '2021-01-01 10:10:00')
)

the following gives you the correct answers and is a little bit shorter that Gordon's answer:

SELECT DISTINCT
    user_id, 
    LAST_VALUE(val) IGNORE NULLS OVER (PARTITION BY user_id ORDER BY val != 'X', tstamp)
FROM data;

and if you don't like the DISTINCT it the ROW_NUMBER can be moved to the QUALIFY and still be a single layer select:

SELECT DISTINCT
    user_id, 
    val
FROM data
QUALIFY ROW_NUMBER() OVER (PARTION BY user_id ORDER BY val != 'X' DESC NULLS LAST, tstamp DESC) = 1

Also the sorting my NULLs change be handled via the ORDER BY of the VAL sort with NULLS LAST

user_439920
Related