Data Densification
Example1:
SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;
DEPTNO DEPT_COUNT
---------------------- ----------------------
20 5
30 6
The above query returns departments and their employee count, what if we want to
repeat the count for all employees of corresponding dept's.
SELECT empno, deptno,
COUNT(*) OVER (PARTITION BY
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);
EMPNO DEPTNO DEPT_COUNT
---------- ---------- ----------
7369 20 5
7566 20 5
7788 20 5
7902 20 5
7876 20 5
7499 30 6
7900 30 6
7844 30 6
7698 30 6
7654 30 6
7521 30 6
Example2:
We had a requirement where in even if the data has gaps the report should be continuous , regardless of presence of data. There are two tables below.
Expected Output:
Query:
select Pen_NAme,Time_id,nvl(sum(quantity),0) from temp ym
LEFT OUTER JOIN
Gags co PARTITION BY (co.pen_name)
ON (co.procurement = ym.time_id)
group by Pen_NAme,Time_id;