sql-server - sql查询解决以下问题

Question

我必须在 SQL Server 中使用下表:

date                 |   status  

2009-01-01 12:00:00      OK
2009-01-01 12:03:00      FAILED
2009-01-01 12:04:00      OK
2009-01-01 12:06:20      OK
2009-01-01 12:07:35      FAILED
2009-01-01 12:07:40      FAILED
2009-01-01 12:20:40      FAILED
2009-01-01 12:25:40      OK

我需要以下内容:从 2009-01-01 12:00:00 开始，从该日期起每 10 分钟我需要查看 OK 和 FAILED 的数量。

类似于:

INTERVAL                                  FAILED      OK
2009-01-01 12:00:00-2009-01-01 12:15:00    1           2
2009-01-01 12:15:01-2009-01-01 12:30:00    0           1

等等..

在 sql 中执行此操作的最佳方法是什么？

Answer 1

好的，首先..

您提到了 10 分钟，并提供了一个 15 分钟的示例。此外，您的示例数据应该返回与您发布的结果不同的结果。

使用 Pivot 的解决方案

Declare @datetimestart datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 15

Select
  *
From
  (
    Select 
     DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart), 
    DateAdd( Minute,@interval + Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval
,@datetimestart) 
, status
    From dtest
  ) As W([from],[to], status)
Pivot (Count(status) For status In ([ok],[failed])) p

这将返回

From                       To                       Ok  Failed
2009-01-01 12:00:00.000 2009-01-01 12:15:00.000     3   3
2009-01-01 12:15:00.000 2009-01-01 12:30:00.000     1   0

评论后更新

此版本将包括数据库中没有值的时间间隔。我们需要动态创建一个临时表..

Declare @datetimestart datetime, @datetimeend datetime, @datetimecurrent datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 10
Set @datetimeend = (Select max([date]) from dtest)

SET @datetimecurrent = @datetimestart

declare @temp as table ([from] datetime,[to] datetime)
while  @datetimecurrent < @datetimeend
BEGIN
  insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
  set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END

Select
  *
From
  (
    Select 
      [from],[to], status
    From @temp t left join dtest d on d.[date] between t.[from] and t.[to]
  ) As W([from],[to], status) 
Pivot (Count(status) For status In ([ok],[failed])) p

现在使用 10 分钟间隔，显示没有值的时间段，返回..

From                       To                       Ok  Failed
2009-01-01 12:00:00.000 2009-01-01 12:10:00.000     3   3
2009-01-01 12:10:00.000 2009-01-01 12:20:00.000     0   0
2009-01-01 12:20:00.000 2009-01-01 12:30:00.000     1   0