Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: possible with one SQL statment?

    Hi,

    I have a set of data for prices that looks like:

    price_type date price
    100 05-JAN-2004 4.99
    100 06-JAN-2004 4.99
    100 07-JAN-2004 4.99
    100 08-JAN-2004 4.99 ......

    100 13-FEB-2004 4.99
    101 14-FEB-2004 3.49
    101 15-FEB-2004 3.49
    101 16-FEB-2004 3.49
    100 17-FEB-2004 4.99 ......

    100 14-MAY-2004 4.99
    101 15-MAY-2004 3.99
    101 16-MAY-2004 3.99
    101 17-MAY-2004 3.99
    100 18-MAY-2004 4.99 .....

    100 30-JUN-2004 4.99

    - a row for each day.

    I need to get a set that looks like

    100 05-JAN-2004 13-FEB-2004 4.99
    101 14-FEB-2004 16-FEB-2004 3.49
    100 17-FEB-2004 14-MAY-2004 4.99
    101 15-MAY-2004 17-MAY-2004 3.99
    100 18-MAY-2004 30-JUN-2004 4.99

    The prices in effect by range of dates - without the overlap.

    Can anyone suggest a SELECT?

    thanks,
    dave

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    select 
      price_type
    ,
      
    min(datestart_date,
      
    max(dateend_date,
      
    price
    from prices
    group by price_type
    price
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    won't quite work...

    thanks duck....but the only problem lies in if the price is the same in a different window of time:

    Code:
    create table prices
    (price_type integer,
     eff_date date,
     price number(10,2));
    
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'05-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'06-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'07-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (101,'08-JAN-04',3.49);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (101,'09-JAN-04',3.49);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (101,'10-JAN-04',3.49);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'11-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'12-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (101,'13-JAN-04',3.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (101,'14-JAN-04',3.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'15-JAN-04',4.99);
    INSERT INTO prices (price_type,eff_date,price)
    VALUES (100,'16-JAN-04',4.99);
    
    
    select 
      price_type, 
      min(eff_date) start_date, 
      max(eff_date) end_date, 
      price 
    from prices
    group by price_type, price;
    gives the result:



    100 5-Jan-2004 14-Jan-2004 4.99
    101 8-Jan-2004 10-Jan-2004 3.49
    101 13-Jan-2004 14-Jan-2004 3.99

    but want:

    100 5-jan-04 07-jan-04 4.99
    101 8-jan-04 10-jan-04 3.49
    100 11-jan-04 12-jan-04 4.99
    101 13-jan-04 14-jan-04 3.99
    100 15-jan-04 16-jan-04 3.99

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think its more complicated than that as he wants intervals of dates where the price doesnt change, so you cant group by price as it doesnt take into account wether the price has changed between the min and max values.

    I think you can do this in one sql but it will take analytics to do it and be quite complex. Otherwise youll have to do it procedurally.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by dstachon
    thanks duck....but the only problem lies in if the price is the same in a different window of time:
    gives the result:



    100 5-Jan-2004 14-Jan-2004 4.99
    101 8-Jan-2004 10-Jan-2004 3.49
    101 13-Jan-2004 14-Jan-2004 3.99

    but want:

    100 5-jan-04 07-jan-04 4.99
    101 8-jan-04 10-jan-04 3.49
    100 11-jan-04 12-jan-04 4.99
    101 13-jan-04 14-jan-04 3.99
    100 15-jan-04 16-jan-04 3.99
    so the PRICE does not determine the WINDOW?
    what exactly determines the window then?
    with your example it looks like if you change the TYPE then the window
    closes for the previous type??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    you need analytics
    I don't know which one though

    the window is defined as the ordered dates having the same price_type. if you select * order by date, and break on price_type, the break points define the window. so, for each window, what is the min/max date
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137

    Lightbulb

    Duck, its when the price changes.

    Anyway this might do the trick (plus a couple of extra columns to help), just dont ask me to explain it . There is more than one way to do it and you may find a simpler solution. It does need 9i though. If you have a large number of rows then indexing on eff_date will help I think and maybe some of the other columns.

    Alan

    Code:
    select substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end), min(eff_date), max(eff_date), min(price), count(*)
    from
    (
    	select SYS_CONNECT_BY_PATH(recnum, '/') Path, recnum, price, eff_date
    	    from 
    	(
    	    select 
    		rownum recnum , price_type, eff_date, price,
    		case when lag(price,1) over (order by eff_date) = price then lag(rownum,1) over (order by eff_date) else 0 end prev 
    		from prices
    		union 
    	    select 0 ,null,null,null,null from dual    
    	) x
    	connect by prior recnum=prev
    	start with recnum=0
    )
    group by substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end)
    having length(substr(path,1,case when instr(path,'/',1,3)=0 then length(path) else instr(path,'/',1,3)-1 end))>2
    Last edited by AlanP; 09-16-04 at 13:59.

  8. #8
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    I have much to learn

    thanks guys...

    hey AlanP...I though I knew SQL pretty well...until I saw your query. awesome.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    I think an additional column would greatly improve and simplify your life.
    PHP Code:
    SQLselect from prices;

    PRICE_TYPE EFF_DATE       PRICE  GROUP_NUM
    ---------- --------- ---------- ----------
           
    100 05-JAN-04       4.99          1
           100 06
    -JAN-04       4.99          1
           100 07
    -JAN-04       4.99          1
           101 08
    -JAN-04       3.49          2
           101 09
    -JAN-04       3.49          2
           101 10
    -JAN-04       3.49          2
           100 11
    -JAN-04       4.99          3
           100 12
    -JAN-04       4.99          3
           101 13
    -JAN-04       3.99          4
           101 14
    -JAN-04       3.99          4
           100 15
    -JAN-04       4.99          5
           100 16
    -JAN-04       4.99          5

      1  select
      2    price_type
    ,
      
    3    min(eff_datestart_date,
      
    4    max(eff_dateend_date,
      
    5    price
      6  from prices
      7  group by price_type
    pricegroup_num
      8
    order by start_date
    SQL
    > /

    PRICE_TYPE START_DAT END_DATE       PRICE
    ---------- --------- --------- ----------
           
    100 05-JAN-04 07-JAN-04       4.99
           101 08
    -JAN-04 10-JAN-04       3.49
           100 11
    -JAN-04 12-JAN-04       4.99
           101 13
    -JAN-04 14-JAN-04       3.99
           100 15
    -JAN-04 16-JAN-04       4.99 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    i agree

    yes, I agree duck....

    I going to add the column, and run this:

    Code:
    CREATE OR REPLACE 
    PROCEDURE price_time_grouping
    IS
       v_price_type            INTEGER;
       v_previous_price_type   INTEGER;
       v_eff_date              DATE;
       v_group_counter         INTEGER := 1;
    
       CURSOR cur
       IS
          SELECT   price_type, eff_date
              FROM prices
          ORDER BY eff_date ASC;
    BEGIN
       OPEN cur;
    
       LOOP
          FETCH cur
           INTO v_price_type, v_eff_date;
    
          EXIT WHEN cur%NOTFOUND;
    
          IF v_price_type = NVL (v_previous_price_type, v_price_type)
          THEN
             NULL;
          ELSE
             v_group_counter := v_group_counter + 1;
          END IF;
    
          UPDATE prices
             SET time_group = v_group_counter
           WHERE eff_date = v_eff_date;
    
          COMMIT;
          v_previous_price_type := v_price_type;
       END LOOP;
    
       CLOSE cur;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line (SQLERRM);
    END;
    thanks.

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    How about..

    Code:
    select price_type, min(eff_date), max(eff_date), price
      from (
        select eff_date, price_type, price, max(rn) over (order by eff_date) grp
          from (
            select eff_date, price_type, price,
                   case when lag(price_type) over (order by eff_date) <> price_type
                        then row_number() over (order by eff_date)
                    end rn
              from prices
               )
           )
     group by price_type, price, grp
     order by grp nulls first
    i.e.: assigning the row_number when the partition changes, leaving all others nulls. then spanning this row_number (and nulls as well) over all others and finally get the mix/max out of the group, exposing first those with nulls.

    I reproduced this answer after watching how Tom did in this thread. (Lucky us, a site like AskTom exists!)

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Another solution. I would suggest its probably best to either go with the solution above or this one rather than adding a column as both should run quite well. Try both on your prod environment and see which runs best after you've added the appropriate indexes if needed.

    Alan

    Code:
    with x as 
    (
            select 
    		rownum recnum , price_type, eff_date, price,
    		case when lag(price,1) over (order by eff_date) = price then lag(rownum,1) over (order by eff_date) else 0 end prev 
    		from prices
            order by eff_date
    )
    select min(price_type),min(eff_date), max(eff_date), price
    from
    ( 
    	select x.*, case when lag(price,1) over (order by eff_date) = price then lag(recnum,1) over (order by eff_date) else recnum end grouping
    	from x  
    	where x.prev=0 or x.recnum in (select recnum-1 from x where x.prev=0) 
    )
    group by grouping, price

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    I personally don't feel ROWNUMBER should be required in order
    so support a business rule. If this is the case, then perhaps the table
    design is inadequate.

    Look at the table. The only true PK column is the date (which would
    always be unique up to the 100th second) since all other columns are
    (can be) duplicates.

    It just seems to me like you are missing a column since you are RELYING
    on rownum/lag. In essence, you are mimmicking my previous post
    on adding the column, but you add the column every query
    instead of hard-coding the column.

    opinions?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I kindof agree with you except for the fact that you might be updating a lot of rows and you might cause locking issues. IF and its a big if, if the select is simply too slow then I might add the extra column, however if the select runs quickly then stick with the single sql statement.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •