在 Oracle SQL 中根据时间在重复分组上运行总计 [英] Running total over repeating group by items based on time in Oracle SQL
问题描述
我的第一篇文章，请耐心等待.我想根据按日期划分的值进行求和，但只需要日期的总和，而不是按项目分组的总和.几天来一直在研究这个，试图避免使用游标，但可能不得不这样做.
My first post, so bear with me. I want to sum based upon a value that is broken by dates but only want the sum for the dates, not for the the group by item in total. Have been working on this for days, trying to avoid using a cursor but may have to.
这是我正在查看的数据示例.顺便说一句，这是在 Oracle 11g 中.
Here's an example of the data I'm looking at. BTW, this is in 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
最终结果应该是这样的:
The final result should look like this:
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
我已经能够获得用于填充空值的密钥.密钥并不总是输入，但在实际更改之前假定为值.
I've been able to get the Key to fill in the Nulls. The key isn't always entered in but is assumed to be the value until actually changed.
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;
但是当我试图得到一个运行总数时，即使有休息时间，它也会在关键上求和.我不知道如何让它在钥匙上断裂.这是我对它的最佳拍摄，它不是很好，也不能正常工作.
But when I try to get just a running total, it sums on the key even with the breaks. I cannot figure out how to get it break on the key. Here's my best shot at it which isn't very good and doesn't work correctly.
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;
任何帮助将不胜感激.也许使用游标是唯一的方法.
Any help would be appreciated. Maybe using cursor is the only way.
匹配样本数据.
推荐答案
为了获得您正在寻找的总和，您需要一种方法来对您感兴趣的值进行分组.您可以使用 a 生成分组 ID一对 ROW_NUMBER 分析函数，一个由键值分区.但是，由于您需要复制 KEY 列值，因此需要分几个阶段完成:
In order to get the sums you are looking for you need a way to group the values you are interested in. You can generate a grouping ID by using the a couple of ROW_NUMBER analytic functions, one partitioned by the key value. However due to your need to duplicate the KEY column values this will need to be done in a couple of stages:
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 的运行总和，每次键值更改时都会重新启动.而代替上面最后一个选择语句使用的以下查询给出了请求的结果，我不会将其称为运行总和.
The above query creates a running sum of AMT that restarts every time the key value changes. Whereas the following query used in place of the last select statement above gives the requested results, which I wouldn't term a running sum.
SELECT key2
, MIN(TIME) start_time
, MAX(TIME) stop_time
, sum(amt) amt
FROM t2
GROUP BY key2, gp;
要查看全时值，您可能需要更改会话 NLS_DATE_FORMAT 如下:
To see the full time values you may want to either alter your session NLS_DATE_FORMAT as below:
ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS';
或者将每个日期列包装在 TO_CHAR 函数中以用于输出.
Or wrap each date column in a TO_CHAR function for output purposes.
这篇关于在 Oracle SQL 中根据时间在重复分组上运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
