代码之家 › 专栏 › 技术社区 › StillLearningToCode

使用postgres的over partition求和动态行

postgresql sql

StillLearningToCode · 技术社区 · 6 年前

关于Postgres9.2

| payer| effective_status | 1     |    2   | 3   | 4+
+------+ -----------------+-------+--------+-----+-----
|  p1  | foo              |     8 |   6000 |    4|  1
|  p1  | bar              |    10 |   5200 |    9|  2
|  p1  | baz              |    11 |   5200 |   11|  2
|  p1  | zip              |     9 |   4500 |   14|  4
|  p1  | zap              |     7 |   4200 |   45|  5
|  p1  | status_n         |     2 |   3900 |   71|  1

    | p1   | effective_status | 1     |    2   | 3   | 4+| 1 total | 2 total|3 total| 4+ total
    +------+ -----------------+-------+--------+-----+---+---------+--------+-------+----------
    |      | foo              |     8 |   6000 |    4|  1|    94   |  6230  |  154  | 15
    |      | bar              |    10 |   5200 |    9|  2|    94   |  6230  |  154  | 15
    |      | baz              |    11 |   5200 |   11|  2|    94   |  6230  |  154  | 15
    |      | zip              |     9 |   4500 |   14|  4|    94   |  6230  |  154  | 15
    |      | zap              |     7 |   4200 |   45|  5|    94   |  6230  |  154  | 15
    |      | status_n         |     2 |   3900 |   71|  1|    94   |  6230  |  154  | 15

我怎么计算??s?我的我试过:

payer
,effective_status
,status_check1
,SUM(status_check1) OVER (PARTITION BY payer) AS status_check1_total
,status_check2
,SUM(status_check2) OVER (PARTITION BY payer) AS status_check2_total
,status_check3
,SUM(status_check3) OVER (PARTITION BY payer) AS status_check3_total
,status_check4
,SUM(status_check4) OVER (PARTITION BY payer) AS status_check4_total

3 回复 | 直到 6 年前

D-Shih 6 年前

如果我理解正确,你可以使用 UNION ALL order by 由 grp

CREATE TABLE T(
  payer varchar(50),
  effective_status varchar(50),
  status_check1 int,
  status_check2 int,
  status_check3 int,  
  status_check4 int
);


INSERT INTO T VALUES ('p1', 'foo',8 ,6000,4,1);
INSERT INTO T VALUES ('p1', 'bar',10,5200,9,2);
INSERT INTO T VALUES ('p1', 'baz',11,5200,11,2);
INSERT INTO T VALUES ('p1', 'zip',9 ,4500,14,4);
INSERT INTO T VALUES ('p1', 'zap',7 ,4200,45,5);
INSERT INTO T VALUES ('p1', 'status_n',2 ,3900,71,1);
INSERT INTO T VALUES ('p2', 'foo',5 ,3500,12,2);
INSERT INTO T VALUES ('p2', 'zip',1 ,5000,1,1);

问题1 :

SELECT * 
FROM (
  SELECT t1.payer
    ,effective_status
    ,status_check1
    ,status_check2
    ,status_check3
    ,status_check4
    ,1 grp
  FROM T t1 
  UNION ALL
  SELECT payer,
         '',
         SUM(status_check1),
         SUM(status_check2),
         SUM(status_check3),
         SUM(status_check4),
         2
  FROM T
  GROUP BY payer 
) t1
ORDER BY payer,grp

Results :

| payer | effective_status | status_check1 | status_check2 | status_check3 | status_check4 | grp |
|-------|------------------|---------------|---------------|---------------|---------------|-----|
|    p1 |              foo |             8 |          6000 |             4 |             1 |   1 |
|    p1 |              bar |            10 |          5200 |             9 |             2 |   1 |
|    p1 |              baz |            11 |          5200 |            11 |             2 |   1 |
|    p1 |              zip |             9 |          4500 |            14 |             4 |   1 |
|    p1 |              zap |             7 |          4200 |            45 |             5 |   1 |
|    p1 |         status_n |             2 |          3900 |            71 |             1 |   1 |
|    p1 |                  |            47 |         29000 |           154 |            15 |   2 |
|    p2 |              foo |             5 |          3500 |            12 |             2 |   1 |
|    p2 |              zip |             1 |          5000 |             1 |             1 |   1 |
|    p2 |                  |             6 |          8500 |            13 |             3 |   2 |

Gordon Linoff 6 年前

union all :

select payer, effective_status, status_check1, status_check2, status_check3, status_check4
from t
union all
select payer, null, sum(status_check1), sum(status_check2), sum(status_check3), sum(status_check4)
order by payer, effective_status nulls last;

Postgres 9.5支持 grouping sets 从而简化了这种逻辑。

Sheruan Bashar 6 年前

实际上,我不清楚你想做什么,但如果你想让结果按付款人和有效的状态分组,可能会是这样的

select 
   payer as p, 
   effective_status as es,
   (sum(col1) + sum(col2) + sum(col3) + sum(col4)) as sum
from table_name
group by p, es