Нахождение максимальной комбинации ключей с одинаковым значением
Вот моя таблица:
CREATE TABLE ABC(
key NUMBER(5),
val NUMBER(5)
);
insert into ABC (key, val) values (1,1);
insert into ABC (key, val) values (1,2);
insert into ABC (key, val) values (1,3);
insert into ABC (key, val) values (2,3);
insert into ABC (key, val) values (1,4);
insert into ABC (key, val) values (2,4);
insert into ABC (key, val) values (2,5);
insert into ABC (key, val) values (3,5);
insert into ABC (key, val) values (1,6);
insert into ABC (key, val) values (2,6);
Желаемый результат:
Я хочу найти максимальные пары ключей с одинаковым значением и перечислить их. В приведенном выше примере максимальные пары ключей, встречающиеся в таблице, равны (1,2), которые имеют общие значения (3,4,6)
Ответы 2
Вы можете использовать самообъединение и аналитическую функцию следующим образом:
Select * from
(Select t.key, tt.key as key1, t.val,
Count(distinct val) over (partition by t.key, tt.key) as cnt
From your_table t join your_table tt
On t.val = tt.val and t.key < tt.key)
Order by cnt desc
fetch first 1 row with ties;
В oracle 11g предложение fetch не поддерживается. Поэтому вы должны использовать dense_rank следующим образом:
select key, key1, val from
(select key, key1, val,
dense_rank() over (order by cnt desc) as dr from(
Select a.key, b.key as key1, a.val,
Count(distinct a.val) over (partition by a.key, b.key) as cnt
From abc a join abc b
On a.val = b.val and a.key < b.key) )
where dr = 1;
http://sqlfiddle.com/#!4/40106f/15
Вы получаете сообщение об ошибке, так как предложение fetch введено в oracle 12c
Пожалуйста, попробуйте, как показано ниже:
select ab,val from (
select rank() over( order by cnt desc) rn, ab, val ,cnt
from (
select listagg(key,',') within group(order by val) ab,val,
count(1) over (partition by (listagg(key,',') within group(order by val))) cnt
from abc
group by val
having count(*) > 1))
where rn = 1;
Привет Прадип Гарг, Есть ли способ получить его без списка?
@StackOne, я использовал listagg, потому что не уверен, будет ли количество ключей постоянным. Также это может быть комбинация из 3-х клавиш
Это должно быть максимальное количество пар. В моем примере 1,2 имеет максимальное количество пар. есть 1 пара на 2,3 и 0 пар на 1,3
Я пытаюсь сказать, есть ли ниже данные: 0 вставить в значения ABC (key, val) (3,3); вставить в ABC (key, val) значения (3,6); вставить в ABC (key, val) значения (3,4); поэтому 3 ключа (1,2,3) будут иметь максимальное количество строк. Это я спрашиваю, всегда ли будет 2 ключа или количество ключей может быть разным
Да, в этом случае вывод должен отображать (key,key1,key2,val) (1,2,3,3 ) (1,2,3,4),(1,2,3,6)
@StackOne - потому что количество столбцов динамическое, поэтому я использовал listagg.
Привет, Попай. Последняя строка кода выдает ошибку, я прикрепил ссылку. sqlfiddle.com/#!4/40106f/11