ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
14 сообщений из 14, страница 1 из 1
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712384
Est_vopros
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Est_vopros
Гость
Есть исходное множество
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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



IDAB112234356478590

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

Код: plsql
1.
2.
3.
4.
5.
6.
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_MAX2451545

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

Sub_Set_IDCntSum_AAvg_B 1;3264 1;4285 3;2285 1;3;2394 1;4;23114.67
...
Рейтинг: 0 / 0
03.10.2018, 13:06
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712424
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Ску-ка-тень.
Даже оформлять "покрасившее" лень.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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)

...
Рейтинг: 0 / 0
03.10.2018, 13:42
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712455
Est_vopros
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Est_vopros
Гость
andrey_anonymous
Спасибо, зачёт!
Для борьбы со скукой - база 10G
...
Рейтинг: 0 / 0
03.10.2018, 14:28
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712462
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Est_vopros,

Ну сделай то же самое через connect by, только нельзя будет отсекать во время построения.
Соответсвенно с просадкой перфоманса. Зато меньше букв.
...
Рейтинг: 0 / 0
03.10.2018, 14:37
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712474
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymous,

Отсекать по верхней границе среднего на этапе построения выглядит не очень "надежно", особенно при немонотонно возрастающих b.
...
Рейтинг: 0 / 0
03.10.2018, 14:47
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712478
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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
...
Рейтинг: 0 / 0
03.10.2018, 14:48
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712481
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
dbms_photoshopandrey_anonymous,
Отсекать по верхней границе среднего на этапе построения выглядит не очень "надежно", особенно при немонотонно возрастающих b.
Туговато соображаю.
Может, контрпример для наглядности?
...
Рейтинг: 0 / 0
03.10.2018, 14:51
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712490
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymous,

Код: plsql
1.
2.
3.
4.
5.
6.
, 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)
...
Рейтинг: 0 / 0
03.10.2018, 15:04
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712521
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
dbms_photoshopandrey_anonymous,
Код: plsql
1.
2.
3.
4.
, Start_Set AS (      
...
  FROM DUAL UNION ALL SELECT 4, 7, 0
...)


Спасибо.
Бессонная ночь не прошла даром :(
Этот чек действительно разумнее вынести наружу.
...
Рейтинг: 0 / 0
03.10.2018, 15:52
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712529
Est_vopros
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Est_vopros
Гость
dbms_photoshopandrey_anonymous,
Код: plsql
1.
2.
3.
4.
5.
6.
, 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)


После перестановки предиката всё работает правильно. Или в чем-то подвох?
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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_B1;32641;2;43114.671;2;53135
...
Рейтинг: 0 / 0
03.10.2018, 15:59
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712531
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Est_voprosПосле перестановки предиката
Коллега просто указал мне на недомыслие.
21694151
...
Рейтинг: 0 / 0
03.10.2018, 16:03
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712564
Est_vopros
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Est_vopros
Гость
-2-
Спасибо! Даже на 10G работает, если listagg заменить.
А какое решение будет работать на 10G если увеличить исходное множество
Код: plsql
1.
, Start_Set AS (SELECT level ID, level+1 A, level+2 B from dual connect by level < 25)
...
Рейтинг: 0 / 0
03.10.2018, 17:06
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712586
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Est_voprosА какое решение будет работать на 10G если увеличить исходное множествоКомбинаторику не проходил?
...
Рейтинг: 0 / 0
03.10.2018, 17:42
| Ответить | Цитировать | Написать  
Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
    #39712605
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Est_voprosА какое решение будет работать на 10G если увеличить исходное множество
Код: plsql
1.
, 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.
...
Рейтинг: 0 / 0
03.10.2018, 18:10
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная. Без PL/SQL определить подмножества, удовлетворяющие условиям
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39712481&tid=1883378]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 136ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 219ms
total:  442ms
созд. / загр.: 442ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]