时间戳

首页 > 解决方案 > Oracle SQL、递归、电子表格计算

sql - Oracle SQL、递归、电子表格计算

问题描述

我想将计算从 Excel 电子表格传输到 Oracle SQL 查询。

共有三个预定义列ID和。IncommingDateProcessingTime

现在我想计算两个额外的列，即Processing Start和Processing End。

结果应如下所示：

结果 Excel

使用公式：

公式1 公式 2

可以看出，ProcessingStart一个条目的 应该是它IncommingDate和ProcessingEnd前一个条目的最大值。

如何使用 SQL 实现这一点？

我在这里准备了一个示例查询：

WITH example AS
    (
        SELECT
            1                                                       AS id,
            to_date ('01.01.2018 00:00:00','dd.MM.yyyy HH24:mi:ss') AS IncommingDate,
            60                                                      AS "Processing Time [sec.]"
        FROM
            dual
        UNION ALL
        SELECT
            2,
            to_date ('01.01.2018 00:05:00','dd.MM.yyyy HH24:mi:ss'),
            60
        FROM
            dual
        UNION ALL
        SELECT
            3,
            to_date ('01.01.2018 00:05:30','dd.MM.yyyy HH24:mi:ss'),
            60
        FROM
            dual
        UNION ALL
        SELECT
            4,
            to_date ('01.01.2018 00:10:00','dd.MM.yyyy HH24:mi:ss'),
            60
        FROM
            dual
    )


SELECT
    *
FROM
    example

你们中有人知道这样做的方法吗？

标签： sqloraclerecursionspreadsheetcalculated-columns

解决方案

看起来您需要使用递归子查询分解：

with rcte (id, IncommingDate, ProcessingTime, ProcessingStart, ProcessingEnd) as (
  select id,
    IncommingDate,
    ProcessingTime,
    IncommingDate,
    IncommingDate + (ProcessingTime/86400)
  from example
  where id = 1
  union all
  select e.id,
    e.IncommingDate,
    e.ProcessingTime,
    greatest(e.IncommingDate, r.ProcessingEnd),
    greatest(e.IncommingDate, r.ProcessingEnd) + (e.ProcessingTime/86400)
  from rcte r
  -- assumes IDs are the ordering criteris and are contiguous
  join example e on e.id = r.id + 1
)
select * from rcte;

        ID INCOMMINGDATE       PROCESSINGTIME PROCESSINGSTART     PROCESSINGEND      
---------- ------------------- -------------- ------------------- -------------------
         1 2018-01-01 00:00:00             60 2018-01-01 00:00:00 2018-01-01 00:01:00
         2 2018-01-01 00:05:00             60 2018-01-01 00:05:00 2018-01-01 00:06:00
         3 2018-01-01 00:05:30             60 2018-01-01 00:06:00 2018-01-01 00:07:00
         4 2018-01-01 00:10:00             60 2018-01-01 00:10:00 2018-01-01 00:11:00

锚成员是 ID 1，并且可以为第一步做一个简单的计算来获得开始/结束时间。

递归成员然后找到下一个原始行并使用它greatest()来决定是根据它的传入时间还是之前的结束时间来进行计算。

这是假设排序基于 ID，并且它们是连续的。如果这不是您实际订购的方式，那么它只会更复杂一些。

推荐阅读