窗口函数:last_value(ORDER BY ... ASC)与last_value(ORDER BY ... DESC)相同 [英] Window Functions: last_value(ORDER BY ... ASC) same as last_value(ORDER BY ... DESC)
问题描述
样本数据
CREATE TABLE test
(id integer, session_ID integer, value integer)
;
INSERT INTO test
(id, session_ID, value)
VALUES
(0, 2, 100),
(1, 2, 120),
(2, 2, 140),
(3, 1, 900),
(4, 1, 800),
(5, 1, 500)
;
当前查询
select
id,
last_value(value) over (partition by session_ID order by id) as last_value_window,
last_value(value) over (partition by session_ID order by id desc) as last_value_window_desc
from test
ORDER BY id
我遇到了last_value()窗口功能的问题:
http://sqlfiddle.com/#!15/bcec0/2
I was running into a problem with the last_value() window function:
http://sqlfiddle.com/#!15/bcec0/2
在小提琴中，我尝试使用last_value()查询中的排序方向.
In the fiddle I am trying to work with the sort direction within the last_value() query.
修改:
问题不是:为什么我没有获得所有时间的最后一个值，以及如何使用frame子句(unbounded preceding和unbounded following).我知道first_value(desc)和last_value()的区别以及last_value()不能为您提供历史上的最后一个值的问题:
The question is not: Why I don't get the all time last value and how to use the frame clause (unbounded preceding and unbounded following). I know about the difference of first_value(desc) and last_value() and the problem that last_value() does not give you the all-time last value:
默认的frame子句在当前行之前是无界的.因此，第一个值始终是带子句的第一行.因此，仅存在一行(frame子句仅包括这一行)还是被缠扰的一行(frame子句包括全部一百行)都没有关系.结果始终是第一个.在DESC顺序中是相同的:无论您获得多少行，DESC都会更改排序顺序，然后第一行是最后一个值.
The default frame clause is unbounded preceding until current row. So first value is always giving the first row withing the clause. So it doesn't matter if there is just one row (the frame clause includes only this one) or one hundered (the frame clause includes all hundred). The result is always the first one. In DESC order it is the same: DESC changes the sort order and then the first row is the last value, no matter how many rows you get.
与last_value()的行为非常相似:如果有一行，它将为您提供默认frame子句的最后一个值:这一行.在第二行，frame子句包含两行，最后一行是第二行.这就是为什么last_value()不会为您提供所有行的最后一行，而是只有最后一行直到当前行.
With last_value() the behavior is very similar: If you have one row, it gives you the last value of the default frame clause: This one row. At the second row, the frame clause contains the two rows, the last one is the second. That's why last_value() does not give you the last row of all rows but only the last row until the current row.
但是，如果我将顺序更改为DESC，我希望我的所有行都排在最后，因此我在第一行中得到了该行，而不是在第二行中获得了最后但第二行，依此类推.但这不是结果.为什么?
But if I change the order to DESC I am expecting that I have the last row of all first, so I get this one at the first row, than the last but second one at the second row and so on. But that's not the result. Why?
对于当前示例，这些是first_value()，first_value(desc)，last_value()，last_value(desc)的结果以及我对last_value(desc)的期望:
For the current example these are the results for first_value(), first_value(desc), last_value(), last_value(desc) and what I am expecting for the last_value(desc):
id | fv_asc | fv_desc | lv_asc | lv_desc | lv_desc(expecting)
----+--------+---------+--------+---------+--------------------
0 | 100 | 140 | 100 | 100 | 140
1 | 100 | 140 | 120 | 120 | 120
2 | 100 | 140 | 140 | 140 | 100
3 | 900 | 500 | 900 | 900 | 500
4 | 900 | 500 | 800 | 800 | 800
5 | 900 | 500 | 500 | 500 | 900
对我来说，默认框架子句last_value()调用中似乎忽略了ORDER BY DESC标志.但这不在first_value()调用之内.所以我的问题是:为什么last_value()结果与last_value(desc)相同?
For me it seems that the ORDER BY DESC flag is ignored within the default frame clause last_value() call. But it is not within the first_value() call. So my question is: Why is the last_value() result the same as the last_value(desc)?
推荐答案
一年后，我得到了解决方案:
After one year I have got the solution:
发表以下声明:
SELECT
id,
array_accum(value) over (partition BY session_ID ORDER BY id) AS window_asc,
first_value(value) over (partition BY session_ID ORDER BY id) AS first_value_window_asc,
last_value(value) over (partition BY session_ID ORDER BY id) AS last_value_window_asc,
array_accum(value) over (partition BY session_ID ORDER BY id DESC) AS window_desc,
first_value(value) over (partition BY session_ID ORDER BY id DESC) AS first_value_window_desc,
last_value(value) over (partition BY session_ID ORDER BY id DESC) AS last_value_window_desc
FROM
test
ORDER BY
id
这给
id window_asc first_value_window_asc last_value_window_asc window_desc first_value_window_desc last_value_window_desc
-- ------------- ---------------------- --------------------- ------------- ----------------------- ----------------------
0 {100} 100 100 {140,120,100} 140 100
1 {100,120} 100 120 {140,120} 140 120
2 {100,120,140} 100 140 {140} 140 140
3 {900} 900 900 {500,800,900} 500 900
4 {900,800} 900 800 {500,800} 500 800
5 {900,800,500} 900 500 {500} 500 500
array_accum显示使用的窗口.在那里，您可以看到窗口的第一个值和当前的最后一个值.
The array_accum shows the used window. There you can see the first and the current last value of the window.
发生的事情显示了执行计划:
What happenes shows the execution plan:
"Sort (cost=444.23..449.08 rows=1940 width=12)"
" Sort Key: id"
" -> WindowAgg (cost=289.78..338.28 rows=1940 width=12)"
" -> Sort (cost=289.78..294.63 rows=1940 width=12)"
" Sort Key: session_id, id"
" -> WindowAgg (cost=135.34..183.84 rows=1940 width=12)"
" -> Sort (cost=135.34..140.19 rows=1940 width=12)"
" Sort Key: session_id, id"
" -> Seq Scan on test (cost=0.00..29.40 rows=1940 width=12)"
您会看到:首先，前三个窗口功能有一个ORDER BY id.
There you can see: First there is an ORDER BY id for the first three window functions.
这给出了(如所述)
id window_asc first_value_window_asc last_value_window_asc
-- ------------- ---------------------- ---------------------
3 {900} 900 900
4 {900,800} 900 800
5 {900,800,500} 900 500
0 {100} 100 100
1 {100,120} 100 120
2 {100,120,140} 100 140
然后您将看到另一种排序方式:ORDER BY id DESC用于接下来的三个窗口功能.这样的结果是:
Then you can see another sort: ORDER BY id DESC for the next three window functions. This sort gives:
id window_asc first_value_window_asc last_value_window_asc
-- ------------- ---------------------- ---------------------
5 {900,800,500} 900 500
4 {900,800} 900 800
3 {900} 900 900
2 {100,120,140} 100 140
1 {100,120} 100 120
0 {100} 100 100
通过这种排序，将执行DESC窗口功能. array_accum列显示了出现的窗口:
With this sorting the DESC window function are executed. The array_accum column shows the resulting windows:
id window_desc
-- -------------
5 {500}
4 {500,800}
3 {500,800,900}
2 {140}
1 {140,120}
0 {140,120,100}
结果(first_value DESC和)last_value DESC现在与last_value ASC完全相同:
The resulting (first_value DESC and) last_value DESC is now absolutely identical to the last_value ASC:
id window_asc last_value_window_asc window_desc last_value_window_desc
-- ------------- --------------------- ------------- ----------------------
5 {900,800,500} 500 {500} 500
4 {900,800} 800 {500,800} 800
3 {900} 900 {500,800,900} 900
2 {100,120,140} 140 {140} 140
1 {100,120} 120 {140,120} 120
0 {100} 100 {140,120,100} 100
现在我很清楚为什么last_value ASC等于last_value DESC.这是因为窗口的第二个ORDER函数提供了一个倒置的窗口.
Now it became clear to me why last_value ASC is equal to last_value DESC. It's because the second ORDER of the window functions which gives an inverted window.
(执行计划的最后一种，即语句的最后一个ORDER BY.)
(The last sort of the execution plan ist the last ORDER BY of the statement.)
有一点好处:此查询显示了一些优化的潜力:如果先调用DESC窗口，然后再调用ASC窗口，则不需要第三种.此时此刻是正确的.
As a little bonus: This query shows a little optimization potential: If you call the DESC windows first and then the ASC ones you do not need the third sort. It is in the right sort at this moment.
这篇关于窗口函数:last_value(ORDER BY ... ASC)与last_value(ORDER BY ... DESC)相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
