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

小编典典

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

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

我已经获得了填写Null的密钥。密钥并非总是输入，而是假定为直到实际更改为止的值。

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;

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

匹配样本数据。

阅读 225

2021-04-15

共1个答案

小编典典

为了获得所需的总和，您需要一种对感兴趣的值进行分组的方法。您可以使用两个ROW_NUMBER解析函数（按键值进行划分）来生成分组ID
。但是，由于需要复制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的运行总和，每次键值更改时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函数中以用于输出目的。

2021-04-15