Oracle 分析：在计算中使用 LAG 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7275334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle analytics: using LAG values in calculation?
提问by chris
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.
我有一个记录任务完成时间的表格。任务属于工作流，但在本例中，我只是想让 LAG 工作。
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?
但这会导致错误。有没有办法计算当前行和上一行之间的差异？
回答by Mike Meyers
According to the Oracle documentation:
根据Oracle 文档：
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.
分析函数是在查询中执行的最后一组操作，除了最后的 ORDER BY 子句。所有连接和所有 WHERE、GROUP BY 和 HAVING 子句都在处理分析函数之前完成。因此，解析函数只能出现在选择列表或 ORDER BY 子句中。
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 LAGfunction 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.
对此有两种解决方案。您可以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
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
)
回答by Wolf
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;
回答by DCookie
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);
