sql - postgres 使用过度分区对动态行求和

Question

在 postgres 9.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

假设上面的查询输出(减去 ??s)。我正在尝试按付款人和有效状态对第 1、2、3 和 4+ 列求和。所以对于 p1 会有一个包含所有 effective_statuses 的列总计，然后 p2 会有一个组总计。

    | 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

这似乎在大多数时候都有效。有时会有错误的总数。这是正确的做法吗？

Answer 1

如果我理解正确，您可以使用 UNION ALL 将总结果集与您的原始表合并。然后按 grp 顺序使用 order by。

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 |