Query that returns grouped data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query that returns grouped data

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    Try this query it should be helpful

    Code:
    SELECT * FROM STORES
    WHERE ROWID IN
    (
    	SELECT MAX(ROWID) OVER(PARTITION BY STORE, CATEGORY)
    	FROM STORES
    )

  4. #4
    Join Date
    Mar 2006
    Posts
    74
    Quote 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?)

  5. #5
    Join Date
    Mar 2004
    Posts
    55

    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.

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    Talking

    Quote 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
    Quote 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

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    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
  •  


Click Here to Expand Forum to Full Width