The simplest way of excluding the current row from the result of an analytic

oracleoracle-10gwindow functions

I want to do something like the following:

with w as ( select level as foo, 
                   decode(level,8,1,mod(level,4))*100 as bar
            from dual connect by level<9 ) 
select foo, bar, 
       min(foo) keep (dense_rank first order by foo) over (partition by bar) as lowest
from w;
/*
FOO                    BAR                    LOWEST                 
---------------------- ---------------------- ---------------------- 
4                      0                      4                      
8                      100                    1                      
5                      100                    1                      
1                      100                    1                      
2                      200                    2                      
6                      200                    2                      
3                      300                    3                      
7                      300                    3     
*/

but I want lowest to show the next lowest if the current row is the lowest for a given bar, or null if it is the only one. In other words I want this result:

/*
FOO                    BAR                    PSEUDOLOWEST                 
---------------------- ---------------------- ---------------------- 
4                      0                                             
1                      100                    5                      
5                      100                    1                      
8                      100                    1                      
2                      200                    6                      
6                      200                    2                      
3                      300                    7                      
7                      300                    3        
*/

What is the simplest way of achieving this?

Best Answer

There may be a simpler way, but here is a solution. If the current row is the minimum foo for the bar, then we always want the next (lead) foo. When there is only one foo for a bar the lead will always be null and for every other case we really do want the minimum foo for the bar.

with w as (select level as foo, decode(level,8,1,mod(level,4))*100 as bar
   from dual connect by level<9) 
select foo, bar
   , case 
        when foo = min(foo) over (partition by bar) 
           then lead(foo) over (partition by bar ORDER BY foo)
        else min(foo) over (partition by bar) 
     end pseudolowest
from w;

Unfortunately rows excluding current row doesn't exist.

Related Solutions

The best way of checking the current user privileges on a particular table from another schema

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.

Designing Simple Schema for Disaggregation of Demand Forecast

Okay, so here's the data model I came up with.

PRODUCT -- to store product information and maintain parent-child hierarchy

id  NUMBER  "Primary Key Not Null"                  
level_code  VARCHAR2    Not Null                    
name    VARCHAR2    Not Null                    
description VARCHAR2                        
parent_id   NUMBER  Foreign Key references PRODUCT(id)

ORDERS -- to store orders for products

id  NUMBER  "Primary Key Not Null"                  
prod_id     NUMBER  "Foreign Key references PRODUCT(id) Not Null"                   
order_type  VARCHAR2    "Not Null Default 'Default'"
order_qty   NUMBER  Not Null
order_date  NUMBER  Foreign Key references DATE_INFO(date_key)
delivery_date   NUMBER  "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"

FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)

id  NUMBER  "Primary Key Not Null"
product_id  NUMBER  "Foreign Key references PRODUCT(id) Not Null"
forecast_value  NUMBER  Not Null
week    NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"

DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level

id  NUMBER  "Primary Key Not Null"
parent_product_id   NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
child_product_id    NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
method  VARCHAR2    Not Null                    
from_week   NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution    NUMBER  Not Null

DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls

date_key    NUMBER  "Primary Key
Not Null"                   
full_date   DATE    Not Null                    
week_begin_date DATE    Not Null                    
week_end_date   DATE    Not Null

As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function

CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
  RETURN DATE
IS
  week_start_date DATE;
BEGIN
  SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
  INTO week_start_date FROM dual;
  RETURN week_start_date;
END;

Best Answer

Related Solutions

The best way of checking the current user privileges on a particular table from another schema

Designing Simple Schema for Disaggregation of Demand Forecast

Related Question