Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Группировка типа "или" - не могу придумать как реализовать  [new]
JoeD
Member

Откуда: Москва
Сообщений: 266
"А вот кому зайца..", мозги поломать.. Первая половина заголовка - скорее метафора, не знаю как точнее описать :)
Сначала задача показалась простой, но все "не алгоритмические" решения выходят зависимыми от данных.. То ли не знал, то ли забыл, а может все сразу.. Интерес скорее академический, но может перерасти и в практический.
(конкретно такого кейса в данных пока нет, есть упрощенные, которые решаются, но это пока..)
Итак: есть таблица, 1)первичный ключ 2)натуральный неуникальный ключ 3) первый аттрибут 4) второй аттрибут
WITH t AS (SELECT 1 ID, 1 item_id, 'A' first_att, 5 second_att FROM DUAL UNION ALL
	       SELECT 2 ID, 1 item_id, 'A' first_att, 6 second_att FROM DUAL UNION ALL
	   	   SELECT 3 ID, 1 item_id, 'B' first_att, 5 second_att FROM DUAL UNION ALL
		   SELECT 4 ID, 1 item_id, 'C' first_att, 7 second_att FROM DUAL UNION ALL
		   SELECT 5 ID, 1 item_id, 'D' first_att, 3 second_att FROM DUAL UNION ALL
		   SELECT 6 ID, 1 item_id, 'E' first_att, 2 second_att FROM DUAL UNION ALL
		   SELECT 7 ID, 1 item_id, 'E' first_att, 1 second_att FROM DUAL UNION ALL
--		   SELECT 8 ID, 1 item_id, 'E' first_att, 5 second_att FROM DUAL UNION ALL
		   SELECT 9 ID, 1 item_id, 'D' first_att, 3 second_att FROM DUAL UNION ALL
		   SELECT 10 ID, 2 item_id, 'A' first_att, 1 second_att FROM DUAL 		   
           )
SELECT * FROM t	
два элемента считаются принадлежащими одной группе, если у них совпадают натуральные ключи (item_id) и первые аттрибуты ИЛИ вторые аттрибуты. Задача - найти максимальные ID для каждой группы. В вышенарисованном примере (с закоментаренной 8) это 3,4,7,9,10. Если раскомментарить строку, результат должен быть 4,8,9,10 (если я не запутался :)) - появляется пересечение двух групп - 3 и 7 объединяются в группу 8.
Под упрощенным случаем я понимаю следующее (для одного item_id) -
A 1
В 2
С 2
т.е. есть "изолированные" элементы (A) и есть _одна_ "группа говна" в которую попадают все остальное, так или иначе связанное друг с другом. Это написать легко (есть или нет связанные записи). Если групп больше одной - я завис... :) Есть идеи?
P.S. Работаю в направлении "убедить разработчика, что нет смысла разбираться в видах дерьма" т.е. не мучаться и все "подозрительное" валить в одну группу, сведя к решаемой задаче. :) Тем более что этот подход кажется IMHO более логичным с точки зрения бизнеса.
24 июл 08, 22:35    [5983397]     Ответить | Цитировать Сообщить модератору
 Re: Группировка типа "или" - не могу придумать как реализовать  [new]
averevkin
Member

Откуда:
Сообщений: 2
Предлагаю сначала разобраться с заданием.
1. Очевидно, следует читать:
"если у них совпадают натуральные ключи (item_id) и ( первые аттрибуты ИЛИ вторые аттрибуты )"
,а не
"если у них совпадают ( натуральные ключи (item_id) и первые аттрибуты ) ИЛИ вторые аттрибуты"
,как стоило бы прочесть с неуказанными скобками.

2. В этом случае мне кажется, пример рассчитан неверно. А где группа, в которой item_id=1 AND first_att = 'A' ? Ее максимум должен быть 2. Где группа, в которой item_id=1 AND second_att = '2'? В ней всего один элемент (он же максимум и равен 6), но значит ли это, что она от этого перестала быть группой? - Элемент с ID 10 Вы засчитываете.

Итак, мое решение задачи будет следующим:

WITH ...
SELECT max(id), first_att, second_att, item_id FROM t	
GROUP BY CUBE (first_att, second_att, item_id)
HAVING (GROUPING(first_att)=1 OR GROUPING(second_att)=1) 
AND NOT (GROUPING(first_att)=1 AND GROUPING(second_att)=1) 
AND NOT GROUPING(item_id)=1
ORDER BY item_id,first_att, second_att

Результат (без ID = 8) будет:

MAX(ID) F SECOND_ATT ITEM_ID
---------- - ---------- ----------
2 A 1
3 B 1
4 C 1
9 D 1
8 E 1
7 1 1
6 2 1
9 3 1
8 5 1
2 6 1
4 7 1

MAX(ID) F SECOND_ATT ITEM_ID
---------- - ---------- ----------
10 A 2
10 1 2

13 rows selected.
25 июл 08, 01:29    [5983709]     Ответить | Цитировать Сообщить модератору
 Re: Группировка типа "или" - не могу придумать как реализовать  [new]
averevkin
Member

Откуда:
Сообщений: 2
P.S. Пример я все же вставил с включенным элементом с ID=8.
25 июл 08, 01:33    [5983712]     Ответить | Цитировать Сообщить модератору
 Re: Группировка типа "или" - не могу придумать как реализовать  [new]
JoeD
Member

Откуда: Москва
Сообщений: 266
averevkin
Предлагаю сначала разобраться с заданием.
1. Очевидно, следует читать:
"если у них совпадают натуральные ключи (item_id) и ( первые аттрибуты ИЛИ вторые аттрибуты )"...
Да, сорри за неоднозначность, именно этот вариант правильный.
averevkin
2. В этом случае мне кажется, пример рассчитан неверно. А где группа, в которой item_id=1 AND first_att = 'A' ?
Увы, здесь как раз все правильно - первая строка (ID=1) - группа 1, вторая строка - тоже группа 1, потому что такой first_att есть среди элементов первой группы, третья строка - тоже группа 1, по тому-же критерию, но только по second_att и т.д.

Т.е. группы выглядят следующим образом:
1,   1,  'A', 5   < - записи 1 и 2 и 3 связанны по одному из полей,  если удобнее - рассматривайте как 
START WITH ID = 1 CONNECT BY NOCYCLE 
prior item_id = item_id AND ((prior first_att=first_att) OR (prior second_att = second_att))
2,   1,  'A', 6
3,   1,  'B', 5

4,   1,  'C', 7

6,   1,  'E', 2
7,   1,  'E', 1

5,   1,  'D', 3
9,   1,  'D', 3

10, 2,  'A', 1
25 июл 08, 02:05    [5983731]     Ответить | Цитировать Сообщить модератору
 Re: Группировка типа "или" - не могу придумать как реализовать  [new]
Elic
Member

Откуда:
Сообщений: 29990
JoeD
START WITH ID = 1 CONNECT BY NOCYCLE 
prior item_id = item_id AND ((prior first_att=first_att) OR (prior second_att = second_att))
Это и есть почти решение :)
break on group_max_id skip 1

select max(group_member_id) as group_max_id, id, item_id, first_att, second_att
  from
  ( select                 id         as group_member_id,
           connect_by_root id         as id,
           connect_by_root item_id    as item_id,
           connect_by_root first_att  as first_att,
           connect_by_root second_att as second_att
      from t
      connect by nocycle item_id = prior item_id 
        and id <> prior id /* magic :( */
        and ( first_att = prior first_att
              or
              second_att = prior second_att
            )
  )
  group by id, item_id, first_att, second_att
  order by group_max_id, id
;
 GROUP_MAX_ID        ID       ITEM_ID F    SECOND_ATT
------------- --------- ------------- - -------------
            3         1             1 a             5
                      2             1 a             6
                      3             1 b             5

            4         4             1 c             7

            7         6             1 e             2
                      7             1 e             1

            9         5             1 d             3
                      9             1 d             3

           10        10             2 a             1

9 rows selected.
 GROUP_MAX_ID        ID       ITEM_ID F    SECOND_ATT
------------- --------- ------------- - -------------
            4         4             1 c             7

            8         1             1 a             5
                      2             1 a             6
                      3             1 b             5
                      6             1 e             2
                      7             1 e             1
                      8             1 e             5

            9         5             1 d             3
                      9             1 d             3

           10        10             2 a             1

10 rows selected.
25 июл 08, 09:19    [5984082]     Ответить | Цитировать Сообщить модератору
 Re: Группировка типа "или" - не могу придумать как реализовать  [new]
JoeD
Member

Откуда: Москва
Сообщений: 266
О!

Спасибище. "Слона то (onnect_by_root) я и не заметил.." :))
25 июл 08, 18:39    [5988170]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить