PostgreSQL，水平和垂直混合 SUM

Question

我有一个临时表，它是以前大量组合数据的结果，我必须从中创建要显示的 html 文档。

此表简要说明了情况:

DROP TABLE IF EXISTS temp11;
CREATE TABLE temp11 (t_idx int PRIMARY KEY, mydate text, myclass int, mypercent double precision, valpercent double precision, valclass double precision);

INSERT INTO temp11
(t_idx, mydate, myclass, mypercent, valpercent, valclass) VALUES 
(1,  '01.01.2014',  1,  10,      10,      1),
(2,  '01.01.2014',  2,  20,      20,      4),
(3,  '01.01.2014',  2,  20,      50,     10),
(4,  '01.01.2014',  1,  10,      17,    1.7),
(5,  '02.01.2014',  2,  20,      40,      8),
(6,  '02.01.2014',  1,  10,      18,    1.8),
(7,  '02.01.2014',  2,  20,      50,     10),
(8,  '03.01.2014',  1,  10,      10,      1),
(9,  '03.01.2014',  2,  20,      40,      8),
(10, '03.01.2014',  1,  10,      20,      2),
(11, '03.01.2014',  2,  20,      30,      6);

现在我有一个查询，用于将其分组并汇总到日期和 valclasses 中:

SELECT mydate, myclass, mypercent, 
      SUM(valpercent)          AS sumvalpercent, 
      SUM(valclass)            AS sumvalclass, 
      SUM(valpercent+valclass) AS sum_row 
FROM  temp11 
GROUP BY mydate, myclass, mypercent 
ORDER BY mydate;

这个查询的结果是可以预期的:

"01.01.2014"   2   20   70  14.0   84.0
"01.01.2014"   1   10   27   2.7   29.7
"02.01.2014"   1   10   18   1.8   19.8
"02.01.2014"   2   20   90  18.0  108.0
"03.01.2014"   2   20   70  14.0   84.0
"03.01.2014"   1   10   30   3.0   33.0

但是需求有点扩展。

是否可以使用 PostgreSQL，在每个日期之后的同一进程中，我垂直获取该日期内的数据总和，毕竟，最后，所有日期的数据总和，因此结果将如下所示:

"01.01.2014"   2   20   70  14.0   84.0
"01.01.2014"   1   10   27   2.7   29.7
                        97  16.7  113.7
"02.01.2014"   1   10   18   1.8   19.8
"02.01.2014"   2   20   90  18.0  108.0
                       108  19.8  127.8
"03.01.2014"   2   20   70  14.0   84.0
"03.01.2014"   1   10   30   3.0   33.0
                       100  17.0  117.0
                       305  53.5  358.5

如果这是可能的(或类似的)，该查询应该如何显示数据？

Answer 1

我能想到的最简单的方法是使用UNION ALL 一次获得所有需要的输出。如果您忽略显示日期这一事实(order by 子句需要)，此查询将以最简单的方式提供请求的输出。

SELECT mydate, myclass, mypercent, 
      SUM(valpercent)          AS sumvalpercent, 
      SUM(valclass)            AS sumvalclass, 
      SUM(valpercent+valclass) AS sum_row 
FROM  temp11 
GROUP BY mydate, myclass, mypercent 
UNION ALL
SELECT mydate || ' total', null, null, 
      SUM(valpercent)          AS sumvalpercent, 
      SUM(valclass)            AS sumvalclass, 
      SUM(valpercent+valclass) AS sum_row 
FROM  temp11 
GROUP BY mydate
UNION ALL
SELECT 'Total', null, null, 
      SUM(valpercent)          AS sumvalpercent, 
      SUM(valclass)            AS sumvalclass, 
      SUM(valpercent+valclass) AS sum_row 
FROM  temp11 
ORDER BY mydate;

这是一个 fiddle

也许可以使用 WITH

更优雅地重写它

编辑:

这样会更有效率，因为它只遍历 temp11 表一次。然后它只使用临时表 temp100，该表的行数要少得多(每天不超过一行)。 UNION 仍然存在，逻辑仍然相同。

WITH temp100 (mydate,myclass,mypercent, sumvalpercent,sumvalclass,sum_row) as (
  SELECT mydate, myclass, mypercent, 
      SUM(valpercent)          AS sumvalpercent, 
      SUM(valclass)            AS sumvalclass, 
      SUM(valpercent+valclass) AS sum_row 
  FROM  temp11 
  GROUP BY mydate, myclass, mypercent 
)
SELECT mydate,myclass,mypercent, sumvalpercent,sumvalclass,sum_row
FROM temp100
UNION ALL
SELECT mydate || ' total' as mydate, null, null, SUM(sumvalpercent), SUM(sumvalclass), SUM(sum_row)
FROM temp100
GROUP BY mydate
UNION ALL
SELECT 'Total' as mydate, null, null, SUM(sumvalpercent), SUM(sumvalclass), SUM(sum_row)
FROM  temp100
ORDER BY mydate;

这是 fiddle