Oracle:当一列中的值发生更改时选择行

Question

我有下表：

PLACE       USER_ID Date
---------- ---------- -----------------------------
ABC      4     14/04/20 12:05:29,255000000  
ABC      4     14/04/20 15:42:28,389000000  
ABC      4     14/04/20 18:33:20,202000000  
ABC      4     14/04/20 22:51:28,339000000    
XYZ      4     14/04/20 11:07:23,335000000     
XYZ      2     14/04/20 12:15:12,123000000    
ABC      4     13/04/20 22:09:33,255000000   
QWE      4     13/04/20 10:18:29,144000000 
XYZ      2     14/04/20 10:05:47,255000000   

当我选择的user_id的位置按日期顺序更改时，我需要获取行。因此所需的结果应该是(对于user_id 4(：

PLACE       USER_ID           DATE
---------- ---------- -----------------------------
ABC      4     14/04/20 12:05:29,255000000     
XYZ      4     14/04/20 11:07:23,335000000 
ABC      4     13/04/20 22:09:33,255000000    
QWE      4     13/04/20 10:18:29,144000000 

我尝试了最小日期，但在我的例子中，如果用户回到那个地方，我就会丢失数据：

SELECT MIN(DATE), PLACE FROM user_places WHERE USER_ID=4 GROUP BY PLACE

我得到的结果(缺少一行(：

PLACE       USER_ID           DATE
---------- ---------- -----------------------------
XYZ      4     14/04/20 11:07:23,335000000 
ABC      4     13/04/20 22:09:33,255000000    
QWE      4     13/04/20 10:18:29,144000000 

提前感谢！

Answer 1

在Oracle 12.1及更高版本中，像这样的间隙和孤岛问题对于match_recognize子句来说是一项简单的任务。例如：

表格设置

alter session set nls_timestamp_format = 'dd/mm/rr hh24:mi:ss,ff';
create table user_places (place, user_id, date_) as 
select 'ABC', 4, to_timestamp('14/04/20 12:05:29,255000000') from dual union all  
select 'ABC', 4, to_timestamp('14/04/20 15:42:28,389000000') from dual union all  
select 'ABC', 4, to_timestamp('14/04/20 18:33:20,202000000') from dual union all
select 'ABC', 4, to_timestamp('14/04/20 22:51:28,339000000') from dual union all
select 'XYZ', 4, to_timestamp('14/04/20 11:07:23,335000000') from dual union all
select 'XYZ', 2, to_timestamp('14/04/20 12:15:12,123000000') from dual union all
select 'ABC', 4, to_timestamp('13/04/20 22:09:33,255000000') from dual union all
select 'QWE', 4, to_timestamp('13/04/20 10:18:29,144000000') from dual union all
select 'XYZ', 2, to_timestamp('14/04/20 10:05:47,255000000') from dual
;
commit;

查询和输出

select place, user_id, date_
from   (select * from user_places where user_id = 4)
match_recognize (
order    by date_
all rows per match
pattern  (a {- b* -} )
define   b as place = a.place
)
order by date_ desc   --   if needed
;
PLACE  USER_ID  DATE_
-----  -------  ---------------------------
ABC          4  14/04/20 12:05:29,255000000
XYZ          4  14/04/20 11:07:23,335000000
ABC          4  13/04/20 22:09:33,255000000
QWE          4  13/04/20 10:18:29,144000000

这里需要注意的几点：

• DATE是一个保留关键字。不是一个好的列名。我使用了DATE_相反注意后面的下划线
• 我对值进行了硬编码4。当然，更好的做法是将其转换为绑定变量
• 如果您一次只需要对一个user_id执行此操作，那么执行我所做的操作是最有效的——首先在子查询中过滤行。但是，如果需要对同一查询中的所有用户id执行此操作，则不需要子查询；您可以从表本身中进行选择，并且需要在order by date_之前的match_recognize子句顶部添加partition by user_id

Answer 2

您可以在子查询中使用lag()来检索"前一个"位置，然后过滤前一个位置与当前位置不同的行：

select place, user_id, date
from (
select t.*, lag(place) over(partition by user_id order by date) lag_place
from mytable t
) t
where lag_place is null or place <> lag_place

这将为您提供所有用户的预期输出。如果您只想要用户4，那么您可以在子查询中进行筛选(不需要partition by用户(：

select place, user_id, date
from (
select t.*, lag(place) over(order by date) lag_place
from mytable t
where user_id = 4
) t
where lag_place is null or place <> lag_place