我有一张表，其中包含我的产品表的日志，如下所示:
process_time product_id product_type_id
04.07.2009 14:08:43 5 4
05.07.2009 15:08:43 5 4
06.07.2009 16:08:43 5 6
07.07.2009 16:08:43 5 6
08.07.2009 17:08:43 5 4
08.07.2009 18:08:43 5 4
我想写一个查询来显示 product_type_id 的变化。对于上面的例子，我的查询结果应该是这样的:
process_time product_id product_type_id
04.07.2009 14:08:43 5 4
06.07.2009 16:08:43 5 6
08.07.2009 17:08:43 5 4
我该如何编写此查询？
最佳答案
像这样:
select * from
(select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products )
where nextrow <> product_type_id or nextrow is null;
对于所有喜欢看看它是如何工作的:
create table products (process_time timestamp, product_id number, product_type_id number);
insert into products values (to_date('2009-07-04 14:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-05 15:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-06 16:08:43','YYYY-MM-DD hh24:mi:ss'),5,6);
insert into products values (to_date('2009-07-07 16:08:43','YYYY-MM-DD hh24:mi:ss'),5,6);
insert into products values (to_date('2009-07-08 17:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
insert into products values (to_date('2009-07-08 18:08:43','YYYY-MM-DD hh24:mi:ss'),5,4);
commit;
select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products
order by process_time;
select * from
(select process_time, product_id, product_type_id
,lag(product_type_id) over (partition by product_id order by process_time) as prevrow
,lead(product_type_id) over (partition by product_id order by process_time) as nextrow
from products )
where nextrow <> product_type_id or nextrow is null;
commit;
drop table products;
执行我们得到:
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
PROCESS_TIME PRODUCT_ID PRODUCT_TYPE_ID PREVROW NEXTROW
------------------------------- ---------- --------------- ---------- ----------
04-JUL-09 02.08.43.000000 PM 5 4 4
05-JUL-09 03.08.43.000000 PM 5 4 4 6
06-JUL-09 04.08.43.000000 PM 5 6 4 6
07-JUL-09 04.08.43.000000 PM 5 6 6 4
08-JUL-09 05.08.43.000000 PM 5 4 6 4
08-JUL-09 06.08.43.000000 PM 5 4 4
6 rows selected.
PROCESS_TIME PRODUCT_ID PRODUCT_TYPE_ID PREVROW NEXTROW
------------------------------- ---------- --------------- ---------- ----------
05-JUL-09 03.08.43.000000 PM 5 4 4 6
07-JUL-09 04.08.43.000000 PM 5 6 6 4
08-JUL-09 06.08.43.000000 PM 5 4 4
3 rows selected.
Commit complete.
Table dropped.