Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Самый популярное ( с весами ) значение атрибута / 3 сообщений из 3, страница 1 из 1
24.08.2016, 15:46:44
    #39297206
Valergrad
Valergrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Самый популярное ( с весами ) значение атрибута
Есть табличка, в которое есть поля code - код товара, скажем ( первичный ключ ) , part - некий разрез, quantity - количество товара, и куча полей атрибутов. Для примера создадим табличку с тремя атрибутами:
Код: plsql
1.
create table test1 ( part number, code number, quantity number, attr1 varchar2(100), attr2 varchar2(100), attr3 varchar2(100) );


Заполним например такими данными:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
insert into test1 values ( 1,  1, 3, 'a', 'a', 'a');
insert into test1 values ( 1,  2, 4, 'a', 'b', 'b');
insert into test1 values ( 1,  3, 5, 'b', 'b', 'c');
insert into test1 values ( 1,  4, 100, null, null, null);
insert into test1 values ( 2,  5, 0, 'a', 'b', 'c');
insert into test1 values ( 2,  6, 1, 'a', 'a', 'b');
insert into test1 values ( 2,  7, 2, 'b', 'b', 'a');
insert into test1 values ( 2,  8, null, 'a', 'b', 'c');
commit;


Необходимо для каждой части из part найти самое популярное значение каждого атрибута с учетом количества.
Скажем, для part = 1 и атрибута attr1 у нас два возможных значения - 'a' и 'b' . Для 'a' - суммарное количество 7 ( 3+4), для 'b' - 5. Стало быть самый популярное значение attr1 в данном разрезе - 'a'. А вот у attr2 значение 'b' дает сумму 9 ( 4+5) и поэтому самое популярное. Атрибут null не может быть самым популярным ( в таком случае нужно вывести второй по популярности ).
Т.е. правильный ответ для таких данных будет выглядеть вот так:

"PART";"ATTR1";"ATTR2";"ATTR3""1";"a";"b";"c""2";"b";"b";"a"

Принимаются ваши идеи как это сделать. Сейчас это выглядит как вот такая жесть:

Код: 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.
select part,
       max((select inn.attr
           from (select row_number() over(order by decode(test1.attr1, null, 0, 1) desc, sum(test1.quantity) desc) rn,
                        test1.attr1 attr
                 from test1
                 where test1.part = t.part
                 group by test1.attr1) inn
           where inn.rn = 1)) attr1,
       max((select inn.attr
           from (select row_number() over(order by decode(test1.attr2, null, 0, 1) desc, sum(test1.quantity) desc) rn,
                        test1.attr2 attr
                 from test1
                 where test1.part = t.part
                 group by test1.attr2) inn
           where inn.rn = 1)) attr2,
       max((select inn.attr
           from (select row_number() over(order by decode(test1.attr3, null, 0, 1) desc, sum(test1.quantity) desc) rn,
                        test1.attr3 attr
                 from test1
                 where test1.part = t.part
                 group by test1.attr3) inn
           where inn.rn = 1)) attr3
from test1 t
group by part ;





Выглядит ужасно и работает медленно ( в таблице может быть до 10 000 строк, атрибутов около 50). Думаю переделать на вот такое:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with inn as
 (select code,
         part,
         attr1,
         nvl2 ( attr1 , sum(test1.quantity) over(partition by part, attr1) , -1) q1,
         attr2,
         nvl2 ( attr1 , sum(test1.quantity) over(partition by part, attr2) , -1) q2,
         attr3,
         nvl2 ( attr1 , sum(test1.quantity) over(partition by part, attr3) , -1) q3
  from test1)
select part, max(attr1) keep ( dense_rank first order by q1 desc ) attr1,
max(attr2) keep ( dense_rank first order by q2 desc ) attr2,
max(attr3) keep ( dense_rank first order by q3 desc ) attr3
from inn
group by part;



Но возможно кто-то предложит совсем красивый вариант без подзапросов, вдруг есть какой-то хитрый трюк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2016, 16:13:43
    #39297228
Глупый Телевизор
Глупый Телевизор
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Самый популярное ( с весами ) значение атрибута
Valergrad,

unpivot + group by
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.08.2016, 13:31:12
    #39297814
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Самый популярное ( с весами ) значение атрибута
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select part
     , max(ax1) a1 
     , max(ax2) a2 
     , max(ax3) a3
from(
select t.part, attr1, attr2, attr3
     , sum(t.quantity) amt
     , first_value(attr1 ignore nulls) over(partition by part order by sum(t.quantity) desc) ax1
     , first_value(attr2 ignore nulls) over(partition by part order by sum(t.quantity) desc) ax2
     , first_value(attr3 ignore nulls) over(partition by part order by sum(t.quantity) desc) ax3
  from test1 t
 group by grouping sets((t.part, t.attr1), (t.part, t.attr2), (t.part, t.attr3)) 
) group by part
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Самый популярное ( с весами ) значение атрибута / 3 сообщений из 3, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 491ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887605]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 193ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 27ms
get tp. blocked users:
 1ms
others: 229ms
total:  491ms
x
x
Закрыть


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