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

Откуда:
Сообщений: 26
Добрый день!

Суть: Как из таблицы выбрать повторяющиеся комбинации чисел.

Скрипт для заполнения данных:
DECLARE @t table(num INT, val VARCHAR(255))

INSERT @t VALUES(1, '04')
INSERT @t VALUES(1, '05')
INSERT @t VALUES(1, '06')
INSERT @t VALUES(1, '07')
INSERT @t VALUES(2, '03')
INSERT @t VALUES(2, '04')
INSERT @t VALUES(2, '05')
INSERT @t VALUES(2, '10')
INSERT @t VALUES(3, '01')
INSERT @t VALUES(3, '03')
INSERT @t VALUES(3, '04')
INSERT @t VALUES(3, '05')
INSERT @t VALUES(4, '04')
INSERT @t VALUES(4, '07')
INSERT @t VALUES(4, '08')
INSERT @t VALUES(4, '09')

Данные:
num val
104
105
106
107
203
204
205
210
301
303
304
305
404
407
408
409


Либо с помощью скрипта:
SELECT num,
       str = REPLACE((SELECT val AS 'data()' FROM @t t2 WHERE t2.num = t1.num FOR XML PATH('')), ' ', ',')
  FROM @t t1
 GROUP BY num

Можно разложить данные в строку:
num str
104;05;06;07
203;04;05;10
301;03;04;05
404;07;08;09

В итоге, нужно получить таблицу типа:
КомбинацияСовпадение
3;4;52
3;43
4;53


Возможно есть красивый вариант без использования курсоров и циклов?

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
5 янв 12, 15:24    [11862144]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
demaxim, sql-ex?
5 янв 12, 15:28    [11862165]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
demaxim
Member

Откуда:
Сообщений: 26
kDnZP
demaxim, sql-ex?

не понял
5 янв 12, 15:30    [11862177]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
demaxim
kDnZP
demaxim, sql-ex?

не понял

Откель такая постановка задачи? Практическое применение расскажите, а то ведь похоже на задачу sql-ex.
5 янв 12, 15:32    [11862185]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
demaxim
Member

Откуда:
Сообщений: 26
kDnZP
demaxim
пропущено...

не понял

Откель такая постановка задачи? Практическое применение расскажите, а то ведь похоже на задачу sql-ex.


)) Знакомый попросил помочь - это часть курсовой работы, всех подробностей к сожалению не знаю.
А что такое sql-ex? Можен ссылку - я посмотрю?
5 янв 12, 15:38    [11862206]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
demaxim
kDnZP
пропущено...

Откель такая постановка задачи? Практическое применение расскажите, а то ведь похоже на задачу sql-ex.


)) Знакомый попросил помочь - это часть курсовой работы, всех подробностей к сожалению не знаю.
А что такое sql-ex? Можен ссылку - я посмотрю?

Не))), отмазка плохая. Значит я думать не буду, ибо с задачами sql-ex не помогаю, а ваша постановка мне что-то смутно напоминает, только вспомнить не могу(((.
5 янв 12, 15:41    [11862219]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
demaxim
Member

Откуда:
Сообщений: 26
kDnZP
demaxim, sql-ex?

)))) Аааа, вспомнил (смотрю что то знакомое название) - сам когда то решал там задачки )).
Это точно не оттуда!!! Там можно было попросить помощи у создателей.

Вот ломаю голову, пока никак - может кто направит?
5 янв 12, 15:48    [11862242]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
Kolya Tchernitsky
Member

Откуда:
Сообщений: 57
;with 
pairs (num,lval, rval, st) as ( 
		select 
				l.num, 
				l.val, 
				r.val, 
				convert(varchar(max),CONVERT(varchar(10),l.val)+';'+CONVERT(varchar(10),r.val)) 
			from #T l join #t r 
				 on r.num=l.num 
				and l.val+1=r.val)
,
combinations (num, lval,rval,st)
as
(		select 	num, 
				lval, 
				rval, 
				convert(varchar(max),St) 
			from pairs
		union all
		select	l.num, 
				l.val, 
				rval, 
				convert(varchar(max),convert(varchar(10),l.val)+';'+convert(varchar(10),ST)) 
			from #T l join combinations r 
				 on l.num=r.num 
				and l.val+1 = r.lval
		union all
		select	l.num, 
				lval, 
				r.val, 
				convert(varchar(max),convert(varchar(10),ST)+';'+convert(varchar(10),r.val)) 
			from combinations l join #T r 
				 on l.num=r.num 
				and l.rval+1 = r.val
)
select ST, COUNT(distinct num) from combinations group by st
9 янв 12, 16:34    [11874592]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
Kolya Tchernitsky
Member

Откуда:
Сообщений: 57
Пардон,


;with 
combinations (num, lval,rval,st)
as
(		select 	num, 
				val, 
				val, 
				convert(varchar(max),val) 
			from #T
		union all
		select	l.num, 
				l.val, 
				rval, 
				convert(varchar(max),convert(varchar(10),l.val)+';'+st) 
			from #T l join combinations r 
				 on l.num=r.num 
				and l.val+1 = r.lval
)
select 
		ST, 
		COUNT(num) 
	from combinations 
	group by st
	having COUNT(*)>1
order by 2 desc 
9 янв 12, 17:13    [11874712]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Kolya Tchernitsky, а без рекурсии?

* Хотя, по совести ТС - врет походу, не краснея ни разу. 100% задачу с sql-ex решает, только я нифига не могу вспомнить ее номер, а читать все - влом.
10 янв 12, 01:22    [11876422]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Kolya Tchernitsky,

INSERT #T VALUES(5, '03')
INSERT #T VALUES(5, '06')
INSERT #T VALUES(5, '08')
INSERT INTO #t(num,val)
SELECT 6,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number%3=0 AND t.number<10
UNION ALL
SELECT 7,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number%2=0 AND t.number<10
UNION ALL
SELECT 8,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number<10

1. 03, 06 - не попадает :)
2. Для больше чем 100 значений надо OPTION (MAXRECURSION nn) писать

Ну и я (раз уж все равно делать нефиг) всеж-таки решил хоть че-то написать из размышлений на тему))).

WITH    cte
          AS (
              SELECT    ROW_NUMBER() OVER (PARTITION BY t.num ORDER BY t.val) rn
                       ,t.num
                       ,t.val
              FROM      #T t
             ),
        cte1
          AS (
              SELECT    t1.rn g1
                       ,t2.rn g2
                       ,t1.num
                       ,t3.val
              FROM      cte t1
              JOIN      cte t2 ON t1.num=t2.num
              CROSS APPLY (
                           SELECT TOP (t1.rn)
                                    t3.val
                           FROM     cte t3
                           WHERE    t2.num=t3.num
                                    AND t2.rn<=t3.rn
                           ORDER BY t2.rn
                                   ,t3.rn
                          ) t3
             )
    SELECT  t.s
           ,COUNT(*) cnt
    FROM    (
             SELECT DISTINCT
                    t1.num
                   ,REPLACE((
                             SELECT t.val [data()] FROM cte1 t WHERE t.g1 = t1.g1 AND t.g2 = t1.g2 AND t.num = t1.num
                            FOR
                             XML PATH('')
                            ),' ',';') s
             FROM   cte1 t1
             GROUP BY t1.g1
                   ,t1.g2
                   ,t1.num
            ) t
    GROUP BY t.s
    HAVING  COUNT(*)>1
    --ORDER BY COUNT(*) DESC
    --       ,t.s

Причесывать и комментировать лень. Идея проста: пронумеровать, получить таблицу перестановок, получить результат.
10 янв 12, 02:28    [11876509]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Слегка оптимизировал. Если кто подскажет с какого фига срывает крышу оптимизатору при выделенных строках, то буду благодарен.
Цель доработки относительно предыдущего варианта - исключить дубликаты до выполнения FOR XML PATH(''), и как следствие - избавиться от DISTINCT

/*
DROP TABLE #T
CREATE TABLE #T(num INT, val VARCHAR(255))
CREATE CLUSTERED INDEX idx ON #T(num,val)
INSERT #T VALUES(1, '04')
INSERT #T VALUES(1, '05')
INSERT #T VALUES(1, '06')
INSERT #T VALUES(1, '07')
INSERT #T VALUES(2, '03')
INSERT #T VALUES(2, '04')
INSERT #T VALUES(2, '05')
INSERT #T VALUES(2, '10')
INSERT #T VALUES(3, '01')
INSERT #T VALUES(3, '03')
INSERT #T VALUES(3, '04')
INSERT #T VALUES(3, '05')
INSERT #T VALUES(4, '04')
INSERT #T VALUES(4, '07')
INSERT #T VALUES(4, '08')
INSERT #T VALUES(4, '09')

INSERT #T VALUES(5, '03')
INSERT #T VALUES(5, '06')
INSERT #T VALUES(5, '08')
INSERT INTO #t(num,val)
SELECT 6,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number%3=0 AND t.number<100
UNION ALL
SELECT 7,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number%2=0 AND t.number<100
UNION ALL
SELECT 8,RIGHT('0'+LTRIM(STR(t.number)),2) FROM master..spt_values t WHERE t.type='p' AND t.number<100
*/

--SET SHOWPLAN_TEXT ON
--SET SHOWPLAN_TEXT OFF

;WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY t.num ORDER BY t.val ) rn,
                        t.num,
                        t.val
               FROM     #T t
             ) ,
        cte1
          AS ( SELECT   t.*
               FROM     ( SELECT    t1.rn g1,
                                    t2.rn g2,
                                    t1.num,
                                    t3.val,
                                    COUNT(*) OVER ( PARTITION BY t1.rn, t2.rn, t1.num ) f
                          FROM      cte t1
                          JOIN      cte t2
                          ON        t1.num = t2.num
                          CROSS APPLY ( SELECT TOP ( t1.rn )
                                                t3.val
                                        FROM    cte t3
                                        WHERE   t2.num = t3.num
                                                AND t2.rn <= t3.rn
                                        ORDER BY t2.rn,
                                                t3.rn
                                      ) t3
                        ) t
                --WHERE    t.f = t.g1 -- Врубаем шаманства (нельзя тута эту строку), иначе оптимизатору сносит крышу
             )
    SELECT  t.s,
            COUNT(*) cnt
    FROM    ( SELECT    t1.num,
                        REPLACE(( SELECT    t.val [data()]
                                  FROM      cte1 t
                                  WHERE     t.g1 = t1.g1
                                            AND t.g2 = t1.g2
                                            AND t.num = t1.num
                                            --AND t.f=t.g1 -- Врубаем шаманства (нельзя тута эту строку), иначе оптимизатору сносит крышу
                                  ORDER BY  t.g1,
                                            t.g2
                                FOR
                                  XML PATH('')
                                ), ' ', ';') s
              FROM      cte1 t1
              WHERE     t1.f = t1.g1 -- А вот здесь, пожалуйста, без проблем... 
              GROUP BY  t1.g1,
                        t1.g2,
                        t1.num
            ) t
    GROUP BY t.s
    HAVING  COUNT(*) > 1
    --ORDER BY COUNT(*) DESC,
    --        t.s
10 янв 12, 11:04    [11877229]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
wasex
Member

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

Проверил -работает
11 янв 12, 14:50    [11885518]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
wasex
kDnZP,

Проверил -работает

Ну вот и хорошо. Значит тег записи для поиска: комбинаторика
* Это чтобы в следующий раз велосипед не изобретать, а воспользоваться поиском... Потому как комбинаторику я че-то не люблю и уже через пару дней все нафиг забуду .
11 янв 12, 15:05    [11885666]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить повторяющиеся комбинации чисел.  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
К сожалению алгоритм выше дает не все перестановки. Так для набора
1,2,3 будут варианты 1; 2; 3; 1-2; 2-3; 1-2-3; но отсутствует 1-3!!! Использовать его нельзя .
6 июн 12, 12:40    [12673721]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить