sql - 如何聚合行并创建数据透视表

Question

我有一个这样的表:

Start | End | Unit | Measure
-----------------------------
01    | 05  | GB   | 1
06    | 10  | GB   | 2
01    | 05  | MB   | 4
01    | 05  | GB   | 1

单位列可以包含三个值 KB、MB 和 GB。

我想创建一个具有以下属性的新表。

Start | End | KB | MB | GB
---------------------------
01    | 05  | 0  | 4  | 2
06    | 10  | 0  | 0  | 2

每个相应单位的测量值应在相应列中求和，在不存在的地方清零。

如果我拥有的记录数以百万计，在 Postgres 中执行此操作的最佳方法是什么？以编程方式执行此操作非常慢。

Answer 1

每个单元的 Case expression 应该有效:

Click here for working SqlFiddle example

CREATE TABLE test_table (
  Start_val CHAR(2),
  End_val  CHAR(2),
  Unit  CHAR(2),
  Measure INTEGER );

INSERT INTO test_table VALUES ('01', '05', 'GB', 1);
INSERT INTO test_table VALUES ('06', '10', 'GB', 2);
INSERT INTO test_table VALUES ('01', '05', 'MB', 4);
INSERT INTO test_table VALUES ('01', '05', 'GB', 1);

CREATE TABLE new_table AS
SELECT Start_val AS Start, 
       End_val AS End, 
       SUM(CASE WHEN Unit = 'KB' THEN Measure ELSE 0 END) AS KB, 
       SUM(CASE WHEN Unit = 'MB' THEN Measure ELSE 0 END) AS MB,
       SUM(CASE WHEN Unit = 'GB' THEN Measure ELSE 0 END) AS GB
FROM   test_table
WHERE Unit IN ('KB', 'MB', 'GB')
GROUP  BY Start_val,
          End_val
ORDER  BY Start_val; 

SELECT * FROM new_table;