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

Откуда:
Сообщений: 25
Подскажите, пожалуйста, как красиво (без курсоров и апдейтов) объединить диапазоны дат идущих подряд.

create table #t (id int identity, d1 date, d2 date)
insert into #t (d1, d2) values
('2005-01-01', '2005-01-10'),
('2005-01-11', '2005-01-15'),
('2005-01-16', '2005-01-20')

результирующая выборка одна строка:
[d1] = '2005-01-01', [d2] = '2005-01-20'
12 дек 13, 03:05    [15280387]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Michael G
Member

Откуда:
Сообщений: 25
P.S. И, разумеется, реально записей больше, чем в указанном примере, поэтому просто MIN, MAX по всей таблице не подойдет.
12 дек 13, 03:22    [15280394]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
6820681
12 дек 13, 04:39    [15280426]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
Michael G,

версию сервера ты не указал, критерии красивости решения не озвучил, поэтому сам себе будешь виноват, если моё решение у тебя не взлетит и на твой вкус покажется страшным :)
--  Дано: 
--|/////|---------------------------------------------------
---------|/////|--------------------------------------------
----------------|/////|-------------------------------------
------------------------------------------------------------
-------------------------------|\\\\\|----------------------
--------------------------------------|\\\\\|---------------
---------------------------------------------|\\\\\|--------
------------------------------------------------------------
--  Требуется получить: 
--|///////////////////|-------------------------------------
-------------------------------|\\\\\\\\\\\\\\\\\\\|--------
--
-- Тестовый набор данных:
with 
  intervals (d1, d2) as 
    (
      select *
        from (
               values ('2005-01-01', '2005-01-10'), --\
                      ('2005-01-11', '2005-01-15'), -- | Первый сплошной диапазон
                      ('2005-01-16', '2005-01-20'), --/

                      ('2006-01-01', '2006-01-10'), --\
                      ('2006-01-11', '2006-01-15'), -- | Второй сплошной диапазон
                      ('2006-01-16', '2006-01-20')  --/
             ) i(b,e)
    )
	
--
-- Основной запрос (наличие MS SQL Server 2012 - обязательно!!!):
select min(d1) as x_d1, max(d2) as x_d2
  from (
         select d1, d2, sum(sog) over(order by d1,d2) as grp_id
           from (
                  select d1, d2, 
                         case 
                           when d1 <= max(dateadd(day,1,d2)) over(order by d1, d2 
                                                    rows between unbounded preceding
                                                             and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = Start Of Group :)
                    from intervals as i
                ) as v0
       ) as v1
 group by grp_id
 order by 1
on-line проверка на sqlfiddle.com
12 дек 13, 06:45    [15280473]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
declare @t table(d1 datetime, d2 datetime, i int identity);
with 
  intervals (d1, d2) as 
    (
      select *
        from (
               values ('2005-01-01', '2005-01-11'), --\ !!! ЛУЧШЕ ИМЕТЬ ИНТЕРВАЛЫ [d1, d2), т.е. d2 НЕ принадлежит интервалу.
                      ('2005-01-11', '2005-01-16'), -- | Первый сплошной диапазон
                      ('2005-01-16', '2005-01-21'), --/

                      ('2006-01-01', '2006-01-11'), --\
                      ('2006-01-11', '2006-01-16'), -- | Второй сплошной диапазон
                      ('2006-01-16', '2006-01-21')  --/
             ) i(b,e)
    )
insert @t select * from	intervals;
select * from @t

--надо быть проще и понятнее
--1. Херачим начала интервалов непрерывности
declare @b table(d datetime, i int identity primary key clustered);
insert @b(d) select d1 from @t t where not exists(select * from @t tt where t.i<>tt.i and t.d1 between tt.d1 and tt.d2) order by d1;
select * from @b;

--2. Херачим концы интервалов непрерывности
declare @e table(d datetime, i int identity primary key clustered);
insert @e(d) select d2 from @t t where not exists(select * from @t tt where t.i<>tt.i and t.d2 between tt.d1 and tt.d2) order by d2
select * from @e;

--3. И фсе.
select b.d d1, e.d d2 from @b b inner join @e e on b.i=e.i
12 дек 13, 08:37    [15280527]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
aleks2
--надо быть проще и понятнее
И на всю жизнь остаться амёбой?
К слову, такое решение я лет пять назад демонстрировал, когда сидел под синим ником.
А Ицек Бен Ган так ещё в прошлом веке показал его всем желающим... ;)
12 дек 13, 10:12    [15280788]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
Ну и раз уж речь зашла об авторстве решения, то ссылка на поиск первоисточника. ;)
12 дек 13, 10:25    [15280846]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
Добрый Э - Эх
aleks2
--надо быть проще и понятнее
И на всю жизнь остаться амёбой?
К слову, такое решение я лет пять назад демонстрировал, когда сидел под синим ником.
А Ицек Бен Ган так ещё в прошлом веке показал его всем желающим... ;)


В решении главное - эффективность. А не новизна.
Так быстрее - значит лучше.

ЗЫ. А изобретение непромокаемого пороха - это оставлю тебе.
12 дек 13, 11:52    [15281478]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
aleks2
Так быстрее - значит лучше.
Насколько я помню, везде где ты это утверждал, доказательств того, что твои "нафталиновые" варианты быстрее, так и не последовало. ;)
12 дек 13, 12:59    [15282035]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
Добрый Э - Эх
aleks2
Так быстрее - значит лучше.
Насколько я помню, везде где ты это утверждал, доказательств того, что твои "нафталиновые" варианты быстрее, так и не последовало. ;)


Это просто ты слеп, как крот.
А зрячий легко узрит: твой опус не может использовать индексов.

Не говоря уже о "высокой степени непрозрачности" этого говнокода.
12 дек 13, 15:54    [15283420]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
aleks2, Вот и сейчас - пустой безосновательный треп, и не более...

З.Ы.
Всегда когда слушаю эту песню, почему-то вспоминаю про тебя... :)
12 дек 13, 16:44    [15283708]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
Добрый Э - Эх
aleks2, Вот и сейчас - пустой безосновательный треп, и не более...

З.Ы.
Всегда когда слушаю эту песню, почему-то вспоминаю про тебя... :)


У тя есть сказать по-существу? Нет?
Тады молчи - сойдешь за умного.
12 дек 13, 16:48    [15283722]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
aleks2
У тя есть сказать по-существу? Нет?
Тады молчи - сойдешь за умного.
Прежде чем советовать другим, сам научись следовать собственным советам ;)
12 дек 13, 17:21    [15283893]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
6820681
А чем вариант не понравился?
DECLARE	@Table TABLE (ID Int IDENTITY PRIMARY KEY, Obj Int, [From] Date, [To] Date, UNIQUE (Obj,[From]), UNIQUE (Obj,[To]))
INSERT	@Table OUTPUT Inserted.* VALUES
 (1,'2005-01-01','2005-01-10')
,(1,'2005-01-11','2005-01-15')
,(1,'2005-01-16','2005-01-20')
,(1,'2006-01-01','2006-01-10')
,(1,'2006-01-11','2006-01-15')
,(1,'2006-01-16','2006-01-20')
UPDATE @Table SET [To] = DateAdd(Day,1,[To])

SELECT	T.ID
,	T.Obj
,	T.[From]
,	N.[To]	-- DateAdd(Day,-1,N.[To])
FROM	@Table	T CROSS APPLY (
	SELECT	TOP(1) N.*
	FROM	@Table	N
	WHERE	N.Obj	= T.Obj
	AND	N.[From]> T.[From]
	AND	NOT Exists(
		SELECT	*
		FROM	@Table	E
		WHERE	E.Obj	 = N.Obj
		AND	E.[From]>  N.[From]
		AND	E.[From]<= N.[To]
	)ORDER BY N.[From]
)	N
WHERE	NOT Exists(
	SELECT	*
	FROM	@Table	E
	WHERE	E.Obj	 = T.Obj
	AND	E.[To]	>= T.[From]
	AND	E.[To]	<  T.[To]
)
ORDER BY T.Obj
	,T.[From]
12 дек 13, 17:47    [15284114]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
внесу свои 5 копеек
with 
	calendar(Dte) 
	as(
		select distinct dateadd(dd, number, '20050101') from master.dbo.spt_values where number between 0 and 1000	
	),
	intervals (d1, d2) 
	as (
      select *
        from (
               values ('20050101', '20050110'), --\
                      ('20050111', '20050115'), -- | Первый сплошной диапазон
                      ('20050116', '20050120'), --/

                      ('20060101', '20060110'), --\
                      ('20060111', '20060115'), -- | Второй сплошной диапазон
                      ('20060116', '20060120')  --/
             ) i(b,e)
    ),
    cteDateList(DateCol,Grouping) 
    as (
	Select 
		Calendar.Dte,       
		Calendar.Dte + row_number() over (order by Calendar.Dte desc)  
	from 
		intervals, Calendar 
	where 
		Calendar.Dte between d1 and d2
	)
Select 
	Min(DateCol),
	Max(DateCol)
from 
	cteDateList
group by Grouping
order by 1


+
ЗЫЖ что-то все какие-то по конец года агрессивные стали. видать накопилась усталость
12 дек 13, 17:47    [15284120]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
HandKot,

счас придет aleks2 и скажет, что плодить кучу строк, чтобы после всё свернуть в обратку - это:
aleks2
"высокой степени непрозрачности" ... говнокод.
12 дек 13, 18:12    [15284261]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
Добрый Э - Эх
HandKot,

счас придет aleks2 и скажет, что плодить кучу строк, чтобы после всё свернуть в обратку - это:
aleks2
"высокой степени непрозрачности" ... говнокод.

Может итак.
Зато у автора есть куча вариантов и он сможет выбрать то, что ему больше по душе.
А может возьмет всё предложенное, скомпонует и придумает что-то свое новое и представит миру
13 дек 13, 10:05    [15286426]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
philips
Member

Откуда:
Сообщений: 365
MSSQL2008R2
a как туже задачу если интервалы по разным клиентам и записей 100000 и интервал начальной и конечной даты совпадают


create table #t (tabnum char(4), d1 date, d2 date)
insert into #t (tabnum,d1, d2) values
('0001','2005-01-01','2005-01-10'),
('0001','2005-01-10', '2005-01-15'),
('0001','2005-01-15', '2005-01-20'),	
('0001','2005-02-16', NULL),	
('0002','2005-01-01', '2005-01-10'),
('0002','2005-01-10', '2005-01-15'),
('0002','2005-01-15', '2005-01-20'),	
('0002','2005-02-16','2006-02-02')

Я так понимаю табличную переменную лучше не использовать
13 дек 13, 11:23    [15286975]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2414
philips
MSSQL2008R2
a как туже задачу если интервалы по разным клиентам и записей 100000 и интервал начальной и конечной даты совпадают


datediff(dd,-1, [конечной даты ])
13 дек 13, 11:39    [15287105]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
philips
MSSQL2008R2
a как туже задачу если интервалы по разным клиентам и записей 100000 и интервал начальной и конечной даты совпадают


create table #t (tabnum char(4), d1 date, d2 date)
insert into #t (tabnum,d1, d2) values
('0001','2005-01-01','2005-01-10'),
('0001','2005-01-10', '2005-01-15'),
('0001','2005-01-15', '2005-01-20'),	
('0001','2005-02-16', NULL),	
('0002','2005-01-01', '2005-01-10'),
('0002','2005-01-10', '2005-01-15'),
('0002','2005-01-15', '2005-01-20'),	
('0002','2005-02-16','2006-02-02')

Я так понимаю табличную переменную лучше не использовать

1. а в чем проблема?
2. табличную переменную уж точно не использовать для таких объемов
13 дек 13, 11:56    [15287290]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
HandKot
2. табличную переменную уж точно не использовать для таких объемов

Просто научитесь их готовить...
13 дек 13, 12:31    [15287570]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
aleks2
Guest
philips
MSSQL2008R2
a как туже задачу если интервалы по разным клиентам и записей 100000 и интервал начальной и конечной даты совпадают


О преимуществах простых решений

declare @t table(tabnum char(4), d1 datetime, d2 datetime, i int identity, primary key clustered (tabnum, d1, i));

insert @t (tabnum, d1, d2)
values ('0001','20050101','20050110')
insert @t (tabnum, d1, d2)
values('0001','20050110', '20050115')
insert @t (tabnum, d1, d2)
values('0001','20050115', '20050120')	
insert @t (tabnum, d1, d2)
values('0001','20050216', '30050216')	-- здесь ужо сам парься за NULL
insert @t (tabnum, d1, d2)
values ('0002','20050101', '20050110')
insert @t (tabnum, d1, d2)
values ('0002','20050110', '20050115')
insert @t (tabnum, d1, d2)
values ('0002','20050115', '20050120')
insert @t (tabnum, d1, d2)
values ('0002','20050216','20060202')

select * from @t

--надо быть проще и понятнее
--1. Херачим начала интервалов непрерывности
declare @b table(tabnum char(4), d datetime, i int identity, primary key clustered(tabnum, i));
insert @b(tabnum, d) 
select tabnum, d1 
  from @t t where not exists(select * from @t tt where t.i<>tt.i and t.tabnum = tt.tabnum and t.d1 between tt.d1 and tt.d2) 
  order by tabnum, d1;
select * from @b;

--2. Херачим концы интервалов непрерывности
declare @e table(tabnum char(4), d datetime, i int identity, primary key clustered(tabnum, i));
insert @e(tabnum, d) 
select tabnum, d2 from @t t where not exists(select * from @t tt where t.i<>tt.i and t.tabnum = tt.tabnum  and t.d2 between tt.d1 and tt.d2) 
order by tabnum, d1;
select * from @e;

--3. И фсе.
select b.tabnum, b.d d1, e.d d2 from @b b inner join @e e on b.i=e.i
13 дек 13, 12:41    [15287657]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
philips
MSSQL2008R2
a как туже задачу если интервалы по разным клиентам и записей 100000 и интервал начальной и конечной даты совпадают
Ну как-то так...
Про временные таблицы и их применимость сам решай. Главное - идея получения итоговой выборки у тебя есть. Остальное - допилишь напильником (NULL-ы в d2)
13 дек 13, 12:42    [15287674]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
sdet
Member

Откуда:
Сообщений: 463
aleks2
HandKot
2. табличную переменную уж точно не использовать для таких объемов

Просто научитесь их готовить...

Зачем вы пишите это не представив каких либо аргументов и пояснений? Это не Форум по загадкам.
13 дек 13, 12:43    [15287684]     Ответить | Цитировать Сообщить модератору
 Re: Объединение диапазонов дат идущих подряд  [new]
Добрый Э - Эх
Guest
sdet
Зачем вы пишите это не представив каких либо аргументов и пояснений? Это не Форум по загадкам.
Забудь. Бездоказательный треп - конек этого товарища.
13 дек 13, 12:46    [15287706]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить