postgresql - 过滤值的总和，直到达到某个阈值

Question

DbFiddle

卡住。需要这样:)

考虑以下值的分布。

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

我的目标是过滤sec然后

• cnt按升序排序，
• id按升序排序

然后将所有行标记/过滤为show - falsewhere cntis< 5并且直到所有隐藏行sum的 of cnt(show=false) 为 >= 5。

所以所有“隐藏”行的总和可能永远不会小于 5。

预期结果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  |

预期结果sec=2：

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

我已经可以对值进行排序并创建总和等。我还没有弄清楚，当不需要“隐藏”时，如何确定如何设置截止点。

我已经在“客户端代码”中这样做了，我想将它迁移到 sql。

标签： postgresql

Answer 1

这LAG()将有助于实现您想要的。您可以编写如下查询：

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

演示