[PostgreSQL]분석함수 사용하지 않고 rank 쿼리 사용

출처 : http://www.gurubee.net/lecture/2192  

쿼리 작성조건

• - 사원 점수에 대한 전체 순위와 부서별 순위를 구하세요.
• - 최종결과는 부서코드, 부서별 순위, 사원번호 순으로 정렬하세요.
• - 오라클 8.0 버전의 환경에서 사용될 SQL이므로 분석함수를 사용할 수 없습니다.

CREATE TABLE t AS
(
    SELECT 1 AS empno, 10 AS deptno, 100AS  point 
    UNION ALL SELECT 2, 10, 90 
    UNION ALL SELECT 3, 10, 80 
    UNION ALL SELECT 4, 20, 100
    UNION ALL SELECT 5, 20, 90
    UNION ALL SELECT 6, 20, 80 
    UNION ALL SELECT 7, 30, 95 
    UNION ALL SELECT 8, 30, 85 
    UNION ALL SELECT 9, 30, 95 
);

<정답1>

SELECT A.EMPNO,A.DEPTNO,A.POINT,
	COUNT(CASE WHEN A.POINT - B.POINT < 0 THEN 0 END)+1RK_ALL,
	COUNT(CASE WHEN A.POINT-B.POINT < 0 AND A.DEPTNO= B.DEPTNO THEN 0 END)+1 RK_DEPT
FROM T A CROSS JOIN T b
GROUP BY A.EMPNO,A.DEPTNO,A.POINT
ORDER BY A.DEPTNO, RK_DEPT,EMPNO;

<정답2>

SELECT A.EMPNO,A.DEPTNO,A.POINT,
	COUNT(B.POINT)+1RK_ALL,
	COUNT(CASE WHEN A.DEPTNO=B.DEPTNO THEN 0 END)+1 RK_DEPT
FROM T A LEFT JOIN T B 
	ON A.POINT <B.POINT
GROUP BY A.EMPNO,A.DEPTNO,A.POINT
ORDER BY A.DEPTNO, RK_ALL,RK_DEPT;