Сгруппировать столбцы по условию, где значение одного столбца меньше максимального значения группы во втором

Question

Пример таблицы:

столбец_1 дата_1         дата_2
11        01.01.2000     01.01.2000
22        01.01.2001     01.01.2003
22        01.01.2002     01.01.2003
33        01.01.2005     01.01.2000
33        01.01.2005     01.01.2000
33        01.01.2007     01.01.2006

что то такое:

select столбец_1,
    count(дата_1)
from таблица,
where дата1< max(дата_2)   --для каждой группы

результат должен быть таким:

11  0
22  2
33  2

Answer 1

Как-то так:

WITH cte AS ( SELECT id, MAX(date2) date2
              FROM test
              GROUP BY id )
SELECT cte.id, COUNT(test.date1) cnt
FROM cte
LEFT JOIN test ON test.id = cte.id
              AND test.date1 < cte.date2
GROUP BY cte.id
ORDER BY id;

Ну или так:

WITH cte AS ( SELECT id, date1, MAX(date2) OVER (PARTITION BY id) date2
              FROM test )
SELECT id, SUM((date1 < date2)::INT) cnt
FROM cte
GROUP BY id
ORDER BY id;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=bbda9563200dbdf0a7fc0a9b374c8a5f

PS. NULL - игнорируются.

БЛОГ НА HUSL

Сгруппировать столбцы по условию, где значение одного столбца меньше максимального значения группы во втором

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