Day055

LAG 함수

이전 행의 값을 출력
LAG(컬럼명 [,offset]  [,default]) OVER( [PARTITION BY 그룹 컬럼명] ORDER BY 정렬 컬럼명)

LEAD 함수

다음 행의 값을 출력
LEAD(컬럼명 [,offset]  [,default]) OVER( [PARTITION BY 그룹 컬럼명] ORDER BY 정렬 컬럼명)

• offset : 값을 가져올 행의 위치 기본값은 1, 생략가능
• default : 값이 없을 경우의 기본값, 생략가능

RANK(), DENSE_RANK(), ROW_NUMBER()

• RANK : 중복 순위의 개수만큼 다음 행의 순위를 증가
  RANK() OVER(PARTITION BY 그룹 컬럼명 ORDER BY 정렬 컬럼명)
• DENSE_RANK : 중복 순위와 상관없이 순차적으로 증가
  DANSE_RANK() OVER(PARTITION BY 그룹 컬럼명 ORDER BY 정렬 컬럼명)

SUM() OVER

• 누적 합계를 출력

JOIN

1. Cartesian Product
   조인 조건이 누락된 경우 모든 대상 행을 출력
2. EQUI Join
   WHERE절에 조인 조건을 작성
3. Non-Equi Join
4. Outer Join : Left Outer Join, Right Outer Join
   무조건 조회가 되어야 하는 메인 테이블의 반대쪽 테이블에(+)를 붙여준다.

---

SELECT *
FROM( SELECT weekno "WEEK", DAY, dayno
FROM cal)
PIVOT ( MAX(dayno)  FOR DAY IN('SUN' as SUN,
                                'MON' as MON,
                                'TUE' as TUE,
                                'WED' as WED,
                                'THU' as THU,
                                'FRI' as FRI,
                                'SAT' as SAT )
       )
        ORDER BY WEEK;
        
SELECT DISTINCT(job)
FROM emp;

SELECT ename, hiredate, sal, LAG(sal, 1, 0) OVER(ORDER BY sal)
FROM  emp;

SELECT empno, ename, sal
    , RANK() OVER (ORDER BY sal)
    , DENSE_RANK() OVER (ORDER BY sal)
FROM emp;

SELECT RANK(1600) WITHIN GROUP (ORDER BY sal)
FROM emp;

SELECT empno, ename, sal, deptno
    , RANK() OVER(ORDER BY sal desc) 급여순위
    , RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) 부서별급여순위
FROM emp
--WHERE deptno = 10;
ORDER BY deptno;

SELECT empno, ename, sal, deptno, job
    , RANK() OVER(PARTITION BY deptno, job ORDER BY sal DESC) RANK
FROM emp
ORDER BY deptno, job;

SELECT p_date, p_code, p_qty, p_total
    , SUM(p_total) OVER(PARTITION BY p_code ORDER BY p_total) 누적합계
FROM panmae
WHERE p_store = 1000;

SELECT p_code, p_store, p_date, p_qty, p_total
    , SUM(p_total) OVER(PARTITION BY p_code, p_store ORDER BY p_total) 누적합계
FROM panmae;

SELECT p_code, p_store, p_date, p_qty, p_total,
    ROUND( RATIO_TO_REPORT(p_total) OVER(PARTITION BY p_code) * 100, 2) || '%' 매출비율
FROM panmae;

SELECT p_code, SUM(p_total) 매출합
    , RATIO_TO_REPORT(SUM(p_total)) OVER() 매출비율
    , ROUND( RATIO_TO_REPORT(SUM(p_total)) OVER() * 100, 2) || '%' "매출비율(%)"
FROM panmae
GROUP BY p_code
ORDER BY 1;

-- 1, 5, 6, 8, 12, 9
--1번
SELECT MAX(sal+NVL(comm, 0)) MAX
     , MIN(sal+NVL(comm, 0)) MIN
     , ROUND( AVG( sal+NVL(comm, 0) ), 1) AVG    
FROM emp;

--5번
SELECT deptno, ename, sal
    , SUM(sal) OVER(ORDER BY sal) TOTAL
FROM emp;

--6번
SELECT 
      MAX(DECODE(name, 'apple', price)) APPLE
     ,MAX(DECODE(name, 'grape', price)) GRAPE
     ,MAX(DECODE(name, 'orange', price)) ORANGE
FROM fruit;

--8번
SELECT deptno, ename, sal
    , SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) TOTAL
FROM emp;

--12번
SELECT l_code 대출종목코드, l_store 대출지점, l_date 대출일자, l_total 대출액, l_qty 대출건수
    , SUM(l_total) OVER(PARTITION BY l_code, l_store ORDER BY l_date) 누적대출금액
FROM loan;

--9번
SELECT deptno, ename, sal
    , SUM(sal) OVER() TOTAL_SAL
    , ROUND(RATIO_TO_REPORT(sal) OVER() * 100, 2) "%"
FROM emp
--GROUP BY deptno, ename, sal
ORDER BY sal DESC;

--11번
SELECT l_date, l_code, l_qty, l_total
     , SUM(l_total) OVER(ORDER BY l_date)
FROM loan
WHERE l_store = 1000;

--14번
SELECT deptno, name, pay, SUM(pay) OVER() "TOTAL PAY"
     , ROUND( RATIO_TO_REPORT(pay) OVER() * 100, 2 ) RATIO
FROM professor
ORDER BY pay DESC;

--15번
SELECT deptno, name, pay, SUM(pay) OVER(PARTITION BY deptno) TOTAL_DEPTNO,
    ROUND( RATIO_TO_REPORT(pay) OVER(PARTITION BY deptno) * 100, 2)RATIO
FROM professor
ORDER BY deptno, name;

--JOIN
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

--profno
SELECT  s.name STU_NAME, p.name PROF_NAME
FROM student s, professor p
WHERE s.profno = p.profno;

SELECT s.name 이름, d.dname 학과, p.name 지도교수
FROM student s, department d, professor p
WHERE s.deptno1 = d.deptno
    AND s.profno = p.profno;

SELECT *
FROM customer;
SELECT c.gname 고객명, c.point 포인트, g.gname 상품
FROM customer c, gift g
WHERE c.point BETWEEN g.g_start AND g.g_end;

SELECT std.name, s.total, h.grade
FROM student std, score s, hakjum h
WHERE std.studno = s.studno
    AND s.total BETWEEN h.min_point AND h.max_point;
    
SELECT s.name 학생이름, p.name 교수이름
FROM student s, professor p
WHERE s.profno = p.profno(+);

SELECT s.name 학생이름, p.name 교수이름
FROM student s, professor p
WHERE s.profno(+) = p.profno;