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

本文介绍了在PostgreSQL中对组内的行进行排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下查询：

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中提取所有lon / lat值（由routeid，observation_time，lat和lon列），将它们按routeid分组，并按观察时间在每个组中对其进行排序。但是，上述SQL无效，因为observation_time出现在ORDER BY子句中，而不出现在GROUP BY中。当我将observation_time添加到GROUP BY时，我没有得到正确的结果。

The goal of this query is to pull all lon/lat values from the route_table (which consists of a routeid, observation_time, lat, and lon columns), group them by routeid, and have them sorted within each group by the observation time. However the SQL above is not valid since observation_time appears in the ORDER BY clause by not in the GROUP BY. When I add observation_time to GROUP BY I don't get the correct result.

假设数据集如下：

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中的组内实现行的顺序？

So the question is: How do I achieve this order of the rows within groups within PostgreSQL?

推荐答案

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

Thanks to MarcB's comment I realized the issue was with sorting within the string_agg function so the solution is:

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;