OLAP-Unbounded Following query

Database
Enthusiast

OLAP-Unbounded Following query

Hi all,

what is the difference between the follwing queries

1) select product_id,sales_date,daily_sales,min(daily_sales)

 over(partition by product_id  order by daily_sales   rows between current row and unbounded following ) from sales_table where product_id=1000 ;

Result set is...

    product_id sales_date    DAILY_SALES    Remaining Min(DAILY_SALES)

1    1,000    10/3/2000    64,300.00    64,300.00

2    1,000    10/4/2000    54,553.10    54,553.10

3    1,000    9/29/2000    54,500.22    54,500.22

4    1,000    9/28/2000    48,850.40    48,850.40

5    1,000    10/1/2000    40,200.43    40,200.43

6    1,000    9/30/2000    36,000.07    36,000.07

7    1,000    10/2/2000    32,800.50    32,800.50

2) select product_id,sales_date,daily_sales,min(daily_sales)

 over(partition by product_id  order by daily_sales Desc  rows between current row and unbounded following ) from sales_table where product_id=1000 ;

   Result set is..

     product_id    sales_date    DAILY_SALES    Remaining Min(DAILY_SALES)

1    1,000    10/2/2000    32,800.50    32,800.50

2    1,000    9/30/2000    36,000.07    32,800.50

3    1,000    10/1/2000    40,200.43    32,800.50

4    1,000    9/28/2000    48,850.40    32,800.50

5    1,000    9/29/2000    54,500.22    32,800.50

6    1,000    10/4/2000    54,553.10    32,800.50

7    1,000    10/3/2000    64,300.00    32,800.50


when i am excuting second one i am getting desired result set....

when using unbounded following only i facing this..

thanks,

Sai




2 REPLIES
Junior Contributor

Re: OLAP-Unbounded Following query

Hi Sai,

the difference is the ordering, ascending vs. descending :-)

#1 returns the daily_sales value of the current row, thus it's equivalent to daily_sales without any OLAP

#2 returns the minimum daily_sales of all rows for a product_id, thus it's equivalent to min(daily_sales) over(partition by product_id)

Dieter

Enthusiast

Re: OLAP-Unbounded Following query

Thank you for clarification Dieter