目前有将自己自学的sql知识传输给他人工作计划，虽然总感觉sql是一门相对简单的语言，但是在日常知识共享过程中依然发现他有值得记录下来的点。因此，想开启一个系列的postgresql编程过程中小知识点的分享文章，以期记录我的sql学习历程。注意咯，这将是简单的知识点分享，可能无法全面的适用所有人，所有实现都是尽可能的使用select语句完成。
  此次分享将集中于写一个存储过程，这个存储过程对于运作效率不具有强硬限制，无复杂计算公式，只是完成简单的数据格式转换。

工作要求

1.原始数据
  轨迹点数据，重要信息包括车辆编号、点位记录时间、经度、纬度，特点是位置的移动是具有一定的时间序列性。
2.任务
  将每一辆车的轨迹点数据按照时间形成一条轨迹点序列，每200个轨迹点组成为一条线，本质为多行变一行的操作

---

数据形式

1.轨迹点存储结构

create table location
(
    platenumber varchar(50),
    lon         double precision,
    lat         double precision,
    time        timestamp(6) with time zone
);

alter table location
    owner to postgres;

create index location_time
    on location (time);

2.轨迹点数据样例

轨迹点数据样例.png
3.输出结果
输出结果样例.png

---

任务分解

  所有尝试均以一天的数据为例。

1.最简单有效的办法

  先利用row_number()提取每个轨迹点在该车辆的行驶轨迹中的位置序列（简单理解为依据时间递增排序的行号），之后利用array_agg(a order by b)（postgresql 9.0及之后才可用）方法，将轨迹点进行合并。

with f1 as(--依据车牌号分组，按时间递增排序并提取每个轨迹点在分组中的行号
    select platenumber, lon, lat,  extract(epoch from time) as timestamp,
       row_number() over (partition by platenumber order by time asc) rid
    from location where time >= '2018-02-21 00:00:00'::timestamp and time < '2018-02-22 00:00:00'::timestamp
),
     f2 as(--经纬度合并为点，并将数据按200分组
         select platenumber, lon||','||lat point, timestamp, round(rid/200) groupbz from f1
     )
select platenumber, array_to_string(array_agg(point ORDER BY timestamp), ';') polyline,
       array_to_string(array_agg(timestamp ORDER BY timestamp), ';') time_seq
from f2 group by platenumber, groupbz;

测试结果.png

2.多此一举的方法

  为了学习返回table形式的存储过程的编写，做了一下多此一举的尝试。

2.1存储过程输入/输出

  确定输入为车辆编号、时间（天）。
  确定输出为该车辆当天的轨迹序列表。
  存储过程框架

create or replace function public.sf_get_polyline_time_seq(in_platnumber text, in_time text) --in_time '2018-02-21'
returns table(
    id text,
    polyline text,
    timestamps text,
    start_time text
             ) as
    $BODY$
    DECLARE
       
    begin
        return ;
    end;
    $BODY$
language 'plpgsql';

2.2数据处理过程

  1、数据需要按时间排序
  2、经纬度、时间需要按照时间顺序重新组合成为新的字段
  3、每200个点组成一条新的记录，末尾不足200的点位同样组成一条新纪录
注意：

rowtype在使用过程中要求传入的字段结构排列顺序与所引用的表结构完全一致，否则会出现值传入错误。参考下面代码中v_list的数据传入方式
table的结果返回通过return next实现，所以需要在存储过程中每返回一条记录的时候就需要return next一次，不可以完全依赖最后的return

create or replace function public.sf_get_polyline_time_seq(in_platnumber text, in_time text) --in_time '2018-02-21'
returns table(
    id text,
    polyline text,
    timestamps text,
    start_time text
             ) as
    $BODY$
    DECLARE
        v_list public.location%ROWTYPE;  --按行读取表中记录
        v_stat_day timestamp := to_timestamp(in_time, 'yyyy-mm-dd hh24:mi:ss');
        v_end_day timestamp := to_timestamp(in_time, 'yyyy-mm-dd hh24:mi:ss') + '1 day';
        v_point text := '';  --输出点位组合结果
        v_time text := '';   --输出时间组合结果
        v_bz int := 0;  --用于判定200个点分组
        v_count int := 0;  --记录当天车辆总点数
    begin
        --获取每天记录的总数目，用于最后判定不足200时同样记录
        select count(*) into v_count from public.location
        where time >= v_stat_day and time < v_end_day and
              platenumber = in_platnumber and is_valid = 1;
        --利用循环方式按顺序重新组合点、时间
        for v_list in(select * from public.location
        where time >= v_stat_day and time < v_end_day and
              platenumber = in_platnumber and is_valid = 1 order by time)
        loop
            --把所有的点合并，所有的时间合并
            v_bz = v_bz + 1;
            v_point = v_point || v_list.lon || ',' || v_list.lat || ';';
            v_time = v_time || extract(epoch from v_list.time) || ';';
            --按200分组
            if v_bz % 200 = 0 or v_bz = v_count then
                id = v_list.platenumber;
                --去除最后多余的';'
                polyline = substring(v_point from 1 for length(v_point)-1);
                timestamps = substring(v_time from 1 for length(v_time)-1);
                --获取每条记录的点位开始时间
                start_time = substring( v_time from 1 for position(';' in v_time)-1);
                --返回结果
                return next;
                --初始化
                v_point = '';
                v_time = '';
            end if;
        end loop;
        return ;
    end;
    $BODY$
language 'plpgsql';

2.3调用存储过程

select id platenumber, timestamps time_seq, polyline, start_time 
from public.sf_get_polyline_time_seq('******', '2018-02-21');

处理结果.png

---

  结束啦，此文仅算一篇学习中教程，在sql编写之路中探索寻中··· ···