PostgreSQL LIMIT over WINDOW

Question

I'm wondering if there's an easy way to limit a query to the top n windows.

i.e. say I have something like

SELECT field1
      ,field2
      ,field3
      ,sum(field2) over (partition by field1) sum2
      ,sum(field3) over (partition by field1) sum3
FROM table1
GROUP BY field1, field2, field3
ORDER BY sum2 DESC LIMIT 100

The above query returns the top 100 records, not the top 100 windows (which makes sense)

What I want to get is the top 100 sum2 windows, even though there may be multiple rows inside that window. So I might be getting 400 records, but only the top 100 windows.

Hopefully that makes sense.

I need the detail in each record to remain. I'm trying to get the detail and the aggregates in the same result set. The actual query is much larger. This was just a sample for the question. — Phil Freeman
– Phil Freeman, Commented Mar 20, 2012 at 20:13
Still not getting..But you have to group by the columns firstly... — Teja
– Teja, Commented Mar 20, 2012 at 20:14

Erwin Brandstetter · Accepted Answer · 2012-03-20 23:15:51Z

1

After the comment and giving it some more thought, I think the following query does what you want.

I pick the first 100 "windows" resulting from the query and return all rows that fall in to those windows. As the windows are partitioned by field1 that's effectively the 100 distinct values of field1 with the greatest sum2. For ties on sum2 the greater field1 wins in my query (you did not specify).

WITH x AS (
    SELECT field1
          ,field2
          ,field3
          ,sum(field2) over w sum2
          ,sum(field3) over w sum3
    FROM   table1
    GROUP  BY field1, field2, field3
    WINDOW w AS (PARTITION BY field1) 
    )
    , y AS (
    SELECT field1
    FROM   x
    GROUP  BY sum2, field1
    ORDER  BY sum2 DESC, field1 DESC
    LIMIT  100
    )
SELECT x.*
FROM   y
JOIN   x USING (field1)
ORDER  BY sum2 DESC, field1 DESC, field2 DESC, field3 DESC;

The crucial point is to generate the aggregate values in a CTE, pick the 100 winning windows out of those in another CTE (could also be done with DISTINCT, I chose a GROUP BY / ORDER BY), and join the result back to the first CTE to get all rows for those windows.

All in all this is quite a complex query.

edited Mar 20, 2012 at 23:15

answered Mar 20, 2012 at 20:39

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phil Freeman Over a year ago

Thanks for the quick reply. I seem to be getting 100 rows regardless. I don't think I explained what I was after clearly. What I was looking for was each record within the group as well. So I would get the top 100 windows, with each record within that window as well. So if there were 5 records within each window, and I wanted the top 100 windows, I'd end up with 500 total records.

Erwin Brandstetter Over a year ago

@PhilFreeman: Effectively you want 100 distinct values for field1 with a row for every combination of (field2, field3)? And are you sure you want to GROUP BY field1, field2, field3 and eliminate duplicates prior to that - so duplicates do not add to the sum? With a complex case like yours communication would be easier with some sample values and the expected sample output - in your question, not in comments.

Phil Freeman Over a year ago

Yes that does it. Thanks a lot.

Collectives™ on Stack Overflow

PostgreSQL LIMIT over WINDOW

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related