在 Oracle SQL 中根据时间对重复的分组项运行总计

我的第一篇文章，所以请耐心等待。我想根据按日期划分的值进行求和，但只需要日期的总和，而不是按项目分组的总和。已经为此工作好几天了，试图避免使用光标，但可能不得不这样做。

这是我正在查看的数据的示例。顺便说一句，这是在 Oracle 11g 中。

 Key     Time               Amt
------ ------------------ ------
 Null    1-1-2016  00:00    50
 Null    1-1-2016  02:00    50
 Key1    1-1-2016  04:00    30
 Null    1-1-2016  06:00    30
 Null    1-1-2016  08:00    30
 Key2    1-1-2016  10:00    40
 Null    1-1-2016  12:00    40
 Key1    1-1-2016  14:00    30
 Null    1-2-2016  00:00    30
 Key2    1-2-2016  02:00    35

最终结果应该是这样的：

 Key    Start            Stop             Amt
------ ---------------- ---------------- -----
 Null   1-1-2016 00:00   1-1-2016 02:00   100
 Key1   1-1-2016 04:00   1-1-2016 08:00    90
 Key2   1-1-2016 10:00   1-1-2016 12:00    80
 Key1   1-1-2016 14:00   1-2-2016 00:00    60
 key2   1-2-2016 02:00   1-2-2016 02:00    35

我已经能够得到填补空值的钥匙。键并不总是被输入，但在实际更改之前被假定为值。

SELECT key ,time ,amt
FROM (
    SELECT DISTINCT amt, time, 
        ,last_value(amt ignore nulls) OVER (
            ORDER BY time
            ) key
    FROM sample
    ORDER BY time, amt
    )
WHERE amt > 0
ORDER BY time, key NULLS first;

但是，当我尝试仅获取运行总计时，即使有休息时间，它也会对键进行求和。我不知道如何让它在钥匙上断裂。这是我最好的尝试，但效果不是很好而且无法正常工作。

SELECT key,time, amt 
     , sum(amt) OVER (PARTITION BY key ORDER BY time) AS running_total
  FROM (SELECT key, time, amt
          FROM (SELECT DISTINCT
                         amt,
                         time, 
                         last_value(amt ignore nulls) OVER (ORDER BY time) key
                  FROM sample
                 ORDER BY time, amt
               )
         WHERE amt > 0
         ORDER BY time, key NULLS first
       )
ORDER BY time, key NULLS first;

任何帮助，将不胜感激。也许使用光标是唯一的方法。

匹配样本数据。

为了获得您正在寻找的总和，您需要一种方法来对您感兴趣的值进行分组。您可以使用以下几个值来生成分组 ID：ROW_NUMBER分析函数，按键值划分。但是由于您需要复制KEY列值这需要分几个阶段完成：

WITH t1 AS (
  SELECT dta.*
       , last_value(KEY IGNORE NULLS)          -- Fill in the missing
               OVER (ORDER BY TIME ASC) key2   -- key values
    FROM your_data dta
), t2 AS (
  SELECT t1.*
       , row_number() OVER (ORDER BY TIME)     -- Generate a
       - row_number() OVER (PARTITION BY key2  -- grouping ID
                                ORDER BY TIME) gp
    FROM t1
)
SELECT t2.*
     , sum(amt) OVER (PARTITION BY gp, key2
                          ORDER BY TIME) running_sums
  FROM t2;

上面的查询创建了 AMT 的运行总和，每次键值更改时都会重新启动。而代替上面最后一个 select 语句使用的以下查询给出了请求的结果，我不会将其称为运行总和。

SELECT key2
     , MIN(TIME) start_time
     , MAX(TIME) stop_time
     , sum(amt) amt
  FROM t2
 GROUP BY key2, gp;

要查看完整时间值，您可能需要更改会话NLS_DATE_FORMAT如下：

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS';

或者将每个日期列包装在TO_CHAR用于输出目的的函数。