Filtering by window function result in Postgresql

Question

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?

score 23 · Answer 1 · answered Feb 18 '11 at 17:23

23

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)

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"

answered Feb 18 '11 at 17:23

be aware that postgres materializes CTEs, so this might be "more elegant" that the subquery version in the sense that it is easier to read but you may experience performance drop – miro Sep 27 '21 at 10:56
1

@miro: not any longer since V12 and it's not uncommon that materializing the CTE actually improves performance (I had to change some queries when migrating from 11 to 12 and add the `materialized` keyword to get the old performance back) – Sep 27 '21 at 11:24

score 20 · Accepted Answer · edited Oct 22 '16 at 21:08

20

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;

edited Oct 22 '16 at 21:08

Peter Sutton

1,145
7
20

answered Feb 18 '11 at 14:36

Chandu

81,493
19
133
134

This works fine. I wonder, however, if there exists a more elegant solution. It is quite strange, that I can't filter based on "total" directly... – Maxim Sloyko Feb 18 '11 at 15:20
1

Looks like the WINDOW functions can't be used in WHERE, HAVING clause(tht's how it is in Oracle too). Check: http://www.postgresql.org/docs/current/static/tutorial-window.html – Chandu Feb 18 '11 at 15:32

Filtering by window function result in Postgresql

2 Answers2

Linked