时间戳

首页 > 解决方案 > 指定 CollectionAggregate 使用的顺序

sql-server - 指定 CollectionAggregate 使用的顺序

问题描述

我GEOMETRYCOLLECTION通过连接 wkt 并STUFF与ORDER BY

（我使用的是 SQL Server 2016，否则我会使用STRING_AGG）

DECLARE @OrderedGeometryList TABLE  
(  
    [GroupId] INT,
    [Geometry] GEOMETRY,
    [Order] INT
)  


INSERT @OrderedGeometryList
VALUES 
     (1, 'LINESTRING(3 3, 4 4)', 3)
    ,(1, 'LINESTRING(0 0, 1 1)', 1)
    ,(1, 'LINESTRING(1 1, 2 2)', 2)
    ,(2, 'POINT(35453 141)', 2)
    ,(2, 'MULTILINESTRING((0 0, 1 1),(2 2, 3 3))', 1)

然后

;WITH Grouped AS (
    SELECT 
        [GroupId]
        ,Geometry::STGeomCollFromText(
            'GEOMETRYCOLLECTION(' + 
                STUFF((
                    SELECT ',' + [Geometry].ToString()
                    FROM @OrderedGeometryList t2
                    WHERE t1.[GroupId] = t2.[GroupId] 
                    ORDER BY [Order]
                    FOR XML PATH ('')
                ), 1, 1, '')
            + ')', MAX([Geometry].STSrid)
        ) as [Geometry]
    FROM @OrderedGeometryList t1
    GROUP BY [GroupId]
)


SELECT GroupId, Geometry.ToString() as WKT
FROM Grouped

产生：

GroupId WKT
1   GEOMETRYCOLLECTION (LINESTRING (0 0, 1 1), LINESTRING (1 1, 2 2), LINESTRING (3 3, 4 4))
2   GEOMETRYCOLLECTION (MULTILINESTRING ((0 0, 1 1), (2 2, 3 3)), POINT (35453 141))

请注意收集顺序（从左到右）如何遵循表中指定的顺序。这是我追求的效果，但我很好奇是否有更清洁的方法来做到这一点。

我考虑编写自己的 CLR 聚合然后使用OVER()withORDER BY但似乎不支持这是否可以将用户定义的聚合 (clr) 与窗口函数一起使用 (over)？

标签： sql-servertsqlgeometryconcatenationgeospatial

解决方案

您可以使用OFFSETat 子查询对行进行排序。

SELECT Geometry::CollectionAggregate([Geometry]).ToString() 
FROM 
    (SELECT * FROM @OrderedGeometryList ORDER BY [GroupId], [Order] OFFSET 0 ROWS) T
GROUP BY [GroupId]

结果：

GEOMETRYCOLLECTION (LINESTRING (0 0, 1 1), LINESTRING (1 1, 2 2), LINESTRING (3 3, 4 4))
GEOMETRYCOLLECTION (MULTILINESTRING ((0 0, 1 1), (2 2, 3 3)), POINT (35453 141))

推荐阅读