Блог

У меня есть следующая таблица, которая называется t :

из которого мне нужно последнее (по tstamp ) ненулевое значение val для каждого пользователя, которого нет X . Если для пользователя нет другого значения, кроме X , оно должно быть просто X (например, user 2). Если для пользователя вообще не было значения, оно должно остаться NULL (например, для пользователя 4). Предполагаемый результат для приведенной выше таблицы следующий:

В основном я ищу способ сделать это с наименьшим количеством необходимых 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, если это поможет.

Я бы просто использовал 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 ), если хотите четко указать преобразование типов.

Использование 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
 

Попробуйте это

 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
 

с данными:

 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')
)
 

следующее дает вам правильные ответы и немного короче, чем ответ Гордона:

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

и если вам не нравится DISTINCT it, ROW_NUMBER можно переместить в QUALIFY и по-прежнему использовать однослойный выбор:

 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
 

Также сортировка моих изменений NULL обрабатывается с помощью ПОРЯДКА сортировки по значению с ПОСЛЕДНИМИ значениями