sql - 根据列 id(顺序)将所选数据合并到同一行

Question

我有一组用于描述位置人流的表格。主要的“travel_flows”表的结构如下面第一个表所示。流的顺序由“order_id”列描述。

+-----------+------------+---------+-------------+----------+| travel_id | purpose_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”值以二进制 origin(from)/destination(to) 格式按“name_id”对返回的行进行分组，类似于以下内容表:

+-----------------+--------------+------------------+---------------+------------+----------------+| from_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 |+-----------------+--------------+------------------+---------------+------------+----------------+

有什么方法可以通过 select 语句来实现吗？

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