我正在尝试计算在一块土地上灌溉的总水量。我所拥有的是记录在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执行此操作-我完全迷失了方向，不知道从哪里开始，将不胜感激。
该答案假定您正在使用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 )
