根据另一个字段的代码聚合一个字段中的多个值

发布于 2025-01-04 19:35:49 字数 630 浏览 4 评论 0原文

我需要根据同一个表的另一个字段中的代码计算表中一个字段的多个值。

该表位于 PostgreSQL 8.3 数据库中。

表：

cod_1 | cod_2 |    date    |  cod_sum| import
1     |   2   | 01/01/2012 |     a   | 1.1
1     |   2   | 01/01/2012 |     a   | 1.2
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1

我需要这样的东西：

cod_1 | cod_2 |date      |sum_import_a|sum_import_b|calc_import_abc(a+b-c)
1     |    2  |01/01/2012|    2.3     |   2.2      |   2.3 (2.3+2.2-2.2)

我希望你能伸出援手......

原文

I need to calculate several values from a field in a table depending on a code in another field of the same table.

The table is in a PostgreSQL 8.3 database.

Table:

cod_1 | cod_2 |    date    |  cod_sum| import
1     |   2   | 01/01/2012 |     a   | 1.1
1     |   2   | 01/01/2012 |     a   | 1.2
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1

I need something like this:

cod_1 | cod_2 |date      |sum_import_a|sum_import_b|calc_import_abc(a+b-c)
1     |    2  |01/01/2012|    2.3     |   2.2      |   2.3 (2.3+2.2-2.2)

I hope you can lend a hand ...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝眼睛不忧郁 2025-01-11 19:35:49

使用新的窗口函数 (PostgreSQL 8.4 或更高版本）您甚至可以在没有子查询的情况下执行此操作：

SELECT cod_1, cod_2, date
      , first_value(sum(import))  OVER w AS sum_import_a
      , nth_value(sum(import), 2) OVER w AS sum_import_b
      ,   first_value(sum(import))  OVER w
        + nth_value(sum(import), 2) OVER w
        - last_value(sum(import))   OVER w AS calc_import_abc
FROM   tbl
GROUP  BY 1, 2, 3, cod_sum
WINDOW w AS (ORDER BY cod_sum
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
LIMIT  1;

但是，为了简单起见，（对于 PostgreSQL 8.3）我会像 @Adrian 演示的那样执行此操作，只是没有逻辑错误：

SELECT cod_1, cod_2, date, sum_import_a, sum_import_b
     ,   sum_import_a
       + sum_import_b
       - sum_import_c AS calc_import_abc
FROM  (
    SELECT cod_1, cod_2, date
          , sum(CASE WHEN cod_sum = 'a' THEN import ELSE 0 END) sum_import_a
          , sum(CASE WHEN cod_sum = 'b' THEN import ELSE 0 END) sum_import_b
          , sum(CASE WHEN cod_sum = 'c' THEN import ELSE 0 END) sum_import_c
    FROM   tbl
    GROUP  BY 1, 2, 3
    ) x;

With the new window functions (PostgreSQL 8.4 or later) you could even do that without subquery:

SELECT cod_1, cod_2, date
      , first_value(sum(import))  OVER w AS sum_import_a
      , nth_value(sum(import), 2) OVER w AS sum_import_b
      ,   first_value(sum(import))  OVER w
        + nth_value(sum(import), 2) OVER w
        - last_value(sum(import))   OVER w AS calc_import_abc
FROM   tbl
GROUP  BY 1, 2, 3, cod_sum
WINDOW w AS (ORDER BY cod_sum
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
LIMIT  1;

For simplicity, however, (and for PostgreSQL 8.3) I would do it like @Adrian demonstrates, just without the logic error:

SELECT cod_1, cod_2, date, sum_import_a, sum_import_b
     ,   sum_import_a
       + sum_import_b
       - sum_import_c AS calc_import_abc
FROM  (
    SELECT cod_1, cod_2, date
          , sum(CASE WHEN cod_sum = 'a' THEN import ELSE 0 END) sum_import_a
          , sum(CASE WHEN cod_sum = 'b' THEN import ELSE 0 END) sum_import_b
          , sum(CASE WHEN cod_sum = 'c' THEN import ELSE 0 END) sum_import_c
    FROM   tbl
    GROUP  BY 1, 2, 3
    ) x;

回复收藏 0 原文

北恋 2025-01-11 19:35:49

假设您的列集始终相同（关于值 a、b 和 c），这就是您需要的：

select
    cod_1, cod_2, date, sum_import_a, sum_import_b, 
    sum_import_a + sum_import_b - sum_import_c as calc_import_abc
from
(    
    select cod_1, cod_2, date
       sum(case cod_sum when 'a' then 1 else 0 end) sum_import_a,
       sum(case cod_sum when 'b' then 1 else 0 end) sum_import_b,
       sum(case cod_sum when 'c' then 1 else 0 end) sum_import_c
    from YourTable
    group by cod_1, cod_2, date
) sel

Supposing your column set is always the same (regarding values a, b and c), here's what you need:

select
    cod_1, cod_2, date, sum_import_a, sum_import_b, 
    sum_import_a + sum_import_b - sum_import_c as calc_import_abc
from
(    
    select cod_1, cod_2, date
       sum(case cod_sum when 'a' then 1 else 0 end) sum_import_a,
       sum(case cod_sum when 'b' then 1 else 0 end) sum_import_b,
       sum(case cod_sum when 'c' then 1 else 0 end) sum_import_c
    from YourTable
    group by cod_1, cod_2, date
) sel

回复收藏 0 原文

~没有更多了~