Oracle analytics: using LAG values in calculation?

Question

I have a table that records when tasks were completed. Tasks belong to a workflow, but in this example I'm just trying to get the LAG working.

I would like to find information about how long each task takes.

I've tried:

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

But this results in an error. Is there a way to calculate the difference between the current row and previous row?

Answer 1

According to the Oracle documentation:

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

This means that you can't use the results of an analytic function in the current level of the query.

There are two solutions to this. You could either include the LAG function as often as necessary in the select list. Notice that this is what you would do even with a normal function because you can't refer to the column alias (prevrow) elsewhere in the same select list anyway.

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

OR you can use a subquery to get the results:

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
)