MySQL的函数和运算符 - 聚合函数 - 聚合函数介绍（3）

重复键处理。当该函数的结果被规范化时，具有重复键的值将被丢弃。为了与不允许重复键的 MySQL JSON 数据类型规范保持一致，在返回对象中仅使用最后遇到的值和该键(" last duplicate key wins ")。这意味着在 SELECT 的列上使用此函数的结果可能取决于返回行的顺序，但不能保证这一点。

当作为窗口函数使用时，如果在一个结构中有重复的键，则结果中只保留该键的最后一个值。如果 ORDER BY 规范保证值具有特定的顺序，则框架中最后一行中的键的值是确定的。如果不是，则键的结果值是不确定的。

考虑以下情况：

mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    5 |
| key  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4}           |
+----------------------+
1 row in set (0.00 sec)

从上一个查询中选择的键是不确定的。如果希望特定的键排序，可以调用 JSON_OBJECTAGG() 作为窗口函数，包含 OVER 子句和 ORDER BY 规范来将特定的顺序施加到结果上。下面的示例展示了使用 ORDER BY 和不使用 ORDER BY 会发生什么情况。

如果没有 ORDER BY，结构就是整个分区：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4}  |
| {"key": 4}  |
| {"key": 4}  |
+-------------+

使用 ORDER BY，其结构是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 的默认值（升序和降序）：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 4}  |
| {"key": 3}  |
+-------------+

使用 ORDER BY 和整个分区的显式结构：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 5}  |
| {"key": 5}  |
+-------------+

要返回特定的键值(比如最小的或最大的)，请在查询中包含 LIMIT 子句。例如：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
+-------------+

● MAX([DISTINCT] expr) [over_clause]

返回 expr 的最大值。MAX() 可以接受一个字符串参数，在这种情况下，它返回最大的字符串值。DISTINCT 关键字可以用来查找 expr 不同值的最大值，但是，这与省略 DISTINCT 产生相同的结果。

如果没有匹配的行，MAX() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

 mysql> SELECT student_name, MIN(test_score), MAX(test_score)

       FROM student
       GROUP BY student_name;

对于 MAX()， MySQL 目前比较 ENUM 和 SET 列的字符串值，而不是字符串在集合中的相对位置。这与 ORDER BY 比较它们的方式不同。

● MIN([DISTINCT] expr) [over_clause]

返回 expr 的最小值。MIN() 可以接受一个字符串参数，在这种情况下，它返回最小的字符串值。DISTINCT 关键字可用于查找 expr 不同值的最小值，但是，这与省略 DISTINCT 产生相同的结果。

如果没有匹配的行，MIN() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

对于 MIN()，MySQL 当前通过字符串值而不是字符串在集合中的相对位置来比较 ENUM 和 SET 列。这与 ORDER BY 比较它们的方式不同。

● STD(expr) [over_clause]

返回 expr 的总体标准差。STD() 是标准 SQL 函数 STDDEV_POP() 的同义词，作为 MySQL 扩展提供。

如果没有匹配的行，则 STD() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● STDDEV(expr) [over_clause]

返回 expr 的总体标准差。STDDEV() 是标准 SQL 函数 STDDEV_POP() 的同义词，为与 Oracle 兼容而提供。

如果没有匹配的行，则 STDDEV() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● STDDEV_POP(expr) [over_clause]

返回 expr 的总体标准差(VAR_POP()的平方根)。还可以使用 STD() 或 STDDEV()，它们是等价的，但不是标准 SQL。

如果没有匹配的行，则 STDDEV_POP() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● STDDEV_SAMP(expr) [over_clause]

返回 expr 的样本标准差(VAR_SAMP() 的平方根)。

如果没有匹配的行，则 STDDEV_SAMP() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● SUM([DISTINCT] expr) [over_clause]

返回 expr 的和。如果返回集没有行，则 SUM() 返回 NULL。DISTINCT 关键字只能用于求和 expr 的不同值。

如果没有匹配的行，SUM() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● VAR_POP(expr) [over_clause]

返回 expr 的总体标准方差。它把行数做为总体，而不是样本，所以它的分母是行数。还可以使用 VARIANCE()，它是等效的，但不是标准的 SQL 函数。

如果没有匹配的行，VAR_POP() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● VAR_SAMP(expr) [over_clause]

返回 expr 的样本方差。也就是说，分母是行数减 1。

如果没有匹配的行，VAR_SAMP() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

● VARIANCE(expr) [over_clause]

返回 expr 的总体标准方差。VARIANCE() 是标准 SQL 函数 VAR_POP() 的同义词，作为 MySQL 扩展提供。

如果没有匹配的行，VARIANCE() 返回 NULL。

如果存在 over_clause 子句，此函数将作为窗口函数执行。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html