Home » SQL & PL/SQL » SQL & PL/SQL » Analytical Function - Window Clause RANGE
Analytical Function - Window Clause RANGE [message #309153] Wed, 26 March 2008 11:18 Go to next message
jayesh_nazre
Messages: 4
Registered: March 2008
Junior Member
Here is the query

SELECT deptno, empno, sal,
       Count(*) OVER (PARTITION BY deptno ORDER BY sal 
         RANGE BETWEEN UNBOUNDED PRECEDING AND (sal/2) PRECEDING) CNT_LT_HALF,
       COUNT(*) OVER (PARTITION BY deptno ORDER BY sal 
         RANGE BETWEEN (sal/2) FOLLOWING AND UNBOUNDED FOLLOWING) CNT_MT_HALF
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, sal

here is the output
   DEPTNO      EMPNO        SAL CNT_LT_HALF CNT_MT_HALF
--------- ---------- ---------- ----------- -----------
       20       7369        800           0           3
       20       7876       1100           0           3
       20       7566       2975           2           0
       20       7788       3000           2           0
       20       7902       3000           2           0
       30       7900        950           0           3
       30       7521       1250           0           1
       30       7654       1250           0           1
       30       7844       1500           0           1
       30       7499       1600           0           1
       30       7698       2850           3           0


I am finding it hard to understand this can someone explain how its counting the last column
Based on what I understand "CNT_LT_HALF" divides the curron row sal and compares with precedding sal row values and that looks fine (not the count does not include the existing row in its count so its less that not less than equal to)
however when I try to apply the same for "CNT_MT_HALF" colum
say take current rows sal/2 eg. 800/2 = 400
now within dept = 20 if you compare this value with all the following sal then for the first row "CNT_LT_HALF" column should have a value of "4" and not "3". I ran the query in Oracle database and the query matches what the article output is ("3"). I know I am understanding it wrong, can someone explain

This is a query based on following article link
http://www.orafaq.com/node/55

[Formatting done by moderator, please read guidelines and do it yourself next time]

[Updated on: Wed, 26 March 2008 11:22] by Moderator

Report message to a moderator

Re: Analytical Function - Window Clause RANGE [message #309162 is a reply to message #309153] Wed, 26 March 2008 12:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I do not understand your logic, so I will just show you mine. As stated in Analytic Functions part of SQL Reference:
Quote:
value_expr PRECEDING or value_expr FOLLOWING
...
If you specified RANGE:
* value_expr is a logical offset. ...

It could be more detailed, but the main idea is there: you give offset to the current row's value.

In words, CNT_MT_HALF really calculates, what is said there: "count of employees getting half more than their salary".

For the first row: its value is 800, offset is 800/2 following, so the window start is at 800 + 800/2 = 1200. As there are 3 employees in department 20 with bigger salary than 1200, the result is correct.

The same rule applies for CNT_LT_HALF: first row's value is 800, offset is 800/2 preceding, so the window end is 800 - 800/2 = 400.
Re: Analytical Function - Window Clause RANGE [message #309176 is a reply to message #309162] Wed, 26 March 2008 13:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Nice explanation flyboy. Here is a little demo just for comparison and to try to make it clearer. The last dup_cnt_mt_half column is just showing what the cnt_mt_half column is extracting in a more efficient way, showing the pieces that are substitutes for the partition, order by , and offset.


SCOTT@orcl_11g> BREAK ON deptno SKIP 1
SCOTT@orcl_11g> SELECT e1.deptno, e1.empno, e1.sal,
  2  	    e1.sal/2 AS offset,
  3  	    e1.sal + e1.sal/2 AS range_start,
  4  	    COUNT(*) OVER (PARTITION BY e1.deptno ORDER BY e1.sal
  5  	      RANGE BETWEEN (e1.sal/2) FOLLOWING AND UNBOUNDED FOLLOWING) CNT_MT_HALF,
  6  	    (SELECT COUNT (*)
  7  	     FROM   emp e2
  8  	     WHERE  e2.deptno = e1.deptno -- partition
  9  	     AND    e2.sal >		  -- order
 10  		    (e1.sal +
 11  		    (e1.sal/2)		  -- offset
 12  		    )) dup_cnt_mt_half
 13  FROM   emp e1
 14  WHERE  e1.deptno IN (20, 30)
 15  ORDER  BY e1.deptno, e1.sal
 16  /

    DEPTNO      EMPNO        SAL     OFFSET RANGE_START CNT_MT_HALF DUP_CNT_MT_HALF
---------- ---------- ---------- ---------- ----------- ----------- ---------------
        20       7369        800        400        1200           3               3
                 7876       1100        550        1650           3               3
                 7566       2975     1487.5      4462.5           0               0
                 7788       3000       1500        4500           0               0
                 7902       3000       1500        4500           0               0

        30       7900        950        475        1425           3               3
                 7521       1250        625        1875           1               1
                 7654       1250        625        1875           1               1
                 7844       1500        750        2250           1               1
                 7499       1600        800        2400           1               1
                 7698       2850       1425        4275           0               0


11 rows selected.

SCOTT@orcl_11g> 

[Updated on: Wed, 26 March 2008 13:39]

Report message to a moderator

Re: Analytical Function - Window Clause RANGE [message #309201 is a reply to message #309176] Wed, 26 March 2008 15:11 Go to previous message
jayesh_nazre
Messages: 4
Registered: March 2008
Junior Member
Thanks
flyboy, Barbara Boehmer

Previous Topic: Problem Truncating a Table
Next Topic: Get session to ignore commits and rollbacks
Goto Forum:
  


Current Time: Tue Dec 06 02:56:36 CST 2016

Total time taken to generate the page: 0.10314 seconds