Как запросить уникальные записи (DISTINCT) вместе с механизмом выбора среди одинаковых нужного значения?

Question

Мне нужно выбрать строки из базы данных, которые уникальны по дате. В идеале таких строк будет очень мало, но если они попадаются, то нужно выбрать среди них только одно значение. DISTINCT предполагает просто фильтр по уникальности, но он не делает акцент на том, какие записи он выбирает. Давайте лучше на примере:

+--+---------+---------------+---------------+--------------------------+
|id|credit_id|some_property_1|some_property_2|date                      |
+--+---------+---------------+---------------+--------------------------+
|1 |1        |3              |null           |2024-01-01 00:00:00.000000|
|2 |1        |null           |false          |2024-02-01 00:00:00.000000|
|3 |1        |3              |true           |2024-03-01 00:00:00.000000|
|4 |1        |3              |null           |2024-03-01 00:00:00.000000|
|5 |1        |3              |null           |2024-03-01 00:00:00.000000|
+--+---------+---------------+---------------+--------------------------+

И так, здесь нужно вернуть все записи по credit_id (для теста они тут все 1) в порядке убывания date и в случае, если date одинаковый, то приоритет в таком подсписке - назовем так - отдается записям с some_property_2 != null (если и таких > 1, то любой из них). То есть при равным date будет выведена всего лишь одна запись по указанному выше условию. Выводить нужно все столбцы. Результат запрос должен быть такой:

+--+---------+---------------+---------------+--------------------------+
|id|credit_id|some_property_1|some_property_2|date                      |
+--+---------+---------------+---------------+--------------------------+
|3 |1        |3              |true           |2024-03-01 00:00:00.000000|
|2 |1        |null           |false          |2024-02-01 00:00:00.000000|
|1 |1        |3              |null           |2024-01-01 00:00:00.000000|
+--+---------+---------------+---------------+--------------------------+

База данных PostgreSQL, но желательно писать переносимый запрос, т.е. стандартный для всех баз данных. Если такое невозможно, то и нативному, разумеется, буду рад. Также я указал в метках Spring и Java - так как пишу эту логику с помощью этих инструментов и буду рад решению на языке, но тут есть тонкости:

Нужна пагинация
Просто делать "в лоб" поиск всех записей не подходит, так как записей может быть и 10 тыс., а вывести на фронт, условно, нужно всего 20 и производительность такого запроса будет нулевой.

P.S. Я не прошу тут решить за меня задачку. Я столкнулся с реальной проблемой и не понимаю какими средствами SQL решить это.

Answer 1

Недавно столкнулся с похожей задачей.

Трюк в том что max от null равно null и null меньше любого значения.

with uniq as (
    select credit_id, date, max(some_property_2::integer) some_property_2 from tmp
    group by credit_id, date
)
select t.* from uniq u left join tmp t 
 on u.credit_id=t.credit_id 
and u.date=t.date
and (u.some_property_2=t.some_property_2::integer
    or u.some_property_2 is null and t.some_property_2 is null);

Answer 2

select distinct on (date) * from public.test order by date,some_property_2;

Но distinct on реализован не во всех SQL

БЛОГ НА HUSL

Как запросить уникальные записи (DISTINCT) вместе с механизмом выбора среди одинаковых нужного значения?

Ответы (2 шт):