如何在oracle 9中对列进行求和以包括第一行和最后一行的部分以及剩余行的完整数量 [英] How to sum column in oracle 9 to include portion of first and last row and full amout of remaining rows

问题描述

这有点难以解释。我试图根据日期列求和一个整数列。问题是我的日期列中的数据很少与我的where子句完全匹配，所以我需要修改第一行和最后一行的结果。以下是一些示例数据：



LogTime，Machine，Status，DownTimeCode，Duration



06/15 / 2017 05:50:00 AM，500,2，null，60

06/15/2017 06:01:00 AM，500,3,10,660

06 / 15/2017 06:03:00 AM，500,2，null，120

06/15/2017 06:04:00 AM，500,3,12,60

06/15/2017 07:01:00 AM，500,3,13,3420



首先让我简单解释数据：

LogTime列包含事件在每个事件结束时记录在数据库中的时间。持续时间列包含事件持续的时间（以秒为单位）。所以对于第一行，事件在上午5:50:00结束，持续时间为60秒意味着事件在上午5:49:00开始。



我尝试了什么：



我试图根据不同的停机时间码和特定时间段内的不同状态对持续时间列求和： br />


例如：

选择总和（持续时间）

来自Machines_Log

其中machine ='500'

和logtime> TO_DATE（'06 / 15/2017 06:00:00 AM'，'MM / DD / YYYY HH：MI：SS AM'）

和logtime< = TO_DATE（'06 / 15 / 2017 07:00:00 AM'，'MM / DD / YYYY HH：MI：SS AM'）

和状态= 3



这将返回（660 + 60）= 720

但是，这个答案不是我需要的。 660秒包含6:00:00 AM之前的600秒时间和6:00:00 AM之后60秒。我只对60秒后感兴趣。最后一行发生了类似的事情。我想要在上午7:00:00之前的3420秒的部分。在这种情况下，我只想要（3420-60）= 3360秒。所以我的总和应该总计为60 + 60 + 3360 = 3480



我目前通过VB.net数据读取器进行多次查询，我在每行读取一行一个人。如果它是第一行，我会根据我的Where子句中的logtime和开始日期调整持续时间。当我得到最后一行时，我启动第二个查询以查找下一行，该行的logtime大于我用datareader读取的最后一个logtime。然后，我只根据where子句中的结束日期从此结果中获取一部分持续时间。我确信必须有更好，更有效的方法来做到这一点，但我对SQL的了解仅限于基本查询。

This is kind of hard to explain. I am trying to sum an integer column based on a date column. The problem is that the data in my date column rarely matches up exactly with my where clause so I need to modify the results of just the first row and last row. Here is some example data:

LogTime,Machine,Status,DownTimeCode,Duration

06/15/2017 05:50:00 AM,500,2,null,60
06/15/2017 06:01:00 AM,500,3,10,660
06/15/2017 06:03:00 AM,500,2,null,120
06/15/2017 06:04:00 AM,500,3,12,60
06/15/2017 07:01:00 AM,500,3,13,3420

First let me briefly explain the data:
The LogTime column contains the time the event was logged in the database which is at the end of each event. The duration column contains the time in seconds the event lasted. So for the first row, the event ended at 5:50:00 AM with a duration of 60 seconds means the event started at 5:49:00 AM.

What I have tried:

I am trying to sum the duration column based on different downtimecodes and different status's for a certain time period:

For example:
Select Sum(duration)
From Machines_Log
Where machine = '500'
and logtime > TO_DATE('06/15/2017 06:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
and logtime <= TO_DATE('06/15/2017 07:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
and status = 3

This will return (660+60) = 720
However, this answer is not what I need. The 660 seconds contains 600 seconds of time before 6:00:00 AM and 60 seconds after 6:00:00 AM. I am only interested in the 60 seconds after. A similar thing occurs on the last row. I want the portion of the 3420 seconds that is before 7:00:00 AM. In this case, I only want (3420-60) = 3360 seconds. So my Sum should total to 60+60+3360 = 3480

I am currently doing this with multiple queries via a VB.net data reader where I read each row one by one. If it is the first row, I adjust the duration based on the logtime and the start date in my Where clause. When I get the last row, I launch a 2nd query to find the next row that has a logtime greater then the last logtime I read with my datareader. I then only take a portion of the duration from this result based on the end date in my where clause. I am sure there has got to be a better and more efficient way to do this but my knowledge of SQL is limited to just basic queries.

推荐答案

with dtrange as
(
select to_date('15-jun-2017 06:00', 'dd-mon-yyyy hh24:mi') range_start,  to_date('15-jun-2017 07:00', 'dd-mon-yyyy hh24:mi') range_end
from dual
),
recs_in_range as
(
select greatest(range_start, downstart) downstart, least(downend, range_end) downend
from
(
select (logtime - duration / 86400) downstart, logtime downend,  range_start, range_end
from machines_log, dtrange
where machine = '500'
and status = 3
and not (logtime < range_start
or (logtime - duration / 86400) > range_end)
)
)
select round(sum((downend - downstart) * 86400))
from recs_in_range
-- 86400 == 24 * 60 * 60 i.e. no of seconds in a day
-- 

这篇关于如何在oracle 9中对列进行求和以包括第一行和最后一行的部分以及剩余行的完整数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！