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.
=======================================================
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.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন