PostgreSQL求中值问题

最新推荐文章于 2024-03-25 19:05:05 发布

__风__

最新推荐文章于 2024-03-25 19:05:05 发布

阅读量673

点赞数 1

CC 4.0 BY-SA版权

分类专栏： PostgreSQL

本文链接：https://blog.youkuaiyun.com/liufeng1980423/article/details/96426991

PostgreSQL 专栏收录该内容

19 篇文章

订阅专栏

本文介绍如何使用PostgreSQL从设备轨迹数据中抽取起始点和中间点。通过具体实例展示了利用百分位连续函数(percentile_disc)选取特定时间点的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

偶碰同事的一个需求，如下描述：

数据库中怎么从一个设备轨迹中抽取三个点，起始点和中间点（根据时间字段）怎么取？每个设备的轨迹点个数不同。

可以使用PostgreSQL的有续集进行处理，测试过程如下所示：

录入测试数据：

sde=# create table test(deviceid int,createtime timestamp, position st_geometry);
CREATE TABLE
sde=# insert into test values (1,now(),st_point(1,1,4326));
INSERT 0 1
sde=# insert into test values (1,now(),st_point(2,2,4326));
INSERT 0 1
sde=# insert into test values (1,now(),st_point(3,3,4326));
INSERT 0 1
sde=# insert into test values (1,now(),st_point(3,3.5,4326));
INSERT 0 1
sde=# insert into test values (1,now(),st_point(4,3.5,4326));
INSERT 0 1
sde=# insert into test values (2,now(),st_point(5,5.5,4326));
INSERT 0 1
sde=# insert into test values (2,now(),st_point(6,6.5,4326));
INSERT 0 1
sde=# insert into test values (2,now(),st_point(5,5.5,4326));
INSERT 0 1
sde=# insert into test values (2,now(),st_point(8,5.5,4326));
INSERT 0 1
sde=# select deviceid,createtime,st_astext(position) from test;
 deviceid |         createtime         |           st_astext
----------+----------------------------+--------------------------------
        1 | 2019-07-18 10:17:24.183081 | POINT ( 1.00000000 1.00000000)
        1 | 2019-07-18 10:17:24.464877 | POINT ( 2.00000000 2.00000000)
        1 | 2019-07-18 10:17:24.722101 | POINT ( 3.00000000 3.00000000)
        1 | 2019-07-18 10:17:24.932533 | POINT ( 3.00000000 3.50000000)
        1 | 2019-07-18 10:17:25.12458  | POINT ( 4.00000000 3.50000000)
        2 | 2019-07-18 10:17:25.32509  | POINT ( 5.00000000 5.50000000)
        2 | 2019-07-18 10:17:25.516098 | POINT ( 6.00000000 6.50000000)
        2 | 2019-07-18 10:17:25.718722 | POINT ( 5.00000000 5.50000000)
        2 | 2019-07-18 10:17:26.183977 | POINT ( 8.00000000 5.50000000)
(9 行记录)

处理SQL：

with t1 as  (select deviceid,percentile_disc(0) within group (order by createtime) as firsttime,percentile_disc(0.5) within group (order by createtime) as middletime from test group by 1)

select t.deviceid,t.createtime,st_astext(t.position) from test t ,t1 where (t.deviceid=t1.deviceid) and (t.createtime=t1.firsttime or t.createtime=t1.middletime)

 deviceid |         createtime         |           st_astext
----------+----------------------------+--------------------------------
        1 | 2019-07-18 10:17:24.183081 | POINT ( 1.00000000 1.00000000)
        1 | 2019-07-18 10:17:24.722101 | POINT ( 3.00000000 3.00000000)
        2 | 2019-07-18 10:17:25.32509  | POINT ( 5.00000000 5.50000000)
        2 | 2019-07-18 10:17:25.516098 | POINT ( 6.00000000 6.50000000)
(4 行记录)