Oracle - ROUPING() 2 단계

(DB) Oracle/SQL.통계-단계별Study 2017. 1. 21. 12:48

[ Oracle - ROUPING() 2 단계 ]

 

☞  

 

1. partition by와 함께 사용하여 부서를 한번만 인쇄하기
   소계, 합계  명칭 보여주기

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)

SELECT DECODE(ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY NAME), 1, DEPT, '')DEPT
     , DECODE(GROUPING(NAME), 1, (DECODE(GROUPING(DEPT), 1, '총계', '소계')), NAME)NAME
     , COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME) 
-- GROUP BY CUBE(DEPT, NAME) 
 HAVING GROUPING(DEPT) + GROUPING(NAME) > 0 ;

 

 DEPT      NAME     COUNT(*)      SUM(SALARY)      GROUPING(DEPT)   GROUPING(NAME)
 ----------------------------------------------------------------------------------
 인사부    구하라       4          870000            0                 0
           이주리       3          480000            0                 0
           저미주       4          448000            0                 0
           소계         11        1798000            0                 1   <--
 총무부    강모리       3         1570000             0                 0
           구하라       4          840000             0                 0
           저미주       3          327000             0                 0
           소계        10         2737000             0                 1   <--
           강모리       3         1570000             1                 0   <--
           구하라       8         1710000             1                 0   <--
           이주리       3          480000             1                 0   <--
           저미주       7          775000             1                 0   <--
           총계        21         4535000             1                 1   <--

 

☞ 2. 부서별소계, 합계만 보여주기

 

  DEPT      COUNT(*)      SUM(SALARY)      GROUPING(DEPT)   GROUPING(NAME)
 ----------------------------------------------------------------------------------
 인사부        11         1798000            0                 1
 총무부        10        2737000             0                   1
 총계          21         4535000             1                   1

2.1 첫번째 방법

WITH salary AS ( .. )
SELECT DECODE(GROUPING(NAME), 1, (DECODE(GROUPING(DEPT), 1, '총계', DEPT)), DEPT)DEPT
     , COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME) 
 HAVING GROUPING(DEPT) + GROUPING(NAME) > 0 ;

--2.2 두번째 방법

WITH salary AS ( .. )
 SELECT DEPT
      , COUNT(*), SUM(SALARY), GROUPING(DEPT)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT) ;

☞  3. 부서별소계, 사원별합계, 총계만 보여주기

 

  TITLE      NAME     COUNT(*)      SUM(SALARY)      GROUPING(DEPT)   GROUPING(NAME)
 ----------------------------------------------------------------------------------
 부서별총계    인사부        11         1798000             0                 1
               총무부        10         2737000             0                 1
 사원별총계    강모리         3         1570000             1                 0
               구하라         8         1710000             1                 0
               이주리         3          480000             1                 0
               저미주         7          775000             1                 0
 총계                        21         4535000             1                 1

* 차후 공부를 해서 아래 SQL문은 좀더 TUNING 이 필요할 듯

WITH salary AS ( .. )
SELECT CASE WHEN DEPT = '총계' THEN '총계'
            WHEN ROW_NUMBER() OVER(ORDER BY DEPT) = 1 THEN '부서별총계'
            WHEN ROW_NUMBER() OVER(ORDER BY NAME) = 1 THEN '사원별총계'
       END TITLE
     , DECODE(DEPT_ID, 0, DEPT, (DECODE(NAME_ID, 1, '', NAME))) NAME
     , CNT, SALARY, DEPT_ID, NAME_ID
  FROM (
        SELECT DECODE(GROUPING(DEPT) + GROUPING(NAME), 2, '총계', DEPT) DEPT, NAME
             , COUNT(*) CNT, SUM(SALARY) SALARY, GROUPING(DEPT) DEPT_ID,GROUPING(NAME) NAME_ID
          FROM salary
         WHERE SMONTH BETWEEN '01' AND '12'
         GROUP BY CUBE(DEPT, NAME) 
        HAVING GROUPING(DEPT) + GROUPING(NAME) > 0 
       ) A
 ORDER  BY DEPT_ID, NAME, NAME_ID ;

 

저작자표시

'(DB) Oracle > SQL.통계-단계별Study' 카테고리의 다른 글

Oracle - OVER 1단계  (0) 2017.01.21
Oracle - GROUPING() 3 단계  (0) 2017.01.21
Oracle - GROUPING() 1 단계  (0) 2017.01.21
Oracel - CUBE 1 단계  (0) 2017.01.21
Oracle - ROLLUP 1 단계  (0) 2017.01.21
Posted by 농부지기
,

Oracle - GROUPING() 1 단계

(DB) Oracle/SQL.통계-단계별Study 2017. 1. 21. 12:48

[ Oracle - GROUPING() 1 단계 ]

 


☞  정의

 

GROUPING()함수 정의
1. GROUPING() 함수 ROLLUP()과 CUBE() 함수에 모두 사용할 수 있다.
2. GROUPING함수는  SQL 문 결과가  GROUP BY에 의해서 나온 결과는 0을 반환하고
   ROLLUP이나 CUBE에 의해서 산출된 결과에는 1을 반환한다.
3. 결론은, GROUPING함수는 SQL 문에 의해서 나온결과가  GROUP BY 자료인지,
   ROLLUP, CUBE에 의해서 나온결과인지를 알 수 있도록 지원해주는 함수 이다.

☞

 

--SQL1

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY CUBE(DEPT, NAME)
 ORDER BY DEPT, NAME;

 DEPT      NAME     COUNT(*)      SUM(SALARY)      GROUPING(DEPT)   GROUPING(NAME)
 ----------------------------------------------------------------------------------
 인사부    구하라       4          870000            0                 0
 인사부    이주리       3          480000            0                 0
 인사부    저미주       4          448000            0                 0
 인사부                11         1798000            0                 1   <--CUBE에의한 결과
 총무부    강모리     3         1570000             0                 0
 총무부    구하라     4          840000             0                 0
 총무부    저미주     3          327000             0                 0
 총무부             10        2737000             0                   1   <--CUBE에의한 결과
        강모리     3         1570000             1                 0   <--CUBE에 의한결과
        구하라     8         1710000             1                 0   <--CUBE에 의한결과
        이주리     3          480000             1                 0   <--CUBE에 의한결과
        저미주     7          775000             1                 0   <--CUBE에 의한결과
                 21         4535000             1                   1   <--CUBE에의한 결과

* 위 결과중 맨 아래자료는 (부서, 이름에 대한 총계 이므로 1,1의 결과가 나왔음)
SQL2 : 기타 추가 SQL문들
       WITH는 위자료를 활용

SELECT DEPT, NAME, COUNT(*), SUM(SALARY), GROUPING(DEPT), GROUPING(NAME)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY DEPT, CUBE(NAME)   --GROUP BY CUBE(DEPT), NAME
 ORDER BY DEPT, NAME;

 

 

저작자표시

'(DB) Oracle > SQL.통계-단계별Study' 카테고리의 다른 글

Oracle - OVER 1단계  (0) 2017.01.21
Oracle - GROUPING() 3 단계  (0) 2017.01.21
Oracle - ROUPING() 2 단계  (0) 2017.01.21
Oracel - CUBE 1 단계  (0) 2017.01.21
Oracle - ROLLUP 1 단계  (0) 2017.01.21
Posted by 농부지기
,

Oracel - CUBE 1 단계

(DB) Oracle/SQL.통계-단계별Study 2017. 1. 21. 12:47

[ Oracel - CUBE  1 단계 ]

 

☞  정의

 

1. DATA가 아래와 같을 경우,  
   부서별총계, 직원별총계,  부서별,직원별 총계 를 구하기
   [아래 자료를 보면  직원은 여러부서로 이동가능 하므로.. 
        아래와 같은 자료가 존재 할 수 있다]
   * 만약, 이 ROLLUP으로 하게 된다면, ROLLUP을 2개 사용후 UNION 을 해야 한다.
     아래와 같은 결과가 나오기 위해서 SQL 문을 구현 (총3가지의 SQL 문으로 해 봤음)

☞ 결과

 

DEPT     NAME     COUNT(*)      SUM(SALARY)
 ---------------------------------------------
 인사부   구하라       4             870000
 인사부   이주리       3             480000
 인사부   저미주       4             448000
 인사부               11            1798000
 총무부   강모리       3            1570000
 총무부   구하라       4             840000
 총무부   저미주       3             327000
 총무부                10            2737000
                       21            4535000  <--위에서 여기까는 첫번재 ROLLUP을이용한 결과
          강모리       3            1570000
          구하라       8            1710000
          이주리       3             480000
          저미주       7             775000
                       21            4535000  <-- 여기까지는  두번재 ROLLUP을 이용한결과

** 위 결과를 보면 총계가 2개나왔다.  CUBE를 사용하게 되면 자연스럽게 1개만 나오게 된다

☞  SQL1

 

WITH salary AS

(

    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME)

UNION ALL

SELECT '', NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(NAME) ;

☞   SQL2
 

-- UNION 만 했을 때의 결과 (data는 위 with절로 처리)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME)

UNION   

SELECT '', NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(NAME) ;

☞  SQL3

 

 

-- 아래쪽 SQL 문을 GROUP BY 로만  했을 때의 결과 (data는 위 with절로 처리)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME)

UNION ALL

SELECT '', NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY NAME ;

 

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY CUBE(DEPT, NAME)
 ORDER BY DEPT, NAME ;

 

 

저작자표시

'(DB) Oracle > SQL.통계-단계별Study' 카테고리의 다른 글

Oracle - OVER 1단계  (0) 2017.01.21
Oracle - GROUPING() 3 단계  (0) 2017.01.21
Oracle - ROUPING() 2 단계  (0) 2017.01.21
Oracle - GROUPING() 1 단계  (0) 2017.01.21
Oracle - ROLLUP 1 단계  (0) 2017.01.21
Posted by 농부지기
,

Oracle - ROLLUP 1 단계

(DB) Oracle/SQL.통계-단계별Study 2017. 1. 21. 12:46

[ Oracle - ROLLUP  1 단계 ]

 

1. 정의 
   ROLLUP을 사용하면 그룹을 계층구조로 생성하고 그 계층구조 안에서 통계(그룹함수 적용)를 산출한다.
   만약, 그룹의 계층구조를 벗어나서 각 그룹이 독립적으로 존재할 경우의 통계도 필요하다면 
   CUBE를 사용한다.

☞  1. 직원별로 급여집계

 

WITH salary AS
(
    SELECT '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual
)
SELECT NAME, SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY NAME;

NAME       SUM(SALARY)
-------- ------------
구하라      870000
저미주      327000

☞ 2. 직원별로 급여집계와 총계 구하기

 

WITH salary AS
(
    SELECT '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual
)
SELECT NAME, SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(NAME);


 NAME     SUM(SALARY)
-------- ------------
구하라       870000
저미주       327000
             327000    <-- ROLLUP을 사용한 결과 NAME항목 전체 대한 총계가 나 왔음

☞  3. 부서별, 직원별 급여집계, 급여개월수  총계 구하기

 

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual
)
SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY DEPT, NAME;


DEPT       NAME        COUNT(*)     SUM(SALARY)
------------------------------------------
인사부    구하라       4            870000
인사부    이주리       3            480000
총무부    강모리       3           1570000
총무부    저미주       3            327000

☞   
 

4. 위 DATA를 가지고 아래와 같은 결과를 얻는 SQL문

DEPT       NAME        COUNT(*)     SUM(SALARY)
------------------------------------------
인사부    구하라         4            870000  <--①
인사부    이주리         3            480000
인사부                   7           1350000  <--②
총무부    강모리         3           1570000
총무부    저미주         3            327000
총무부                   6           1897000
                        13            3247000  <--③

① ROLLUP(DEPT, NAME)한 결과    DEPT, NAME에 대한 합계 조회
② ROLLUP(DEPT, NAME)한 결과    DEPT 에 대한 합계 조회
③ ROLLUP(DEPT, NAME)한 결과    전체계월수, 전체급여합계액 조회

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual
)

SELECT DEPT, NAME, COUNT(*), SUM(SALARY)
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY ROLLUP(DEPT, NAME);

☞  정리

 

-. 정의 : 1.그룹핑된 결과에 그룹별합계정보를 추가하여 조회한다.
          2. ROLLUP함수는 주로 GROUP BY와 같이 사용되며 주어진 Grouping조건에 따라 각 그룹의 
             그룹핑 항목이 있으면 우측부터 하나씩 제외하면서 그 결과를 반환한다.
-. ROLLUP(DEPT, NAME)을 하게 되면
   1. 먼저      , GROUP BY DEPT, NAME 과 같은 결과 가 나오고
   2. 그 다음에 , ROLLUP(DEPT, NAME)에 의해서   DEPT 에 대한 합계 조회
   3. 마지막으로, ROLLUP(DEPT, NAME)에 의해서   총계가 조회 됨  

저작자표시

'(DB) Oracle > SQL.통계-단계별Study' 카테고리의 다른 글

Oracle - OVER 1단계  (0) 2017.01.21
Oracle - GROUPING() 3 단계  (0) 2017.01.21
Oracle - ROUPING() 2 단계  (0) 2017.01.21
Oracle - GROUPING() 1 단계  (0) 2017.01.21
Oracel - CUBE 1 단계  (0) 2017.01.21
Posted by 농부지기
,

Oracle - ROLLUP

(DB) Oracle/SQL.통계-통계함수 2017. 1. 21. 12:44

[ Oracle - ROLLUP ]

 

☞ ROLLUP 예제1

 

원하는 결과 ] 
    --------------------------------------------------------------------------
      계정명           전표일자     일련번호     차변금액         대변금액 
    --------------------------------------------------------------------------
    인건비            2010.01.01        1           100
    인건비            2010.01.01        2           110
    인건비            2010.03.03        1           110
    인건비            2010.03.04        1                            50
    인건비계                                        270
    차량유지비        2010.02.14      300            50
    차량유지비       2010.02.26        3            20
    차량유지비        2010.03.15       50           100
    차량유지비계                                    150
    총계                                            570              50 

SQL : 
   WITH DATA1 AS (
       SELECT '인건비'     AS NAME, '2010.01.01' AS DATE1, 1   AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고1' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.01.01' AS DATE1, 1   AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고2' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.02.01' AS DATE1, 2   AS NUMBER1, 110  AS AMT1, NULL  AS AMT2, '비고3' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.03.03' AS DATE1, 1   AS NUMBER1, 110  AS AMT1, NULL  AS AMT2, '비고4' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.03.04' AS DATE1, 1   AS NUMBER1, NULL AS AMT1, 50    AS AMT2, '비고5' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.02.14' AS DATE1, 300 AS NUMBER1, 50   AS AMT1, NULL  AS AMT2, '비고6' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.02.26' AS DATE1, 3   AS NUMBER1, NULL AS AMT1, 20    AS AMT2, '비고7' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.03.15' AS DATE1, 50  AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고8' AS NOTES FROM DUAL 
    )

     SELECT DECODE(A.GUBUN, 0, A.NAME, 1, A.NAME || ' 계', 3, '총계')   AS "계정명"
          , A.DATE1  AS "전표일자"
          , A.NOTES
          , DECODE(A.GUBUN, 0, A.NUMBER1, 1, NULL)                      AS "일련번호"
          , DECODE(A.GUBUN, 0, A.AMT1, 1, A.AMT1 - A.AMT2, 3, AMT1)     AS "차변금액"
          , DECODE(A.GUBUN, 0, A.AMT2, 1, NULL, 3, AMT2)                AS "대변금액"
          , GUBUN
       FROM (
             SELECT A.NAME
                  , A.DATE1
                  , A.NOTES
                  , MIN(A.NUMBER1)   AS NUMBER1
                  , SUM(AMT1)    AS AMT1
                  , SUM(AMT2)    AS AMT2
                  , GROUPING_ID(A.NAME, A.DATE1) AS GUBUN
               FROM DATA1 A
              GROUP BY ROLLUP(a.name, (a.date1, a.number1, a.notes))
             ) A ; 


 
 

 

 

저작자표시

'(DB) Oracle > SQL.통계-통계함수' 카테고리의 다른 글

Oracle - ROLLUP예제1  (0) 2017.01.21
Oracle - RANK()  (0) 2017.01.21
Oracle - RANK()  (0) 2017.01.21
Oracle - LAG.LEAD()  (0) 2017.01.21
Oracle - GROUPING / GROUPING_ID  (0) 2017.01.21
Posted by 농부지기
,

Oracle - GROUPING / GROUPING_ID

(DB) Oracle/SQL.통계-통계함수 2017. 1. 21. 12:43

[ Oracle - GROUPING / GROUPING_ID ]

 

 

☞ GROUPING_ID

 

정의 : GROUPING_ID함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환.
       GROUPING_ID함수는 행과 관련된 GROUPING 비트 벡터에 대응하는 수치를 반환한다. 
        GROUPING_ID는 ROLLUP,CUBE,GROUPING함수와 같은 GROUP BY 확장 기능을 포함한 SELECT문장에서
       적용할수 있다. 많은 GROUP BY 표현을 가지는 쿼리에서,많은 GROUPING함수를 필요로 하는 
       특정한 행의 GROUP BY 레벨을 지정하기 위해서는 복장한 SQL이 요구된다. 
       GROUPING_ID는 이런 경우 유용하다.

       GROUPING_ID함수는 다수의 GROUPING함수의 결과와 비트 벡터(1과 0의 문자열)를 연결하는 것과
       동일하다. GROUPING_ID를 이용하여서 다수의 GROUPING함수를 사용하지 않고서,
       표현하기 위한 행의 필터 조건이 간단해진다. 행 필터링은 원하는 행이 GROUPING_ID=n의 
       단일 조건으로 정의 될수 있어서 더 쉬워진다. 
       함수는 단일 테이블에서 다수의 집계의 레벨을 저장할때 유용하다.

SQL 예 : SELECT channel_id, promo_id, sum(amount_sold) s_sales  
               , GROUPING(channel_id) gc
              , GROUPING(promo_id) gp
              , GROUPING_ID(channel_id, promo_id) gcp
              , GROUPING_ID(promo_id, channel_id) gpc 
         FROM   sales 
         WHERE  promo_id > 496 
         GROUP BY CUBE(channel_id, promo_id) ;  

결과)

    C  PROMO_ID      S_SALES       GC         GP        GCP       GPC 
    - ----------   ---------- ---------- ---------- ---------- ---------- 
    C        497     26094.35       0         0         0         0 
    C        498      22272.4       0         0         0         0 
    C        499      19616.8       0         0         0         0 
    C       9999     87781668       0         0         0         0 
    C              87849651.6       0         1         1         2 
    I        497      50325.8       0         0         0         0 
    I        498      52215.4       0         0         0         0 
    I        499     58445.85       0         0         0         0 
    I       9999    169497409       0         0         0         0 
    I               169658396       0         1         1         2 
    P        497     31141.75       0         0         0         0 
    P        498      46942.8       0         0         0         0 
    P        499        24156       0         0         0         0 
    P       9999     70890248       0         0         0         0 
    P              70992488.6       0         1         1         2 
    S        497    110629.75       0         0         0         0 
    S        498     82937.25       0         0         0         0 
    S        499     80999.15       0         0         0         0 
    S               267480357       0         1         1         2 
    T        497       8319.6       0         0         0         0 
    T        498      5347.65       0         0         0         0 
    T        499        19781       0         0         0         0 
    T       9999     28095689       0         0         0         0 
    T              28129137.3       0         1         1         2 
             497    226511.25       1         0         2         1 
             498     209715.5       1         0         2         1 
             499     202998.8       1         0         2         1 
             9999    623470805       1         0         2         1 
                     624110031       1         1         3         3 

설명)
   1. GROUPING(channel_id) gc
         : 0 -> channel_id가 Record에 나오는 ROW는 0
                즉, CUBE, ROLL_UP 후 해당 컬럼별로 SUM한 결과가 나오는 ROW는 0
           1 -> channel_id가 Record에 나오지 않는 ROW는 1
                즉, CUBE, ROLL_UP 후 총 SUM한 결과가 나오는 ROW는 1

   2. GROUPING_ID(channel_id, promo_id) gcp
         : 0 -> channel_id, promo_id 가 Record에 나오는 ROW는 0
           1 -> channel_id는 존재하고, promo_id는 미존재하는 Record일 경우 ROW는 1
           2 -> channel_id는 미존재하고, promo_id는 존재하는 Record일 경우 ROW는 2
           .... 

 


 
 

 

저작자표시

'(DB) Oracle > SQL.통계-통계함수' 카테고리의 다른 글

Oracle - ROLLUP예제1  (0) 2017.01.21
Oracle - RANK()  (0) 2017.01.21
Oracle - RANK()  (0) 2017.01.21
Oracle - LAG.LEAD()  (0) 2017.01.21
Oracle - ROLLUP  (0) 2017.01.21
Posted by 농부지기
,

Oracle - 모든 테이블에서 특정 컬럼에 대한 특정값 찾기

(DB) Oracle/SELECT-System.Schema 2017. 1. 19. 23:54

[  ◎ 모든 테이블에서 특정 컬럼에 대한 특정값 찾기  ]     

 

 

☞   모든 테이블에서 PLAN_ID = 9 인 값 찾기

 


SELECT table_name
     , TO_NUMBER ( dbms_xmlgen.getxmltype( 'SELECT COUNT(*) c FROM ' || table_name || ' WHERE PLAN_ID = ' || '9' ).Extract('//text()') ) cnt
  FROM user_tables
 --WHERE table_name in ( 특정스키마 )
  ;
 

저작자표시
Posted by 농부지기
,

Oracle - PARTITION BY OUTER JOIN

(DB) Oracle/SELECT-JOIN 2017. 1. 19. 23:53

                        [  ◎ PARTITION BY OUTER JOIN ]     
 

 

 

☞  요청사항 : 매장, 년월, 매출액이 존재 하는 테이블 있다.
               2014년도 매장별, 월별, 매출액을 조회하라.
               이때 2014년도 상반기 자료를 조회 하는데, 각 매장별 매출액이 없는 월도 0 으로 조회 되어야 된다.
               즉, 모든 매장에 2014.01 ~ 2014.06 월까지 모두 조회 되어야 한다.   

 


첫 번째 방법 : 아래 처럼 단순 JOIN으로 할 경우 모든월이 조회 되지 않는다.

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT A.SHOP, B.YM, SUM(A.AMT) AS MM_AMT
  FROM YM B JOIN SALE A
         ON (A.YM = B.YM)
 GROUP BY A.SHOP, B.YM ;
 
-

 


두 번째 방법 : 모든매장, 모든월이 존재 하는 테이블 만들어서 OUTER JOIN으로 한다.
               이때 불필요하게 매장목록만 존재하는 테이블이 별도로 존재해야 된다..

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  SHOP_LIST(SHOP) AS
   ( SELECT '동대문' FROM DUAL UNION ALL
     SELECT 'GS마켓' FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT X.SHOP, Y.YM, SUM(A.AMT) AS MM_AMT
  FROM SHOP_LIST X JOIN YM Y
              ON (1=1)
                   LEFT OUTER JOIN SALE A
              ON (    A.SHOP = X.SHOP
                  AND A.YM   = Y.YM )
 GROUP BY X.SHOP, Y.YM
 ORDER BY X.SHOP, Y.YM ;
 

☞   

 

세 번째 방법 : PARTITION BY OUTER JOIN을 사용하여 처리한다.

- 중요 : 이때 LEFT OUTER JOIN 이라는 문구와  PARTITON BY 절의 위치가 변경되면 안된다.

WITH SALE (SHOP, YM, AMT) AS
   ( SELECT '동대문', '201401',  12800 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201403',  22700 FROM DUAL UNION ALL
     SELECT '동대문', '201404',  32350 FROM DUAL UNION ALL
     SELECT '동대문', '201405',  22000 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201401',  13020 FROM DUAL UNION ALL
     SELECT 'GS마켓', '201404',  14100 FROM DUAL
   )
,  YM AS
   ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), LEVEL - 1), 'YYYYMM') AS YM
       FROM DUAL
      CONNECT BY LEVEL <= 06
   )
SELECT A.SHOP, B.YM, SUM(A.AMT) AS MM_AMT
  FROM YM B LEFT OUTER JOIN SALE A PARTITION BY (A.SHOP)
         ON (A.YM = B.YM)
 GROUP BY A.SHOP, B.YM
 ORDER BY A.SHOP, B.YM;
 

     

  

 

 

저작자표시
Posted by 농부지기
,

Oracle - 정규식 - AND, OR 검색

(DB) Oracle/SELECT-정규식 2017. 1. 19. 23:52

[ Oracle - 정규식 - AND, OR 검색 ]

 

 

 

☞ 1. OR 검색 : 문자열에서 'CD  or  12  or  가나' 인 경우 찾기

 


WITH A AS
  (SELECT 'ABCD 1234 가나다라' AS ald UNION ALL
   SELECT 'CDEF 3456 다라마바' AS ald UNION ALL
   SELECT 'EFGH 5678 마바사아' AS ald
  )
SELECT * FROM A
 WHERE REGEXP_LIKE ( ald, 'CD|12|가나' );
 

☞ 2. AND 검색 : 문자열에서 'C  and  13 and  마' 인 경우 찾기

 

 


WITH A AS
  (SELECT 'ABCD 1234 가나다라' AS ald UNION ALL
   SELECT 'CDEF 3456 다라마바' AS ald UNION ALL
   SELECT 'EFGH 5678 마바사아' AS ald
  )
SELECT *
  FROM (SELECT REGEXP_COUNT ( ald, 'C|3|마' ) n
             , ald
          FROM A
       ) B
 WHERE B.n = REGEXP_COUNT ( 'C|3|마', '\|' ) + 1 ;
 

 

 

 

저작자표시

'(DB) Oracle > SELECT-정규식' 카테고리의 다른 글

Oracle - 정규식이란?  (0) 2017.01.19
Posted by 농부지기
,

Oracle - 정규식이란?

(DB) Oracle/SELECT-정규식 2017. 1. 19. 23:51

                   [  ◎ 정규식이란? ]     

☞ 정의
   

☞ 기본 메타 문자

 

 .

 모든 문자 일치

 |

 OR 왼쪽 문자(혹은 패턴) 혹은 오른쪽 문자(혹은 패턴)과 일치

 []

 문자 집합 구성원 중 하나와 일치
 예) [abc] : a 혹은 b 혹은 c

 [^]

 문자 집합 구성원을 제외하고 일치, [^abc] : a,b,c 제외한 모든 문자

 -

 범위 정의 ([A-Z]와 같은 형태로 대문자 A에서 Z사이의 문자를 의미)

 \

 다음에 오는 문자를 이스케이프1

기본 메타 문자는 글자 하나의 의미를 말한다. 이자체만으로는 큰 활용도가 없고 다른 기능들과 같이 쓰일때 의미가 있다. 일단 이런게 있구나 하고 넘어가고, 밑에 같이 사용될 때 다시와서 참조해야 제대로 이해 할 수 있을 것이다.

☞ 수량자

 

 *

 앞의 문자나 부분식2이 0개 이상 탐욕적으로 찾기

 *?

 탐욕적 수량자 *를 게으른(lazy) 수량자로 바꿔 찾기

 +

 앞의 문자나 부분식을 하나 이상 탐욕적으로 찾기

 +?

 탐욕적 수량자 +를 게으른(lazy) 수량자로 바꿔 찾기

 ?

 앞의 문자나 부분식을 0개나 1개 찾기

 {n}

 앞의 문자나 부분식이 정확히 n번 일치하는 경우 찾기

 {m,n}

 앞의 문자나 부분식이 m번에서 n번 일치하는 경우 찾기

 {n,}

 앞의 문자나 부분식이 n번 이상인 경우를 탐욕적으로 찾기

 {n,}?

 탐욕적 수량자 {n,}를 게으른(lazy) 수량자로 바꿔 찾기

문자 하나가 아니라 조건에 해당하는 붙어있는 여러개의 문자를 찾는다는 것이 핵심이다. *, + 의 차이를 이해해야 한다. *는 없는 경우가 포함되고, +는 무조건 하나는 있어야 한다는 의미다. 예를 들어, a*4와 a+4 모두 "123aaa456"를 빨간색처럼 찾는다.  하지만 "123456" 인 경우에는 a*4인 경우에만 4를 찾고(123456), a+4는 아무것도 찾지 못한다. 왜냐하면 *는 "a" 없이 "4"만 있어도 되지만 +는 a가 반드시 하나는 있어야 하기 때문이다.

 

다음으로 탐욕적 수량자와 게으른 수량자를 이해해야 한다. 이부분은 매우 중요하면서도 처음엔 어려울 수 있는 부분이다. 탐욕적 수량자의 핵심은 조건에 맞지 않을 때까지 하나의 패턴으로 인식하는 것이고, 게으른 수량자의 핵심은 조건에 맞으면 욕심부리지 말고 거기서 끝내는 것이다. 다음의 예제를 보자.

 

Test0<div>Test1</div>Test<div>Test2</div>Test4

다음은 정규표현식에 따른 결과다.

  • <div>.*</div> : Test0<div>Test1</div>Test<div>Test2</div>Test4 => <div>를 처음 발견한 후 </div>를 발견했지만 탐욕적으로 그 다음 </div>가 없는지 찾는다. 로직 상에는 <div>를 찾으면 </div>를 뒤에서부터 찾는다고 한다.
  • <div>.*?</div> : Test0<div>Test1</div>Test<div>Test2</div>Test4 => <div>를 발견한 이후에 </div>를 만나면 욕심부리지 않고 여기서 끝낸다.

 

여기서는 "?"의 의미가 헷갈리지 않도록 정리를 잘 해야 한다.*, +, {} 다음에 나오는 물음표(?)는 탐욕적 수량자를 게으른 수량자로 바꾸는 의미를 가지지만, 패턴 다음에 오는 물음표는 해당 패턴이 있을 수도 있다의 의미를 가진다. 전후방탐색에서도 물음표의 의미가 달라지므로 확실한 의미를 파악해두는 것이 좋다.

☞ 위치 지정

 

 ^

 입력 문자열의 시작 부분에서 그 다음 나오는 문자나 부분식과 일치하는지 검사

 \A

 어떤 정규식에서는 ^의 역할을 함

 $

 문자열의 끝과 일치

 \Z

 어떤 정규식에서는 $의 역할을 함

 \b

 단어 경계(단어와 공백 사이의 위치)와 일치
 예) 'st\b'는 "test"의 st는 찾지만, "tester"의 st는 찾지 않음
 !주의! test test 에서 test 사이의 빈공간인 space와는 다른 의미

 \B

 \b와 반대(비단어 경계)로 일치. 즉 "\b"의 예제의 반대 결과가 나옴

말이 좀 어려운데, 결국은 글자자체를 찾는 물리적 의미가 아니라 내부적인 의미를 찾는 논리적 수행만 한다. 이것도 이자체만으로는 의미가 없으며  ^, \b, \B 다음이나 \b, \B, $ 전에 나오는 문자나 부분식과 함께 동작한다.

☞ 특수한 문자

 

 [\b]

 역스페이스

 \cx

 x로 표시된 제어문자 찾기. x는 [A-Za-z] 이어야 함. 아니면 c는 리터럴 'c'로 간주

 예) \cM = Control-M 이나 캐리지 리턴 문자. 

 \d

 모든 숫자와 일치. [0-9]와 동일

 \D

 \d와 반대. [^0-9]와 동일

 \f

 용지 공급 문자 찾기. 페이지 넘기기(formfeed) = \x0a3, \cL

 \n

 줄 바꿈 문자 찾기. \x0a, \cJ

 \r

 캐리지 리턴. \x0d, \cM

 \s

 공백, 탭, 용지 공급 등과 같은 문자 찾기. [\f\n\r\t\v]

 \S

 \s와 반대로 일치. [^\f\n\r\t\v]

 \t

 탭 문자 찾기. \x09, \cI

 \v

 세로 탭 문자 찾기. \x0b, \cK

 \w

 영숫자 문자나 밑줄과 일치. [a-zA-Z0-9_]

 \W

 \w와 반대로 일치. [^a-zA-Z0-9_]

 \xn

 n으로 표시된 16진수 이스케이프 값과 일치. 16진수는 정확히 2자리여야 함

 \0n

 n으로 표시된 8진수 숫자와 일치. 가급적 사용하지 않는 것이 좋음

하나하나 [] 안에 넣어주어야 하는 문자들을 미리 정의해둔 것으로 이해하면 된다. []를 사용하는 것과 동작의 차이는 없지만 패턴 글자의 수를 줄일 수 있기 때문에 알아두면 보기 편하다.

☞ 역참조와 전후방탐색

 

(pattern)

 하위 표현식 정의. 패턴을 찾아 일치하는 항목을 캡처하는 부분식. 가로 자체 '('나 ')'를 찾고 싶으면 '\'를 붙여 이스케이프하면 된다. '\(', '\)'

\1

 첫 번째 일치한 하위 표현식, 두 번째 일치한 하위 표현식은 \2로 표기

 (?=pattern)

 전방탐색

 (?!pattern)

 부정형 전방탐색

 ?(BR4)true

 조건 지정

 ?(BR)true|false

 else 표현식 조건 지정

()는 []와 전혀 다른 의미라는 사실을 머리에 각인해야 한다. 이 파트가 정규표현식에서 가장 어려운 부분이며 프로그래밍 할 때 가장 많이 사용되는 부분이기도 하다. 가로로 묶은 하위표현식은 역참조 시 사용하기도 하지만, 프로그래밍에서는 하위표현식에 해당하는 문자열을 배열이나 그룹으로 따로 제공해 주기 때문에 유용하게 사용할 수 있다. 

☞ 대소문자 변환

 

 \E

 \L 혹은 \U 변환을 끝냄

 \l

다음에 오는 글자를 소문자로 변환 

 \L

 \E를 만날 때까지 모든 문자를 소문자로 변환

 \u

 다음에 오는 글자를 대문자로 변환

 \U

 \E를 만날 때까지 모든 문자를 대문자로 변환

문자열을 찾은 후 이를 바꾸는 용도로 사용한다. 찾기만 하려고 정규식을 사용한다면 필요없는 부분이기도 하다. 

☞ 옵션

 

i

 ignore case, 대소문자를 무시하여 찾음. 굳이 [a-zA-Z]라고 할 필요가 없음

 g

 global, 찾는 패턴을 하나만 찾지 말고 계속 해서 찾음. 탐욕적 수량자처럼 일치하는 구간을 늘리는 것이 아니라 일치하는 패턴의 개수가 늘어남

 m

 multiline, 다중행 모드라고도 하며 라인 별로 처리하는 것이 아니라 입력 문자열에 줄바꿈이 있어도 이를 특수문자로 변환하여 하나로 봄. 가장 큰 차이를 경험할 수 있는 기능은 ^와 $임.

 
 
 

1. [] : 대괄호 안에 값을 넣은 기준으로 찾을 범위를 지정
        예) [abc], [ABC], [0123]
2. -  : 마이너스 앞뒤에 존재하는 내에서 찾을 범위를 지정
        예) 1-5,  A-C, A-Z, a-m, 가-힝, ㄱ-ㅎ
3.

  1. [/^] : 정규식 선언의 시작을 알림.
2. [$/] : 정규식 선언의 끝을 알림
☞ 정규식 Function

 

1. REGXP_REPLACE :
2. REGXP_SUBSTR  :
3. REGEXP_COUNT  :
4. REGEXP_LIKE   :

☞

  참고 URL : http://skillsocius.com/220184877136

저작자표시

'(DB) Oracle > SELECT-정규식' 카테고리의 다른 글

Oracle - 정규식 - AND, OR 검색  (0) 2017.01.19
Posted by 농부지기
,

티스토리툴바