Нумерация групп при разбиении результирующего набора на части по значению в одном из столбцов

Полезный простенький запрос для разбиения результирующего набора на группы по значению в одном из столбцов. Для чего применяется - точно сказать не могу, т.к. к написанию этого запроса сподвиг мой коллега, но, очевидно, информация используется где-то на клиенте для удобного отображения таких групп.

На вход попадают следующие данные об адресах ADDRID и уникальных точках привязки к этим адресам IDX. Считаем, что сортировка данных проводится вне системы, но требуется сохранить естественный порядок поступления данных. Некоторые строки не имеют привязок адреса к точкам, такие строки считаем разделителями групп привязок точек к адресу:

ADDRID    IDX

999   1

999   2

999

999   4

999   5

999

999   7

          ... 

Таким образом, требуется сформировать столбец GROUPID, содержащий номера групп привязок точек к адресам, разбиваемых NULL значениями в столбце IDX, т.е. на выходе требуется получить примерно вот такие данные:

ADDRID    IDX    GROUPID

999   1      1

999   2      1

999

999   4      2

999   5      2

999

999   7      3

          ...  

Как вариант результата, NULL значения можно тоже относить к какой-либо группе, но это практически не меняет логику написания самого запроса. Как же написать такой запрос. 

q_data: Имитируем источник исходных данных, отдающий данные с учетом собственной сортировки (согласно начальным требованиям задания).

q_data_indexed: Подразумевается, что мы не можем управлять порядком следования данных в источнике, да в нем, очевидно, отсутствует тот столбец, по которому можно упорядочить данные. Поэтому, прежде всего нужно закрепить порядок поступаемых данных, для чего применяем либо ROW_NUMBER() OVER (ORDER BY <...выражение дополнительной сортировки>) RN, либо используем обычный ROWNUM. Также создаем столбец NULLC для выделения значением 1 тех строк, по которым происходит разбиение на группы, в нашем случае это те строки, в которых значение IDX IS NULL

q_data_grouped: В принципе, после выполненных выще действий остается только произвести суммирование единичек в столбце NULLC с помощью аналитической функции SUM() OVER(ORDER BY...) согласно закрепленному порядку по столбцу RN, чтобы получить нарастающий итог в столбце GROUPID. Каждое значение NULLC = 1 будет добавлять 1 в индекс группы GROUPID, а нули в NULLC не будут добавлять в него значение.

select: После формирования значения индекса группы можно убрать лишние значения индексов групп в строках, по которым производилось разбиение, но это безусловно диктуется общей логикой запроса - этого конечно можно не делать. В результате получаем следующий запрос:

with q_data as (

    select 999 addrid, 1 idx from dual union all 

    select 999 addrid, 2 idx from dual union all 

    select 999 addrid, null idx from dual union all 

    select 999 addrid, 4 idx from dual union all 

    select 999 addrid, 5 idx from dual union all 

    select 999 addrid, null idx from dual union all 

    select 999 addrid, 7 idx from dual),

q_data_indexed as (

    select addrid, idx, rownum rn, case when idx is null then 1 else 0 end nullc from q_data),

q_data_grouped as (

    select addrid, idx, sum(nullc) over(order by rn) + 1 groupid from  q_data_indexed)

select addrid, idx, case when idx is null then null else groupid end groupid from q_data_grouped

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

with dat as (

    select 1 as ord, 99 adrid, 1 as id from dual union all

    select 2 as ord, 99 adrid, 2 as id from dual union all

    select 3 as ord, 99 adrid, null as id from dual union all

    select 4 as ord, 99 adrid, 3 as id from dual union all

    select 5 as ord, 99 adrid, 4 as id from dual union all

    select 6 as ord, 99 adrid, null as id from dual union all

    select 7 as ord, 99 adrid, 5 as id from dual union all

    select 8 as ord, 99 adrid, 6 as id from dual),

l as (

    select listagg(nvl(to_char(id),'*'),',') within group(order by ord) line from dat),

parts as (

    select level as gr ,trim(',' from regexp_substr(line, '[^*]+',1,level)) as p from l connect by regexp_substr(line, '[^*]+',1,level) is not null)

select 

    t.*, p.gr 

from 

    dat t

    left join parts p on t.id in (select regexp_substr(p.p, '[^,]+',1,level) from dual connect by regexp_substr(p.p, '[^,]+',1,level) is not null)