Filter a sum of values until a certain threshold is reached

user_834114 0
Viewed 54

DbFiddle

Stuck. Need SO :)

Consider the following distribution of values.

ID  CNT SEC SHOW(Bool)
1   10  1
2   1   1
3   25  1
4   1   1
5   2   1
6   10  1
7   50  2
8   90  2

My goal is to filter by sec and then

  • sort by cnt ascending,
  • sort by id ascending

and then flag/filter all rows as show - false where cnt is < 5 and until the sum of cnt of all hidden rows (show=false) is >= 5.

So the sum of all "hidden" rows may never be < 5.

Expected outcome for sec=1:

| id | cnt | cnt_sum | show  |
|----|-----|---------|-------|
| 2  | 1   | 1       | false |
| 4  | 1   | 2       | false |
| 5  | 2   | 4       | false |
| 1  | 10  | 14      | false | -- The sum of all hidden rows before this point is 4
| 6  | 10  | 24      | true  | -- The total of all hidden rows is now >= 5. 
| 3  | 25  | 49      | true  |

Expected outcome for sec=2:

| id | cnt | cnt_sum | show  |
|----|-----|---------|-------|
| 7  | 50   | 50     | true  |
| 8  | 90   | 140    | true  |

I can already sort the values and create the sums etc. I have not figured out, how to determine how to set the cutoff point, when "hidding" is not necessary.

I am already doing this in "client code" and I want to migrate it to sql.

postgresql
1 Answers

Here LAG() will help to achieve what you want. You can write your query like below:

with cte as (
SELECT
    id, cnt, sec,
    sum(cnt) over (partition by sec order by cnt,id) sum_
FROM
    tbl )
    
    select 
    id, cnt, sum_,
    case
    when sum_<5 or lag(sum_) over (partition by sec order by cnt,id) <5 then 'false'
    else
    'true'
    end as "show"
    from cte

DEMO

user_77724830
Related