窗口函数: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.
问题不是:为什么我没有得到所有时间最后一个值以及如何使用框架子句(unbounded before 和 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:
默认框架子句在当前行之前是无界的.所以第一个值总是给出子句的第一行.因此，是只有一行(框架子句仅包括这一行)还是一百行(框架子句包括所有一百)都没有关系.结果永远是第一个.在 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 子句包含两行，最后一行是第二行.这就是为什么 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屋！
