SQL函数对特定时间段内的值求和/总计

Question

我正在尝试计算在一块土地上灌溉的总水量。我所拥有的是记录在SQL数据库中的瞬时流量变化。-以立方米/小时为单位。

Date  Time          Flow Value
2009/10/22 04:00:00.0 0
2009/10/22 04:00:16.2 23
2009/10/22 04:00:20.6 34
2009/10/22 04:00:39.7 95
2009/10/22 04:00:41.7 97
2009/10/22 04:01:15.1 110
2009/10/22 04:03:17.0 95
2009/10/22 04:06:53.8 82
2009/10/22 04:26:50.7 77
2009/10/22 04:36:50.8 76
2009/10/22 04:46:51.7 72
2009/10/22 04:56:52.2 74
2009/10/22 05:16:52.7 72
2009/10/22 05:26:53.2 70
2009/10/22 05:36:22.1 84
2009/10/22 05:46:16.3 81
2009/10/22 05:56:16.2 75
2009/10/22 06:16:17.3 73
2009/10/22 06:26:16.9 75
2009/10/22 06:36:17.7 71
2009/10/22 06:57:38.7 57
2009/10/22 06:57:48.9 44
2009/10/22 06:57:53.4 28
2009/10/22 06:57:55.3 12
2009/10/22 07:07:55.1 0

根本不能将这些值相加并假定是灌溉的总水量。

需要做的是计算每个时间戳的时差，并计算该持续时间的音量，然后在用户选择的小时数内进行计算。

因此对于上述数据，时间差为（第一个小时）

time  diff volume
00:00:04.4 101.20
00:00:19.1 649.40
00:00:02.0 190.00
00:00:33.5 3249.50
00:02:01.9 13409.00
00:03:36.8 20596.00
00:19:56.9 98145.80
00:10:00.1 46207.70
00:10:00.9 45668.40
00:10:00.5 43236.00
00:20:00.5 88837.00
00:10:00.5 13521.60

该小时（从凌晨4点到凌晨5点）的总灌溉量为：373811.6立方米的水除以3600 = 103.8365556

问题是：我该如何使用SQL执行此操作-我完全迷失了方向，不知道从哪里开始，将不胜感激。

Answer 1

该答案假定您正在使用SQL Server。您的样本“第一个小时”实际上包含的内容比第一个小时还多；它应该在我认为的00：10：00.1行之后停止。

您可以通过将表本身连接起来，然后再连接一次，然后说前两行之间什么都没有，来找到每一行的上一行：

select 
    StartDate = prev.date
,   EndDate = cur.date
,   Milliseconds = datediff(ms,prev.date,cur.date)
,   Volume = datediff(ms,prev.date,cur.date) / 1000.0 * prev.flow
from @flow cur
inner join @flow prev
    on prev.date < cur.date
left join @flow inbetween
    on prev.date < inbetween.date
    and inbetween.date < cur.date
where inbetween.date is null

这样就可以得出每个期间的总和。计算小时总数需要您分割跨越小时边界的条目。您可以通过在每个小时的末尾添加一个条目来做到这一点，例如：

select date, flow
from @flow
union
-- Add end of hour
select DATEADD(Hour, DATEDIFF(Hour, 0, date)+1, 0), flow
from @flow 
where date in (select max(date) from @flow group by datepart(hh,date))

您可以使用WITH语句合并两个查询，以计算每小时的总和：

;with FlowWithHourBounds as (
    select date, flow
    from @flow
    union
    -- Add end of hour
    select DATEADD(Hour, DATEDIFF(Hour, 0, date)+1, 0), flow
    from @flow 
    where date in (
        select max(date) from @flow group by datepart(hh,date))
)
,  FlowPerPeriod as (
    select 
        StartDate = prev.date
    ,   EndDate = cur.date
    ,   Milliseconds = datediff(ms,prev.date,cur.date)
    ,   Volume = datediff(ms,prev.date,cur.date) / 1000.0 * prev.flow
    from FlowWithHourBounds cur
    inner join FlowWithHourBounds prev
        on prev.date < cur.date
    left join FlowWithHourBounds inbetween
        on prev.date < inbetween.date
        and inbetween.date < cur.date
    where inbetween.date is null
)
select datepart(hh,StartDate), sum(Volume)
from FlowPerPeriod
group by datepart(hh,StartDate)

结果是：

hour volume
4    285340,5
5    273288,5
6    255408,3
7    5701,2

这是我根据您的帖子创建的示例数据集：

declare @flow table ([date] datetime, flow float)
insert into @flow values ('2009/10/22 04:00:00.0', 0  )
insert into @flow values ('2009/10/22 04:00:16.2', 23 )
insert into @flow values ('2009/10/22 04:00:20.6', 34 )
insert into @flow values ('2009/10/22 04:00:39.7', 95 )
insert into @flow values ('2009/10/22 04:00:41.7', 97 )
insert into @flow values ('2009/10/22 04:01:15.1', 110)
insert into @flow values ('2009/10/22 04:03:17.0', 95 )
insert into @flow values ('2009/10/22 04:06:53.8', 82 )
insert into @flow values ('2009/10/22 04:26:50.7', 77 )
insert into @flow values ('2009/10/22 04:36:50.8', 76 )
insert into @flow values ('2009/10/22 04:46:51.7', 72 )
insert into @flow values ('2009/10/22 04:56:52.2', 74 )
insert into @flow values ('2009/10/22 05:16:52.7', 72 )
insert into @flow values ('2009/10/22 05:26:53.2', 70 )
insert into @flow values ('2009/10/22 05:36:22.1', 84 )
insert into @flow values ('2009/10/22 05:46:16.3', 81 )
insert into @flow values ('2009/10/22 05:56:16.2', 75 )
insert into @flow values ('2009/10/22 06:16:17.3', 73 )
insert into @flow values ('2009/10/22 06:26:16.9', 75 )
insert into @flow values ('2009/10/22 06:36:17.7', 71 )
insert into @flow values ('2009/10/22 06:57:38.7', 57 )
insert into @flow values ('2009/10/22 06:57:48.9', 44 )
insert into @flow values ('2009/10/22 06:57:53.4', 28 )
insert into @flow values ('2009/10/22 06:57:55.3', 12 )
insert into @flow values ('2009/10/22 07:07:55.1', 0  )