Интересная головоломка с SQL.

Как без циклов или курсоров взять список интервалов дат и превратить их в строку из 1 и 0, чтобы:

  • каждый бит представляет каждый день от минимального (все даты) до максимального (все даты)
  • бит равен 1, если этот день попадает в любой из интервалов дат
  • бит равен 0, если этот день не попадает ни в один из интервалов

Так, например, если бы интервалы были:

  • 1/1/2011 to 1/2/2011
  • 1/4/2011 to 1/5/2011

Затем написанный вами SQL должен выводить 11011. Вот сценарий установки, который вы можете использовать:

declare @TimeSpan table
(
    start datetime
    ,finish datetime
)

-- this is a good data set, with overlapping and non-overlapping time spans
insert into @TimeSpan values ('02/02/2010', '02/02/2010')
insert into @TimeSpan values ('02/03/2010', '02/03/2010')
insert into @TimeSpan values ('02/04/2010', '02/05/2010')
insert into @TimeSpan values ('02/05/2010', '02/06/2010')
insert into @TimeSpan values ('02/07/2010', '02/09/2010')
insert into @TimeSpan values ('02/08/2010', '02/08/2010')
insert into @TimeSpan values ('02/08/2010', '02/10/2010')
insert into @TimeSpan values ('02/14/2010', '02/16/2010')

-- for this set of data, the output string would be 111111111000111

sql sql-server puzzle
person Milimetric    schedule 15.04.2011    source источник
comment
Считается ли рекурсивный CTE циклом?   -  person Martin Smith    schedule 15.04.2011
comment
Если у вас есть строка из 1 и 0, каждый элемент больше бита. :-)   -  person corsiKa    schedule 15.04.2011
comment
@JNK - это действительно появилось на работе, а @Martin - я решил это без рекурсивного CTE. Например, как я сделал это одним предложением? :)   -  person Milimetric    schedule 15.04.2011
comment
@milimetric, ты спрашиваешь, когда уже знаешь ответ? Например, конкурс мелочей или что-то в этом роде? -1 :-(   -  person corsiKa    schedule 15.04.2011
comment
@Milimetric - Предполагая, что таблица чисел доступна?   -  person Martin Smith    schedule 15.04.2011
comment
@Martin - вы определенно можете создать свою собственную таблицу подсчета, если вам это нужно   -  person Milimetric    schedule 15.04.2011
comment
@glowcoder - да, это просто для удовольствия, я не хотел подразумевать обратное. Я надеялся, что загадка в заголовке укажет на это.   -  person Milimetric    schedule 15.04.2011
comment
@Milimetric - Итак, вы собираетесь опубликовать свое решение?   -  person Martin Smith    schedule 15.04.2011
comment
Я смотрю на вашу прямо сейчас и проверяю производительность. Я отмечу оба решения, потому что они верны, но я приму решение с лучшей производительностью. Я также опубликую свое решение. Он похож на ваш, думаю немного проще :)   -  person Milimetric    schedule 15.04.2011

Ответы (3)


arrow_upward
2
arrow_downward

Мне пришлось использовать рекурсивный CTE ;-)

DECLARE @BitString varchar(100);
Declare @minStart datetime
DECLARE @MaxEnd datetime
declare @RangeDates table
(
    start datetime
    ,finish datetime
)

-- this is a good data set, with overlapping and non-overlapping time spans
insert into @RangeDates values ('02/02/2010', '02/02/2010')
insert into @RangeDates values ('02/03/2010', '02/03/2010')
insert into @RangeDates values ('02/04/2010', '02/05/2010')
insert into @RangeDates values ('02/05/2010', '02/06/2010')
insert into @RangeDates values ('02/07/2010', '02/09/2010')
insert into @RangeDates values ('02/08/2010', '02/08/2010')
insert into @RangeDates values ('02/08/2010', '02/10/2010')
insert into @RangeDates values ('02/14/2010', '02/16/2010')

SELECT @minStart = MIN(start) FROM @RangeDates
SELECT @MaxEnd = MAX(finish) FROM @RangeDates

;WITH Dates AS (

        SELECT myDate = CONVERT(DateTime, @minStart), 
        CASE
            WHEN exists (SELECT * FROM @RangeDates where @minStart between start and finish) then '1'
            else '0'
        END as myBit
        UNION ALL
        SELECT myDate = DATEADD(DAY,1,myDate),
        CASE
            WHEN exists (SELECT * FROM @RangeDates where myDate between start and finish) then '1'
            else '0'
        END
        FROM Dates
        where myDate <= @MaxEnd
)

SELECT @BitString = COALESCE(@BitString,'') + myBit FROM Dates
SELECT @BitString
person Abe Miessler    schedule 15.04.2011
comment
Круто, это решение, так что +1. Но я подожду, чтобы посмотреть, сделает ли кто-нибудь это без такого количества подзапросов. - person Milimetric; 15.04.2011

arrow_upward
1
arrow_downward

Итак, вот мое решение. Немного быстрее, чем другое решение для таблицы подсчета, но не очень хорошо. Кроме того, он ограничен преобразованием в числовое значение, чтобы разрешить только меньшие минимальные и максимальные диапазоны дат. Странно, что рекурсивные CTE быстрее, чем итоговые таблицы. Лучше ли масштабируются итоговые таблицы?

declare @Tally table
(
    N int identity(1,1),
    T bit
)

 insert into @Tally
 select TOP 11000 0 as T
   from master.dbo.SysColumns sc1, master.dbo.SysColumns sc2


declare @begin datetime = (select MIN(start) from @TimeSpan);
declare @end datetime = (select MAX(finish) from @TimeSpan);

with strings as
(
 select S.*
        ,
        '1'+
        REPLICATE('0', DATEDIFF(DAY, @begin, DATEADD(DAY,N-1,S.start)))+
        '1'+
        REPLICATE('0', DATEDIFF(DAY, DATEADD(DAY,N-1,S.start), @end)) task

   from @TimeSpan S
            inner join
        @Tally T                    ON DateAdd(DAY,T.N-1,S.start) <= S.finish
)

 select SUM(DISTINCT convert(numeric(38,0),task)) 
        - COUNT(DISTINCT task)*(convert(numeric(38,0), '1' + REPLICATE('0',DATEDIFF(d,@begin,@end)+1)))
   from strings
person Milimetric    schedule 15.04.2011
comment
RE: таблицы Tally пытаются добавить первичный ключ. См. этот ответ для некоторых сравнений производительности. stackoverflow.com/questions/10819/ Я изменил свой ответ, чтобы не требовать сейчас. - person Martin Smith; 15.04.2011

arrow_upward

arrow_downward

person    schedule
comment
@Milimetric - меня это не особенно удивляет. Постоянная таблица подсчета превзойдет таблицу CTE с перекрестным соединением, особенно когда вы начнете присоединяться к ней. Будучи головоломкой, я не беспокоился о производительности. В любом случае обновился гораздо более простой версией. - person Martin Smith; 15.04.2011
comment
Чувак, Круто! Это определенно правильный путь. - person Milimetric; 15.04.2011
comment
Если я еще недостаточно подчеркнул это, это прекрасное решение, отличная работа. - person Milimetric; 15.04.2011
comment
Спасибо. Я исправил ошибку, из-за которой я забыл добавить 1 к начальной длине строки, и это означало, что она не работала, если последний день в диапазоне был отдельным днем. - person Martin Smith; 15.04.2011