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

Откуда:
Сообщений: 86
например есть данные:
ColId EmpId QualifId QualifCatId StartDate EndDate
2392 1 12 2 2012-01-15 00:00:00.000 2037-12-31 23:59:59.000
539 2 5 4 2011-08-02 00:00:00.000 2011-08-02 00:00:00.000
540 2 5 4 2011-08-03 00:00:00.000 2011-08-03 00:00:00.000
541 2 5 4 2011-08-04 00:00:00.000 2012-03-31 23:59:59.000
2514 2 5 9 2012-04-01 00:00:00.000 2013-04-14 23:59:59.000
539 2 5 9 2012-04-15 00:00:00.000 2013-04-15 23:59:59.000
4151 2 5 4 2013-04-15 00:00:00.000 2010-11-15 00:00:00.000
526 3 5 4 2010-11-15 00:00:00.000 2037-12-31 23:59:59.000
740 4 5 4 2011-11-01 00:00:00.000 2037-12-31 23:59:59.000


нужно получить - столбец DR
ColId EmpId QualifId QualifCatId StartDate EndDate DR
2392 1 12 2 2012-01-15 00:00:00.000 2037-12-31 23:59:59.000 1
539 2 5 4 2011-08-02 00:00:00.000 2011-08-02 00:00:00.000 2
540 2 5 4 2011-08-03 00:00:00.000 2011-08-03 00:00:00.000 2
541 2 5 4 2011-08-04 00:00:00.000 2012-03-31 23:59:59.000 2
2514 2 5 9 2012-04-01 00:00:00.000 2013-04-14 23:59:59.000 3
539 2 5 9 2012-04-15 00:00:00.000 2013-04-15 23:59:59.000 3
4151 2 5 4 2013-04-15 00:00:00.000 2010-11-15 00:00:00.000 4
526 3 5 4 2010-11-15 00:00:00.000 2037-12-31 23:59:59.000 5
740 4 5 4 2011-11-01 00:00:00.000 2037-12-31 23:59:59.000 6


DR должен изменится как новый EmpId или QualifId, или QualifCatId или есть разрыв между StartDate и предыдущем EndDate (order by EmpId, StartDate)

через overloaded cte могу - но 1к записей очень долго более минуты.
а "обычным" селектом (без overload) что то не получается - за ранее спасибо и благодарен

+ scriptts

SELECT *
FROM (
VALUES 
(2392,1,12,2,CAST('20120115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(539,2,5,4,CAST('20110802 00:00:00.000' as DATETIME),CAST('20110802 00:00:00.000' as DATETIME)),
(540,2,5,4,CAST('20110803 00:00:00.000' as DATETIME),CAST('20110803 00:00:00.000' as DATETIME)),
(541,2,5,4,CAST('20110804 00:00:00.000' as DATETIME),CAST('20120331 23:59:59.000' as DATETIME)),
(2514,2,5,9,CAST('20120401 00:00:00.000' as DATETIME),CAST('20130414 23:59:59.000' as DATETIME)),
(539,2,5,9,CAST('20120415 00:00:00.000' as DATETIME),CAST('20130415 23:59:59.000' as DATETIME)),
(4151,2,5,4,CAST('20130415 00:00:00.000' as DATETIME),CAST('2010-11-15 00:00:00.000' as DATETIME)),
(526,3,5,4,CAST('20101115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(740,4,5,4,CAST('20111101 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME))) AS vtable 
([ColId],[EmpId],[QualifId],[QualifCatId],[StartDate],[EndDate])


надо получить - столбец DR
SELECT * 
FROM (
VALUES 
(2392,1,12,2,CAST('20120115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME),1),
(539,2,5,4,CAST('20110802 00:00:00.000' as DATETIME),CAST('20110802 00:00:00.000' as DATETIME),2),
(540,2,5,4,CAST('20110803 00:00:00.000' as DATETIME),CAST('20110803 00:00:00.000' as DATETIME),2),
(541,2,5,4,CAST('20110804 00:00:00.000' as DATETIME),CAST('20120331 23:59:59.000' as DATETIME),2),
(2514,2,5,9,CAST('20120401 00:00:00.000' as DATETIME),CAST('20130414 23:59:59.000' as DATETIME),3),
(539,2,5,9,CAST('20120415 00:00:00.000' as DATETIME),CAST('20130415 23:59:59.000' as DATETIME),3),
(4151,2,5,4,CAST('20130415 00:00:00.000' as DATETIME),CAST('20101115 00:00:00.000' as DATETIME),4),
(526,3,5,4,CAST('20101115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME),5),
(740,4,5,4,CAST('20111101 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME),6)) AS vtable 
([ColId],[EmpId],[QualifId],[QualifCatId],[StartDate],[EndDate],[DR])

13 мар 14, 19:00    [15719941]     Ответить | Цитировать Сообщить модератору
 Re: группировка и найти разрывы  [new]
zindur
Member

Откуда:
Сообщений: 86
спасибо Добрый Э - Эх 1080190

пока не делал фулл тест - но пока какраз то что надо, но если есть другие идеи - буду рад рассмотреть :)

изначально думалось генерировать DR по которому смог бы сгруппировать и делать (мах) и (мин) по [StartDate] и [EndDate]

собственно код:

;with cte as (SELECT *
FROM (
VALUES 
(2392,1,12,2,CAST('20120115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(539,2,5,4,CAST('20110802 00:00:00.000' as DATETIME),CAST('20110802 00:00:00.000' as DATETIME)),
(540,2,5,4,CAST('20110803 00:00:00.000' as DATETIME),CAST('20110803 00:00:00.000' as DATETIME)),
(541,2,5,4,CAST('20110804 00:00:00.000' as DATETIME),CAST('20120331 23:59:59.000' as DATETIME)),
(2514,2,5,9,CAST('20120401 00:00:00.000' as DATETIME),CAST('20130414 23:59:59.000' as DATETIME)),
(539,2,5,9,CAST('20120415 00:00:00.000' as DATETIME),CAST('20130415 23:59:59.000' as DATETIME)),
(4151,2,5,4,CAST('20130415 00:00:00.000' as DATETIME),CAST('2010-11-15 00:00:00.000' as DATETIME)),
(526,3,5,4,CAST('20101115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(740,4,5,4,CAST('20111101 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME))) AS vtable 
([ColId],[EmpId],[QualifId],[QualifCatId],[StartDate],[EndDate])
)
select v_begin.*, EndDate
  from 
       ( -- Находим все начала диапазонов:
          select EmpId, QualifId, QualifCatId
                ,StartDate, row_number() over(order by StartDate) as rn
            from cte s1
           where not exists (select null
                                 from cte s2
                                where s2.StartDate < s1.StartDate
                                  and dateadd(day,1,s2.EndDate) >= s1.StartDate
                                  and s2.EmpId = s1.EmpId
                                  and s2.QualifId = s1.QualifId
                                  and s2.QualifCatId = s1.QualifCatId)
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select EndDate, row_number() over(order by EndDate) as rn
            from cte s1
           where not exists ( select null
                                 from cte s2
                                where s2.EndDate > s1.EndDate
                                  and s2.StartDate <= dateadd(day,1,s1.EndDate)
                                  and s2.EmpId = s1.EmpId
                                  and s2.QualifId = s1.QualifId
                                  and s2.QualifCatId = s1.QualifCatId   )
       ) v_end
    on v_begin.rn = v_end.rn    
    order by 1,4
13 мар 14, 19:48    [15720157]     Ответить | Цитировать Сообщить модератору
 Re: группировка и найти разрывы  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
zindur,
Решение, в частности этой задачи и ей подобных, подробно рассмотрено в книге "Microsoft SQL Server 2012 Высокопроизводительный код T-SQL Оконные функции" автор "Ицик Бен-Ган".
Книга есть в русском переводе.
14 мар 14, 12:07    [15722838]     Ответить | Цитировать Сообщить модератору
 Re: группировка и найти разрывы  [new]
Добрый Э - Эх
Guest
wizli,

местный адепт aleks2 утверждает, что оконные функции и их применение - это "высокой степени непрозрачности" говнокод.
14 мар 14, 12:15    [15722899]     Ответить | Цитировать Сообщить модератору
 Re: группировка и найти разрывы  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
Добрый Э - Эх,
Он не прозрачен для того, кто не понимает принцип их работы. Помню, случай у меня был на прошлой работе, когда я написал довольно сложный код с использованием оконных функции, и после релиза другие разработчики увидели, и сказали переписать, потому что он был очень сложен и они не понимали, как он работает. Т.е. для них этот код был не прозрачным, потому что они никогда не использовали эти функции и не знали, как они работают.

А так все решения надо использовать с умом, в каких-то ситуациях оконные функции будут не так эффективны, как другие подходы и наоборот. В книге, которую я упомянул выше, описаны решения задач, наподобие задачи ТС, разными способами, с использованием оконных функций и без них. И приведено доказательство, почему оконные функции, в данном случае, будут наиболее релевантным решением, чем другие подходы.
В таких случаях я совершенно не вижу смысла использовать заведомо менее производительный подход, только из-за того, что кому-то этот код будет непрозрачен.
14 мар 14, 12:35    [15723084]     Ответить | Цитировать Сообщить модератору
 Re: группировка и найти разрывы  [new]
zindur
Member

Откуда:
Сообщений: 86
спасибо и wizli
вот настрочил ещё вариант:

WITH cte AS (
SELECT *
FROM (
VALUES 
(2392,1,12,2,CAST('20120115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(539,2,5,4,CAST('20110802 00:00:00.000' as DATETIME),CAST('20110802 00:00:00.000' as DATETIME)),
(540,2,5,4,CAST('20110803 00:00:00.000' as DATETIME),CAST('20110803 00:00:00.000' as DATETIME)),
(541,2,5,4,CAST('20110804 00:00:00.000' as DATETIME),CAST('20120331 23:59:59.000' as DATETIME)),
(2514,2,5,9,CAST('20120401 00:00:00.000' as DATETIME),CAST('20130414 23:59:59.000' as DATETIME)),
(539,2,5,9,CAST('20120415 00:00:00.000' as DATETIME),CAST('20130415 23:59:59.000' as DATETIME)),
(4151,2,5,4,CAST('20130415 00:00:00.000' as DATETIME),CAST('2010-11-15 00:00:00.000' as DATETIME)),
(526,3,5,4,CAST('20101115 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME)),
(740,4,5,4,CAST('20111101 00:00:00.000' as DATETIME),CAST('20371231 23:59:59.000' as DATETIME))) AS vtable 
([ColId],[EmpId],[QualifId],[QualifCatId],[StartDate],[EndDate])
), rdbegin AS
(	SELECT DISTINCT 
	       s1.EmpId, s1.QualifId, s1.QualifCatId
	      ,s1.StartDate 
	   ,DENSE_RANK() OVER(PARTITION BY s1.EmpId 
	                     ORDER BY s1.QualifId, s1.QualifCatId, s1.StartDate) AS rn
	FROM cte AS s1
	WHERE NOT EXISTS
		(SELECT * FROM cte AS s2
		 WHERE s2.EmpId = s1.EmpId
			AND s2.QualifId = s1.QualifId
			AND s2.QualifCatId = s1.QualifCatId
			AND s1.StartDate > s2.StartDate
			AND s1.StartDate <= dateadd(day, 1, s2.EndDate))
), rdend AS
(	SELECT DISTINCT 
	      s1.EmpId, s1.QualifId, s1.QualifCatId
	     ,s1.EndDate
	   ,DENSE_RANK() OVER(PARTITION BY s1.EmpId 
	                     ORDER BY s1.QualifId, s1.QualifCatId, s1.EndDate) AS rn
	FROM cte AS s1
	WHERE NOT EXISTS
		(SELECT * FROM cte AS s2
		 WHERE s2.EmpId = s1.EmpId
			AND s2.QualifId = s1.QualifId
			AND s2.QualifCatId = s1.QualifCatId
			AND dateadd(day, 1,s1.EndDate) >= s2.StartDate
			AND s1.EndDate < s2.EndDate)
)
SELECT rd1.EmpId, rd1.QualifId, rd1.QualifCatId
     ,rd1.StartDate
     ,rd2.EndDate
FROM rdbegin AS rd1
 JOIN rdend AS rd2 ON rd1.EmpId = rd2.EmpId
                  AND rd1.rn = rd2.rn
14 мар 14, 16:59    [15725722]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить