本文介绍了在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;

这篇关于在PostgreSQL中对组内的行进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-18 17:42
查看更多