PostgreSQL：按规则为GPS点分配过夜到访场合编号

需求说明 #

需要为每个person_id（人员ID）的所有trip_id中，满足overnight = 'Yes'的每个point_id（GPS点ID）分配对应的occasion（到访场合编号），规则如下：

• 同一trip_id内，若同一place_id（overnight='Yes'）的两个point_id之间存在至少一个属于其他place_id（overnight='Yes'）的点，则这两个点需视为不同场合，即重访算作不同场合。
• 不同trip_id内，同一place_id（overnight='Yes'）的point_id需视为不同场合。

表结构与示例数据 #

CREATE TABLE my_table (
    point_id INT,
    trip_id INT,
    person_id INT,
    place_id INT,
    overnight VARCHAR,
    occasion INT
);

INSERT INTO my_table (point_id, trip_id, person_id, place_id, overnight, occasion) VALUES
(1, 5, 1, 9, 'Yes', 1),
(2, 5, 1, 10, 'Yes', 1),
(3, 5, 1, 10, 'Yes', 1),
(4, 5, 1, 9, NULL, NULL),
(5, 5, 1, 9, 'Yes', 2),
(6, 5, 1, 10, 'Yes', 2),
(7, 5, 1, 9, 'Yes', 3),
(8, 5, 1, 10, 'Yes', 3),
(9, 5, 1, 10, 'No', NULL),
(10, 5, 1, 10, 'No', NULL),
(11, 5, 1, 10, 'Yes', 3),
(12, 95, 2, NULL, NULL, NULL),
(13, 95, 2, 9, 'No', NULL),
(14, 96, 2, 9, 'Yes', 1),
(15, 96, 2, NULL, NULL, NULL),
(16, 96, 2, 9, 'Yes', 1),
(17, 97, 2, NULL, NULL, NULL),
(18, 97, 2, NULL, NULL, NULL),
(19, 97, 2, 9, 'Yes', 2),
(20, 98, 2, 9, 'Yes', 3),
(21, 98, 2, 9, 'No', NULL),
(22, 99, 2, 10, 'No', NULL),
(23, 99, 2, 9, 'Yes', 4),
(24, 99, 2, 10, 'No', NULL),
(25, 99, 2, 10, 'Yes', 1);

当前查询语句 #

当前查询输出的final_visit_rank字段与预期的occasion一致，但不确定该方案在数十万行数据集下的效率与优雅性，寻求优化建议：

with t_revisit as (
select  *, 
        case when place_id is not distinct from lag(place_id) over (
            partition by person_id order by point_id rows between unbounded preceding and current row) and 
                trip_id is not distinct from lag(trip_id) over (
                    partition by person_id order by point_id rows between unbounded preceding and current row)
                        then 1
             else 0
        end as revisit
from my_table
where overnight = 'Yes'
), 

t_visit_rank as (
select  *,
        row_number() over (
            partition by person_id, place_id order by trip_id, point_id 
                rows between unbounded preceding and current row) as visit_rank
from t_revisit
where revisit = 0)

select  t.*, visit_rank, 
        case when t.revisit = 1 
                then lag(visit_rank) over (
                    partition by t.person_id, t.place_id, t.overnight order by t.point_id 
                        rows between unbounded preceding and current row)
            else visit_rank
        end as final_visit_rank
from (select my_table.*, revisit
      from my_table
      left join t_revisit
      on my_table.point_id = t_revisit.point_id) t
left join t_visit_rank
on t.point_id = t_visit_rank.point_id
order by person_id, point_id; 

内容的提问来源于stack exchange，提问作者Beau