sql - 努力将数据集从行旋转到列

Question

我有一个数据集，其中有多条记录代表同一实体的不同统计数据。

例子:

DEVICE  METRIC           SCORE   WHEN
-------------------------------------------------
devA    reads_per_sec    100    2011-03-01 12:00:00 AM
devA    writes_per_sec   50     2011-03-01 12:00:00 AM
devA    total_per_sec    150    2011-03-01 12:00:00 AM
devB    reads_per_sec    200    2011-03-01 12:00:00 AM
devB    writes_per_sec   50     2011-03-01 12:00:00 AM
devB    total_per_sec    250    2011-03-01 12:00:00 AM
devC    reads_per_sec    300    2011-03-01 12:00:00 AM
...

我想将此表中的所有内容都转换为具有此结构的新表

DEVICE  READS  WRITES  TOTAL  WHEN
--------------------------------------------------
devA    100    50      150    2011-03-01 12:00:00 AM
devB    200    50      250    2011-03-01 12:00:00 AM
devC    300    50      150    2011-03-01 12:00:00 AM
...

我从数以千计的时间戳中为数以千计的设备中的每一个设置了十多个“指标”。

如果我可以在每个采样周期查看设备的一条记录，那么查询查看给定设备在给定时间的所有指标分数会容易得多。

那么，我的问题是，处理和执行它的最佳方法是什么？我并不是要让任何人为我做我的工作，但这超出了我对数据库的了解。

Answer 1

这将重现您列出的预期结果:

  SELECT t.device,
         SUM(CASE WHEN t.metric = 'reads_per_sec' THEN t.score ELSE 0 END) AS reads,
         SUM(CASE WHEN t.metric = 'writes_per_sec' THEN t.score ELSE 0 END) AS writes,
         SUM(CASE WHEN t.metric = 'total_per_sec' THEN t.score ELSE 0 END) AS total,
         t.when
    FROM YOUR_TABLE t
GROUP BY t.device, t.when
ORDER BY t.device, t.when

如果您需要处理各种指标，您可能不得不使用 dynamic SQL .您需要获得一个唯一的指标值列表(除非您有一个用于外键引用的类型代码表):

SELECT DISTINCT t.metric
  FROM YOUR_TABLE t

然后，在执行查询之前连接每个指标的 SUM(CASE ...。