Как реализовать first_value(field) с фильтром на field внутри окна?

Question

[Иван Мельников]

Имеется два запроса на PostgreSQL 15, формирующих аналогичные результаты:

select
	id,
	banner_id,
	max(banner_id) filter (where banner_id in (13,14,15,17)) over(partition by id) as max_banner_id_1,
	max(banner_id) filter (where banner_id in (4,177,178)) over(partition by id) as max_banner_id_2
from (values (1,13),(1,null),(1,15),(1,null),(1,13),(1,17),(1,14),(1,177),(1,178),(1,4),(1,177)) as T(id, banner_id)
order by banner_id

select
	id,
	banner_id,
	max(case when banner_id in (13,14,15,17) then banner_id end) over(partition by id) as max_banner_id_1,
	max(case when banner_id in (4,177,178) then banner_id end) over(partition by id) as max_banner_id_2
from (values (1,13),(1,null),(1,15),(1,null),(1,13),(1,17),(1,14),(1,177),(1,178),(1,4),(1,177)) as T(id, banner_id)
order by banner_id

С ними всё понятно.
Но есть вопрос: как реализовать аналогичную логику, но не с помощью max(), а с помощью first_value() ?
Конструкция filter (where ...) допустима только для агрегирующих функций, поэтому остается 2-й вариант:

select
	id,
	banner_id,
	first_value(case when banner_id in (13,14,15,17) then banner_id end) over(partition by id order by banner_id desc) as max_banner_id_1,
	first_value(case when banner_id in (4,177,178) then banner_id end) over(partition by id order by banner_id desc) as max_banner_id_2
from (values (1,13),(1,null),(1,15),(1,null),(1,13),(1,17),(1,14),(1,177),(1,178),(1,4),(1,177)) as T(id, banner_id)
order by banner_id

Как видно, результат не тот, что ожидается.
Как реализовать это с подзапросами я понимаю.
Вопрос в том, можно ли это реализовать не прибегая к вложенностям?
Может это можно сделать на других СУБД?

Comments

[Akina]

Ну это по сути "Вот я тут накропал, вы там сами разберитесь, чего мне было нужно, а потом ещё и скажите, как то же сделать лучше"...

[Иван Мельников]

Akina, я недостаточно объяснил, какой результат я хочу получить?
Или не привёл свою попытку решения?

[Akina]

Иван Мельников, я не вижу подробного описания логики происходящего. Только желание зачем-то избавиться от агрегатной функции в её оконном представлении, на практике совершенно бессмысленное.

Или вопрос носит чисто академический характер и не имеет реального практического воплощения?

[Иван Мельников]

Akina, если вам будет легче, представьте, что я просто исследую возможности SQL, в отрыве от какой-либо конкретной практической задачки.

Answer 1

[Akina]

SELECT 
  *,
  FIRST_VALUE(CASE WHEN banner_id IN (13,14,15,17)
                   THEN banner_id 
                   END) OVER (PARTITION BY id
                              ORDER BY CASE WHEN banner_id IN (13,14,15,17)
                                            THEN banner_id 
                                            END DESC NULLS LAST) max_banner_id_1,
  FIRST_VALUE(CASE WHEN banner_id IN (4,177,178)
                   THEN banner_id 
                   END) OVER (PARTITION BY id
                              ORDER BY CASE WHEN banner_id IN (4,177,178)
                                            THEN banner_id
                                            END DESC NULLS LAST) max_banner_id_2
FROM test
ORDER BY banner_id

Comments

[Иван Мельников]

То, что нужно!
Спасибо!