在 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
我已经获得了填写 Null 的密钥.密钥并不总是被输入，而是被假定为实际更改之前的值.
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 的运行总和，每次键值更改时都会重新启动.而用于代替上面最后一个 select 语句的以下查询给出了请求的结果，我不会将其称为运行总和.
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.
相关文章