如何在Oracle中的两个时间间隔之间分布平均值 [英] How to spread the average between two intervals in oracle

问题描述

如果给出一年中每个日期的24小时平均值.我想将每小时平均值分散到每分钟的平均值上. 例如给

If given the Average for 24 hours for each date in a year. I want to spread this hourly average to average at each minute. e.g. given

Date        Time    Average
01-Jan-15   23:00   20
02-Jan-15   00:00   50
02-Jan-15   01:00   30

我希望对输出进行如下计算....

I want the output to be calculated something as below ....

DateTime              AVG_VALUE
01/01/2015 23:00:00   20
01/01/2015 23:01:00   20.5
01/01/2015 23:02:00   21
01/01/2015 23:03:00   21.5
01/01/2015 23:04:00   22
01/01/2015 23:05:00   22.5
01/01/2015 23:06:00   23
01/01/2015 23:07:00   23.5
01/01/2015 23:08:00   24
01/01/2015 23:09:00   24.5
01/01/2015 23:10:00   25
01/01/2015 23:11:00   25.5
01/01/2015 23:12:00   26
01/01/2015 23:13:00   26.5
01/01/2015 23:14:00   27
01/01/2015 23:15:00   27.5
01/01/2015 23:16:00   28
01/01/2015 23:17:00   28.5
01/01/2015 23:18:00   29
01/01/2015 23:19:00   29.5
01/01/2015 23:20:00   30
01/01/2015 23:21:00   30.5
01/01/2015 23:22:00   31
01/01/2015 23:23:00   31.5
01/01/2015 23:24:00   32
01/01/2015 23:25:00   32.5
01/01/2015 23:26:00   33
01/01/2015 23:27:00   33.5
01/01/2015 23:28:00   34
01/01/2015 23:29:00   34.5
01/01/2015 23:30:00   35
01/01/2015 23:31:00   35.5
01/01/2015 23:32:00   36
01/01/2015 23:33:00   36.5
01/01/2015 23:34:00   37
01/01/2015 23:35:00   37.5
01/01/2015 23:36:00   38
01/01/2015 23:37:00   38.5
01/01/2015 23:38:00   39
01/01/2015 23:39:00   39.5
01/01/2015 23:40:00   40
01/01/2015 23:41:00   40.5
01/01/2015 23:42:00   41
01/01/2015 23:43:00   41.5
01/01/2015 23:44:00   42
01/01/2015 23:45:00   42.5
01/01/2015 23:46:00   43
01/01/2015 23:47:00   43.5
01/01/2015 23:48:00   44
01/01/2015 23:49:00   44.5
01/01/2015 23:50:00   45
01/01/2015 23:51:00   45.5
01/01/2015 23:52:00   46
01/01/2015 23:53:00   46.5
01/01/2015 23:54:00   47
01/01/2015 23:55:00   47.5
01/01/2015 23:56:00   48
01/01/2015 23:57:00   48.5
01/01/2015 23:58:00   49
01/01/2015 23:59:00   49.5
02/01/2015            50
02/01/2015 00:01:00   49.66666667
02/01/2015 00:02:00   49.33333333
02/01/2015 00:03:00   49
02/01/2015 00:04:00   48.66666667
02/01/2015 00:05:00   48.33333333

这个想法是得到两个间隔之间的平滑的倾斜或下降图.在输出中，您可以看到随着我们从20-> 50

The idea is to get the smooth incline or decline graph between two interval. In the output you can see the avg is gradually increasing with increasing minutes as we move from 20->50

可以使用Oracle Query或某些PL/SQL代码来实现吗?

Can this be achived using Oracle Query or some PL/SQL code?

推荐答案

添加了联合以包括最后丢失的行

Added union to include the final missing row

类似这样的事情可能起作用.假设输入数据在表a中，

Some thing like this may work. Assuming the input data is in table a,

with b as
(select level-1 lev
from dual
connect by level <= 60
),
v as
(
select start_date, value current_value, lead(value) over (order by start_date) next_value
from a
)
select start_date+ (lev)/(24*60), (current_value*((60-(b.lev))/60) + next_value*(b.lev)/60) avg_value
from v, b
where v.next_value is not null
union
select start_date, current_value
from v
where v.next_value is null
order by 1

这篇关于如何在Oracle中的两个时间间隔之间分布平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！