Oracle analytics: using LAG values in calculation?

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.

开发者_C百科

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?

---

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
)

---

You are very close, try moving your difference calculation to the analytic function column:

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

---

Try this:

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);