在Oracle 11g中，您如何在两个日期之间每小时对加权平均数据计时? [英] In Oracle 11g how do you time weight average data hourly between two dates?
本文介绍了在Oracle 11g中，您如何在两个日期之间每小时对加权平均数据计时?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我用这个最终答案代替了原来的问题.在MTO先生和Ponder Stibbons先生的帮助下，以及在我的Oracle 11G实例上进行了四个月的测试之后，我终于在这里有了所见所闻.该查询主要是为SCADA系统设计的，将执行以下操作...
I have replaced my original question with this final answer. With the help from Mr. MTO and Mr. Ponder Stibbons and four months of playing around with my oracle 11G instance, I finally have what you see here. This query is Designed for SCADA systems primarily and will do the following...
此查询将在两个日期时间之间以TWA的形式执行时间加权平均值，该时间间隔内的最小值和最大值为Vmin和Vmax.它还将返回最小时间和最大时间作为Hmin和Hmax. (这是出现最小值和最大值的日期时间).开始和结束时间间隔值为VSTART和VEND.在三月或八月的夏令时，此查询不会失败. (这就是为什么我使用TO_TIMESTAMP_TZ)
This query will perform a Time Weighted Average hourly between two date times as TWA, Minimum and Maximum Values during that Interval as Vmin and Vmax. It will also return Time of Minimum and Time of Maximum as Hmin and Hmax. (These are the date times of the minimum value occurrence and maximum value occurrence ).Starting and Ending Interval values as VSTART and VEND. This query will not fail on daylight savings in March or August. (This is why I am using TO_TIMESTAMP_TZ)
注意:此查询的设置间隔为1小时，只要替换并添加一些项目，就可以设置任何所需的间隔.所以享受！！！
Note: this query is setup for 1 hour intervals and any desired interval is possible by replacing and adding just a few items. So Enjoy!!!
此查询可在我的Oracle 11g实例中使用，写完这篇文章后，我将下面的确切文本复制并粘贴到我的SQL Developer中.这样就可以了！！ 我在sqlfiddle中运行时遇到麻烦，但是很快我会弄清楚这一点并为您提供运行测试.
This Query works in my Oracle 11g instance and after writing this post i copy-ed the exact text below and pasted into my SQL Developer. So it works!! I am having trouble running this in sqlfiddle but soon i will figure this out and have a running test for you.
-- Lets Begin the Query
WITH INPUTS AS (
SELECT RECNM,
TO_TIMESTAMP_TZ ( '01-JAN-15 00:00:00 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS START_TIME,
TO_TIMESTAMP_TZ ( '06-NOV-15 23:59:59 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS END_TIME
FROM POINTS
WHERE ACRONYM = 'WELL32-PSI'
) ,
ALL_INTERVALS AS (
SELECT RECNM,
START_TIME + NUMTODSINTERVAL ( ( LEVEL-1 ) , 'HOUR' ) AS TIME
FROM INPUTS
CONNECT BY
LEVEL-1 <=
EXTRACT ( DAY FROM END_TIME - START_TIME ) * 24 +
EXTRACT ( HOUR FROM END_TIME - START_TIME )
) ,
ALL_TIMES AS (
SELECT
TIME,
VALUE,
1 AS HAS_VALUE
FROM HST H
INNER JOIN INPUTS I
ON ( H.RECNM = I.RECNM
AND H.TIME BETWEEN CAST ( I.START_TIME AS TIMESTAMP )
AND CAST ( I.END_TIME AS TIMESTAMP ) )
UNION ALL
SELECT
TIME,
NULL,
0
FROM ALL_INTERVALS
ORDER BY TIME,1, 2 NULLS FIRST
) ,
LEAD_LAG_TIMES AS (
SELECT
TIME,
LAST_VALUE ( VALUE IGNORE NULLS ) OVER ( ORDER BY TIME ASC, VALUE ASC ) AS VALUE,
24 * 60 * 60 * EXTRACT ( DAY FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
60 * 60 * EXTRACT ( HOUR FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
60 * EXTRACT ( MINUTE FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
EXTRACT ( SECOND FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) AS DURATION
FROM ALL_TIMES
)
SELECT CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE ) AS TIME,
SUM ( VALUE * DURATION ) / SUM ( DURATION ) AS TWA,
MIN ( VALUE ) AS VMIN,
MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE DESC ) AS TMIN,
MAX ( VALUE ) AS VMAX,
MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE ASC ) AS TMAX,
SUM ( VALUE ) AS TOTAL,
MAX ( VALUE ) KEEP (DENSE_RANK FIRST ORDER BY TIME ASC) as VSTART,
MAX ( VALUE ) KEEP (DENSE_RANK LAST ORDER BY TIME ASC) as VEND,
SUM ( DURATION ) AS TOTAL_DURATION
FROM LEAD_LAG_TIMES
GROUP BY CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE )
ORDER BY TIME ASC
您可以将其包含在最终的select语句中，以获得1小时的时间加权滚动平均值！我发现这在废水行业非常有用，因为州法规/报告要求24小时滚动平均值和72分钟滚动平均值.如果您需要24次滚动平均值，则将"ROWS 1 PROCECDING"更改为"ROWS 24 PROCEDING"
You can Include this in the final select statement for a 1 hour Rolling Average that is Time Weighted! I find this very useful in the waste water industry as state regulations/reporting require 24 hour rolling averages and 72 minute rolling averages. If you need a 24 rolling average change ROWS 1 PROCECDING to ROWS 24 PROCEDING
ROUND( AVG ( SUM ( value * DURATION ) / sum ( DURATION ) ) OVER (ORDER BY CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ), CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ) ROWS 1 PRECEDING),2) AS ROLLING_1H_VAVG,
标准偏差很有趣，因此也要添加它.
Standard deviation is fun, so add this as well.
ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,
如果在开始时间之前和停止时间之后需要该值，则可以将该值与另一个联合所有值一起放置.
If you need the value prior to your start time and after your stop time you can place this with the other union all's.
UNION ALL
SELECT
MAX(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC) AS TIME,
MAX(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC),
1
FROM INPUTS I
INNER JOIN HST H
ON H.TIME < I.START_TIME
UNION ALL
SELECT
MIN(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME) AS TIME,
MIN(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME),
1
FROM INPUTS I
INNER JOIN HST H
ON H.TIME > I.END_TIME
推荐答案
使用示例数据-它没有一整小时的数据，因此我已经完成了每分钟的加权平均值.
Using your sample data - it didn't have a full hour's data so I've done a weighted average per minute.
您尚未指定要在边界处执行的操作，因此我采用了紧随其后的值的加权平均值.
You haven't specified what you want to do at the boundaries so I've taken the weighted average of the immediately preceding and succeeding values.
Oracle 11g R2架构设置:
CREATE TABLE TEST ( Acronym, Date_Time, Value ) AS
SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:07.120000000', 63.7363 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:17.088000000', 64.5604 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:27.864000000', 66.3004 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:45.080000000', 66.804 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:55.056000000', 67.4908 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:11.384000000', 66.9872 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:30.424000000', 67.4451 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:40.408000000', 67.9487 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:50.408000000', 68.6813 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:01.304000000', 68.1777 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:11.304000000', 67.1245 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:21.264000000', 66.5293 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:31.232000000', 65.4762 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:45.736000000', 65.0183 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:59.312000000', 64.5604 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:03:14.712000000', 64.1026 FROM DUAL;
查询1 :
WITH temp AS (
SELECT ACRONYM,
DATE_TIME,
VALUE
FROM TEST
UNION
SELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ),
NULL
FROM TEST
GROUP BY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
UNION
SELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ) + INTERVAL '1' MINUTE,
NULL
FROM TEST
GROUP BY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDER BY
1,2
),
temp2 AS (
SELECT ACRONYM,
DATE_TIME,
COALESCE(
VALUE,
COALESCE(
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
+
(
COALESCE(
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
-
COALESCE(
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
)
*
EXTRACT( SECOND FROM ( DATE_TIME - LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ) ) )
/
EXTRACT( SECOND FROM (
LEAD( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
-
LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
) )
) AS VALUE,
LEAD( DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ) AS NEXT_DATE_TIME
FROM temp
)
SELECT ACRONYM,
TO_DATE( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ) AS DATE_TIME,
SUM( VALUE * EXTRACT( SECOND FROM ( NEXT_DATE_TIME - DATE_TIME ) ) ) / 60 AS VALUE
FROM temp2
WHERE NEXT_DATE_TIME IS NOT NULL
GROUP BY
ACRONYM,
TO_DATE( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDER BY
1,2
结果 :
Results:
| ACRONYM | DATE_TIME | VALUE |
|----------|---------------------------|-------------------|
| 32-PRESS | January, 01 0015 00:00:00 | 65.43946117333333 |
| 32-PRESS | January, 01 0015 00:01:00 | 67.56109262835211 |
| 32-PRESS | January, 01 0015 00:02:00 | 66.32093658633383 |
| 32-PRESS | January, 01 0015 00:03:00 | 64.20983764043636 |
编辑
Oracle 11g R2架构设置:
CREATE TABLE POINTS ( RECNM NUMBER, ACRONYM VARCHAR2(20) );
INSERT INTO POINTS VALUES(1136, '32-PRESS');
INSERT INTO POINTS VALUES(1138, 'OTHER_POINT');
CREATE TABLE HST ( RECNM NUMBER, TIME TIMESTAMP, VALUE NUMBER );
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:00',63.3);
INSERT INTO HST VALUES(1138, TIMESTAMP '15-01-01 00:00:00',0.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:07',63.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:17',64.6);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:28',66.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:45',66.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:55',67.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:11',67.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:30',67.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:40',67.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:50',68.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:01',68.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:11',67.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:21',66.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:31',65.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:46',65.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:59',64.6);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:15',64.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:25',63.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:35',62.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:04:05',62.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:04:32',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:05:40',61.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:05:55',60.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:20',60.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:38',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:48',61.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:58',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:11:27',62.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:13:54',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:14:10',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:14:41',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:15:18',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:15:51',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:16:19',60.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:16:32',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:04',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:27',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:37',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:58',59.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:18:22',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:18:50',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:00',60.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:25',60.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:34',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:45',62.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:55',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:20:30',63.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:20:51',63.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:21:03',63.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:22:04',64.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:22:28',64.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:23:17',64.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:23:27',63.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:24:31',63.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:26:06',63.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:27:20',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:27:30',61.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:28:08',62.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:28:37',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:29:21',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:29:38',62.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:31:27',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:32:01',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:32:25',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:35:07',62.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:35:56',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:36:06',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:36:59',61.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:39:31',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:12',61.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:22',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:35',60.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:55',60.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:41:22',60.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:41:46',60.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:42:31',60.6);
查询1 :
WITH inputs AS (
SELECT RECNM,
TIMESTAMP '15-01-01 00:00:00' AS start_time,
TIMESTAMP '15-01-01 00:40:00' AS end_time
FROM POINTS
WHERE ACRONYM = '32-PRESS'
),
all_minutes AS (
SELECT RECNM,
start_time + (LEVEL-1)/24/60 AS time
FROM inputs
CONNECT BY
LEVEL - 1 <= EXTRACT( MINUTE FROM end_time - start_time )
),
all_times AS (
SELECT TIME,
VALUE,
1 AS HAS_VALUE
FROM HST h
INNER JOIN inputs i
ON ( h.RECNM = i.RECNM
AND h.TIME BETWEEN i.start_time
AND i.end_time )
UNION ALL
SELECT TIME,
NULL,
0
FROM all_minutes
ORDER BY 1, 2 NULLS FIRST
),
lag_lead_ignore_nulls AS (
SELECT TIME,
VALUE,
COUNT( VALUE ) OVER ( ORDER BY TIME ASC, VALUE ASC NULLS FIRST ) AS LAG_GRP,
COUNT( VALUE ) OVER ( ORDER BY TIME DESC, VALUE DESC NULLS LAST ) AS LEAD_GRP
FROM all_times
),
lag_lead_values AS (
SELECT TIME,
VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITION BY LAG_GRP ORDER BY VALUE ASC NULLS LAST ) AS PREV_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITION BY LAG_GRP ORDER BY VALUE ASC NULLS LAST ) AS PREV_MEASURED_VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITION BY LEAD_GRP ORDER BY VALUE ASC NULLS LAST ) AS NEXT_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITION BY LEAD_GRP ORDER BY VALUE ASC NULLS LAST ) AS NEXT_MEASURED_VALUE,
LEAD( TIME ) OVER ( ORDER BY TIME ASC ) AS NEXT_TIME
FROM lag_lead_ignore_nulls
),
interpolated_values AS (
SELECT CAST( TIME AS DATE ) TIME,
COALESCE(
VALUE,
PREV_MEASURED_VALUE
+ ( NEXT_MEASURED_VALUE - PREV_MEASURED_VALUE )
* (
60 * EXTRACT( MINUTE FROM TIME - PREV_MEASURED_TIME )
+ EXTRACT( SECOND FROM TIME - PREV_MEASURED_TIME )
)
/ (
60 * EXTRACT( MINUTE FROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
+ EXTRACT( SECOND FROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
)
) AS INTERPOLATED_VALUE,
60 * EXTRACT( MINUTE FROM NEXT_TIME - TIME )
+ EXTRACT( SECOND FROM NEXT_TIME - TIME ) AS DURATION
FROM lag_lead_values
)
SELECT TRUNC( TIME, 'MI' ) AS TIME,
SUM( INTERPOLATED_VALUE * DURATION ) / SUM( DURATION ) AS TWA,
SUM( DURATION ) AS TOTAL_DURATION
FROM interpolated_values
WHERE INTERPOLATED_VALUE IS NOT NULL
GROUP BY TRUNC( TIME, 'MI' )
ORDER BY TIME ASC
结果 :
Results:
| TIME | TWA | TOTAL_DURATION |
|---------------------------|--------------------|----------------|
| January, 01 0015 00:00:00 | 65.38833333333333 | 60 |
| January, 01 0015 00:01:00 | 67.56302083333334 | 60 |
| January, 01 0015 00:02:00 | 66.30575757575758 | 60 |
| January, 01 0015 00:03:00 | 63.48385416666667 | 60 |
| January, 01 0015 00:04:00 | 62.02027777777778 | 60 |
| January, 01 0015 00:05:00 | 61.45441176470588 | 60 |
| January, 01 0015 00:06:00 | 60.79056603773585 | 60 |
| January, 01 0015 00:07:00 | 60.677358490566036 | 60 |
| January, 01 0015 00:08:00 | 60.56415094339623 | 60 |
| January, 01 0015 00:09:00 | 60.450943396226414 | 60 |
| January, 01 0015 00:10:00 | 60.62924528301887 | 60 |
| January, 01 0015 00:11:00 | 62.09051724137931 | 60 |
| January, 01 0015 00:12:00 | 62.18775510204082 | 60 |
| January, 01 0015 00:13:00 | 61.96530612244898 | 60 |
| January, 01 0015 00:14:00 | 61.28333333333333 | 60 |
| January, 01 0015 00:15:00 | 61.252027027027026 | 60 |
| January, 01 0015 00:16:00 | 60.27410714285714 | 60 |
| January, 01 0015 00:17:00 | 59.47416666666667 | 60 |
| January, 01 0015 00:18:00 | 59.34888888888889 | 60 |
| January, 01 0015 00:19:00 | 61.06 | 60 |
| January, 01 0015 00:20:00 | 62.86071428571429 | 60 |
| January, 01 0015 00:21:00 | 63.895 | 60 |
| January, 01 0015 00:22:00 | 64.61114754098361 | 60 |
| January, 01 0015 00:23:00 | 64.16431972789115 | 60 |
| January, 01 0015 00:24:00 | 63.52513020833333 | 60 |
| January, 01 0015 00:25:00 | 63.27789473684211 | 60 |
| January, 01 0015 00:26:00 | 63.002526315789474 | 60 |
| January, 01 0015 00:27:00 | 62.245045045045046 | 60 |
| January, 01 0015 00:28:00 | 62.23263157894737 | 60 |
| January, 01 0015 00:29:00 | 62.56314393939394 | 60 |
| January, 01 0015 00:30:00 | 62.81926605504587 | 60 |
| January, 01 0015 00:31:00 | 62.544587155963306 | 60 |
| January, 01 0015 00:32:00 | 62.29191176470588 | 60 |
| January, 01 0015 00:33:00 | 62.58641975308642 | 60 |
| January, 01 0015 00:34:00 | 62.73456790123457 | 60 |
| January, 01 0015 00:35:00 | 62.87131687242798 | 60 |
| January, 01 0015 00:36:00 | 62.02166666666667 | 60 |
| January, 01 0015 00:37:00 | 61.50328947368421 | 60 |
| January, 01 0015 00:38:00 | 61.70065789473684 | 60 |
| January, 01 0015 00:39:00 | 61.94731359649123 | 60 |
这篇关于在Oracle 11g中，您如何在两个日期之间每小时对加权平均数据计时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
