sql - 如何根据时间戳确定一周的第一天和最后一天？

Question

我有一个包含以下数据的表格:

trtime:2019-01-31 11:03:33.000 || tr_count:1
trtime:2019-01-31 11:23:33.000 || tr_count:2
trtime:2019-01-31 12:13:33.000 || tr_count:5
trtime:2019-01-31 12:43:33.000 || tr_count:1

我想要这种格式的输出:

time_till:2019-01-31 12:00:00.000 || tr_count:3
time_till:2019-01-31 13:00:00.000 || tr_count:6

即每小时巩固一次。

谢谢

Answer 1

您可以使用casting 和grouping 将类型转换为下面的查询

with tab(trtime,tr_count) as
(
select convert(varchar, '2019-01-31 11:03:33.000', 120),1 union all
select convert(varchar, '2019-01-31 11:23:33.000', 120),2 union all
select convert(varchar, '2019-01-31 12:13:33.000', 120),5 union all
select convert(varchar, '2019-01-31 12:43:33.000', 120),1 
)    
select cast(cast(trtime as date) as varchar) +' '
      +cast(1+datepart(hour,trtime) as varchar) + ':00:00.000'
       as time_till,
       sum(tr_count) as tr_count
  from tab
 group by cast(cast(trtime as date) as varchar) +' '
         +cast(1+datepart(hour,trtime ) as varchar) + ':00:00.000' 

time_till                tr_count
-----------------------  --------
2019-01-31 12:00:00.000     3
2019-01-31 13:00:00.000     6

Demo