SQL动态聚合与多字段拼接技巧

业务场景与问题描述

在数据分析和报表生成中，我们经常会遇到需要将明细行数据转换成汇总列展示的情况，这通常被称为“行转列”或“数据透视”。更进一步地，如果转换后的列需要聚合多个字段的信息并进行拼接，例如将订单中的不同产品及其数量和规格聚合到同一行中的不同列，问题会变得更加复杂。

考虑以下订单明细表 table1：

我们的目标是将其转换为如下格式，按 order_id 和 batch_id 分组，并将不同 product_id 的信息（数量 x 规格）聚合到各自的列中：

这里的挑战在于：

1. 需要将 product_id 的值转换为列名（例如 product1, product5）。
2. 每个产品列需要聚合该产品对应的 COUNT(*) 和 size 信息，并以 [数量] x [规格] 的形式拼接。
3. 最关键的是，product_id 的种类可能是动态变化的，如果每次都手动编写 CASE 语句，将难以维护。

静态解决方案及其局限性

对于固定数量的 product_id，我们可以使用 GROUP_CONCAT 结合 CASE 语句来实现：

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

代码解析：

• GROUP_CONCAT(...) 函数用于将组内的字符串值连接起来。
• CASE WHEN product_id = N THEN ... END 语句根据 product_id 的值选择性地生成要拼接的字符串。
• CONCAT(1, ' x ', size) 用于将数量（这里假设为1，因为原始数据中 type_id 始终为1，且示例输出为 1 x small，实际上是该规格的计数）和 size 拼接成 [数量] x [规格] 的格式。

局限性： 这种方法的缺点是显而易见的：如果 product_id 的种类非常多或者会动态变化，查询语句将变得非常庞大且难以维护。每次新增或删除产品种类，都需要手动修改 SQL 查询。

动态 SQL 解决方案（推荐）

为了克服静态解决方案的局限性，我们可以利用 MySQL 的预处理语句（Prepared Statements）来构建动态 SQL。这种方法允许我们根据数据库中的实际数据动态生成查询的列，从而实现高度的灵活性和可维护性。

以下是实现动态聚合和多字段拼接的完整步骤和代码：

-- 步骤1：获取需要动态生成的列名和聚合表达式
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) t1
);

-- 步骤2：构建完整的动态查询语句
SET @query := CONCAT(
    'SELECT order_id, batch_id, ',
    @columns,
    ' FROM (SELECT product_id, order_id, batch_id, size, COUNT(*) cnt FROM table1 GROUP BY product_id, order_id, batch_id, size) t1 GROUP BY order_id, batch_id'
);

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

-- 步骤4：释放预处理语句
DEALLOCATE PREPARE stmt;

代码解析与步骤详解：

1. *预聚合计数 (`COUNT() cnt)：** 在构建动态列之前，我们需要先对原始数据进行一次预处理，计算每个(product_id, order_id, batch_id, size)组合出现的次数。这是因为最终输出格式是[数量] x [规格]，而这个“数量”是特定规格产品的计数，而不是原始type_id` 列的值。

   SELECT product_id, order_id, batch_id, size, COUNT(*) cnt
   FROM table1
   GROUP BY product_id, order_id, batch_id, size

   这个子查询的结果将作为外部查询的基础数据源 t1。例如，对于 product_id=5, size='small'，cnt 将为1；对于 product_id=5, size='medium'，cnt 也将为1。

2. 动态生成列表达式 (SET @columns := ...)： 这一步是动态 SQL 的核心。我们首先从 table1 中获取所有不重复的 product_id。然后，利用 GROUP_CONCAT 将这些 product_id 转换为一系列 GROUP_CONCAT(CASE WHEN ... END) AS productN 形式的字符串。

   • SELECT DISTINCT product_id FROM table1：获取所有不同的产品ID。
   • CONCAT(...)：针对每个 product_id，生成一个类似于 GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1 的字符串片段。这里的 cnt 来自于前面预聚合的计数。
   • GROUP_CONCAT(...)：将所有这些片段用逗号连接起来，形成最终的动态列列表字符串，存储在用户变量 @columns 中。例如，如果 product_id 有 1 和 5，@columns 的值将是 "GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1,GROUP_CONCAT(CASE WHEN product_id=5 THEN CONCAT(cnt,' x ', size) END) AS product5"。

3. 构建完整查询语句 (SET @query := CONCAT(...))： 将静态的 SELECT order_id, batch_id, 部分、动态生成的 @columns 变量以及预聚合的子查询 FROM (...) t1 GROUP BY order_id, batch_id 拼接起来，形成一个完整的 SQL 查询字符串，存储在用户变量 @query 中。

4. 准备并执行 (PREPARE stmt FROM @query; EXECUTE stmt;)：

   • PREPARE stmt FROM @query;：MySQL 准备一个名为 stmt 的预处理语句，其内容是 @query 变量中存储的动态 SQL 字符串。
   • EXECUTE stmt;：执行这个预处理语句，完成数据查询。

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

注意事项与总结

• MySQL 特性： 这种动态 SQL 的方法主要依赖于 MySQL 的用户变量和预处理语句功能。在其他数据库（如 PostgreSQL、SQL Server、Oracle）中，实现动态 SQL 的语法和方式会有所不同，例如可能使用存储过程、PL/SQL、T-SQL 或其他特定的动态 SQL 构造。
• 性能考量： 动态 SQL 虽然强大，但在极端情况下（例如 product_id 种类非常多，导致生成的 SQL 字符串过长）可能会有性能开销或达到字符串长度限制。GROUP_CONCAT 也有默认的长度限制（group_concat_max_len 系统变量），可能需要调整。
• 安全性： 在实际应用中，如果动态 SQL 的构建涉及用户输入，务必进行严格的输入验证和过滤，以防止 SQL 注入攻击。本例中 product_id 是从数据库中获取的，相对安全。
• 可读性与调试： 动态 SQL 可能会降低查询的可读性和调试难度。在开发阶段，可以通过打印 @query 变量来查看实际执行的 SQL 语句。

通过上述动态 SQL 的方法，我们能够灵活地将行数据透视并聚合为多列，有效解决了产品种类动态变化带来的维护难题，是处理复杂数据转换需求时一个非常实用的技巧。