postgresql - 在聚合函数之前对分组行进行排序

Question

我有一个带有点几何图形的 postgis 表。

积分表:

id | uid | date | geom

具有相同uid 的点是相同的目标。我正在尝试使用 ST_MakeLine 来GROUP BY uid创建一个完整的目标 LineString。

SELECT uid, ST_MakeLine(geom)
FROM points
GROUP BY uid

这可行，但我想确保点的顺序正确。我尝试通过在分组之前添加一个 ORDER BY date 来做到这一点。

SELECT uid, ST_MakeLine(geom)
FROM points
ORDER BY date <-- does not work
GROUP BY uid

ERROR: syntax error at or near "GROUP"

有没有办法在将分组的行输入聚合函数之前对其进行排序？

Answer 1

ORDER BY 子句可以放在聚合参数的末尾。

SELECT uid, ST_MakeLine(geom ORDER BY date)
FROM points
GROUP BY uid

http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-AGGREGATES