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

Откуда: Пенза
Сообщений: 75
Имеем таблицу с 12 полями: id, set, val0, val1 ... val9, где valX тип int.
Как можно выбрать уникальные пары чисел из val0 - val9?
Сдается мне, что для простоты нужно таблицу развернуть и соединять саму с собой.
23 окт 14, 13:03    [16747860]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Glory
Member

Откуда:
Сообщений: 104751
ApXyC
Имеем таблицу с 12 полями

И, например, миллионом записей ?
23 окт 14, 13:05    [16747878]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
Glory,

чуть меншь, 100500
23 окт 14, 13:06    [16747889]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Glory
Member

Откуда:
Сообщений: 104751
ApXyC
чуть меншь, 100500

Т.е. больше одной.
И уникальные пары надо искать для каждой записи ?
23 окт 14, 13:07    [16747902]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ApXyC
Glory,

чуть меншь, 100500
Пример данных и результата - в студию!
23 окт 14, 13:08    [16747908]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
Уникальные пары в разрезе одной записи, а результат можно затолкать во вторую таблицу в виде set_id, val1, val2, где set_id внешний ключ, а val1, val2 одна из уникальной пары.
23 окт 14, 13:11    [16747931]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
iap,

DECLARE @set AS TABLE(
id INT IDENTITY(0,1),
setN INT,
val0 INT,val1 INT,val2 INT,val3 INT,val4 INT,val5 INT,val6 INT,val7 INT,val8 INT,val9 INT
)
INSERT @set 
VALUES (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
       (100500, 3, 4, 1, 2, 1, 2, 2, 2, 1, 2)
SELECT * FROM @set
--для первого: 1,2 ; 1,3; 1,4; 1,5; 2,3; 2,4; 2,5; 3,4; 3,5
--для второго: 1,2 ; 1,3; 1,4; 2,3; 2,4; 3,4;


например так
23 окт 14, 13:20    [16748015]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ApXyC
iap,

DECLARE @set AS TABLE(
id INT IDENTITY(0,1),
setN INT,
val0 INT,val1 INT,val2 INT,val3 INT,val4 INT,val5 INT,val6 INT,val7 INT,val8 INT,val9 INT
)
INSERT @set 
VALUES (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
       (100500, 3, 4, 1, 2, 1, 2, 2, 2, 1, 2)
SELECT * FROM @set
--для первого: 1,2 ; 1,3; 1,4; 1,5; 2,3; 2,4; 2,5; 3,4; 3,5
--для второго: 1,2 ; 1,3; 1,4; 2,3; 2,4; 3,4;



например так
А как же 100500?
На простом русском языке можете объяснить задачу?
Пока всё больше запутывается.
23 окт 14, 13:25    [16748067]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Пока напрашивается преобразование каждой строки чисел через запятую в множество строк с этими числами
+ JOIN двух таких наборов с DISTINCT по условию "число из второго набора больше числа из первого"
+ сборка результата обратно в строку, например, с помощью FOR XML PATH('')
23 окт 14, 13:29    [16748108]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
45 пар перебрать для каждой строки, можно и cross apply написать.
23 окт 14, 13:32    [16748139]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
iap,

а говорите не поняли =) Спасибо за ответ, подтвердили мое предположение.
23 окт 14, 13:33    [16748148]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4896
ApXyC
iap,

DECLARE @set AS TABLE(
id INT IDENTITY(0,1),
setN INT,
val0 INT,val1 INT,val2 INT,val3 INT,val4 INT,val5 INT,val6 INT,val7 INT,val8 INT,val9 INT
)
INSERT @set 
VALUES (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
       (100500, 3, 4, 1, 2, 1, 2, 2, 2, 1, 2)
SELECT * FROM @set
--для первого: 1,2 ; 1,3; 1,4; 1,5; 2,3; 2,4; 2,5; 3,4; 3,5
--для второго: 1,2 ; 1,3; 1,4; 2,3; 2,4; 3,4;


например так


Я правильно понимаю, надо для (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
1) Взять все уникальные значения в одной записи (множество (1, 2, 3, 4, 5),)
2) выбрать все попарные комбинации этих значений, где первое не совпадает со вторым (1,2 ; 1,3; 1,4; 1,5; 2,3; 2,4; 2,5; 3,4; 3,5)
?

Ну тык надо на каждую запись подзапрос в нём UNPIVOT, DISTINCT, CROSS JOIN/APPLY саму на себя
23 окт 14, 13:37    [16748180]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
a_voronin

Я правильно понимаю, надо для (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
1) Взять все уникальные значения в одной записи (множество (1, 2, 3, 4, 5),)
2) выбрать все попарные комбинации этих значений, где первое не совпадает со вторым (1,2 ; 1,3; 1,4; 1,5; 2,3; 2,4; 2,5; 3,4; 3,5)
?

Ну тык надо на каждую запись подзапрос в нём UNPIVOT, DISTINCT, CROSS JOIN/APPLY саму на себя


Ага, благодарствую!

Владислав Колосов
45 пар перебрать для каждой строки, можно и cross apply написать.

Ни асилил как в данному случае применить CROSS APPLY. Есть идеи?
23 окт 14, 13:43    [16748255]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Владислав Колосов
45 пар перебрать для каждой строки, можно и cross apply написать.
Не пойдёт, ибо выберет каждую пару дважды: (F1,F2) и (F2,F1)
23 окт 14, 13:55    [16748351]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ApXyC,

про парсинг строк тут сотни тем.
И статья хорошая (но достаточно старая) есть.

Вот, например: https://www.sql.ru/forum/847500/funkciya-kotoraya-delit-stroku-na-slova
И много тем было, где спецы по XML строку с числами на строки разбирали
23 окт 14, 13:58    [16748371]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4896
DECLARE @set AS TABLE(
id INT IDENTITY(0,1),
setN INT,
val0 INT,val1 INT,val2 INT,val3 INT,val4 INT,val5 INT,val6 INT,val7 INT,val8 INT,val9 INT
)
INSERT @set 
VALUES (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
       (100500, 3, 4, 1, 2, 1, 2, 2, 2, 1, 2)
SELECT 
	id, SetN,
	(
		SELECT CONCAT(v1, ',',  v2, ';')
		FROM 
		(
			SELECT DISTINCT v1 = a.v, v2 = b.v FROM 
				(SELECT v = val0 UNION ALL SELECT val1 UNION ALL SELECT val2 UNION ALL SELECT val3 UNION ALL SELECT val4 UNION ALL SELECT val5 UNION ALL SELECT val6 UNION ALL SELECT val7 UNION ALL SELECT val8 UNION ALL SELECT val9 INT )a,
				(SELECT v = val0 UNION ALL SELECT val1 UNION ALL SELECT val2 UNION ALL SELECT val3 UNION ALL SELECT val4 UNION ALL SELECT val5 UNION ALL SELECT val6 UNION ALL SELECT val7 UNION ALL SELECT val8 UNION ALL SELECT val9 INT )b
			WHERE a.v > b.v
		) x
		FOR XML PATH('')
	)
FROM @set


Если у вас не 2012, то CONCAT перепишите на обычную конкатенацию строк с CAST
23 окт 14, 14:00    [16748389]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
a_voronin,

Хе-хе, спасибо =)
23 окт 14, 14:03    [16748421]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
iap
Владислав Колосов
45 пар перебрать для каждой строки, можно и cross apply написать.
Не пойдёт, ибо выберет каждую пару дважды: (F1,F2) и (F2,F1)


В 45 пар такая комбинация не входит, только уникальные пары.

select * from (values (f1,f2),(f1,f3)) t1 (fld1,fld2)


etc.
23 окт 14, 14:28    [16748660]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Владислав Колосов
iap
пропущено...
Не пойдёт, ибо выберет каждую пару дважды: (F1,F2) и (F2,F1)


В 45 пар такая комбинация не входит, только уникальные пары.

select * from (values (f1,f2),(f1,f3)) t1 (fld1,fld2)



etc.
CROSS JOIN разве не даст и (f1,f2), и (f2,f1)?
Если, конечно, не отбросить лишние в WHERE
23 окт 14, 14:31    [16748688]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Имеется в виду, если значение в паре будет перевернуто, т.е. (5,1) = (1,5)? Отфильтровать можно, так и есть.
23 окт 14, 14:53    [16748893]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
ТС не сказал, что для каждого setN нужно получить одну строку, поэтому еще одно решение:

DECLARE @set AS TABLE(
id INT IDENTITY(0,1),
setN INT,
val0 INT,val1 INT,val2 INT,val3 INT,val4 INT,val5 INT,val6 INT,val7 INT,val8 INT,val9 INT
)
INSERT @set 
VALUES (1, 1, 2, 3, 4, 5, 1, 1, 1, 3, 2),
       (100500, 3, 4, 1, 2, 1, 2, 2, 2, 1, 2)
;
with num as (--просто таблица чисел
     select *
       from (values (1), (2), (3), (4), (5), (6), (7), (8), (9)) as T(n)
)
, B as (--сворачиваем исходную таблицу
  select distinct setN
        ,case n when 1 then val0
                when 2 then val1
                when 2 then val2
                when 3 then val3
                when 4 then val4
                when 5 then val5
                when 6 then val6
                when 7 then val7
                when 8 then val8
                when 9 then val9
         end as v
    from       num n1
    cross join @set   a
)

select  a.setN, a.v as v1, b.v as v2 --формируем пары
  from B  a
  join B  b on a.setN=b.setN and   a.v<b.v
  order by a.setN,a.v,b.v


Результат:

setN v1 v2
1 1 2
1 1 3
1 1 4
1 1 5
1 2 3
1 2 4
1 2 5
1 3 4
1 3 5
1 4 5
100500 1 2
100500 1 3
100500 1 4
100500 2 3
100500 2 4
100500 3 4
23 окт 14, 16:26    [16749684]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
ApXyC
Member

Откуда: Пенза
Сообщений: 75
Wlr-l,

Да, вывод результата специально опущен. То же интересное решение. Спасибо.
23 окт 14, 16:30    [16749721]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать пары чисел  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
ApXyC,
Забыл сказать, что ключевое слово distinct можно перенести из "сворачивания" в "формирование", что позволит немного выиграть на сортировках.
23 окт 14, 16:41    [16749807]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить