Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Поиск дублей в EAV / 19 сообщений из 19, страница 1 из 1
09.12.2020, 15:47
    #40025979
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Подскажите, как решать такую задачу.
Есть дополнительные атрибуты объекта, реализованные с помощью EAV.
Например так:
itemtypevalueitem1aa11item1bb22item1cc33item2aa11item2bb11item3aa11item3bb11item3cc11item3dd44
Нужно найти дубли сочетаний атрибутов.
Например я считаю, что сочетание атрибутов aa+bb должно быть уникальным.
В приведенном примере эта уникальность нарушается для item2 и item3.

Пока сделал "в лоб", развернул атрибуты в столбцы и сравниваю вложенные запросы:
Код: plsql
1.
2.
3.
4.
5.
with details (select ..., a1.value as a1, a2.value as a2 from items join attr a1 on a1.item=items.item and a1.type='aa' join attr a2 on a2.item=items.item and a2='bb')
select ...
from details
group by a1, a2
having count(*) > 1



А можно ли как-то проверять на этапе соединения?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 15:54
    #40025987
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SQL> ed
Wrote file afiedt.buf

  1  with t (item,type,value) as (
  2  select 'item1','aa',       11 from dual union all
  3  select 'item1','aa',       77 from dual union all
  4  select 'item1','bb',       22 from dual union all
  5  select 'item1','cc',       33 from dual union all
  6  select 'item2','aa',       11 from dual union all
  7  select 'item2','bb',       11 from dual union all
  8  select 'item3','aa',       11 from dual union all
  9  select 'item3','bb',       11 from dual union all
 10  select 'item3','cc',       11 from dual union all
 11  select 'item3','dd',       44 from dual
 12  )
 13* select item from t where type in ('aa','bb') group by item having count(distinct value)<>count(value)
SQL> /

ITEM
-----
item3
item2



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 15:55
    #40025989
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
В смысле, from items having count (distinct type) >=2 ?
Так набор атрибутов может быть произвольным, как и их значения.
Или как?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 15:57
    #40025991
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.Нужно найти дубли сочетаний атрибутов.
Например я считаю, что сочетание атрибутов aa+bb должно быть уникальным.

В этом случае выбор EAV был ошибкой проектирования.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 16:17
    #40026003
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Хорошо, завтра отправлю письмо машиной времени.
Но сегодня EAV уже есть, а необходимость проверки на уникальность сочетаний атрибутов появилась недавно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 16:26
    #40026010
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.

Или как?


поправил 22245007

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 16:56
    #40026023
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Мне непонятна постановка задачи (как и две предыдущих от вас, решение которых затягивалось из-за плохой постановки).

Как вы определяете уникальность сочетаний атрибутов, внутри entity или внутри таблицы?

Item2 и item3 нарушают уникальность бежду собой, или каждый для себя, потому что у каждого аа.value=bb.value?

aa+bb дан как пример из более общего правила, или уникальность определена только для aa+bb?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 17:01
    #40026024
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Ну или напишите ваш запрос без ошибок. Где по-русски двусмыслица, код поможет донести мыслЮ.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 17:26
    #40026035
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.Но сегодня EAV уже есть, а необходимость проверки на уникальность сочетаний атрибутов
появилась недавно.

Раз появилась такая необходимость, значит переделывайте структуру, выносите эти два
атрибута в обычную таблицу.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 17:29
    #40026038
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Вот рабочее решение:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with CL as
(
  select '#'||C.CUSTOMER_ID as ID
  , CC.VALUE as CODE
  , nvl2(CP.VALUE, nvl2(CN.VALUE, CN.VALUE||' @'||CP.VALUE, null), null) as ATTR
  from CUSTOMERS C
  left join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
  left join BM_CUSTOMER_CONTACT CN on (CN.CUSTOMER_ID = C.CUSTOMER_ID and CN.CONTACT_DICT_ID = 220)
  left join BM_CUSTOMER_CONTACT CP on (CP.CUSTOMER_ID = C.CUSTOMER_ID and CP.CONTACT_DICT_ID = 221)
)
select ATTR, replace(wm_concat(nvl(CODE,ID)),',','; ')
from CL
where ATTR is not null
group by ATTR
having count(*) > 1



Вопрос был в том, можно ли его улучшить.
Вариант с having count(distinct value)<>count(value) довольно необычный, нужно обдумать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 18:21
    #40026068
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.
Вариант с having count(distinct value)<>count(value) довольно необычный, нужно обдумать.
по-моему, самый простой



можно еще так, но тут нет
Stax
Alibek B.,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SQL> ed
Wrote file afiedt.buf

  1  with t (item,type,value) as (
  2  select 'item1','aa',       11 from dual union all
  3  select 'item1','aa',       77 from dual union all
  4  select 'item1','bb',       22 from dual union all
  5  select 'item1','cc',       33 from dual union all
  6  select 'item2','aa',       11 from dual union all
  7  select 'item2','bb',       11 from dual union all
  8  select 'item3','aa',       11 from dual union all
  9  select 'item3','bb',       11 from dual union all
 10  select 'item3','cc',       11 from dual union all
 11  select 'item3','dd',       44 from dual
 12  )
 13* select item from t where type in ('aa','bb') group by item having count(distinct value)<>count(value)
SQL> /

ITEM
-----
item3
item2




.....
stax


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SQL> WITH T (item,TYPE,VALUE) AS (
 SELECT 'item1','aa',       11 FROM dual UNION ALL
 SELECT 'item3','aa',       11 FROM dual UNION ALL
 SELECT 'item3','bb',       11 FROM dual
 )
SELECT item
  FROM T
 WHERE TYPE IN ('aa','bb')
 GROUP BY item
HAVING COUNT(DISTINCT VALUE)<>COUNT(VALUE)

ITEM 
-----
item3
1 row selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 18:40
    #40026073
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
для любителей извращений чуть посложнее
Код: 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.
SQL> WITH T (item,TYPE,VALUE) AS (
 SELECT 'item1','aa',       11 FROM dual UNION ALL
 SELECT 'item3','aa',       11 FROM dual UNION ALL
 SELECT 'item3','bb',       11 FROM dual UNION ALL
 SELECT 'item4','aa',       43 FROM dual UNION ALL
 SELECT 'item4','bb',       44 FROM dual UNION ALL
 SELECT 'item5','aa',       43 FROM dual UNION ALL
 SELECT 'item5','bb',       44 FROM dual
 )
SELECT * FROM( 
SELECT item,
       COUNT(*) OVER(PARTITION BY
            MAX(CASE WHEN TYPE IN ('aa') THEN VALUE END),
            MAX(CASE WHEN TYPE IN ('bb') THEN VALUE END)
            ) cnt
  FROM T
  GROUP BY item
)
WHERE cnt > 1

ITEM         CNT
----- ----------
item4          2
item5          2

2 rows selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 18:50
    #40026079
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
andreymx,

судя из 22245082
Alibek B. наверное надо другое

для контрактов 100, 220, 221 надо что-то искать, а не для типов аа/ бб

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.12.2020, 19:11
    #40026094
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Alibek B.
Вот рабочее решение:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with CL as
(
  select '#'||C.CUSTOMER_ID as ID
  , CC.VALUE as CODE
  , nvl2(CP.VALUE, nvl2(CN.VALUE, CN.VALUE||' @'||CP.VALUE, null), null) as ATTR
  from CUSTOMERS C
  left join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
  left join BM_CUSTOMER_CONTACT CN on (CN.CUSTOMER_ID = C.CUSTOMER_ID and CN.CONTACT_DICT_ID = 220)
  left join BM_CUSTOMER_CONTACT CP on (CP.CUSTOMER_ID = C.CUSTOMER_ID and CP.CONTACT_DICT_ID = 221)
)
select ATTR, replace(wm_concat(nvl(CODE,ID)),',','; ')
from CL
where ATTR is not null
group by ATTR
having count(*) > 1



Вопрос был в том, можно ли его улучшить.
Вариант с having count(distinct value)<>count(value) довольно необычный, нужно обдумать.


Если выполняются условия, что в таблице BM_CUSTOMER_CONTACT нет строк с value=null, то можно сократить, особо не мудря:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CL as
(
  select '#'||C.CUSTOMER_ID as ID
  , CC.VALUE as CODE
  , CN.VALUE as CNATTR
  , CP.VALUE as CPATTR
  from CUSTOMERS C
       join BM_CUSTOMER_CONTACT CN on (CN.CUSTOMER_ID,CN.CONTACT_DICT_ID) = ((C.CUSTOMER_ID,220))
       join BM_CUSTOMER_CONTACT CP on (CP.CUSTOMER_ID,CP.CONTACT_DICT_ID) = ((C.CUSTOMER_ID,221))
  left join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID,CC.CONTACT_DICT_ID) = ((C.CUSTOMER_ID,100))
)
select CNATTR||' @'||CPATTR, replace(wm_concat(nvl(CODE,ID)),',','; ')
from CL
group by CNATTR,CPATTR
having count(*) > 1



Внутренние джойны уберут необходимость фильтровать по null.

Можно разворачивать через pivot (меньше текста), но тогда нужно снова фильтровать null.
Общая идея, находит дубли только в таблице EAV.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
select cn_attr,cp_attr,
       wm_concat(customer_id)
  from BM_CUSTOMER_CONTACT b
 pivot (max(value) as attr for (contact_dict_id) in (220 as cn,221 as cp))
 where cn_attr is not null and cp_attr is not null -- эта часть выглядит громоздко, на мой взгляд
 group by cn_attr,cp_attr
having count(*) > 1



Тест таблицы, если другие хотят поиграться и улучшить:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with CUSTOMERS (customer_id,customer_name) as (
  select 1, 'Peter' from dual union all
  select 2, 'Megan' from dual
),
BM_CUSTOMER_CONTACT (customer_id,contact_dict_id,value) as (
  select 1, 100, 'Peters100' from dual union all
  select 1, 220, 'Peters220' from dual union all
  select 1, 221, 'Peters221' from dual union all
  select 2, 100, 'Megans100' from dual union all
  select 2, 220, 'Peters220' from dual union all
  select 2, 221, 'Peters221' from dual
)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2020, 00:16
    #40026184
mcureenab
mcureenab
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
НеофитSQL

Можно разворачивать через pivot (меньше текста), но тогда нужно снова фильтровать null.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
select cn_attr,cp_attr,
       wm_concat(customer_id)
  from BM_CUSTOMER_CONTACT b
 pivot (max(value) as attr for (contact_dict_id) in (220 as cn,221 as cp))
 where cn_attr is not null and cp_attr is not null -- эта часть выглядит громоздко, на мой взгляд
 group by cn_attr,cp_attr
having count(*) > 1





Добавь в pivot (max(value)) тэг 0 или 1:

pivot (max(value||nvl2(value,'0', '1'))

тогда cn_attr,cp_attr будут null только при отсутствии записи EAV. в остальных случаях последним символом cn_attr,cp_attr будет 0 или 1, что в group by cn_attr,cp_attr различит value is null их от несуществующего value как поля записи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2020, 02:16
    #40026216
НеофитSQL
НеофитSQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
Попутный комментарий: проверка на нежелательное присутствие null в нескольких колонках довольно частая операция.

Если колонки одного типа, я использую
Код: plsql
1.
where coalesce(colX,colY,colZ) is not null


чтобы отфильтровать "все null", и

Код: plsql
1.
where least(colX,colY,colZ) is not null


чтобы отфильтровать "хотя бы один null".

Мне это нравится больше, чем повторяющиеся "is not null or" или "is not null and".
Обе функции принимают переменное число параметров и наверное эффективны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2020, 02:18
    #40026217
mcureenab
mcureenab
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
НеофитSQL

Общая идея, находит дубли только в таблице EAV.


заход с другой стороны

Код: 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.
29.
30.
31.
32.
33.
34.
35.
create table eav ( e number, a char(2), v number, unique (a, e));

insert into eav values (1, 'x', 101);
insert into eav values (1, 'y', 102);
insert into eav values (1, 'z', 103);
insert into eav values (2, 'x', 201);
insert into eav values (2, 'y', 202);
insert into eav values (2, 'z', 203);
insert into eav values (3, 'x', 201);
insert into eav values (3, 'y', 302);
insert into eav values (3, 'z', 303);
insert into eav values (4, 'x', 201);
insert into eav values (4, 'y', 202);
insert into eav values (4, 'z', 403);
insert into eav values (5, 'x', 201);
insert into eav values (5, 'y', 302);
insert into eav values (5, 'z', 503);
insert into eav values (6, 'x', 201);
insert into eav values (6, 'y', 302);
insert into eav values (6, 'z', 603);

create type e_t as table of number;

with dup1 as (
select a, v, cast(collect(e) as e_t) et
from eav
where a in ('x', 'y')
group by a, v
having count(*) > 1
)
select t.* from dup1 dx, table(dx.et) t where dx.a = 'x'
intersect
select t.* from dup1 dx, table(dx.et) t where dx.a = 'y'

drop table eav;



возвращает кучей объекты с дублями AV как x так и y. можно набор ключевых атрибутов расширять единообразно. неполный набор A (без x и/или y во всех записях группы) не считает.

для группировки или сортировки повторяющихся (x.V, y.V) над E, скорее всего нужен pivot в том или ином виде.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2020, 09:52
    #40026259
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
andreymx
для любителей извращений чуть посложнее

Если я правильно понял, это для контроля уникальности набора атрибутов.
А мне нужен контроль уникальности значений набора атрибутов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2020, 09:54
    #40026261
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск дублей в EAV
mcureenab
Можно разворачивать через pivot (меньше текста)

Все время забываю уточнить, у меня 10g.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Поиск дублей в EAV / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 614ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880624]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 213ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 310ms
total:  614ms
x
x
Закрыть


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