MySQL – 行列转换

MySQL – 行列转换

在MySQL中,有时需要将表中的行数据转换为列数据,或将列数据转换为行数据。这种操作被称为“行列转换”。

MySQL提供了两个常用的函数来实现行列转换:GROUP BYPIVOT

阅读更多:MySQL 教程

将行数据转换为列数据

使用GROUP BY函数

GROUP BY函数可以将相同列数据的行数据进行合并,并将其合并后的值作为新的一列数据。

例如,我们有如下一张存储订单信息的表格:

OrderID CustomerID OrderDate Product
1 101 2020-01-01 Apple
2 102 2020-01-02 Banana
3 101 2020-01-03 Orange
4 102 2020-01-04 Grape
5 101 2020-01-05 Mango

如果需要将每个客户购买的所有商品按照订单日期列出,如下所示:

Date Customer101 Customer102
2020-01-01 Apple NULL
2020-01-02 NULL Banana
2020-01-03 Orange NULL
2020-01-04 NULL Grape
2020-01-05 Mango NULL

可以使用如下语句:

SELECT OrderDate, 
       MAX(CASE WHEN CustomerID = 101 THEN Product ELSE NULL END) AS Customer101,
       MAX(CASE WHEN CustomerID = 102 THEN Product ELSE NULL END) AS Customer102
FROM orders
GROUP BY OrderDate;
Mysql

CASE函数可以根据条件返回不同的值,这里我们根据CustomerID来判断是哪个客户。MAX函数可以帮助我们将列中的多个值合并成单个值。

使用PIVOT函数

MySQL没有提供官方的PIVOT函数,但是我们可以使用自定义函数来实现PIVOT功能。

下面是一个简单的PIVOT自定义函数的示例:

DELIMITER //

CREATE FUNCTION pivot (
  SELECT_QUERY VARCHAR(1000),
  GROUPBY_FIELDS VARCHAR(1000),
  PIVOT_FIELD VARCHAR(100),
  FILTER VARCHAR(100)
)
RETURNS VARCHAR(2000)
DETERMINISTIC
BEGIN
  DECLARE PIVOT_SQL VARCHAR(2000);
  DECLARE SEARCH_STR VARCHAR(1000);
  DECLARE REPLACE_STR VARCHAR(2000);
  SET SEARCH_STR = CONCAT("'",FILTER,"',");
  SET REPLACE_STR = CONCAT("`",FILTER,"`");
  SET PIVOT_SQL = CONCAT(
    "SELECT ", GROUPBY_FIELDS, ", ",
    "MAX(CASE ", REPLACE(PIVOT_FIELD, SEARCH_STR, REPLACE_STR), " END) AS VALUE ",
    "FROM (",
      SELECT_QUERY,
    ") RAW_DATA ",
    "GROUP BY ", GROUPBY_FIELDS
  );
  RETURN PIVOT_SQL;
END//

DELIMITER ;
Mysql

该函数接受四个参数:原始查询语句、GROUP BY字段、需要被PIVOT的字段、替换的字符。

使用该函数的例子:

SELECT pivot(
  'SELECT CustomerID, OrderDate, Product FROM orders',
  'OrderDate',
  'CustomerID',
  'Customer'
);
Mysql

将会返回如下结果:

SELECT OrderDate, 
       MAX(CASE CustomerID WHEN 101 THEN Product ELSE NULL END) AS Customer101,
       MAX(CASE CustomerID WHEN 102 THEN Product ELSE NULL END) AS Customer102
FROM (SELECT CustomerID, OrderDate, Product FROM orders) RAW_DATA 
GROUP BY OrderDate;
Mysql

将列数据转换为行数据

使用UNION函数

UNION函数可以将多个查询的结果集合并成一个。

例如,我们有如下一张存储国家和语言信息的表格:

Country Language1 Language2 Language3
China Mandarin Cantonese Shanghainese
Japan Japanese NULL NULL
Thailand Thai NULL NULL

如果需要将语言信息展开成行数据,如下所示:

Country Language
China Mandarin
China Cantonese
China Shanghainese
Japan Japanese
Thailand Thai

可以使用如下语句:

SELECT Country, Language1 AS Language FROM languages
UNION
SELECT Country, Language2 AS Language FROM languages
UNION
SELECT Country, Language3 AS Language FROM languages
WHERE Language3 IS NOT NULL;
Mysql

该语句使用了三个查询,分别查询Language1Language2Language3的值,然后使用UNION将结果合并。

使用自连接

另一种将列数据转换为行数据的方法是使用自连接。自连接是通过使用JOIN将表与自身连接得到的结果。

例如,我们有如下一张存储客户交易信息的表格:

CustomerID TransactionDate TransactionAmount
101 2020-01-01 100
101 2020-02-01 200
102 2020-01-03 150
102 2020-02-05 300

如果需要将每个客户交易信息按时间展开成行数据,如下所示:

CustomerID TransactionDate TransactionAmount
101 2020-01-01 100
101 2020-02-01 200
102 2020-01-03 150
102 2020-02-05 300

可以使用如下语句:

SELECT t1.CustomerID, t2.TransactionDate, t2.TransactionAmount
FROM transactions t1, transactions t2
WHERE t1.CustomerID = t2.CustomerID and t2.TransactionDate >= t1.TransactionDate;
Mysql

该语句使用了自连接,得到了每个客户的交易信息,并按时间展开成行数据。

总结

MySQL中的行列转换可以使用GROUP BYPIVOTUNION和自连接等方法实现。这些方法各有优缺点,在使用时需要根据具体场景选择合适的方法。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

登录

注册