在PostgreSQL中按窗口函数过滤结果

本文介绍了在PostgreSQL中按窗口函数过滤结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

好吧，起初这只是和我的一个朋友开玩笑，但后来变成了有趣的技术问题：）

Ok, initially this was just a joke we had with a friend of mine, but it turned into interesting technical question :)

我有以下东西表：

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

我有一个指定容量的袋子，例如 1000 。我想从表中选择我可以放进袋子的所有东西，首先包装最重要的东西。

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.

这似乎是使用窗口函数的情况，所以这里是我想出的查询：

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

但是，问题在于Postgres抱怨：

The problem with it, however, is that Postgres complains:

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 all stuff 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?

推荐答案

我没有使用PostgreSQL。但是，我最好的猜测是使用内联视图。

I haven't worked with PostgreSQL. However, my best guess would be using an inline view.

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;