发布于 由admin

MySQL高级讲座篇之：MySQL的`JSON`聚合函数：如何进行高效的数据透视？

各位观众老爷，晚上好！我是你们的老朋友，今天咱们聊点有意思的——MySQL 的 JSON 聚合函数，以及如何利用它们进行高效的数据透视。别害怕 "JSON" 和 "聚合" 这些词儿，保证用最接地气的方式，把这些高大上的概念给你们讲明白！

开场白：数据透视是个啥？

先问大家一个问题，什么是数据透视？ 简单来说，数据透视就是把数据从一个角度转换到另一个角度，方便我们观察和分析。想想Excel里的透视表，是不是很熟悉？ MySQL 里的 JSON 聚合函数，也能实现类似的功能，而且效率更高！

举个例子，假设我们有一个销售数据表，包含以下字段：

  • sale_date: 销售日期
  • product_category: 产品类别
  • sales_amount: 销售额

如果我们想知道每个月各个产品类别的销售额，就需要进行数据透视。 传统的做法可能会比较麻烦，需要用到 GROUP BY 和 CASE WHEN 等语句，代码冗长，效率也相对较低。 但是，有了 JSON 聚合函数，就能轻松搞定！

第一部分：JSON 聚合函数概览

MySQL 5.7.22 引入了两个非常给力的 JSON 聚合函数：

  • JSON_ARRAYAGG(expr): 将结果集中的 expr 表达式的值聚合成一个 JSON 数组。
  • JSON_OBJECTAGG(key, value): 将结果集中的 key 和 value 表达式的值聚合成一个 JSON 对象。

这两个函数就像一对黄金搭档，可以帮助我们轻松构建复杂的 JSON 结构，实现各种数据透视的需求。

第二部分：JSON_ARRAYAGG() 实战演练

JSON_ARRAYAGG() 的作用是将一组数据聚合成一个 JSON 数组。 让我们通过一个具体的例子来演示它的用法。

假设我们有一个 students 表，包含以下字段：

  • student_id: 学生 ID
  • student_name: 学生姓名
  • course_name: 课程名称

表结构如下：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    course_name VARCHAR(255)
);

INSERT INTO students (student_id, student_name, course_name) VALUES
(1, '张三', '语文'),
(1, '张三', '数学'),
(2, '李四', '英语'),
(2, '李四', '数学'),
(3, '王五', '物理');

现在，我们想把每个学生选修的课程，放到一个 JSON 数组里。可以使用以下 SQL 语句：

SELECT
    student_name,
    JSON_ARRAYAGG(course_name) AS courses
FROM
    students
GROUP BY
    student_name;

执行结果如下：

student_name courses
张三 ["语文", "数学"]
李四 ["英语", "数学"]
王五 ["物理"]

看到了吗？ JSON_ARRAYAGG() 函数把每个学生选修的课程，放到一个 JSON 数组里了。是不是很简单？

进阶用法：去重和排序

JSON_ARRAYAGG() 函数还支持去重和排序。

  • 去重: 可以使用 DISTINCT 关键字去重。
  • 排序: 可以使用 ORDER BY 子句排序。

例如，如果我们想把每个学生选修的课程去重并排序，可以使用以下 SQL 语句：

SELECT
    student_name,
    JSON_ARRAYAGG(DISTINCT course_name ORDER BY course_name) AS courses
FROM
    students
GROUP BY
    student_name;

执行结果如下：

student_name courses
张三 ["数学", "语文"]
李四 ["数学", "英语"]
王五 ["物理"]

第三部分：JSON_OBJECTAGG() 大显身手

JSON_OBJECTAGG() 的作用是将一组 key-value 对聚合成一个 JSON 对象。 这个函数在数据透视中非常有用。

让我们回到之前的销售数据表。 假设我们想知道每个月各个产品类别的销售额，可以使用以下 SQL 语句：

CREATE TABLE sales (
    sale_date DATE,
    product_category VARCHAR(255),
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, product_category, sales_amount) VALUES
('2023-01-01', '电子产品', 1000.00),
('2023-01-01', '服装', 500.00),
('2023-02-01', '电子产品', 1200.00),
('2023-02-01', '食品', 300.00),
('2023-03-01', '服装', 600.00),
('2023-03-01', '食品', 400.00);

SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    JSON_OBJECTAGG(product_category, sales_amount) AS sales_data
FROM
    sales
GROUP BY
    sale_month;

执行结果如下：

sale_month sales_data
2023-01 {"电子产品": 1000.00, "服装": 500.00}
2023-02 {"电子产品": 1200.00, "食品": 300.00}
2023-03 {"服装": 600.00, "食品": 400.00}

看到了吗？ JSON_OBJECTAGG() 函数把每个月的销售数据，转换成了一个 JSON 对象，其中 key 是产品类别，value 是销售额。 这样，我们就可以很方便地查看每个月各个产品类别的销售情况了。

更进一步：动态列透视

上面的例子中，产品类别是固定的。 如果产品类别是动态的，怎么办呢？ 也就是说，我们事先不知道有哪些产品类别。 这时，就需要用到动态列透视。

动态列透视的思路是：

  1. 获取所有产品类别。
  2. 构建动态 SQL 语句。
  3. 执行动态 SQL 语句。

下面是一个示例：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN product_category = ''',
      product_category,
      ''' THEN sales_amount ELSE 0 END) AS `',
      product_category, '`'
    )
  )
INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT DATE_FORMAT(sale_date, ''%Y-%m'') AS sale_month, ', @sql, ' FROM sales GROUP BY sale_month');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个 SQL 语句首先动态生成 CASE WHEN 语句，然后将这些语句拼接到最终的 SQL 语句中。 执行结果如下：

sale_month 电子产品 服装 食品
2023-01 1000.00 500.00 0.00
2023-02 1200.00 0.00 300.00
2023-03 0.00 600.00 400.00

第四部分：JSON 聚合函数与其他函数的配合

JSON 聚合函数可以与其他函数配合使用，实现更复杂的数据透视需求。

例如，我们可以使用 JSON_MERGE() 函数，将多个 JSON 对象合并成一个 JSON 对象。

SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    JSON_MERGE(JSON_OBJECT('电子产品', SUM(CASE WHEN product_category = '电子产品' THEN sales_amount ELSE 0 END)),
               JSON_OBJECT('服装', SUM(CASE WHEN product_category = '服装' THEN sales_amount ELSE 0 END)),
               JSON_OBJECT('食品', SUM(CASE WHEN product_category = '食品' THEN sales_amount ELSE 0 END))) AS sales_data
FROM
    sales
GROUP BY
    sale_month;

执行结果如下：

sale_month sales_data
2023-01 {"电子产品": 1000.00, "服装": 500.00, "食品": 0.00}
2023-02 {"电子产品": 1200.00, "服装": 0.00, "食品": 300.00}
2023-03 {"电子产品": 0.00, "服装": 600.00, "食品": 400.00}

第五部分：性能优化小技巧

使用 JSON 聚合函数进行数据透视时，需要注意性能问题。 以下是一些性能优化小技巧：

  1. 索引: 在经常用于分组的字段上创建索引。
  2. 数据类型: 选择合适的数据类型，避免不必要的类型转换。
  3. 数据量: 如果数据量太大，可以考虑分批处理。
  4. MySQL 版本: 尽量使用较新的 MySQL 版本，因为新版本通常会优化 JSON 函数的性能。

总结：JSON 聚合函数，数据透视的利器

总而言之，MySQL 的 JSON 聚合函数是进行数据透视的利器。 它们可以帮助我们轻松构建复杂的 JSON 结构，实现各种数据透视的需求。 掌握了这些函数，你就能像玩魔方一样，轻松玩转数据，让数据为你所用！

表格总结

为了方便大家理解，我把今天讲的内容用表格总结一下：

函数名称 功能 示例
JSON_ARRAYAGG() 将结果集中的值聚合成一个 JSON 数组。 SELECT JSON_ARRAYAGG(course_name) FROM students;
JSON_OBJECTAGG() 将结果集中的 key-value 对聚合成一个 JSON 对象。 SELECT JSON_OBJECTAGG(product_category, sales_amount) FROM sales;

结束语

今天的讲座就到这里。希望大家能够掌握 MySQL 的 JSON 聚合函数，并在实际工作中灵活运用。 记住，技术是死的，人是活的，要根据实际情况选择合适的解决方案。

最后，感谢大家的观看，祝大家工作顺利，早日升职加薪！ 如果有任何问题，欢迎在评论区留言，我会尽力解答。 拜拜！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注