Oracle分析:在计算中使用LAG值

Question

我有一个记录任务完成时间的表。任务属于工作流，但是在这个例子中，我只是试图让LAG工作。

我想知道每个任务需要多长时间。

我试过:

select
  completed_date,
  lag(completed_date) over (order by id) prevrow,
  prevrow - completed_date
from
  task_complete
where workflow_id = 1

但是这会导致错误。是否有一种方法来计算当前行和前一行之间的差异?

Answer 1

根据Oracle文档:

解析函数是查询中执行的最后一组操作除了最后的ORDER BY条款。所有join和所有WHERE, GROUP在解析函数之前完成BY、HAVING子句处理。因此，解析函数只能在选择中出现list或ORDER BY子句

这意味着您不能在当前查询级别中使用分析函数的结果。

有两个解决方案。您可以根据需要在选择列表中经常包含LAG函数。请注意，即使使用普通函数也会这样做，因为无论如何都不能在同一选择列表的其他地方引用列别名(prevrow)。

select
  completed_date,
  lag(completed_date) over (order by id) as prevrow,
  lag(completed_date) over (order by id) - completed_date as date_diff
from
  task_complete
where workflow_id = 1

或者您可以使用子查询来获取结果:

select
  completed_date,
  prevrow,
  prevrow - completed_date as date_diff
from (
  select
    completed_date,
    lag(completed_date) over (order by id) as prevrow
  from
    task_complete
  where workflow_id = 1
)

Answer 2

您非常接近，尝试将您的差异计算移动到解析函数列:

select
  completed_date,
  lag(completed_date) over (order by id) - completed_date diff,
from
  task_complete
where workflow_id = 1;

Answer 3

试试这个:

SELECT completed_date
     , prevrow - completed_date thediff
  FROM (select completed_date,
               lag(completed_date) over (order by id) prevrow
          from task_complete
         where workflow_id = 1);