postgresql 动态添加过滤条件,在PostgreSQL中按窗口函数过滤结果

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 stuff table:

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.

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:

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?

解决方案

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;