MySQL 行列转换

1. 横表和纵表

在数据库设计中，横表（Wide Table）和纵表（Long Table）是两种不同的数据表示方式，它们各自有特定的使用场景和优缺点。

1.1. 横表（Wide Table）

• 结构：横表通常具有较少的行和较多的列。每一列代表一个特定的属性或度量。
• 数据表示：在横表中，每一行代表一个实体，实体的属性或度量值分布在不同的列中。
• 适用场景：横表适用于数据相对静态，并且每个实体的属性数量相对固定的情况。比如存储用户信息的表，其中每个用户都有姓名、年龄、地址等固定属性。

横表示例：

CREATE TABLE IF NOT EXISTS user_wide
(
    user_id  INT,
    username VARCHAR(50),
    age      INT,
    address  VARCHAR(100)
    -- 其他用户属性
);

1.2. 纵表（Long Table）

• 结构：纵表通常具有较多的行和较少的列。每一列代表一个变量，每一行代表一个观测值。
• 数据表示：在纵表中，每个实体的多个属性或度量值分布在多行中，通常由一个标识符关联实体和它的属性。
• 适用场景：纵表适用于数据动态变化，或者实体的属性数量不固定的情况。比如存储用户行为日志，其中每个用户可能有多个行为记录。

纵表示例：

CREATE TABLE user_long
(
    log_id          INT,
    user_id         INT,
    attribute_name  VARCHAR(50),
    attribute_value VARCHAR(100)
    -- 其他行为记录
);

2. 行列转换

在实际应用中，可能需要在横表和纵表之间进行转换。

2.1. 将横表转换为纵表

这通常涉及到“松散化”（Pivoting）过程，将多个列的值转换为行。

SELECT user_id,
       'username' AS attribute_name,
       username   AS attribute_value
FROM user_wide
UNION ALL
SELECT user_id,
       'age',
       age
FROM user_wide
UNION ALL
SELECT user_id,
       'address',
       address
FROM user_wide;

2.2. 将纵表转换为横表

这通常涉及到“逆松散化”（Unpivoting）过程，将多行的值转换为列。

SELECT user_id,
       MAX(CASE WHEN attribute_name = 'username' THEN attribute_value END) AS username,
       MAX(CASE WHEN attribute_name = 'age' THEN attribute_value END)      AS age,
       MAX(CASE WHEN attribute_name = 'address' THEN attribute_value END)  AS address
FROM user_long
GROUP BY user_id;

3. CASE 表达式

CASE 表达式是一种多路选择器，类似于编程语言中的 if-else 或 switch 语句。它允许在 SQL 查询中根据条件返回不同的值。CASE 表达式可以用于 SELECT、UPDATE、INSERT 或 DELETE 语句中。

3.2. 简单 CASE 表达式

简单 CASE 表达式通常用于对一个表达式进行条件判断，并且返回相应的结果。其基本语法如下：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

• expression 是要计算的表达式。
• WHEN 关键字后面跟一个值，然后是 THEN 关键字和对应的结果。
• ELSE 关键字后面是默认结果，如果前面的 WHEN 条件都不满足，那么返回该结果。
• END 关键字标志着 CASE 表达式的结束。

3.2. 搜索 CASE 表达式

搜索 CASE 表达式不计算特定的表达式，而是根据一系列条件返回结果。其基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

• 每个 WHEN 后面跟一个条件，然后是 THEN 关键字和对应的结果。

3.3.示例

假设有一个名为 Employees 的表，包含员工的 Department 和 Salary：

CREATE TABLE Employees (
    EmployeeID INT,
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

可以使用 CASE 表达式根据部门的不同，将每个员工的薪资增加不同的百分比：

SELECT EmployeeID,
       Department,
       Salary,
       CASE Department
           WHEN 'Sales' THEN Salary * 1.1 -- 销售部门增加10%
           WHEN 'Engineering' THEN Salary * 1.05 -- 工程部门增加5%
           ELSE Salary -- 其他部门薪资不变
           END AS NewSalary
FROM Employees;

再举一个使用搜索 CASE 表达式的例子，假设想要根据薪资的不同范围，为员工分配不同的薪资等级：

SELECT EmployeeID,
       Salary,
       CASE
           WHEN Salary >= 80000 THEN 'High'
           WHEN Salary >= 50000 THEN 'Medium'
           ELSE 'Low'
           END AS SalaryGrade
FROM Employees;

CASE 表达式非常有用，尤其是在需要根据一系列复杂的业务规则进行转换或分类数据时。

参考文档

• https://kimi.moonshot.cn/