MySQL :: MySQL 8.4 参考手册 :: 14.16.12 空间聚合函数

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.16 空间分析函数 / 14.16.12 空间聚合函数

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 95%

14.16.12 空间聚合函数14.16.12 Spatial Aggregate Functions

MySQL 支持对一组值执行计算的聚合函数。有关这些函数的一般信息，请参见第 14.19.1 节，“聚合函数描述”。本节描述了 ST_Collect() 空间聚合函数。MySQL supports aggregate functions that perform a calculation on a set of values. For general information about these functions, see Section 14.19.1, “Aggregate Function Descriptions”. This section describes the ST_Collect() spatial aggregate function.

ST_Collect() 可以作为窗口函数使用，其语法描述中的 [over_clause] 表示可选的 OVER 子句。over_clause 在第 14.20.2 节，“窗口函数概念与语法” 中有描述，该部分还包括关于窗口函数使用的其他信息。ST_Collect() can be used as a window function, as signified in its syntax description by [over_clause], representing an optional OVER clause. over_clause is described in Section 14.20.2, “Window Function Concepts and Syntax”, which also includes other information about window function usage.

ST_Collect([DISTINCT] g) [over_clause]
聚合几何值并返回一个单一的几何集合值。使用 DISTINCT 选项时，返回不同几何参数的聚合结果。Aggregates geometry values and returns a single geometry collection value. With the DISTINCT option, returns the aggregation of the distinct geometry arguments.
与其他聚合函数一样，可以使用 GROUP BY 将参数分组为子集。ST_Collect() 为每个子集返回一个聚合值。As with other aggregate functions, GROUP BY may be used to group arguments into subsets. ST_Collect() returns an aggregate value for each subset.
如果存在over_clause，此函数将作为窗口函数执行。over_clause的描述如第 14.20.2 节，“窗口函数的概念和语法”所述。与大多数支持窗口的聚合函数相比，ST_Collect()允许over_clause与DISTINCT同时使用。This function executes as a window function if over_clause is present. over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. In contrast to most aggregate functions that support windowing, ST_Collect() permits use of over_clause together with DISTINCT.
ST_Collect()处理其参数的方式如下：ST_Collect() handles its arguments as follows:
- NULL参数将被忽略。NULL arguments are ignored.
- 如果所有参数都是NULL或聚合结果为空，则返回值为NULL。If all arguments are NULL or the aggregate result is empty, the return value is NULL.
- 如果任何几何参数不是语法上格式正确的几何体，则会发生ER_GIS_INVALID_DATA 错误。If any geometry argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.
- 如果任何几何参数是在未定义空间参考系统（SRS）中的语法上良构的几何，将会发生ER_SRS_NOT_FOUND错误。If any geometry argument is a syntactically well-formed geometry in an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.
- 如果有多个几何参数且这些参数在相同的 SRS 中，则返回值也在该 SRS 中。如果这些参数不在相同的 SRS 中，则会发生ER_GIS_DIFFERENT_SRIDS_AGGREGATION错误。If there are multiple geometry arguments and those arguments are in the same SRS, the return value is in that SRS. If those arguments are not in the same SRS, an ER_GIS_DIFFERENT_SRIDS_AGGREGATION error occurs.
- 结果是可能的最窄的MultiXxx或GeometryCollection值，结果类型由非NULL几何参数确定，如下所示：The result is the narrowest MultiXxx or GeometryCollection value possible, with the result type determined from the non-NULL geometry arguments as follows:
  - 如果所有参数都是 Point 值，则结果是 MultiPoint 值。If all arguments are Point values, the result is a MultiPoint value.
  - 如果所有参数都是 LineString 值，则结果是 MultiLineString 值。If all arguments are LineString values, the result is a MultiLineString value.
  - 如果所有参数都是 Polygon 值，则结果是 MultiPolygon 值。If all arguments are Polygon values, the result is a MultiPolygon value.
  - 否则，参数是不同类型的几何图形，结果是 GeometryCollection 值。Otherwise, the arguments are a mix of geometry types and the result is a GeometryCollection value.
此示例数据集显示了按年份和制造地点划分的假设产品：This example data set shows hypothetical products by year and location of manufacture:
```
CREATE TABLE product (
  year INTEGER,
  product VARCHAR(256),
  location Geometry
);

INSERT INTO product
(year,  product,     location) VALUES
(2000, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
(2000, "Computer"  , ST_GeomFromText('point(28 -77)',4326)),
(2000, "Abacus"    , ST_GeomFromText('point(28 -77)',4326)),
(2000, "TV"        , ST_GeomFromText('point(38  60)',4326)),
(2001, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
(2001, "Computer"  , ST_GeomFromText('point(28 -77)',4326));
```
使用 ST_Collect() 对数据集进行的一些示例查询：Some sample queries using ST_Collect() on the data set:
```
mysql> SELECT ST_AsText(ST_Collect(location)) AS result
       FROM product;
+------------------------------------------------------------------+
| result                                                           |
+------------------------------------------------------------------+
| MULTIPOINT((60 -24),(28 -77),(28 -77),(38 60),(60 -24),(28 -77)) |
+------------------------------------------------------------------+

mysql> SELECT ST_AsText(ST_Collect(DISTINCT location)) AS result
       FROM product;
+---------------------------------------+
| result                                |
+---------------------------------------+
| MULTIPOINT((60 -24),(28 -77),(38 60)) |
+---------------------------------------+

mysql> SELECT year, ST_AsText(ST_Collect(location)) AS result
       FROM product GROUP BY year;
+------+------------------------------------------------+
| year | result                                         |
+------+------------------------------------------------+
| 2000 | MULTIPOINT((60 -24),(28 -77),(28 -77),(38 60)) |
| 2001 | MULTIPOINT((60 -24),(28 -77))                  |
+------+------------------------------------------------+

mysql> SELECT year, ST_AsText(ST_Collect(DISTINCT location)) AS result
       FROM product GROUP BY year;
+------+---------------------------------------+
| year | result                                |
+------+---------------------------------------+
| 2000 | MULTIPOINT((60 -24),(28 -77),(38 60)) |
| 2001 | MULTIPOINT((60 -24),(28 -77))         |
+------+---------------------------------------+

# selects nothing
mysql> SELECT ST_Collect(location) AS result
       FROM product WHERE year = 1999;
+--------+
| result |
+--------+
| NULL   |
+--------+

mysql> SELECT ST_AsText(ST_Collect(location)
         OVER (ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))
         AS result
       FROM product;
+-------------------------------+
| result                        |
+-------------------------------+
| MULTIPOINT((28 -77))          |
| MULTIPOINT((28 -77),(60 -24)) |
| MULTIPOINT((60 -24),(28 -77)) |
| MULTIPOINT((28 -77),(38 60))  |
| MULTIPOINT((38 60),(60 -24))  |
| MULTIPOINT((60 -24),(28 -77)) |
+-------------------------------+
```

首页上一级下一节