SQL数据转换：利用GROUP_CONCAT和动态SQL实现行转列及多字段聚合

本教程详细介绍了如何在SQL中将“长格式”数据转换为“宽格式”，并聚合多个字段。文章首先展示了使用GROUP_CONCAT和CASE WHEN的静态方法，并指出其局限性。随后，重点阐述了如何利用MySQL的预处理语句（Prepared Statement）动态生成查询，从而实现高度可维护和可扩展的行转列与多字段聚合，适用于产品ID数量不确定的场景。

在数据库操作中，我们经常会遇到需要将数据的行转换为列（即“行转列”或“数据透视”），并对转换后的数据进行聚合的需求。特别是在处理具有多类别属性的数据时，例如将不同产品id的详细信息聚合到同一行中，这会变得尤为重要。本文将深入探讨如何使用mysql中的group_concat函数和动态sql来实现这种复杂的数据转换和聚合。

1. 问题描述

假设我们有一个名为table1的订单详情表，其结构和数据示例如下：

id	order_id	batch_id	bucket_id	menu_id	product_id	type_id	size
1	1	1	1	1	1	1	small
2	1	1	1	1	5	1	small
3	1	1	1	1	5	1	medium

我们的目标是将其转换为以下“宽格式”，其中product_id的值（例如1和5）变为列名，并且每个产品下的type_id、size以及它们的计数被聚合为一个字符串：

order_id	batch_id	product1	product5
1	1	1 x small	1 x small, 1 medium

2. 静态行转列与聚合方案

对于已知且数量有限的product_id，我们可以使用GROUP_CONCAT结合CASE WHEN语句来实现。

示例代码：

SELECT
    order_id,
    batch_id,
    GROUP_CONCAT(CASE WHEN product_id = 1 THEN CONCAT(type_id, ' x ', size) END) AS product1,
    GROUP_CONCAT(CASE WHEN product_id = 5 THEN CONCAT(type_id, ' x ', size) END) AS product5
FROM
    table1
GROUP BY
    order_id,
    batch_id;

解释：

• GROUP_CONCAT(expression)：此函数将组内expression的所有非NULL值连接成一个字符串。默认分隔符是逗号。
• CASE WHEN product_id = N THEN CONCAT(type_id, ' x ', size) END：对于每个product_id，我们使用CASE WHEN来有条件地生成一个字符串（例如'1 x small'）。如果product_id不匹配，则CASE语句返回NULL，GROUP_CONCAT会忽略这些NULL值。
• GROUP BY order_id, batch_id：确保我们按订单和批次对数据进行分组，以便聚合。

局限性： 这种方法的主要问题在于其非动态性。如果product_id的数量非常多，或者product_id的值会动态变化，那么手动编写和维护这样的查询将变得极其困难且不切实际。例如，如果有成百上千个产品，查询语句会变得非常庞大且难以管理。

3. 动态行转列与聚合方案（MySQL预处理语句）

为了解决静态方案的局限性，我们可以利用MySQL的预处理语句（Prepared Statement）来动态构建查询。这允许我们根据实际数据自动生成列名和聚合逻辑。

核心思想：

1. 首先，查询出所有不重复的product_id。
2. 然后，根据这些product_id动态构建GROUP_CONCAT和CASE WHEN的列表达式字符串。
3. 最后，将这个动态生成的列表达式插入到主查询中，并通过预处理语句执行。

示例代码：

-- 步骤1：预先计算每个产品、订单、批次和尺寸组合的计数
-- 这是为了正确处理类似 "1 x small" 和 "1 medium" 中的数量前缀
-- 因为原始数据中，product_id=5 有两行，但size不同，需要分别计数
SET @pre_aggregated_data := '
    (SELECT
        product_id,
        order_id,
        batch_id,
        size,
        COUNT(*) AS cnt
    FROM
        table1
    GROUP BY
        product_id,
        order_id,
        batch_id,
        size) AS t_pre_agg';

-- 步骤2：动态生成列名和对应的GROUP_CONCAT表达式
-- 例如，如果 product_id 有 1 和 5，则会生成 "GROUP_CONCAT(...) AS product1, GROUP_CONCAT(...) AS product5"
SET @columns := (
    SELECT
        GROUP_CONCAT(
            CONCAT("GROUP_CONCAT(CASE WHEN product_id=", product_id, " THEN CONCAT(cnt,' x ', size) END) AS product", product_id)
        )
    FROM
        (SELECT DISTINCT product_id FROM table1) AS t_distinct_products
);

-- 步骤3：拼接完整的动态SQL查询语句
SET @query := CONCAT('SELECT order_id, batch_id, ', @columns, ' FROM ', @pre_aggregated_data, ' GROUP BY order_id, batch_id');

-- 步骤4：准备并执行动态查询
PREPARE stmt FROM @query;
EXECUTE stmt;

-- 步骤5：释放预处理语句资源
DEALLOCATE PREPARE stmt;

详细解释：

1. SET @pre_aggregated_data := '...' (内部预聚合子查询)： 为了正确处理聚合计数（例如1 x small和1 medium），我们首先需要一个子查询来计算每个product_id、order_id、batch_id和size组合的出现次数。这个子查询的结果集被命名为t_pre_agg，它包含了我们最终聚合所需的所有基础数据和计数。

   SELECT
       product_id,
       order_id,
       batch_id,
       size,
       COUNT(*) AS cnt -- 这里的cnt就是我们需要的数量前缀
   FROM
       table1
   GROUP BY
       product_id,
       order_id,
       batch_id,
       size

2. SET @columns := (...) (动态生成列表达式)：

   • SELECT DISTINCT product_id FROM table1：这个内部子查询获取table1中所有不重复的product_id。
   • GROUP_CONCAT(CONCAT(...))：对于每个唯一的product_id，它构造一个GROUP_CONCAT表达式字符串。例如，当product_id为1时，它会生成"GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1"。所有这些生成的字符串会通过逗号连接起来，形成一个完整的列列表，如"..., product1, ..., product5, ..."。
   • CONCAT(cnt,' x ', size)：这里的cnt就是从步骤1的预聚合子查询中得到的计数。

3. SET @query := CONCAT(...) (拼接完整查询)： 这一步将固定的SELECT order_id, batch_id,部分与动态生成的列表达式@columns，以及预聚合子查询@pre_aggregated_data拼接起来，形成一个完整的SQL查询字符串。

4. PREPARE stmt FROM @query; EXECUTE stmt; (执行动态查询)：

   • PREPARE stmt FROM @query;：MySQL会解析并编译存储在@query变量中的SQL字符串，创建一个名为stmt的预处理语句。
   • EXECUTE stmt;：执行这个预处理语句，返回查询结果。

5. DEALLOCATE PREPARE stmt; (释放资源)： 执行完毕后，释放stmt预处理语句所占用的系统资源。这是一个良好的编程习惯。

4. 注意事项与总结

• MySQL特有性： 本文中的GROUP_CONCAT函数和预处理语句的语法是MySQL特有的。在其他数据库系统（如PostgreSQL、SQL Server、Oracle）中，实现行转列可能需要使用不同的函数（如STRING_AGG, LISTAGG）或技术（如PIVOT操作符，XML/JSON路径）。
• GROUP_CONCAT的长度限制： GROUP_CONCAT函数有一个默认的最大长度限制（group_concat_max_len系统变量，默认为1024字节）。如果聚合的字符串非常长，可能会被截断。可以通过SET GLOBAL group_concat_max_len = N;来增加此限制。
• 性能考量： 动态SQL虽然提供了灵活性，但在非常大的数据集上，它的性能可能不如静态查询或数据库内置的PIVOT功能（如果可用）。动态生成和编译查询会带来一定的开销。
• 数据类型与NULL值： GROUP_CONCAT会忽略NULL值。如果CONCAT函数中的任何参数为NULL，则整个CONCAT结果为NULL。在实际应用中，可能需要使用COALESCE或其他函数处理潜在的NULL值。
• 安全性： 虽然这里我们是从数据库内部查询数据来构建动态SQL，降低了SQL注入的风险，但在其他场景（例如用户输入来构建动态SQL）中，务必注意SQL注入防护。

通过上述动态SQL方案，我们成功地解决了product_id不确定或数量庞大时进行行转列和多字段聚合的挑战。这种方法极大地提高了SQL查询的灵活性和可维护性，是处理复杂数据转换任务的强大工具。

以上就是SQL数据转换：利用GROUP_CONCAT和动态SQL实现行转列及多字段聚合的详细内容，更多请关注php中文网其它相关文章！