PIVOT：轻松透视多维数据

PIVOT（数据透视）是数据分析中的一项核心操作，它能将原始数据转化为直观易懂的汇总视图，帮助用户快速发现数据中的关键内容和趋势。本文将带您了解 PIVOT 的核心价值与典型应用场景，并详细介绍如何在数据分析中有效运用 PIVOT 功能。通过结合 Datalayers，您将能够从复杂数据中提取更有价值的业务洞察。

产品介绍

Datalayers 是澜图未来（成都）数据科技有限公司开发的一款为工业物联网、车联网、具身、AI、能源等行业设计、开发的分布式多模态、超融合数据库，提供时序、日志、键值、Metrics、Traces 等存储模型，具备存算分离、读写分离、自适应压缩、原生 SQL、边云同步和云原生等特点。

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，让您的数据真正发挥价值！