Как использовать «Разделение по» в Oracle SQL

Разделение по аналогично группировке, просто укажите, по каким столбцам вы хотите сгруппировать.

Если вы хотите также сгруппировать по типу, вам необходимо включить тип

COUNT(*) OVER (PARTITION BY person_id) AS rec_count

Это означает, что вы хотите сгруппировать по person_id, но тип не включен.

Поэтому, пожалуйста, укажите тип, как показано ниже.

COUNT(*) OVER (PARTITION BY person_id, type) AS rec_count

Вот один из вариантов получить результат, ответив на оба вопроса:

WITH    --  S a m p l e    D a t a :
    PER_PERSON_ADDR_USAGES_F (PERSON_ID, ADDR_ID, TYPE, EFF_START_DATE, EFF_END_DATE) AS
        ( Select    6207445, '7390814', 'HOME', DATE '2024-02-26', DATE '4712-12-31' From Dual Union All
          Select    6207445, '0042548', 'HOME', DATE '2023-05-15', DATE '4712-12-31' From Dual Union All 
          Select    6207445, '6462255', 'HOME', DATE '2022-01-31', DATE '4712-12-31' From Dual Union All
          Select    6207445, '6462287', 'MAIL', DATE '2022-01-31', DATE '4712-12-31' From Dual Union All
          Select    6207445, '0042571', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' From Dual Union All
          Select    1111111, '0042578', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' From Dual 
        )

--      M a i n    S Q L :
SELECT    PERSON_ID, TYPE, PERSON_TYPE_CNT "REC_COUNT", PERSON_TYPE_LAST_ADDR "LAST ADDR_ID"
FROM    ( SELECT    PERSON_ID,  
                    TYPE,
                    COUNT(*) OVER (PARTITION BY PERSON_ID, TYPE) AS PERSON_TYPE_CNT,
                    ADDR_ID,
                    Case When Max(EFF_START_DATE) OVER(PARTITION BY PERSON_ID, TYPE) = EFF_START_DATE 
                         Then ADDR_ID
                    End AS PERSON_TYPE_LAST_ADDR, 
                    EFF_START_DATE, 
                    EFF_END_DATE
          FROM      PER_PERSON_ADDR_USAGES_F 
        )
Where    PERSON_TYPE_LAST_ADDR Is Not Null
Order By PERSON_TYPE_CNT

/*    R e s u l t :
 PERSON_ID TYPE  REC_COUNT LAST ADDR_ID
---------- ---- ---------- ------------
   1111111 MAIL          1 0042578
   6207445 MAIL          2 0042571
   6207445 HOME          3 7390814        */

ПРИМЕЧАНИЕ. Раздел «По» работает как «Группа по», но результат присутствует в каждой строке. Обычная агрегация с помощью Group By дает столько строк, сколько существует различных групп.
Выражение Case используется для определения строки с последним ADDR_ID, поэтому его можно использовать в качестве фильтра во внешнем запросе.

Аналитические функции работают со всем набором результатов и возвращают ровно столько же строк вывода, сколько и входных данных; если вы укажете PARTITION BY, то функция разделит набор результатов на разные разделы и вычислит результат внутри этого раздела, но между входными и выходными строками все равно будет однозначное соответствие.

Если вы хотите агрегировать так, чтобы на каждый раздел выводилась только одна строка, не используйте аналитические функции и используйте GROUP BY:

SELECT person_id,
       type, 
       COUNT(*) AS rec_count
FROM   PER_PERSON_ADDR_USAGES_F 
GROUP BY person_id, type
ORDER BY rec_count DESC

Что для примера данных:

CREATE TABLE PER_PERSON_ADDR_USAGES_F (PERSON_ID, ADDR_ID, TYPE, EFF_START_DATE, EFF_END_DATE) AS
SELECT 6207445, '7390814', 'HOME', DATE '2024-02-26', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042548', 'HOME', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462255', 'HOME', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462287', 'MAIL', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042571', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1111111, '0042578', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL;

Выходы:

Если вы хотите найти последний адрес, используйте KEEP (DENSE_RANK LAST ...):

SELECT person_id,
       type, 
       MAX(addr_id) KEEP (DENSE_RANK LAST ORDER BY eff_start_date) AS addr_id,
       COUNT(*) AS rec_count
FROM   PER_PERSON_ADDR_USAGES_F 
GROUP BY person_id, type
ORDER BY rec_count DESC

Что для примера данных выводит:

Альтернативно, используйте аналитические функции, а затем фильтруйте, чтобы получить только первую строку каждого раздела:

SELECT person_id,
       type, 
       addr_id,
       rec_count
FROM   (
  SELECT person_id,
         type, 
         addr_id,
         ROW_NUMBER() OVER (PARTITION BY person_id, type ORDER BY eff_start_date DESC)
           AS rn,
         COUNT(*) OVER (PARTITION BY person_id, type) AS rec_count
  FROM   PER_PERSON_ADDR_USAGES_F 
)
WHERE  rn = 1
ORDER BY rec_count DESC

Что выводит то же, что и предыдущий запрос.

рабочий пример