MySQL：长格式数据转宽格式数据

在实际的数据分析和处理中，经常会遇到长格式数据（又称为tall格式数据）需要转换成宽格式数据（又称为wide格式数据）的情况，这在之前的数据处理中也称为数据的“变形”（reshaping）操作。MySQL提供了一些内置函数来更方便地实现这种转换过程。

阅读更多：MySQL 教程

什么是长格式数据和宽格式数据

在长格式数据中，每个观察值占一行，而每个变量占一列。在宽格式数据中，每个观察值仍然占一行，但是变量被拆分成多列，这样可以更直观地展现数据。

例如，下面是三个城市（纽约、旧金山、芝加哥）在3天里的温度数据的长格式数据：

将上述数据转换为宽格式数据后，则变成了这样：

可以看出，长格式数据和宽格式数据虽然包含的信息是一样的，但是展现方式截然不同，宽格式数据更加直观。

MySQL的转换函数

MySQL提供了一些内置函数来快速实现长格式数据转宽格式数据的转换。

GROUP_CONCAT函数

GROUP_CONCAT函数可以将同一组的多个值连接成一个字符串，因此可以用来将多行转化为一个长字符串表示一行。它可用来将同一组的多个值合并在一起，就像SQL聚合函数SUM，AVG等。

例如，将上述温度数据从长格式数据转换为一行表示的长字符串，则可以使用如下代码：

SELECT city, GROUP_CONCAT(CONCAT(day, ':', temperature) SEPARATOR ',') AS temp_str 
FROM table1 
GROUP BY city;

结果如下：

SUBSTRING_INDEX函数

SUBSTRING_INDEX函数可用于通过指定分隔符，将长字符串拆分成多个数据，从而实现长格式数据到宽格式数据的转换。

例如，上述温度数据的长字符串为：

'NYC', 'Monday:80,Tuesday:75,Wednesday:82'
'SF', 'Monday:65,Tuesday:70,Wednesday:75'
'CHI', 'Monday:70,Tuesday:72,Wednesday:68'

则可以使用如下代码将其转换为宽格式数据：

SELECT city, SUBSTRING_INDEX(temp_str, ',', 1) AS Monday, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(temp_str, ',', 2), ',', -1) AS Tuesday, 
    SUBSTRING_INDEX(temp_str, ',', -1) AS Wednesday 
FROM (SELECT city, GROUP_CONCAT(CONCAT(day, ':', temperature) SEPARATOR ',') AS temp_str
      FROM table1
      GROUP BY city) t

结果如下：

总结

长格式数据和宽格式数据在数据处理和分析过程中都有各自的作用，而MySQL提供的GROUP_CONCAT和SUBSTRING_INDEX函数可用于长格式数据到宽格式数据的转换，极大地简化了数据转换的流程。在实际应用中，可以根据具体数据结构和需求选择合适的函数和方法进行数据的变形。