SQL Bucket 时间戳以 2 小时为增量

Question

我想写一个 sql 来将时间分桶成 2 小时的增量。例如0-2、2-4、6-8、…………18-20、20-22、22-24

Time             I want it to be
6/8/2015 20:49       20-22
6/5/2015 12:47       12-14
6/9/2015 16:46       16-18

谢谢，

Answer 1

您可以使用 case 表达式和一些简单的算术将时间值分组到桶中:

select
    time, 
    case 
       when datepart(hour, time) % 2 = 0 then  -- n % 2 = 0 determines if hour is even
          cast(datepart(hour, time) as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 2 as varchar(2))
       else  -- hour is odd
          cast(datepart(hour, time) - 1 as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 1 as varchar(2))
    end as bucket
from t

请注意，我假设奇数小时应分入偶数分桶，并且不应有任何奇数分桶(如 1-3、3-5 等)。

Sample SQL Fiddle

示例输出:

|                   time | bucket |
|------------------------|--------|
| June, 08 2015 00:49:00 |    0-2 |
| June, 08 2015 23:49:00 |  22-24 |
| June, 08 2015 20:49:00 |  20-22 |
| June, 05 2015 12:47:00 |  12-14 |
| June, 05 2015 13:47:00 |  12-14 |
| June, 09 2015 16:46:00 |  16-18 |