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

Откуда:
Сообщений: 173
Добрый день!
Подскажите как можно реализовать следующее, никак не могу сообразить.

Есть таблица с данными base (s1 varchar(max),s2 varchar(max),s3 varchar(max))

Из нее необходимо выбрать только уникальные пары:

Например из данных:

S1 S2 S3
------------
A B C
A B D
B E F
B D A
F B E
C X P


Нужно выбрать только строчку со значением A,B,C и С,X,P.
Те нужно выбрать "уникальные пары" которые идут первыми в таблице (таблица отсортирована по s1 asc,s2 asc,s3 asc)
По уникальными в данном случае понимается то, что не встречается другой такой строки где есть такие же значения в любом порядке столбцов те (ABC = CBA = ACB = BCA итд)

PS - без использования циклов, только select и/или with
26 июл 17, 13:38    [20676380]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
iap
Member

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

классно объяснил задачу!
(A,B) встречаются 2 раза - это ничего, да?
26 июл 17, 13:42    [20676406]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
assmsk
Member

Откуда:
Сообщений: 173
iap
assmsk,

классно объяснил задачу!
(A,B) встречаются 2 раза - это ничего, да?



По уникальностью имею ввиду отсутствие совпадений по всем 3 полям.
Т.е для A/B/C нет таких строк, а для A/B/D дубликатом является B/D/A
26 июл 17, 13:50    [20676454]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
982183
Member

Откуда: VL
Сообщений: 3352
Т.Е. выбрать строки в которых комбинация значений полей уникально.
Вроде все просто. Надо сгруппировать и отфильтровать по count=1
Вот только как сгруппировать?
"горизонтальная сортировка конечно поможет, но не красиво будет.
26 июл 17, 13:59    [20676499]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
assmsk,

надо пронумеровать строки, затем отсортировать колонки внутри строк.
Для этого преобразовать колонки с строки и обратно. Затем искать having count(*)=1

Если много памяти и дисков - декартово произведение 3x3.
26 июл 17, 14:02    [20676509]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
WITH CTE AS
(
 SELECT S1,S2,S3
 ,C=COUNT(*)OVER(PARTITION BY
  (SELECT S FROM(SELECT ROW_NUMBER()OVER(ORDER BY S),S FROM(VALUES(S1),(S2),(S3))T(S))TT(n,S) WHERE n=1)
 ,(SELECT S FROM(SELECT ROW_NUMBER()OVER(ORDER BY S),S FROM(VALUES(S1),(S2),(S3))T(S))TT(n,S) WHERE n=2)
 ,(SELECT S FROM(SELECT ROW_NUMBER()OVER(ORDER BY S),S FROM(VALUES(S1),(S2),(S3))T(S))TT(n,S) WHERE n=3))
FROM base
)
SELECT S1,S2,S3 FROM CTE WHERE C=1;
Может, чего лишнего написал, конечно...
26 июл 17, 14:25    [20676586]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
982183
Member

Откуда: VL
Сообщений: 3352
А если сгруппировать так

if(s1>s2 and s2>s3, S1+S2+S3,if(s2>s3 and s3>s1,S2+S3+S1, - и далее по тому же принципу
26 июл 17, 15:24    [20676838]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
982183
А если сгруппировать так

if(s1>s2 and s2>s3, S1+S2+S3,if(s2>s3 and s3>s1,S2+S3+S1, - и далее по тому же принципу
Мне это показалось громоздким и плохо расширяемым при увеличении количества сравниваемых полей.
А то, что я тут написал, можно оформить в виде CROSS APPLY, если приспичит.
26 июл 17, 15:37    [20676867]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
та же идея, что и у iap, но подешевле
declare @t table (S1 varchar(max), S2 varchar(max), S3 varchar(max))
insert into @t values 
('A', 'B', 'C'),
('A', 'B', 'D'),
('B', 'E', 'F'),
('B', 'D', 'A'),
('F', 'B', 'E'),
('C', 'X', 'P');

with cte as
(
select  *
from @t cross apply ( select max(S) as max_s from ( values (S1),(S2),(S3)) v(S))a1
        cross apply ( select min(S) as min_s from ( values (S1),(S2),(S3)) v(S))a2
		cross apply ( select case 
		                         when S1 < max_s and S1 > min_s then S1
		                         when S2 < max_s and S2 > min_s then S2
		                         when S3 < max_s and S3 > min_s then S3
							 end mid_s
		)a3
)

select S1, S2, S3
from cte cross apply (select count(*) over(partition by max_s,	min_s,	mid_s) cnt)a
where cnt = 1;

и только при условии S1<>S2<>S3
ну или надо по-другому искать "среднего"
26 июл 17, 15:55    [20676923]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
982183
А если сгруппировать так

if(s1>s2 and s2>s3, S1+S2+S3,if(s2>s3 and s3>s1,S2+S3+S1, - и далее по тому же принципу


складывать строки нехорошо.
т.к. разные строки в сумме могут давать одно и то же:
zyx, w, v -> zyxwv
zy, xw, v -> zyxwv
26 июл 17, 16:02    [20676942]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
не знаю насколько жизненный, посчитать сумму символов ASCII и выкинуть одинаковые :)
26 июл 17, 16:08    [20676968]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
TaPaK
не знаю насколько жизненный, посчитать сумму символов ASCII и выкинуть одинаковые :)


все то же самое: сумма совершенно разных слагаемых может быть одинаковой:
select ascii('a') + ascii('z'), ascii('b') + ascii('y') -- 219, 219
а вы их выкидываете
26 июл 17, 16:27    [20677070]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
o-o
TaPaK
не знаю насколько жизненный, посчитать сумму символов ASCII и выкинуть одинаковые :)


все то же самое: сумма совершенно разных слагаемых может быть одинаковой:
select ascii('a') + ascii('z'), ascii('b') + ascii('y') -- 219, 219
а вы их выкидываете

вот гадина :)
26 июл 17, 16:29    [20677081]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
TaPaK
не знаю насколько жизненный, посчитать сумму символов ASCII и выкинуть одинаковые :)

Я тоже подумал о таком варианте, нашел случай, когда сумма разных наборов равна, придумал еще добавить произведение символов ASCII. Но без строгого математического доказательства уникальности набора (сумма, произведение) для одного набора данных я не готов рекомендовать такой способ.
26 июл 17, 17:09    [20677269]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
CrazyDr1v3r
Guest
o-o
TaPaK
не знаю насколько жизненный, посчитать сумму символов ASCII и выкинуть одинаковые :)


все то же самое: сумма совершенно разных слагаемых может быть одинаковой:
select ascii('a') + ascii('z'), ascii('b') + ascii('y') -- 219, 219
а вы их выкидываете


Можно складывать квадраты значений.
26 июл 17, 17:29    [20677379]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
CrazyDr1v3r
Можно складывать квадраты значений.

любителям считать суммы кодов, а также их квадраты.
у ТС все строки varchar(max).
это вообще-то до 2Гб строки.
для получения сумм кодов ascii надо распарсить посимвольно всю строку,
уже на одном этом для достаточно длинных строк решение хорошо задумается.

но самое смешное, что любая перестановка букв в строке это уже совершенно другая строка.
но с абсолютно той же суммой/произведением/суммой квадратов и т.д. кодов ascii
26 июл 17, 17:42    [20677443]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
q
Guest
в лоб
declare @t table (id int identity, S1 varchar(max), S2 varchar(max), S3 varchar(max))
insert into @t values 
('A', 'B', 'C'),
('A', 'B', 'D'),
('B', 'E', 'F'),
('B', 'D', 'A'),
('F', 'B', 'E'),
('C', 'X', 'P');

select * from @t t1
where not exists
				(select 1 from @t t2 
					where	t1.id<>t2.id 
						and	t1.S1 in (t2.S1,t2.S2,t2.S3) 
						and t1.S2 in (t2.S1,t2.S2,t2.S3) 
						and t1.S3 in (t2.S1,t2.S2,t2.S3) 
						and t2.S1 in (t1.S1,t1.S2,t1.S3) 
						and t2.S2 in (t1.S1,t1.S2,t1.S3) 
						and t2.S3 in (t1.S1,t1.S2,t1.S3) 
				) 
26 июл 17, 17:44    [20677456]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
CrazyDr1v3r
Можно складывать квадраты значений.

Сами придумали?

Нельзя.

Пруф:

select sv.number, sv1.number, sv2.number, power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) as power_sum
from master..spt_values as sv 
		inner join master..spt_values as sv1 on sv1.type = 'p' and sv1.number between 1 and 255
		inner join master..spt_values as sv2 on sv2.type = 'p' and sv2.number between 1 and 255
where sv.type = 'p' and sv.number between 1 and 255
	and sv1.number >= sv.number
	and sv2.number >= sv1.number
	and power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) = 25118


Метод получения:

select power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) as power_sum
from master..spt_values as sv 
		inner join master..spt_values as sv1 on sv1.type = 'p' and sv1.number between 1 and 255
		inner join master..spt_values as sv2 on sv2.type = 'p' and sv2.number between 1 and 255
where sv.type = 'p' and sv.number between 1 and 255
	and sv1.number >= sv.number
	and sv2.number >= sv1.number
	and power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) = 25118
group by power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2)
having count(distinct sv.number) > 1 or count(distinct sv1.number) > 1 or count(distinct sv2.number) > 1
26 июл 17, 17:45    [20677462]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
Метод получения:

select power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) as power_sum
from master..spt_values as sv 
		inner join master..spt_values as sv1 on sv1.type = 'p' and sv1.number between 1 and 255
		inner join master..spt_values as sv2 on sv2.type = 'p' and sv2.number between 1 and 255
where sv.type = 'p' and sv.number between 1 and 255
	and sv1.number >= sv.number
	and sv2.number >= sv1.number
	--and power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2) = 25118
group by power(sv.number, 2) + power(sv1.number, 2) + power(sv2.number, 2)
having count(distinct sv.number) > 1 or count(distinct sv1.number) > 1 or count(distinct sv2.number) > 1

Поправился
26 июл 17, 17:47    [20677474]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
o-o
CrazyDr1v3r
Можно складывать квадраты значений.

любителям считать суммы кодов, а также их квадраты.
у ТС все строки varchar(max).
это вообще-то до 2Гб строки.
для получения сумм кодов ascii надо распарсить посимвольно всю строку,
уже на одном этом для достаточно длинных строк решение хорошо задумается.

но самое смешное, что любая перестановка букв в строке это уже совершенно другая строка.
но с абсолютно той же суммой/произведением/суммой квадратов и т.д. кодов ascii

Так это понятно, и ясно как решается - скучно даже.
Но никто не мешает попробовать решить более узкую задачу (для одного символа) более оптимальным способом.
26 июл 17, 17:51    [20677493]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
CrazyDr1v3r
Guest
o-o
любителям считать суммы кодов, а также их квадраты.
у ТС все строки varchar(max).
это вообще-то до 2Гб строки.
для получения сумм кодов ascii надо распарсить посимвольно всю строку,
уже на одном этом для достаточно длинных строк решение хорошо задумается.

но самое смешное, что любая перестановка букв в строке это уже совершенно другая строка.
но с абсолютно той же суммой/произведением/суммой квадратов и т.д. кодов ascii


Для сравнения больших данных придумали хеш-функции ;) Но сравнивать миллионы двухгигабайтных значений - задачка не на пару минут, согласен :)
26 июл 17, 17:53    [20677502]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
у меня в коде наврано, кстати.
копирую значит себе свое же решение, чтобы не перенабивать данные,
код тоже попал, а в ответе не то.
вот так должно быть:

declare @t table (S1 varchar(max), S2 varchar(max), S3 varchar(max))
insert into @t values 
('A', 'B', 'C'),
('A', 'B', 'D'),
('B', 'E', 'F'),
('B', 'D', 'A'),
('F', 'B', 'E'),
('C', 'X', 'P');

with cte as
(
select  *
from @t cross apply ( select max(S) as max_s from ( values (S1),(S2),(S3)) v(S))a1
        cross apply ( select min(S) as min_s from ( values (S1),(S2),(S3)) v(S))a2
		cross apply ( select case 
		                         when S1 < max_s and S1 > min_s then S1
		                         when S2 < max_s and S2 > min_s then S2
		                         when S3 < max_s and S3 > min_s then S3
							 end mid_s
		)a3
)

,cte1 as
(
select *, count(*) over(partition by max_s,	min_s,	mid_s) cnt
from cte 
)

select S1, S2, S3
from cte1
where cnt = 1;
26 июл 17, 19:10    [20677726]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
982183
Member

Откуда: VL
Сообщений: 3352
o-o
982183
А если сгруппировать так

if(s1>s2 and s2>s3, S1+S2+S3,if(s2>s3 and s3>s1,S2+S3+S1, - и далее по тому же принципу


складывать строки нехорошо.
т.к. разные строки в сумме могут давать одно и то же:
zyx, w, v -> zyxwv
zy, xw, v -> zyxwv


Делов то
if(s1>s2 and s2>s3, S1+"#"+S2+"#"+S3,if(s2>s3 and s3>s1,S2+"#"+S3+"#"+S1, - и далее по тому же принципу[/quot]
27 июл 17, 07:29    [20678465]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
o-o
Guest
У меня встречаются строки вида
#, ##, ###
27 июл 17, 08:29    [20678513]     Ответить | Цитировать Сообщить модератору
 Re: Удалить повторяющееся значения по разным полям  [new]
assmsk
Member

Откуда:
Сообщений: 173
Всем спасибо за помощь,
решил свою задачу таким способом, на взгляд значительно проще предложенного.


declare @t table (S1 varchar(max), S2 varchar(max), S3 varchar(max))
insert into @t values 
('A', 'B', 'C'),
('A', 'B', 'D'),
('B', 'E', 'F'),
('B', 'D', 'A'),
('F', 'B', 'E'),
('C', 'X', 'P');



With DS AS (Select *
,dense_rank() over (order by S1) as N1
,dense_rank() over (order by S2) as N2
,dense_rank() over (order by S3) as N3
from @t)

Select S1,S2,S3 from DS where N2 >= N1 and N3 >= N2
27 июл 17, 13:15    [20679574]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить