-
Query that returns grouped data
I am having troubles writing a query that would only return results where a certain value differs from the previous record based on partitioned data and a timestamp
My simple table is
ROWID STORE DATE CATEGORY
1 1000 01/01/2007 B
2 1000 02/02/2007 B
3 1000 03/02/2007 A
4 1001 02/02/2007 B
5 1001 02/02/2007 B
6 1002 03/02/2007 B
7 1002 03/02/2007 A
What I want is in my query result is the data for ROWID 3,2 & 7,6 as only those rows have changes in category for a given store
Thanks
-
Analytic functions may help you.
See http://download-uk.oracle.com/docs/c...nctions001.htm
Code:
select store, ts, category, prev_category, next_category
from
(
select store, ts, category,
lag(category,1,category) over(partition by store order by ts) as prev_category,
lead(category,1,category) over(partition by store order by ts) as next_category
from simple
)
where category!=prev_category or category!=next_category
Ales The whole difference between a little boy and an adult man is the price of toys
-
Try this query it should be helpful
Code:
SELECT * FROM STORES
WHERE ROWID IN
(
SELECT MAX(ROWID) OVER(PARTITION BY STORE, CATEGORY)
FROM STORES
)
-
Originally Posted by ales
Analytic functions may help you.
See http://download-uk.oracle.com/docs/c...nctions001.htm
Code:
select store, ts, category, prev_category, next_category
from
(
select store, ts, category,
lag(category,1,category) over(partition by store order by ts) as prev_category,
lead(category,1,category) over(partition by store order by ts) as next_category
from simple
)
where category!=prev_category or category!=next_category
you forgot to do store code checking too.. your query will e.g. return rowid 4, because it has a different category to rowid 3
you need to say:
Code:
select store, ts, category, prev_category, next_category
from
(
select store, ts, category,
lag(category,1,category) over(partition by store order by ts) as prev_category,
lead(category,1,category) over(partition by store order by ts) as next_category,
lag(store,1,store) over(partition by store order by ts) as prev_store,
lead(store,1,store) over(partition by store order by ts) as next_store
from simple
)
where (category <> prev_category and store = prev_store) or (category <> next_category and store = next_store)
also, i swapped your != for <> because I didnt know oracle did != for not equals
I dont know why the timestamp column is mentioned, it doesnt seem to be used by the OP (other than an order by for the partition?)
-
Adding a ts filter
Thanks for those responses.
My next question is for Cjards query that worked well.
The question is how do I now limit that result set so that it only returns a result set for those stores who have had category changed between two dates say as weeks worth.
What this means is that the date parameter can only be applied to the very latest updates however we still want to see the history and do not want it filtered out.
Stores that have had their last update made outside of this 'week' should not appear.
-
Originally Posted by cjard
you forgot to do store code checking too.. your query will e.g. return rowid 4, because it has a different category to rowid 3?)
Did you test the query or it's just a guess?
Rowid 3 and 4 have different stores and the aren't compared because of the "partition by store" clause.
Can you see the row_id 4 in the result set?
Code:
create table simple (row_id number, store char(4), ts date, category char);
insert into simple values(1,'1000',to_date('01/01/2007','dd/mm/yyyy'),'B');
insert into simple values(2,'1000',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(3,'1000',to_date('03/02/2007','dd/mm/yyyy'),'A');
insert into simple values(4,'1001',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(5,'1001',to_date('02/02/2007','dd/mm/yyyy'),'B');
insert into simple values(6,'1002',to_date('03/02/2007','dd/mm/yyyy'),'B');
insert into simple values(7,'1002',to_date('03/02/2007','dd/mm/yyyy'),'A');
commit;
select * from simple order by row_id;
ROW_ID STOR TS C
---------- ---- ------------------- -
1 1000 01.01.2007 00:00:00 B
2 1000 02.02.2007 00:00:00 B
3 1000 03.02.2007 00:00:00 A
4 1001 02.02.2007 00:00:00 B
5 1001 02.02.2007 00:00:00 B
6 1002 03.02.2007 00:00:00 B
7 1002 03.02.2007 00:00:00 A
7 rows selected.
select row_id, store, ts, category, prev_category, next_category
from
(
select row_id, store, ts, category,
lag(category,1,category) over(partition by store order by ts) as prev_category,
lead(category,1,category) over(partition by store order by ts) as next_category
from simple
)
where category!=prev_category or category!=next_category
/
ROW_ID STOR TS C P N
---------- ---- ------------------- - - -
2 1000 02.02.2007 00:00:00 B B A
3 1000 03.02.2007 00:00:00 A B A
6 1002 03.02.2007 00:00:00 B B A
7 1002 03.02.2007 00:00:00 A B A
Originally Posted by cjard
I dont know why the timestamp column is mentioned, it doesnt seem to be used by the OP (other than an order by for the partition?)
It's only for checking purpose. I posted a proposal, not a complete solution.
Ales The whole difference between a little boy and an adult man is the price of toys
-
I dare to try to answer :-))
mozart: you can add a filter to the inner query, e.g.
Code:
...
from simple
where store in
(
select store
from simple
where ts between date1 and date2
group by store
having count(distinct category)>1
)
Ales The whole difference between a little boy and an adult man is the price of toys
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|