根据列ID(顺序)将选定的数据组合到同一行中

Question

i具有用于描述人的位置流的表组合。主要的" Travel_flows"表具有下表中的第一个表中所示的结构。流的顺序由" order_id"列描述。

------------ --------------- --------------- ----------------------------------------------------------------------------------------- |travel_id |PLISE_ID |name_id |location_id |order_id | ------------ --------------- --------------- ----------------------------------------------------------------------------------------- |434 |23 |55 |85 |1 ||212 |43 |55 |45 |2 ||411 |41 |55 |17 |3 ||148 |23 |32 |32 |1 ||153 |11 |32 |19 |2 | ------------ --------------- --------------- ----------------------------------------------------------------------------------------- 

我尝试使用PostgreSQL 9.6实现的目标是将基于'location_id'和'order_id'值的二进制来源(来自(/destinate(to(格式分组，类似于以下内容表：

------------------- ------------------ ------------------------------------------ -  ----------------- -------------- ---------------------- |来自_purpose_id |from_name_id |from_location_id |to_purpose_id |to_name_id |to_location_id | ------------------- ------------------ ------------------------------------------ -  ----------------- -------------- ---------------------- |23 |55 |85 |43 |55 |45 ||43 |55 |45 |41 |55 |17 ||23 |32 |32 |11 |32 |19 | ------------------- ------------------ ------------------------------------------ -  ----------------- -------------- ---------------------- 

有什么方法可以通过选择语句？

Answer 1

您可以使用lead窗口函数进行此操作。

select * from (
select purpose_id,name_id,location_id,
lead(purpose_id) over(partition by name_id order by order_id) as to_purpose_id,
lead(name_id) over(partition by name_id order by order_id) as to_name_id,
lead(location_id) over(partition by name_id order by order_id) as to_location_id
from tbl
) t
where to_purpose_id is not null and to_name_id is not null and to_location_id is not null

Answer 2

您可以通过窗口函数轻松实现此目标(请参阅https://www.postgresql.org/docs/current/current/static/static/tutorial-window.html(，具体，功能lead()：

with flow as (
  select
    name_id,
    purpose_id as from_purpose_id,
    lead(purpose_id) over w1 as next_purpose_id,
    location_id as from_location_id,
    lead(location_id) over w1 as next_location_id,
    order_id
  from travel_flows
  window w1 as (partition by name_id order by order_id)
)
select
  name_id, from_purpose_id, next_purpose_id, from_location_id, next_location_id
from flow
where next_purpose_id is not null
order by name_id, order_id
;