用于对特定时间段内的值求和/总计交易量的 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  )