Oracle SQL按分区使用LEAD()函数的循环查询问题

Hey there, let's break down what you've got so far and tweak things to make it more reliable:

原始数据集 #

Here's your sample data formatted for clarity:

你执行的SQL语句 #

SELECT ID, date_IN, Date_out, lead(date_out) over ( partition by (ID) order by ID) as next_out From table

当前查询结果 #

一个关键优化点 #

Notice that your ORDER BY ID inside the window function doesn't actually do anything useful—since you're already partitioning by ID, all rows in the partition have the same ID. To ensure the lead() function picks the next chronological record for each ID, you should sort by the check-in or check-out date instead. For example:

SELECT ID, date_IN, Date_out, 
       lead(date_out) over (partition by ID order by date_IN) as next_out 
From table

This guarantees that next_out always corresponds to the subsequent stay's check-out date, regardless of how the data is stored in the table.

You mentioned there are more edge cases in your real data—feel free to share details like overlapping dates, gaps you want to calculate, or specific business logic you're aiming for, and we can refine this further.

内容的提问来源于stack exchange，提问作者a.s.1