Analitical Function

                                       Analytical Function (OVER)                            
=======================================================

select employee_id,department_id,count(*) from employees
where department_id in(20,30)
group by employee_id,department_id


EMPLOYEE_ID DEPARTMENT_ID   COUNT(*)
----------- ------------- ----------
        117            30          1
        119            30          1
        115            30          1
        114            30          1
        116            30          1
        118            30          1
        201            20          1
        202            20          1

8 rows selected.


select employee_id,department_id,count(*) over (partition by
department_id
order by department_id) id from employees
where department_id in(20,30)

EMPLOYEE_ID DEPARTMENT_ID         ID
----------- ------------- ----------
        201            20          2
        202            20          2
        115            30          6
        116            30          6
        118            30          6
        119            30          6
        117            30          6
        114            30          6

8 rows selected.




                                                                     LEAD and LAG
================================================================




LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:



LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)



<sql_expr> is the expression to compute from the leading row.
<offset> is the index of the leading row relative to the current row.
<offset> is a positive integer with default 1.
<default> is the value to return if the <offset> points to a row outside the partition range.

The syntax of LAG is similar except that the offset for LAG goes into the previous rows.



SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;

 DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
     10   7839  5000           2450               0
     10   7782  2450           1300            5000
     10   7934  1300              0            2450
     20   7788  3000           3000               0
     20   7902  3000           2975            3000
     20   7566  2975           1100            3000
     20   7876  1100            800            2975
     20   7369   800              0            1100

8 rows selected.

1 টি মন্তব্য: