Как найти несколько пар в одной таблице оракула

SQL идеально подходит для подхода грубая сила для решения проблем (единственная проблема в том, что для больших данных запрос будет вешать навсегда).

Здесь возможный подход шаг за шагом, учитывающий только один встречный счет на первом этапе, чем два на втором этапе и т. д.

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

Первый шаг — тривиальное самообъединение, объединение таблицы и ограничение GROUP и value. Создается таблица результатов, которая в дальнейшем используется для ограничения уже совпавших строк.

create table tab_match as 
-- 1 row match
select b.ID, b.GROUP_ID, b.BILL_NUMBER, b.VALUE, a.partner_number from tab a
join tab b 
on a.group_id = b.group_id and /* same group */
-1 * a.value = b.value /* oposite value */
where a.partner_number is not NULL  /* consider group row only */

На втором шаге вы повторяете то же самое, только добавляя одно объединение (исследуем два субсчета) с дополнительным ограничением на общую стоимость -1 * a.value = (b.value + c.value) Также мы подавляем все уже назначенные partner_number и bills. Результат вставляется во временную таблицу.

insert into tab_match (ID, GROUP_ID, BILL_NUMBER, VALUE, PARTNER_NUMBER)
select b.ID, b.GROUP_ID, b.BILL_NUMBER, b.VALUE, a.partner_number partner_number_match from tab a
join tab b 
on a.group_id = b.group_id and /* same group */
sign(a.value) * sign(b.value) < 0 and  /* values must have oposite signs */
abs(a.value) > abs(b.value) /* the partial value is lower than the sum */
join tab c /* join to 2nd table */
on a.group_id = c.group_id and 
sign(a.value) * sign(c.value) < 0 and 
abs(a.value) > abs(c.value) and
-1 * a.value = (b.value + c.value)
where a.partner_number is not NULL and /* consider open group row only */
a.partner_number  not in (select partner_number from tab_match) and
a.id not in (select id from tab_match) /* ignore matched rows */
;

Вы должны продолжить обработку 3,4 и т.д. строк, пока не будут назначены все partner_numbers и bills.

Добавить следующее присоединение

join tab d  
on a.group_id = d.group_id and 
sign(a.value) * sign(d.value) < 0 and 
abs(a.value) > abs(d.value)

и настроить предикат общей суммы на каждом шаге

-1 * a.value = (b.value + c.value + d.value)

Удачи;)

Следующий код SQL был протестирован с Oracle 12c и 18c соответственно. Идеи/шаги:

{1} Разбить исходную таблицу на таблицы MINUSES и PLUSES, содержащие только положительные числа, что избавит нас от нескольких вызовов функций позже.

{2} Создайте 2 представления, которые будут находить комбинации плюсов, соответствующие определенному минусу (и наоборот).

{3} Перечислите все компоненты через запятую в таблице под названием ALLCOMPONENTS.

{4} Таблица GAPFILLERS: Раскройте (через запятую) идентификаторы всех компонентов, получив таким образом все необходимые значения для заполнения пробелов в исходной таблице.

{5} LEFT JOIN исходной таблицы с GAPFILLERS.

Исходная таблица/данные

create table bills ( id primary key, bgroup, bnumber, bvalue, partner )
as
select 1, 1, 111, 10.90, null from dual union all
select 2, 1, 751, 40.28, null from dual union all
select 3, 1, 438, 125.60, null from dual union all 
select 4, 1, 659, -10.90, 987 from dual union all
select 5, 1, 387, -165.88, 755 from dual union all
select 6, 1, 774, -100.10, null from dual union all 
select 7, 1, 664, -80.12, null from dual union all 
select 8, 1,   259, 180.22, 999 from dual union all
select 9, 2,   774, -200.10, null from dual union all 
select 10, 2,   664, -80.12, null from dual union all 
select 11, 2,   259, 280.22, 777 from dual ;

{1} разделить таблицу на ПЛЮС и МИНУС

-- MINUSes
create table minuses as
select id
, bgroup       as mgroup
, bnumber      as mnumber
, bvalue * -1  as mvalue
, partner      as mpartner 
from bills where bvalue < 0 ;

-- PLUSes
create table pluses as
select id
, bgroup  as pgroup
, bnumber as pnumber
, bvalue  as pvalue
, partner as ppartner  
from bills where bvalue >= 0 ;

{2} Просмотр: поиск компонентов PLUSvalues

-- used here: "recursive subquery factoring" 
-- and LATERAL join (needs Oracle 12c or later)
create or replace view splitpluses
as
with recursiveclause ( nextid, mgroup, tvalue, componentid )
as (
  select                     -- anchor member
    id            as nextid
  , mgroup        as mgroup
  , mvalue        as tvalue  -- total value 
  , to_char( id ) as componentid
  from minuses
  union all
  select                     -- recursive member
    M.id
  , R.mgroup
  , R.tvalue + M.mvalue
  , R.componentid || ',' || to_char( M.id )
  from recursiveclause R
    join minuses M
      on M.id > R.nextid and M.mgroup = R.mgroup -- only look at values in the same group
)
--
select
  mgroup
, tvalue      as plusvalue
, componentid as minusids
, ppartner
from 
  recursiveclause R
, lateral ( select ppartner from pluses P where R.tvalue = P.pvalue ) -- fetch the partner id
where 
  tvalue in ( select pvalue from pluses where ppartner is not null ) -- get all relevant pvalues that must be broken down into components
  and ppartner is not null -- do this for all pluses that have a partner id
;

{2b} Представление: поиск компонентов MINUSvalues

create or replace view splitminuses
as
with recursiveclause ( nextid, pgroup, tvalue, componentid )
as (
  select                     -- anchor member
    id            as nextid
  , pgroup        as pgroup
  , pvalue        as tvalue  -- total value 
  , to_char( id ) as componentid
  from pluses
  union all
  select                     -- recursive member
    P.id
  , R.pgroup
  , R.tvalue + P.pvalue
  , R.componentid || ',' || to_char( P.id )
  from recursiveclause R
    join pluses P
      on P.id > R.nextid and P.pgroup = R.pgroup
)
--
select
  pgroup
, tvalue      as minusvalue
, componentid as plusids
, mpartner
from 
  recursiveclause R
, lateral ( select mpartner from minuses M where R.tvalue = M.mvalue )
where 
  tvalue in ( select mvalue from minuses where mpartner is not null )
  and mpartner is not null
;

Представления дают нам следующие наборы результатов:

SQL> select * from splitpluses;
MGROUP  PLUSVALUE  MINUSIDS  PPARTNER  
1       180.22     6,7       999       
2       280.22     9,10      777    

SQL> select * from splitminuses ;
PGROUP  MINUSVALUE  PLUSIDS  MPARTNER  
1       10.9        1        987       
1       165.88      2,3      755 

{3} Таблица ALLCOMPONENTS: список всех "компонентов"

create table allcomponents ( type_, group_, value_, cids_, partner_ )
as
select 'components of PLUS' as type_, M.* from splitminuses M
union all
select 'components of MINUS', P.* from splitpluses P
;

SQL> select * from allcomponents ;
TYPE_                GROUP_  VALUE_  CIDS_  PARTNER_  
components of PLUS   1       10.9    1      987       
components of PLUS   1       165.88  2,3    755       
components of MINUS  1       180.22  6,7    999       
components of MINUS  2       280.22  9,10   777 

{4} Таблица GAPFILLERS: производная от ALLCOMPONENTS, содержит все значения, необходимые для заполнения «пробелов» в исходной таблице.

-- One row for each CSV (comma-separated value) of ALLCOMPONENTS
create table gapfillers
as
select unique type_, group_, value_
, trim( regexp_substr( cids_, '[^,]+', 1, level ) ) cids_
, partner_
from (
  select type_, group_, value_, cids_, partner_
  from allcomponents
) AC 
connect by instr( cids_, ',', 1, level - 1 ) > 0
order by group_, partner_ ;

SQL> select * from gapfillers ;
TYPE_                GROUP_  VALUE_  CIDS_  PARTNER_  
components of PLUS   1       165.88  2      755       
components of PLUS   1       165.88  3      755       
components of PLUS   1       10.9    1      987       
components of MINUS  1       180.22  6      999       
components of MINUS  1       180.22  7      999       
components of MINUS  2       280.22  10     777       
components of MINUS  2       280.22  9      777       

7 rows selected.

{5} Последнее ЛЕВОЕ СОЕДИНЕНИЕ

select
  B.id, bgroup, bnumber, bvalue
, case 
    when B.partner is null then G.partner_
    else B.partner
  end as partner
from bills B
  left join gapfillers G on B.id = G.cids_ 
order by 1 ; 

-- result
ID  BGROUP  BNUMBER  BVALUE   PARTNER  
1   1       111      10.9     987      
2   1       751      40.28    755      
3   1       438      125.6    755      
4   1       659      -10.9    987      
5   1       387      -165.88  755      
6   1       774      -100.1   999      
7   1       664      -80.12   999      
8   1       259      180.22   999      
9   2       774      -200.1   777      
10  2       664      -80.12   777      
11  2       259      280.22   777      


11 rows selected. 

DBFIDDLE здесь.