问题描述
我有以下查询:
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;
这篇关于在PostgreSQL中对组内的行进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!