PIVOT：轻松透视多维数据
PIVOT（数据透视）是数据分析中的核心能力之一，它可以将原始明细数据转换为结构化汇总视图，帮助用户快速识别关键指标与变化趋势。 本文将介绍 PIVOT 的核心价值、典型应用场景，以及在 Datalayers 中的使用方法，帮助你更高效地完成多维数据分析。
产品介绍
Datalayers 是澜图未来（成都）数据科技有限公司开发的一款一款分布式多模态数据库，原生支持时序、全文检索、向量、键值等应用场景，以SQL为核心查询语言，兼容PromQL、Redis及InfluxDB行协议。面向可观测性、物联网（IoT/IIoT）、AI 等数据密集型场景，提供低延迟写入、低成本存储、高性能分析及云原生弹性部署能力，同时深度优化边缘环境适配性，满足各类边缘部署需求。
PIVOT 介绍
PIVOT 函数用于将“行数据”转换为“列数据”，可实现类似 Excel 数据透视表的效果。通过透视与聚合，原本分散的明细数据能够以更清晰的结构呈现，便于报表构建和后续分析。
在 Datalayers 中使用 PIVOT
在工业物联网 (IIoT) 场景中，我们经常需要对来自不同传感器的数据进行多维度监控和分析。 例如，某工厂有多台机器，每台机器都需要采集“温度”这一指标数据，如数据集 device_temperature 所示，其中每一行代表一个时间点某设备的温度读数，包含时间戳、设备ID、温度数值等信息：
CREATE TABLE device_temperature(
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
device_id STRING,
temperature REAL,
timestamp key (ts),
)
PARTITION BY HASH(device_id) PARTITIONS 1
ENGINE=TimeSeries;
INSERT INTO device_temperature (ts, device_id, temperature) VALUES
('2025-06-28 10:00:00', 'M001', 85.0),
('2025-06-28 10:00:00', 'M002', 79.5),
('2025-06-28 10:00:00', 'M003', 110.0),
('2025-06-28 10:00:00', 'M004', 122.0),
('2025-06-28 10:00:00', 'M005', 120.0),
('2025-06-28 10:00:00', 'M006', 94.6),
('2025-06-28 10:00:10', 'M001', 90.0),
('2025-06-28 10:00:10', 'M002', 86.0),
('2025-06-28 10:00:10', 'M003', 99.5),
('2025-06-28 10:00:10', 'M004', 105.0),
('2025-06-28 10:00:10', 'M005', 90.0),
('2025-06-28 10:00:10', 'M006', 86.0),
('2025-06-28 10:00:15', 'M001', 86.5),
('2025-06-28 10:00:15', 'M002', 102.0),
('2025-06-28 10:00:15', 'M003', 93.0),
('2025-06-28 10:00:15', 'M004', 89.0);
如果我们需要查看每台设备在同一时刻下的温度，传统的查询可能需要复杂的聚合和分组，例如：
SELECT
ts,
MAX(CASE WHEN device_id = 'M001' THEN temperature ELSE 0 END) AS 'M001',
MAX(CASE WHEN device_id = 'M002' THEN temperature ELSE 0 END) AS 'M002',
MAX(CASE WHEN device_id = 'M003' THEN temperature ELSE 0 END) AS 'M003',
MAX(CASE WHEN device_id = 'M004' THEN temperature ELSE 0 END) AS 'M004',
MAX(CASE WHEN device_id = 'M005' THEN temperature ELSE 0 END) AS 'M005',
MAX(CASE WHEN device_id = 'M006' THEN temperature ELSE 0 END) AS 'M006'
FROM
device_temperature
GROUP BY
ts
ORDER BY ts
这将得到如下的结果：
+---------------------------+------+-------+-------+-------+-------+------+
| ts | M001 | M002 | M003 | M004 | M005 | M006 |
+---------------------------+------+-------+-------+-------+-------+------+
| 2025-06-28T18:00:00+08:00 | 85.0 | 79.5 | 110.0 | 122.0 | 120.0 | 94.6 |
| 2025-06-28T18:00:10+08:00 | 90.0 | 86.0 | 99.5 | 105.0 | 90.0 | 86.0 |
| 2025-06-28T18:00:15+08:00 | 86.5 | 102.0 | 93.0 | 89.0 | 0.0 | 0.0 |
+---------------------------+------+-------+-------+-------+-------+------+
如上 SQL，当希望查询结果中有更多 device_id 时，这将导致我们 SQL 变得难以维护，但通过 PIVOT 则可简单、快速实现以上功能。参考PIVOT文档
PIVOT 语法如下：
SELECT ...
FROM
<input>
PIVOT (
<aggregate_function>(<value_column>)
FOR <pivot_column> IN (<pivot_value_1>, <pivot_value_2>, ...)
[ DEFAULT ON NULL (<default_value>) ]
)
通过 PIVOT，我们只需要下面这样简单的语句就能做到相同的效果：
SELECT *
FROM
device_temperature
PIVOT
(
MAX(temperature)
FOR device_id IN ('M001', 'M002', 'M003', 'M004', 'M005', 'M006')
DEFAULT ON NULL (0)
)
这条 PIVOT 语句指定了三个重要信息：
- device_id 列作为 pivot_column：在结果中根据 device_id 列的指定值，新增 'M001', 'M002', 'M003', ... 等六列。
- ts 作为分组键：将输入数据（device_temperature 表）按照除了 device_id, temperature 列以外的列（此例中即为 ts）进行分组，结果中的每一行对应一个分组，都以 ts 作为一行的唯一标识。
- temperature 列作为 value_column：对于分到同一组的数据，根据 device_id 列的不同取值，将 value 值填入对应位置并执行聚合。
经过这样转换后，原表中同一时刻下的多行数据，被转换到了一行显示，并通过列名区分不同设备，极大地方便了对设备运行状态的整体监控。
注：Datalayers 在 v2.3.4 版本中实现了 PIVOT，如需使用 PIVOT，Datalayers 版本必须大于等于 v2.3.4。
应用场景
PIVOT 的主要作用是将数据从 “长” 格式（多行少列）转换为 “宽” 格式（少行多列）。除了上述例子外，PIVOT 还可以应用在其他场景下，比如：
- 多指标数据联合分析：在监控系统中，一台设备上的多个传感器可能会同时采集多种指标数据（如温度、压力、湿度）。初始状态下，这些数据通常都是以 “长” 格式存储，如 (设备ID, 时间戳, 指标类型, 指标值)。如果想分析在同一个时刻，某个设备多种指标的变化，就可以使用 PIVOT 语句，将 “设备ID” 和 “时间戳” 作为分组键进行分组，将 “指标类型” 作为 pivot_column 进行扩展，将 “指标值” 填入对应位置进行聚合。
- 改变数据表示形式以适应分析工具：许多数据可视化工具或机器学习算法更偏好 “宽” 格式的数据作为输入。PIVOT 可以帮助我们将规范化的、多行少列的数据转换成符合这些工具要求的 “宽” 格式。使得数据更易于导入到 Excel 等工具，或作为 Python/R 中数据分析库的直接输入。
结语
作为数据分析的核心工具，PIVOT 能将杂乱无章的原始数据转化为结构清晰、一目了然的图表，让隐藏在数据背后的价值浮出水面。借助 Datalayers 强大的数据分析功能，您可以： 秒级处理海量复杂数据 精准捕捉关键业务信息 深度挖掘数据潜在价值
立即开始使用 Datalayers，让您的数据真正发挥价值！
立即体验 Datalayers
高性能、云原生的时序数据存储引擎，轻松应对海量数据的写入与查询