Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
Est_vopros
Member

Откуда:
Сообщений: 51
Есть исходное множество
WITH
    Start_Set
    AS
        (SELECT 1 ID, 1 A, 2 B FROM DUAL
         UNION ALL
         SELECT 2, 3, 4 FROM DUAL
         UNION ALL
         SELECT 3, 5, 6 FROM DUAL
         UNION ALL
         SELECT 4, 7, 8 FROM DUAL
         UNION ALL
         SELECT 5, 9, 0 FROM DUAL)
SELECT *
  FROM Start_Set 


IDAB
112
234
356
478
590


Есть условия, которым должны удовлетворять подмножества:
1. Количество строк подмножества в диапазоне (2,4) - Cnt
2. Сумма по полю A в диапазоне (5,15) - Sum_A
3. Среднее арифметическое по полю B в диапазоне (4,5) -Avg_B

WITH
    Sub_Set_Thresholds
    AS
        (SELECT 2 Cnt_Min, 4 Cnt_Max, 5 Sum_A_Min, 15 Sum_A_Max, 4 Avg_B_Min, 5 Avg_B_Max from dual)
SELECT *
  FROM Sub_Set_Thresholds


CNT_MINCNT_MAXSUM_A_MINSUM_A_MAXAVG_B_MINAVG_B_MAX
2451545


результат вывести в виде списка ID строк подмножеств с рассчитанными значениями

Sub_Set_IDCntSum_AAvg_B
1;3264
1;4285
3;2285
1;3;2394
1;4;23114.67
3 окт 18, 13:06    [21693985]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16909
Ску-ка-тень.
Даже оформлять "покрасившее" лень.
+
WITH Sub_Set_Thresholds AS
            (SELECT 2 Cnt_Min, 4 Cnt_Max, 5 Sum_A_Min, 15 Sum_A_Max, 4 Avg_B_Min, 5  Avg_B_Max from dual)
, Start_Set AS (      SELECT 1 ID, 1 A, 2 B
  FROM DUAL UNION ALL SELECT 2, 3, 4
  FROM DUAL UNION ALL SELECT 3, 5, 6
  FROM DUAL UNION ALL SELECT 4, 7, 8
  FROM DUAL UNION ALL SELECT 5, 9, 0
  FROM DUAL)
, v(path,cnt,sum_a,sum_b,id) as
 (select to_char(id), 1, a, b, id
    from start_set
  union all
  select v.path || ';' || x.id,
         v.cnt + 1,
         v.sum_a + x.a,
         v.sum_b + x.b,
         x.id
    from v, start_set x
   where x.id > v.id
     and v.cnt < (select cnt_max from Sub_Set_Thresholds)
     and v.sum_a + x.a <= (select sum_a_max from Sub_Set_Thresholds)
     and v.sum_b + x.b <=
         (v.cnt + 1) * (select avg_b_max from Sub_Set_Thresholds))
select path sub_set_id, cnt, sum_a, round(sum_b / cnt, 2) avg_b
  from v
 where sum_a >= (select cnt_min from Sub_Set_Thresholds)
   and sum_b >= cnt * (select avg_b_min from Sub_Set_Thresholds)
   and cnt >= (select cnt_min from Sub_Set_Thresholds)
3 окт 18, 13:42    [21694040]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
Est_vopros
Member

Откуда:
Сообщений: 51
andrey_anonymous

Спасибо, зачёт!
Для борьбы со скукой - база 10G
3 окт 18, 14:28    [21694111]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
Est_vopros,

Ну сделай то же самое через connect by, только нельзя будет отсекать во время построения.
Соответсвенно с просадкой перфоманса. Зато меньше букв.
3 окт 18, 14:37    [21694127]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
andrey_anonymous,

Отсекать по верхней границе среднего на этапе построения выглядит не очень "надежно", особенно при немонотонно возрастающих b.
3 окт 18, 14:47    [21694151]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
-2-
Member

Откуда:
Сообщений: 14107
select s.*
from (
  select
    listagg(to_char(pt.column_value), ',') within group (order by pt.column_value) Sub_Set_ID,
    count(*) Cnt, sum(ss.a) Sum_A, avg(ss.b) Avg_B
  from (
    select rownum stid, column_value st
    from table(powermultiset(cast(multiset(select id from Start_Set) as ku$_objnumset)))
       ) p, 
    table(p.st) pt,
    Start_Set ss
  where ss.id = pt.column_value
  group by p.stid
) s
join Sub_Set_Thresholds t
on  s.Cnt   between t.Cnt_Min   and t.Cnt_Max
and s.Sum_A between t.Sum_A_Min and t.Sum_A_Max
and s.Avg_B between t.Avg_B_Min and t.Avg_B_Max
;

SUB_SET_ID        CNT      SUM_A   AVG_B
---------- ---------- ---------- -------
1,3                 2          6    4.00
2,3                 2          8    5.00
1,2,3               3          9    4.00
1,4                 2          8    5.00
1,2,4               3         11    4.67
3 окт 18, 14:48    [21694156]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16909
dbms_photoshop
andrey_anonymous,
Отсекать по верхней границе среднего на этапе построения выглядит не очень "надежно", особенно при немонотонно возрастающих b.

Туговато соображаю.
Может, контрпример для наглядности?
3 окт 18, 14:51    [21694160]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
andrey_anonymous,

, Start_Set AS (      SELECT 1 ID, 1 A, 2 B
  FROM DUAL UNION ALL SELECT 2, 3, 12
  FROM DUAL UNION ALL SELECT 3, 5, 6
  FROM DUAL UNION ALL SELECT 4, 7, 0
  FROM DUAL UNION ALL SELECT 5, 9, 1
  FROM DUAL)
3 окт 18, 15:04    [21694181]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16909
dbms_photoshop
andrey_anonymous,
, Start_Set AS (      
...
  FROM DUAL UNION ALL SELECT 4, 7, 0
...)

Спасибо.
Бессонная ночь не прошла даром :(
Этот чек действительно разумнее вынести наружу.
3 окт 18, 15:52    [21694246]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
Est_vopros
Member

Откуда:
Сообщений: 51
dbms_photoshop
andrey_anonymous,
, Start_Set AS (      SELECT 1 ID, 1 A, 2 B
  FROM DUAL UNION ALL SELECT 2, 3, 12
  FROM DUAL UNION ALL SELECT 3, 5, 6
  FROM DUAL UNION ALL SELECT 4, 7, 0
  FROM DUAL UNION ALL SELECT 5, 9, 1
  FROM DUAL)

После перестановки предиката всё работает правильно. Или в чем-то подвох?
+
WITH Sub_Set_Thresholds AS
            (SELECT 2 Cnt_Min, 4 Cnt_Max, 5 Sum_A_Min, 15 Sum_A_Max, 4 Avg_B_Min, 5  Avg_B_Max from dual)
, Start_Set AS (      SELECT 1 ID, 1 A, 2 B
  FROM DUAL UNION ALL SELECT 2, 3, 12
  FROM DUAL UNION ALL SELECT 3, 5, 6
  FROM DUAL UNION ALL SELECT 4, 7, 0
  FROM DUAL UNION ALL SELECT 5, 9, 1
  FROM DUAL)
, v(path,cnt,sum_a,sum_b,avg_b,id) as
 (select to_char(id), 1, a, b, b, id
    from start_set
  union all
  select v.path || ';' || x.id,
         v.cnt + 1,
         v.sum_a + x.a,
         v.sum_b + x.b,
         (v.sum_b + x.b) /  (v.cnt + 1),
         x.id
    from v, start_set x
   where x.id > v.id
     and v.cnt < (select cnt_max from Sub_Set_Thresholds)
     and v.sum_a + x.a <= (select sum_a_max from Sub_Set_Thresholds)
     )
select path sub_set_id, cnt, sum_a, round( avg_b,2)
  from v,Sub_Set_Thresholds
 where sum_a >= cnt_min
   and cnt >= cnt_min
   and avg_b between avg_b_min and avg_b_max

SUB_SET_IDCNTSUM_AAVG_B
1;3264
1;2;43114.67
1;2;53135


3 окт 18, 15:59    [21694256]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16909
Est_vopros
После перестановки предиката

Коллега просто указал мне на недомыслие.
21694151
3 окт 18, 16:03    [21694261]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
Est_vopros
Member

Откуда:
Сообщений: 51
-2-

Спасибо! Даже на 10G работает, если listagg заменить.
А какое решение будет работать на 10G если увеличить исходное множество
, Start_Set AS (SELECT level ID, level+1 A, level+2 B from dual connect by level < 25)
3 окт 18, 17:06    [21694359]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28492
Est_vopros
А какое решение будет работать на 10G если увеличить исходное множество
Комбинаторику не проходил?
3 окт 18, 17:42    [21694392]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям  [new]
-2-
Member

Откуда:
Сообщений: 14107
Est_vopros
А какое решение будет работать на 10G если увеличить исходное множество
, Start_Set AS (SELECT level ID, level+1 A, level+2 B from dual connect by level < 25)
Законнектбыть powermultiset_by_cardinality между Cnt_Min и Cnt_Max. Для Cnt=2-4 получится около 25 тысяч комбинаций, что для sql посильно.
Кроме того, в предположении, что нет отрицательных, из перестановок можно выкинуть исходные Start_Set, заведомо превышающие Sum_A_Max.
3 окт 18, 18:10    [21694422]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить