你如何按任何基于时间的间隔分组? [英] How do you group by any time based interval?

问题描述

在 SQL SERVER 中，您如何按任何基于时间的间隔进行分组?

In SQL SERVER How do you group by any time based interval?

为了节省一些时间，我想出了这个解决方案，对我来说效果很好.您可以生成任何时基，然后按任何间隔分组.非常适合做时间加权平均.如果有人有更好的方法来做这件事，我很乐意听取您的意见.

To save someone time I have come up with this solution, For me it works very well. You can generate any time base then group by any interval. Great for doing time weighted averages. If someone has a better way of doing this I would love to hear from you.

小时

declare @startdate datetime2
declare @enddate datetime2
declare @interval int
set @startdate = '2017-01-01 00:00:00'
set @enddate   = '2017-01-31 00:00:00'
set @interval = 4 --Group by Every 4 hours
;with 
ALL_INTERVALS 
AS (
    SELECT TOP (DATEDIFF(HOUR,@startdate,@enddate))
    TIMES = DATEADD(HOUR,CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])),@startdate),
    1 AS VALUE
        FROM sys.all_objects AS s1
        CROSS JOIN
        sys.all_objects AS s2
)
select DATEADD(HOUR,((DATEDIFF(HOUR, @startdate,TIMES)/@interval)*@interval),@startdate) AS TIMES,SUM(VALUE) AS TESTDATA 
from ALL_INTERVALS
group by DATEADD(HOUR,((DATEDIFF(HOUR, @startdate,TIMES)/@interval)*@interval),@startdate)
order by DATEADD(HOUR,((DATEDIFF(HOUR, @startdate,TIMES)/@interval)*@interval),@startdate)

分钟

注意.您可以将间隔设置为 60 以实现小时，将间隔设置为 1440 以实现天......

Note. you can set your interval to 60 to achieve hours, 1440 to achieve days....

declare @startdate datetime2
declare @enddate datetime2
declare @interval int
set @startdate = '2017-01-01 00:00:00'
set @enddate   = '2017-01-31 00:00:00'
set @interval = 7
;with 
ALL_INTERVALS 
AS (
    SELECT TOP (DATEDIFF(MINUTE,@startdate,@enddate))
    TIMES = DATEADD(MINUTE,CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])),@startdate),
    1 AS VALUE
        FROM sys.all_objects AS s1
        CROSS JOIN
        sys.all_objects AS s2
)
select DATEADD(MINUTE,((DATEDIFF(MINUTE, @startdate,TIMES)/@interval)*@interval),@startdate) AS TIMES,SUM(VALUE) AS TESTDATA 
from ALL_INTERVALS
group by DATEADD(MINUTE,((DATEDIFF(MINUTE, @startdate,TIMES)/@interval)*@interval),@startdate)
order by DATEADD(MINUTE,((DATEDIFF(MINUTE, @startdate,TIMES)/@interval)*@interval),@startdate)

推荐答案

我认为你把事情复杂化了.
您可以使用 GROUP BY (DATEDIFF(MINUTE, '2017-01-01', TheDateTime)/30 每 30 分钟分组一次.当然，我选择的日期只是一个随机日期.如果需要，您可以选择示例数据中的第一个(或最后一个)日期.
您还可以使用此技术获取任何时间部分的每个间隔 - 只需将关键字 MINUTE 更改为您要使用的任何日期部分，并将 intreval 30 更改为 any你想要的间隔.

I think you are over complicating thins.
You can use GROUP BY (DATEDIFF(MINUTE, '2017-01-01', TheDateTime) / 30 for grouping by every 30 minutes. Of course, the date I've chosen is a just a random date. You can choose, if you want, the first (or last) date in your sample data.
And you can also use this technique to get every interval of any time part - just change the keyword MINUTE to any date part you want to use, and the intreval 30 to any interval you want.

考虑以下示例数据:

;WITH CTE AS 
(
    SELECT CAST('2017-01-01T00:00:00' as datetime) As TheDateTime, 0 as rn
    UNION ALL
    SELECT DATEADD(MINUTE, 1, TheDateTime), rn + 1
    FROM CTE
    WHERE rn < 60
)

SELECT TheDateTime, rn INTO #T
FROM CTE
OPTION(MAXRECURSION 0)

#T 现在包含以下数据:

TheDateTime                 rn
2017-01-01 00:00:00.000     0
2017-01-01 00:01:00.000     1
2017-01-01 00:02:00.000     2
2017-01-01 00:03:00.000     3
...
2017-01-01 00:59:00.000     59
2017-01-01 01:00:00.000     60

要获得按 30 分钟分组的最大 rn，您只需要:

To get the maximum rn grouped by 30 minutes you just need this:

SELECT DATEDIFF(MINUTE, '2017-01-01', TheDateTime) / 30, MAX(rn)
FROM #T
GROUP BY DATEDIFF(MINUTE, '2017-01-01', TheDateTime) / 30

结果:

interval    max_rn
0           29
1           59
2           60

这篇关于你如何按任何基于时间的间隔分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！