在Oracle 11g中，如何在两个日期之间按小时加权平均数据？

Question

我已经用这个最终答案替换了我的原始问题。在MTO先生和庞德·斯蒂布斯先生的帮助下，以及四个月的对我的Oracle 11G实例进行试验，我终于有了你在这里看到的结果。这个查询主要是为SCADA系统设计的，将执行以下操作......

此查询将在两个日期时间之间执行时加权平均值作为TWA，该间隔期间的最小值和最大值作为Vmin和Vmax。它还将返回最小值发生的时间和最大值发生的时间作为Hmin和Hmax。(这些是最小值出现和最大值出现的日期时间)。起始和结束间隔值为VSTART和VEND。这个查询不会在三月或八月的夏令时失败。(这就是为什么我使用TO_TIMESTAMP_TZ的原因)

注意: 这个查询设置为1小时间隔，任何所需的间隔都可以通过替换和添加一些项来实现。所以享受吧!!!

这个查询在我的Oracle 11g实例中工作，在编写这篇文章后，我将确切的文本复制并粘贴到我的SQL Developer中。所以它是有效的!! 我在sqlfiddle中运行这个查询时遇到了麻烦，但很快我就会解决这个问题，并为您提供一个运行测试。

SQL Fiddle

的英译为：

{{链接1: SQL Fiddle}}

。

 -- 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

编辑：您可以将此包含在最终的选择语句中，用于时间加权的1小时滚动平均！我发现这在废水行业非常有用，因为州法规/报告要求24小时滚动平均和72分钟滚动平均。如果您需要24小时滚动平均，请将ROWS 1 PROCECDING更改为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,

标准差很有趣，因此也要添加这个。

ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,

如果您需要开始时间之前和停止时间之后的值，您可以将其与其他联合所有项放在一起。

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

- ID10T_ERROR

---

我的数据库版本是：Oracle Database 11g Release 11.1.0.7.0 - Production。我还忘了说这是我的第一篇帖子！干杯！ - ID10T_ERROR

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - ID10T_ERROR

由于您有小数秒，因此您的where条件最好是 to_timestamp('01/01/2015:00:00:00','mm/dd/YYYY:HH24:MI:SS') AND TO_TIMESTAMP('01/01/2015:23:59:59.999999999','mm/dd/YYYY:HH24:MI:SSxff')。 - Wernfried Domscheit

你是如何计算“每分钟时间加权平均值”示例中的行权重的？ - ksa

谢谢Wernfried，我会改正的。KSA，你可以通过计算sum（time*value）/sum（time）来计算加权平均时间。我在Excel中手动为每分钟完成了这项工作。 - ID10T_ERROR

Answer 1

使用您的示例数据 - 由于没有完整的一小时数据，因此我已经按每分钟进行了加权平均。

您没有指定边界处要执行什么操作，因此我已经取了前后值的加权平均。

SQL Fiddle

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 |

编辑

SQL Fiddle

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

结果:

|                      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 |

- MT0

---

感谢MTO的帮助！！非常感激！！！在Oracle 11.1g中无法使用lead和lag，如何进行编辑？最近我一直在阅读11.1和11.2之间的区别。 - ID10T_ERROR

LAG和LEAD在Oracle 11.1中可用-我已经链接到了该版本的文档。与11.1相比，唯一的区别是在11.2中，您可以使用RESPECT NULLS和IGNORE NULLS，但这里不需要。 - MT0

嗨MTO，希望一切都好！我已经花了整整一晚上来尝试解决如何填充缺失的分钟、小时、天甚至是周的数据问题。有时它会跳过缺失的分钟，有时则会在其位置输出零，但运行速度非常快！！！有什么建议吗？http://sqlfiddle.com/#!4/5850c/3 我似乎无法从fiddle中获得输出，但在我的数据库中使用sqlplus运行得非常好。 - ID10T_ERROR

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - MT0

Answer 2

这个查询生成了所需的值：

with input as (
  select value, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
      extract(day from d)+extract(hour from d)/24+
      extract(minute from d)/(24*60)+extract (second from d)/(24*60*60) tm
    from (select value, htime, htime-timestamp '1899-12-30 00:00:00' d from test))
select distinct mnt, round(
    sum(tm*value) over (partition by mnt)/sum(tm) over (partition by mnt), 6) wav
  from input order by mnt

输出：

MNT               WAV
----------------  ----------
2015-01-01 12:00   65.77838
2015-01-01 12:01   67.765575
2015-01-01 12:02   66.147733
2015-01-01 12:03   64.1026

^SQLFiddle

根据文档，Excel日历从'1900-01-01'开始，但我必须稍微修改这个日期以实现“零日期”，以便获得与电子表格中完全相同的时间差数值。其余的只是减去时间戳，将此差异转换为数字，并使用分析版本中的sum()函数对每分钟的结果求和。

如果数据中存在间隙，则需要使用递归查询(connect by)为每分钟创建时间段，然后将此查询与我的查询进行左连接，使用函数lag(wav ignore nulls)填充间隙的数据，从前一分钟(们)收集加权平均值。

---

编辑：版本填补空缺：

with input as (
    select value, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
        extract(day from d)+extract(hour from d)/24+
        extract(minute from d)/(24*60)+extract (second from d)/(24*60*60) tm
      from (select value, htime, htime-timestamp '1899-12-30 00:00:00' d from data)),
  period as (select to_date(min(mnt), 'yyyy-mm-dd hh24:mi') m1, 
                    to_date(max(mnt), 'yyyy-mm-dd hh24:mi') m2 from input),
  minutes as (
    select to_char(to_date(m1) + (level - 1)/(24*60), 'yyyy-mm-dd hh24:mi') mnt
      from period connect by level+1<(m2-m1)*24*60),
  calc as (
    select distinct mnt, 
        round(sum(tm*value) over (partition by mnt)/sum(tm) over (partition by mnt), 6) wav
      from minutes left join input using (mnt) order by mnt)
select mnt, wav, nvl(wav, lag(wav ignore nulls) over (order by mnt)) wavg from calc

^SQLFiddle

子查询input 准备数据以进一步处理，period从表中选择最小和最大分钟（您可以手动插入一些值，而不是从表中查询，例如“date'2015-01-01 13:52:00'”），minutes递归生成...给定时间段的分钟数，calc通过连接输入和分钟数计算加权平均值，最后一个选择为空分钟填充最后已知的平均值 - 您可以在SQLFiddle中观察到它对于分钟6、7、12。

- Ponder Stibbons

---

好的，我会看一下。列“缩写”很重要吗？应该为每个缩写单独计算加权平均值吗？ - Ponder Stibbons

缩写词和recnm是设备名称。 缩写词是一个记忆术，而recnm则是一个实际记录号。它们是静态的。 1136表示32-PRESS。 如果我想选择特定的设备，我会说“where points.recnm = 1136”或“where points.acronym like '32-PRESS'”，因为它们是相同的。表HST中没有保存缩写词。 使用它作为示例只是为了方便。 HST仅具有hst.recnm。 - ID10T_ERROR

我打算在工作时稍后查看这个，并制作版本，以便我可以按秒，（分钟已完成，谢谢），小时，天，然后年份进行时间加权平均。我有很多年的数据在我的系统上。玩弄这个将会很有趣。 :-) - ID10T_ERROR

祝你好运。我想成为绝地武士，但他们说我的中间氯元素太少了 :/ - Ponder Stibbons

Ponder Stibbons先生，有两个问题需要快速解决。我必须添加一个where语句，只选择来自“1136”“首字母缩写”的数据，如果我使用您的语句，我将选择表中的所有记录。仅仅为了演示，我在Oracle SQL Developer和sqlplus中放置了您的语句，但是我收到了一个错误：“ERROR at line 15: ORA-00907: missing right parenthesis”，我正在运行Oracle Database 11g Release 11.1.0.7.0 - Production。我注意到过去存在一些问题，因为fiddle是11gR2。错误正好出现在IGNORE NULLS的最后一行，确切地说是IGNORE中的I。 - ID10T_ERROR