sql-sum但是过滤2 max列

kfgdxczn 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(326)

我有一张像下面这样的table。

qid  segment task    before  after   diff
------------------------------------------
1       0    aaa       5     5       0
1       0    aaa       5     5       0
1       1    aaa       15    15      0
1       1    aaa       10    5      10
1       0    aaa       10    5       5
3       0    bbb       10    4       6

对于每一个任务，我需要对before、after和diff值求和，但是我只想对高qid和段执行此操作。
例如：对于任务a，
高qid=1
高位段=1
这是我想作为任务a的行。
对于任务b，
高qid=3
高位段=0
最后一行是我要计算的。

预期产量：

task    before  after   diff
-----------------------------
aaa       25    20      10
bbb       10    4       6

sql postgresql group-by sum greatest-n-per-group

来源：https://stackoverflow.com/questions/62299649/sql-sum-but-filter-the-2-max-column

2条答案

按热度按时间

nvbavucw1#

尝试 dense_rank . 这是演示。

with cte as
(
    select
        *,
        dense_rank() over (partition by task order by qid desc, segment desc) as rnk
    from myTable
)

select
    task,
    sum(before) as before,
    sum(after)  as after,
    sum(diff) as  diff
from cte
where rnk = 1
group by
    task

输出：

| task | before | after | diff |
| ---- | ------ | ----- | ---- |
| aaa  | 25     | 20    | 10   |
| bbb  | 10     | 4     | 6    |

赞(0）回复(0）举报 2021-07-29

vxf3dgd42#

您可以使用聚合和 distinct on :

select distinct on (task) 
    qid, 
    segment, 
    task, 
    sum(before) before,
    sum(after) after,
    sum(diff) diff
from mytable t
group by task, qid, segment
order by task, qid desc, segment desc

赞(0）回复(0）举报 2021-07-29

我来回答

sql-sum但是过滤2 max列

预期产量：

2条答案

相关问题

热门标签

最新问答