有趣的SQL难题

Question

没有循环或光标，如何将日期间隔列表转换为一串0和1的字符串，使得：

• 每个位表示从所有日期中的最小值到最大值的每一天
• 如果该天在任何一个日期间隔内，则该位为1
• 如果该天不在任何一个日期间隔内，则该位为0

例如，如果区间是：

• 2011年1月1日至2011年1月2日
• 2011年1月4日至2011年1月5日

那么你编写的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

- Milimetric

---

递归CTE算作循环吗？ - Martin Smith

如果你有一串由1和0组成的字符串，每个元素都不仅仅是一个比特。 :-) - corsiKa

@JNK - 这实际上是在工作中遇到的问题，@Martin - 我解决了这个问题，而没有使用递归CTE。你看我用一个句子就解决了吗？ :) - Milimetric

@Milimetric - 假设数字表可用？ - Martin Smith

@glowcoder - 是的，这只是为了好玩，没有其他意思。我希望标题中的“谜题”能表明这一点。 - Milimetric

Answer 1

DECLARE @Result VARCHAR(MAX), @start DATETIME

SELECT @start= MIN(start) ,
       @Result =REPLICATE('0',1+DATEDIFF(DAY,MIN(start),MAX(finish)))
FROM @TimeSpan

SELECT @Result = STUFF(@Result,
                       DATEDIFF(DAY,@start,start)+1,
                       DATEDIFF(DAY,start,finish)+1,
                       REPLICATE('1',1+DATEDIFF(DAY,start,finish)))
FROM @TimeSpan 

SELECT @Result       

- Martin Smith

---

@Milimetric - 对我来说并不特别令人惊讶。永久的计数表将击败交叉连接的CTE，特别是当你开始在其上进行连接时。作为一个“谜题”，我并不担心性能。无论如何，已经更新为更简单的版本。 - Martin Smith

哥们，太棒了！这绝对是正确的方式。 - Milimetric

如果我还没有强调足够，这是一个美妙的解决方案，做得很好。 - Milimetric

谢谢。我修复了一个错误，我忘记将初始字符串长度加1，这意味着如果范围中的最后一天是单独的一天，则无法正常工作。 - Martin Smith

Answer 2

I'm had to use a recursive 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

- Abe Miessler

---

很好，这是一个解决方案，所以+1。但我会等一下看看是否有人可以不使用那么多子查询来完成它。 - Milimetric

Answer 3

好的，这是我的解决方案。比其他计数表解决方案稍微快一点，但并不是很好。此外，它受到将转换为数字的限制，只允许较小的最小-最大日期范围。递归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

- Milimetric

---

回复：Tally表尝试添加主键。请参考此答案以获取一些性能比较。https://dev59.com/anVD5IYBdhLWcg3wWKLc#2663232 我已经修改了我的答案，现在不需要任何东西。 - Martin Smith