Oracle WINDOW 함수, 분석함수, 누적급여합계, 최고금액, 전일매출차액 출력

27 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 이전사원급여합계

FROM TEMP

 

 

28, 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 부서별 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (PARTITION BY DEPT_CODE

                                       ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 부서별이전사원급여합계

FROM TEMP

 

 

29 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼 3개를 생성하여 첫번째는 사원들의 급여의 총액

두 번째는 사원들의 누적급여총액 그리고 또 다른 하나의 세 번째는 두 번째를 역순으로 표시하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS BETWEEN UNBOUNDED PRECEDING

                                       AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

 

         ,SUM(SALARY) OVER (ORDER BY SALARY

                                      ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

 

         ,SUM(SALARY) OVER (ORDER BY SALARY DESC

                                      ROWS BETWEEN CURRENT ROW

                                      AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

 

 

30 29번 결과 값을 참고하여 사원이 아닌 부서별로 사원들의 총액, 부서별 사원들의 누적급여액 등을 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS BETWEEN UNBOUNDED PRECEDING

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

 

               ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

 

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY DESC

                                                            ROWS BETWEEN CURRENT ROW

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

 

 

31 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

31번 문제는 PARTITION, GROUP, RANGE 활용 문제이다

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

                 ROUND(AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_SITE

                                                                                              ORDER BY SALE_DATE

                                                                                              RANGE INTERVAL '2' DAY PRECEDING)) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

 

 

32 각 ROW의 판매액, 동일일자/ 동일품목의 최대판매액, 최대판매액사업장, 해당 사업장 최소판매액, 최소판매액 사업장을 구하여라

SELECT SALE_DATE, SALE_ITEM, SALE_AMT, SALE_SITE

          ,FIRST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                    ORDER BY SALE_AMT DESC

                                                    ROWS BETWEEN UNBOUNDED PRECEDING

                                                    AND UNBOUNDED FOLLOWING ) AS 최고판매액

 

         ,FIRST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                   ORDER BY SALE_AMT DESC

                                                   ROWS BETWEEN UNBOUNDED PRECEDING

                                                   AND UNBOUNDED FOLLOWING ) AS 최고판매사업장

 

        ,LAST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매액

 

        ,LAST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매사업장

FROM SALE_HIST;

 

 

33 SALE_HIST의 자료를 이용하여‘01’ 사업장‘ PENCIL’ 품목의 일자별 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER ( ORDER BY SALE_DATE

                                          RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01'

 

 

34 품목별/일자별 과거 판매매액을 모두 이용하는 이동평균값을 구하라

SELECT SALE_ITEM , SALE_DATE, SUM(SALE_AMT)

          ,AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_ITEM

                                                 ORDER BY SALE_DATE

                                                 ROWS UNBOUNDED PRECEDING ) AS 품목일자별이동평균

FROM SALE_HIST

GROUP BY SALE_ITEM , SALE_DATE

 

 

35 SALE_HIST의 자료를 이용하여 “01”사업장 PENCIL 품목에 대해 일자별 매출액과 전일 매출, 당일과 전일의 매출액 차이를 구하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT

          , LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출

          , SALE_AMT - LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출차액

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’