在 Postgresql 中按窗口函数结果过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5042525/
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
Filtering by window function result in Postgresql
提问by Maxim Sloyko
Ok, initially this was just a joke we had with a friend of mine, but it turned into interesting technical question :)
好吧，最初这只是我们和我一个朋友开的玩笑，但它变成了有趣的技术问题 :)
I have the following stufftable:
我有下stuff表：
CREATE TABLE stuff
(
id serial PRIMARY KEY,
volume integer NOT NULL DEFAULT 0,
priority smallint NOT NULL DEFAULT 0,
);
The table contains the records for all of my stuff, with respective volume and priority (how much I need it).
该表包含我所有东西的记录，以及各自的数量和优先级（我需要多少）。
I have a bag with specified volume, say 1000. I want to select from the table all stuff I can put into a bag, packing the most important stuff first.
我有一个指定体积的袋子，比如说1000。我想从表中选择我可以放入袋子的所有东西，首先打包最重要的东西。
This seems like the case for using window functions, so here is the query I came up with:
这似乎是使用窗口函数的情况，所以这是我想出的查询：
select s.*, sum(volume) OVER previous_rows as total
from stuff s
where total < 1000
WINDOW previous_rows as
(ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW)
order by priority desc
The problem with it, however, is that Postgres complains:
然而，它的问题在于 Postgres 抱怨：
ERROR: column "total" does not exist
LINE 3: where total < 1000
If I remove this filter, total column gets properly calculated, results properly sorted but allstuff gets selected, which is not what I want.
如果我删除这个过滤器，总列得到正确计算，结果正确排序，但所有的东西都被选中，这不是我想要的。
So, how do I do this? How do I select only items that can fit into the bag?
那么，我该怎么做呢？如何只选择可以放入包中的物品？
采纳答案by Chandu
I haven't worked with PostgreSQL. However, my best guess would be using an inline view.
我没有使用过 PostgreSQL。但是，我最好的猜测是使用内联视图。
SELECT a.*
FROM (
SELECT s.*, sum(volume) OVER previous_rows AS total
FROM stuff AS s
WINDOW previous_rows AS (
ORDER BY priority desc
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY priority DESC
) AS a
WHERE a.total < 1000;
回答by a_horse_with_no_name
I don't know if this qualifies as "more elegant" but it is written in a different manner than Cybernate's solution (although it is essentially the same)
我不知道这是否符合“更优雅”的标准，但它的编写方式与 Cybernate 的解决方案不同（尽管本质上是相同的）
WITH window_table AS
(
SELECT s.*,
sum(volume) OVER previous_rows as total
FROM stuff s
WINDOW previous_rows as
(ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW)
)
SELECT *
FROM window_table
WHERE total < 1000
ORDER BY priority DESC
If by "more elegant" you mean something that avoids the sub-select, then the answer is "no"
如果“更优雅”的意思是避免子选择，那么答案是“不”
