在PostgreSQL中对组内的行进行排序

Question

我有以下查询：

SELECT
  routeid, 
  'SRID=4326;LINESTRING(' || string_agg(lon || ' ' || lat, ',') || ')' AS the_geom
FROM route_table 
WHERE observation_time BETWEEN '2012-09-12 10:00:00' AND '2012-09-12 10:15:00'
GROUP BY routeid HAVING COUNT(lon) > 1 ORDER BY observation_time ASC;

该查询的目标是从route_table（由routeid，observation_time，lat和lon列组成）中提取所有lon /
lat值，按routeid对其进行分组，并在每个组中按观察时间对它们进行排序。但是，上面的SQL无效，因为observation_time出现在ORDER
BY子句中，而不出现在GROUP BY中。当我将observation_time添加到GROUP BY时，我没有得到正确的结果。

假设这样的数据集：

routeid | observation_time      | lat | lon
---------------------------------------------
    1   | '2012-09-12 01:00:00' | 30  | -75
    1   | '2012-09-12 01:05:00' | 31  | -76
    1   | '2012-09-12 01:10:00' | 31  | -76.5
    2   | '2012-09-12 01:03:00' | 39  | -22
    2   | '2012-09-12 01:00:00' | 40  | -22
    2   | '2012-09-12 01:06:00' | 41  | -22

输出应如下所示：

routeid | the_geom
--------------------------------------------------------
    1   | 'SRID=4326;LINESTRING('-75 30,-76 31,-76.5 31)
    2   | 'SRID=4326;LINESTRING('-22 40,-22 39,-22 41)

所以问题是：如何在PostgreSQL的组中实现行的这种顺序？

Answer 1

感谢MarcB的评论，我意识到问题在于在string_agg函数中进行排序，因此解决方案是：

SELECT 
  routeid, 
  'SRID=4326;LINESTRING(' || string_agg(lon || ' ' || lat, ',' ORDER BY time ASC) || ')' AS the_geom
FROM route_table 
WHERE observation_time BETWEEN '2012-09-12 10:00:00' AND '2012-09-12 10:15:00'
GROUP BY routeid HAVING COUNT(lon) > 1;