SQL如何做到1小时间隔 [英] SQL how to do 1 hour interval

问题描述

我有 sql 查询，它按指定的日期返回记录我想要做的是按 1 小时的间隔将它们分组我的查询返回日期和时间间隔.间隔值看起来像这样8:00,8:30,9:00,9:30,10:00正如您所看到的，间隔产生了 5 个值，我想做的是按此对它们进行分组8:00-9:00,9:00-10:00

I have sql query which returns record by date specified What I want to do is group them by 1 hour interval My query returns a date and interval. interval value looks like this 8:00,8:30,9:00,9:30,10:00 as you can see the interval has produce 5 value what I want to do is group them by this 8:00-9:00,9:00-10:00

我设计了一个查询:

SELECT DATEPART(HOUR,VC.DATE+ VC.INTERVAL) AS DATE
      ,DATEPART(HOUR,VC.INTERVAL) AS INTERVAL 
      FROM VMUK_Q1R_IB_CONSOLIDATED VC

但是它显示的问题是这样的 8,8,9,9,10我如何实现这一目标?

But the problem with this it display like this 8,8,9,9,10 How to I achieve this?

推荐答案

您需要的是创建一组每小时值，并根据您的值的小时部分重新连接到它.这将确保表示缺少的桶".以下 CTE 将为您提供 24 小时的查找 - 您也可以使用静态查找表执行相同的操作.

What you need is to create a set of hourly values and join back to it based on the hour part of your value. This will make sure the missing 'buckets' are represented. The following CTE will give you the lookup for 24 hours - you could do the same thing with a static lookup table too.

with ranges 
as 
(
select 0 as value
union all 
select r.value+ 1 from ranges r where r.value <= 24
)
select 
r.value start
from ranges r

这篇关于SQL如何做到1小时间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！