4

i have table which include my product table's logs like that:

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

I want to write a query which shows the changes of product_type_id. For the example above, the result of my query should be like that:

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

How can I write this query?

Improve this question

2 Answers 2

Reset to default
4

Like this:

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;

For all who like to see how this works:

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;

Executed we get:

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Use the LAG analytic function to find the prior value of the product_type_id column. If the current and prior values are different then that should be the row that you want. For the first row, the LAG function will return null because there is no prior row so you will also need to test for that.

select process_time, product_id, product_type_id, from ( select process_time, product_id, product_type_id, lag(product_type_id) over (order by process_time) as prior_product_type_id from the_table ) where (prior_product_type_id <> product_type_id or prior_product_type_id is null)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.