MySQL 8.0高效实现行转列

一、业务场景

如果存在如下样式的数据存储在Mysql数据库中:

订单编号	发货日期
SF55922,SF55923,SF55924,SF55925	2024-07-22
SF30816,SF30818	2024-07-23
......	......

需要以每个单子的维度进行导出统计，即如下样式：

订单编号	发货日期
SF55922	2024-07-22
SF55923	2024-07-22
SF55924	2024-07-22
SF55925	2024-07-22
SF30816	2024-07-23
SF30818	2024-07-23
......	......

二、SQL

SELECT 
    TRIM(BOTH '"' FROM JSON_UNQUOTE(JSON_EXTRACT(json_each.value, '$'))) AS id,
    DATE(docCreationDate) AS date
FROM 
    your_schema.your_table,
    JSON_TABLE(CONCAT('["', REPLACE(order_no, ',', '","'), '"]'),
    '$[*]' COLUMNS (value JSON PATH '$')) json_each
ORDER BY date, id ;

---

操作步骤释义

   1、JSON_TABLE

• 使用 JSON_TABLE 函数将 order_no 字符串转换为JSON数组。
• 使用 REPLACE 函数将逗号 , 替换为 ","，并用 CONCAT 函数将整个字符串包裹成一个JSON数组格式。

   2、JSON_EXTRACT

• 从JSON数组中提取每一个元素，生成多行。
• 使用 JSON_UNQUOTE 函数移除引号。
• 使用 TRIM 函数去掉多余的引号。

   3、最终选择:

• 选择设备ID和对应的 docCreationDate。
• 按日期和设备ID排序。

---

综上：这样，每个订单号都与对应的创建日期在单独的行中显示。使用JSON函数来拆分字符串通常比递归CTE更高效，特别是对于大数据集。

---

三、去重

如果在上述场景下，还需要保证截取得到的订单号是唯一的，且规则为发货日期最新的一条（即需要根据一定规则去重）。那么就需要使用到窗口函数

---

ROW_NUMBER()

用于为结果集中的每一行分配一个唯一的序号。这个函数特别适用于需要对查询结果进行编号、分组或排序的场景

--基本语法
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

• PARTITION BY子句是可选的，用于将结果集划分为多个分区。如果省略此子句，整个结果集将被视为一个分区。
• ORDER BY子句是必需的，用于定义每个分区中行的逻辑顺序

---

主要用途：

1. 对结果集进行编号：为查询结果中的每一行分配一个唯一的序号，便于后续操作如分页查询。
2. 分组和排序：结合PARTITION BY和ORDER BY子句，可以对结果集进行分组并在每个组内进行排序，为每组内的行分配唯一的序号。

---

最终SQL

-- 使用JSON函数拆分字符串并转换为多行，并确保唯一性和最新日期
WITH split_data AS (
    SELECT 
        id,
        docCreationDate,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY docCreationDate DESC) AS rn
    FROM (
        SELECT 
            TRIM(BOTH '"' FROM JSON_UNQUOTE(JSON_EXTRACT(json_each.value, '$'))) AS id,
            docCreationDate
        FROM 
            your_schema.your_table,
            JSON_TABLE(CONCAT('["', REPLACE(order_no, ',', '","'), '"]'),
            '$[*]' COLUMNS (value JSON PATH '$')) json_each
    ) AS extracted_data
)
-- 选择每个ID最新的记录
SELECT id, DATE(docCreationDate)
FROM split_data
WHERE rn = 1
ORDER BY docCreationDate DESC, id;

操作步骤释义

• 子查询 extracted_data:

  • 使用 JSON_TABLE 函数将 order_no 字符串转换为JSON数组，并提取每个元素作为单独的行。
  • 使用 JSON_UNQUOTE 和 TRIM 函数处理JSON提取的值，得到订单号和对应的 docCreationDate。
  • 仅选择 docCreationDate 为最新的数据。

• CTE split_data:

  • 对于每个订单，使用 ROW_NUMBER() 窗口函数按照 docCreationDate 倒序生成唯一的行号。

• 最终选择:

  • 选择行号为1的记录，这些记录是每个订单最新的记录。
  • 按 docCreationDate 倒序和订单号排序

---

综上，你就可以确保每个订单唯一，并且只选择最新的 docCreationDate 对应的记录。运行上述SQL后，将得到你期望的去重后的结果。