如何将water_temp表多深度温度数据按时间戳行转列并导出CSV？

一、SQL直接实现行转列 #

你的表是每个时间戳对应14个固定深度的记录，直接用SQL就能完成转置，分两种写法：

1. 通用CASE WHEN写法（所有SQL数据库兼容） #

假设原表字段为unixtimestamp（时间戳）、depth（深度值，比如10、20）、temperature（温度），通过分组聚合+CASE WHEN提取各深度的温度：

SELECT
    unixtimestamp,
    MAX(CASE WHEN depth = 10 THEN temperature END) AS depth10,
    MAX(CASE WHEN depth = 20 THEN temperature END) AS depth20,
    -- 依次补全剩下12个深度的CASE WHEN语句
    MAX(CASE WHEN depth = 140 THEN temperature END) AS depth140
FROM water_temp
GROUP BY unixtimestamp;

这里用MAX（或MIN、AVG，因为每个时间戳+深度的组合唯一，聚合函数不影响结果）提取对应深度的温度，分组后每个时间戳只会保留一行。

2. 数据库原生PIVOT语法（部分数据库支持） #

如果用MySQL 8.0+、PostgreSQL、SQL Server这类支持PIVOT的数据库，语法更简洁：

MySQL 8.0+ 示例：

SELECT *
FROM (
    SELECT unixtimestamp, CONCAT('depth', depth) AS depth_col, temperature
    FROM water_temp
) AS src
PIVOT (
    MAX(temperature)
    FOR depth_col IN ('depth10', 'depth20', ..., 'depth140')
) AS pivoted;

PostgreSQL 示例（需先安装tablefunc扩展）：

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    'SELECT unixtimestamp, depth, temperature FROM water_temp ORDER BY 1,2',
    'SELECT unnest(ARRAY[10,20,...,140])'
) AS ct(unixtimestamp BIGINT, depth10 NUMERIC, depth20 NUMERIC, ..., depth140 NUMERIC);

二、导出为CSV格式 #

转置后的结果可以直接用数据库自带功能导出：

• MySQL：在查询末尾追加INTO OUTFILE '/目标路径/文件名.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';（注意文件路径权限）
• PostgreSQL：用COPY (你的转置查询语句) TO '/目标路径/文件名.csv' WITH (FORMAT CSV, HEADER);
• SQL Server：可以用bcp命令行工具，或者在SSMS界面直接导出查询结果为CSV

三、原表结构的合理性分析 #

你的原表属于**长表（窄表）**结构，是时间序列测量数据的常见存储方式，本身是合理的，优缺点如下：

• 优点：
  • 扩展性极强：新增深度不需要修改表结构，直接插入对应depth值的记录即可
  • 存储效率高：避免宽表中出现大量空值（如果部分时间戳缺少某些深度的数据）
  • 适合批量分析：比如统计所有深度的温度变化趋势，长表结构更便于聚合计算
• 缺点：
  • 查询单时间点的多深度数据需要写聚合逻辑，比宽表写法复杂
  • 直观可读性差，无法直接看到一个时间点的所有深度数据

如果你的业务以报表展示、单时间点多深度数据查询为主，宽表更方便；如果以批量数据处理、多维度分析、频繁新增深度为主，原长表结构更合适。

内容的提问来源于stack exchange，提问作者user2225403