Postgres-基于另一列之和的结果来计算总和[英] Postgres - Calculating sums based on the result of sum of another column

本文是小编为大家收集整理的关于Postgres-基于另一列之和的结果来计算总和的处理方法,想解了Postgres-基于另一列之和的结果来计算总和的问题怎么解决?Postgres-基于另一列之和的结果来计算总和问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我正在使用 Postresql 9.6,并试图弄清楚如何创建一个查询来根据不同列的离散总和(都在同一个表中)计算列的总和.例如,我希望每组行的计数总和直到体积 >= 100,然后重新开始计算计数总和.

示例数据:

id          count       volume
1           5           12
2           6           14
3           2           11
4           10          9
5           5           14
6           17          19
7           0           8
8           12          4
9           18          6
10          12          14
11          10          10
12          15          7
13          8           12
14          2           17
15          5           30
16          9           24
17          2           16.5

假设的中间结果(只是为了理解我的期望):

id         sum(count)  discrete volume
1          5           12
2          11          26
3          13          37
4          23          46
5          28          60
6          45          79          
7          45          87
8          57          91
9          75          97
10         87          111
11         10          10   (reset since volume >= 100)
12         25          17
13         33          29
14         35          46
15         40          76
16         49          100
17         2           16.5   (reset since volume >= 100)

预期的最终结果:

sum(count) discrete volume
87         111
49         100
2          16.5   (partial result, which is desired)

到目前为止我已经尝试过:SQL Fiddle

我对 SQL Fiddle 中显示的当前查询有所了解,来自 another StackOverflow answer 这显然不能正常工作(但它对于不同的我试图解决的问题).我从 不同的 StackOverflow 答案 中看到我可能想要正在使用 RECURSIVE 查询(PostgreSQL 文档),但我可以'不知道如何正确编写查询以便它工作:(

我当然可以用 Java 代码编写它(并且已经有了),但我想用 SQL 来编写它,因此希望它比读取所有行并计算结果更快.我也很可能编写一个存储过程来完成这项工作,但我宁愿避免这种情况,因为我正在使用 JPA(在本例中使用本机查询)并希望将所有代码保留在同一个位置(Java 代码库).另外,我希望能够动态地包含/排除列,所以我想在 Java 代码中构造查询(实际表的列比我的示例多得多).

感谢您提供的任何帮助.

编辑:

感谢@klin 的评论并查看引用的 StackOverflow 问题,我越来越接近了.这是我所拥有的(生成中间结果):

WITH RECURSIVE WorkTable(id, count_sum, volume_sum) AS 
(
    SELECT 
        id,
        count AS count_sum,
        volume AS volume_sum
    FROM measurements
    WHERE id = 1
    UNION ALL
    SELECT
        measurements.id,
        CASE WHEN WorkTable.volume_sum >= 100
            THEN measurements.count
            ELSE WorkTable.count_sum + measurements.count
        END AS count_sum,
        CASE
            WHEN WorkTable.volume_sum >= 100
            THEN measurements.volume
            ELSE WorkTable.volume_sum + measurements.volume
        END AS discrete_sum_volume
        FROM measurements
        JOIN WorkTable
        ON measurements.id = WorkTable.id + 1
)
SELECT *
FROM WorkTable
ORDER BY id

但是,我仍然缺少的是如何获得最终结果.如果我使用 WHERE volume_sum >= 100 我不会得到最终(部分)结果.我不能使用 OR id = MAX(id) 因为 Postgres 不允许在 WHERE 子句中使用它.

编辑:顺便说一句,在 @klin 和 @JorgeCampos 花费大量时间查看和回答我的问题之后,我发现此查询不适用于具有数百万行的表.我创建了一个存储过程;我不想去那里,但似乎没有其他表现良好的选择.对于大型表,存储过程的性能优于 RECURSIVE 查询.

推荐答案

我使用了 row_count() 因为需要连续的行号.您不应该依赖主键,因为它通常可能包含间隙.

with recursive cte as (
    select *, row_number() over (order by id)
    from measurements
),
work_table as (
    select 
        id,
        count as count_sum,
        volume as volume_sum,
        row_number
    from cte
    where row_number = 1
    union all
    select
        c.id,
        case when w.volume_sum >= 100
            then c.count
            else w.count_sum + c.count
        end as count_sum,
        case
            when w.volume_sum >= 100
            then c.volume
            else w.volume_sum + c.volume
        end as discrete_sum_volume,
        c.row_number
    from cte c
    join work_table w
    on c.row_number = w.row_number + 1
)
select count_sum, volume_sum
from work_table
where volume_sum >= 100
or id = (select max(id) from work_table)
order by id

结果:

 count_sum | volume_sum 
-----------+------------
        87 |        111
        49 |        100
         2 |       16.5
(3 rows)

SqlFiddle.

本文地址:https://www.itbaoku.cn/post/1764135.html