복수행 함수

단일 행 함수와 달리 한꺼번에 여러 건의 데이터가 함수로 입력됨.



GROUP 함수종류

대부분의 그룹함수는 함수에 * 를 사용하면 Null 을 포함하고 해당 컬럼에 데이터가 있는 경우만 ( 즉 NULL 값을 제외하고 ) 작업을 해서 출력함.


   함수명

 COUNT

    입력되는 데이터들이 총 건수를 출력 

 SUM

    합계 

 AVG

    평균값 

 MAX

    최대값 

 MIN

    최소값 

 STDDEV

    표준편차 

 VARIANCE

    분산 값 

 ROLLUP

    소계값 

 CUBE

    소계 및 전체 총계 
 GROUPINGSET

    한번의 쿼리로 여러 개의 함수들을 그룹으로 수행 가능




  • COUNT() : 입력되는 데이터의 총 건수를 반환
    • SCOTT>SELECT COUNT(*), COUNT(comm)
  2  FROM emp;

  COUNT(*) COUNT(COMM)
---------- -----------
        13           4
  • SUM() : 합계값을 반환
    • SCOTT>SET null --null-- ;
SCOTT>SELECT ename, comm
  2  FROM emp;

ENAME            COMM
---------- ----------
SMITH      --null--
ALLEN             300
WARD              500
JONES      --null--
MARTIN           1400
BLAKE      --null--
CLARK      --null--
SCOTT      --null--
KING       --null--
TURNER              0
JAMES      --null--
FORD       --null--
MILLER     --null--


    

SCOTT>SELECT COUNT(comm), SUM(comm)
  2  FROM emp;

COUNT(COMM)  SUM(COMM)
----------- ----------
          4       2200
  • AVG() : 평균값 변환

    • 주의해야할 사항은 모든 그룹함수를 사용할 때, null값이 존재할 경우 자동으로 null값은 제외하고 계산하기 때문에 의도치 않는 오류가 발생할 수 있다는 점이다. 그래서 NVL함수로 null값을 처리해줘야 정확한 계산이 나온다.

    SCOTT>SELECT COUNT(comm), SUM(comm), AVG(comm)
  2  FROM emp;

COUNT(COMM)  SUM(COMM)  AVG(COMM)
----------- ---------- ----------
          4       2200        550
    SCOTT>SELECT COUNT(*), SUM(comm), AVG(NVL(comm,0))
  2  FROM emp;

  COUNT(*)  SUM(COMM) AVG(NVL(COMM,0))
---------- ---------- ----------------
        13       2200       169.230769
  • MAX() / MIN() : 최대 / 최소값 반환

    • 여러 건의 데이터를 입력 받아서 순서대로 정렬을 하고 그중 최대값/최소값을 추출하기 때문에 시간이 오래 걸리는 함수 중 한가지이며 그렇기에 사용할 때 주의해야함. 최대값/ 최소값을 구할 때는 MAX / MIN 함수 대신 인덱스 권장함.

    SCOTT>SELECT MAX(sal), MIN(sal)
  2  FROM emp;

  MAX(SAL)   MIN(SAL)
---------- ----------
      5000        800
    SCOTT>SELECT MAX(hiredate) "MAX",
  2          MIN(hiredate) "MIN"
  3  FROM emp;

MAX          MIN
------------ ------------
19-APR-87    17-DEC-80
  • STDDEV() / VARIANCE() : 표준편차 / 분산값을 반환


    • GROUP BY 절 사용하기

    전체 값을 조회하는 것이 아니라 특정 조건을 주고 해당 조건에 맞는 결과를 출력.

  • 그룹핑하기
    • SCOTT>SELECT deptno, AVG(NVL(sal,0)) "AVG"
  2  FROM emp
  3  GROUP BY deptno;          //부서별로 평균 급여 출력

    DEPTNO        AVG
---------- ----------
        30 1566.66667
        20    2443.75
        10 2916.66667
    SCOTT>SELECT deptno, job, AVG(NVL(sal,0)) "AVG_SAL"
  2  FROM emp
  3  GROUP BY deptno, job       //부서별로 먼저 그룹핑후 같은 학과일 경우 직급별로 한번더 그룹핑
  4  ORDER BY 1,2;              //정렬

    DEPTNO JOB          AVG_SAL
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         3000
        20 CLERK            800
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        1400

    GROUP BY 절 사용시 주의사항:
    1. SELECT 절에 사용된 그룹함수 이외의 칼럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 함.
    2. GROUP BY 절에 사용된 컬럼이라도 SELECT 절에는 사용되지 않아도 됨.
    3. 반드시 컬럼명이 사용되어야 함 (Alias 사용불가)


    HAVING 절 사용하기 (그룹핑한 조건으로 검색)

    WHERE 절은 그룹함수를 비교조건으로 쓸 수가 없음. 만약, 그룹함수를 조건으로 사용하고 싶을 경우에는 WHERE 대신에 HAVING 절을 사용하면 해결됨.

  • HAVING 절의 위치는 GROUP BY 절 전/후 다 상관없음.
    • SCOTT>SELECT deptno, AVG(NVL(sal,0))
  2  FROM emp
  3  WHERE deptno > 10
  4  GROUP BY deptno
  5  HAVING AVG(NVL(sal,0)) > 2000;

    DEPTNO AVG(NVL(SAL,0))
---------- ---------------
        20         2443.75


    기본적인것 이외의 GROUP 함수들

  • ROLLUP(): 소계값 / CUBE() : 전체총계
    • SCOTT>SELECT deptno, position, COUNT(*), SUM(PAY)
  2  FROM professor
  3  GROUP BY ROLLUP(deptno, position);
    
    DEPTNO POSITION                         COUNT(*)   SUM(PAY)
---------- ------------------------------ ---------- ----------
       101 instructor                              1        270
       101 a full professor                        1        550
       101 assistant professor                     1        380
       101                                         3       1200
       102 instructor                              1        250
       102 a full professor                        1        490
       102 assistant professor                     1        350
       102                                         3       1090
       103 instructor                              1        290
       103 a full professor                        1        530
       103 assistant professor                     1        330
       103                                         3       1150

    실습을 위해 professor 테이블 복사 후 101번 학과에 중복된 직급 데이터를 추가로 입력.

    SCOTT>CREATE TABLE professor2
  2  AS SELECT deptno, position, pay
  3     FROM professor;
    
Table created.

SCOTT>SELECT * FROM professor2;

    DEPTNO POSITION                              PAY
---------- ------------------------------ ----------
       101 a full professor                      550
       101 assistant professor                   380
       101 instructor                            270
       102 instructor                            250
       102 assistant professor                   350
       102 a full professor                      490
       103 a full professor                      530
       103 assistant professor                   330
       103 instructor                            290


SCOTT>INSERT INTO professor2 VALUES(101,'instructor', 100);

1 row created.

SCOTT>INSERT INTO professor2 VALUES(101,'a full professor', 100);

1 row created.

SCOTT>INSERT INTO professor2 VALUES(101,'assistant professor', 100);

1 row created.

SCOTT>commit;

Commit complete.

    
SCOTT>SELECT * FROM professor2
  2  ORDER BY deptno, position;

 D  EPTNO POSITION                              PAY
---------- ------------------------------ ----------
       101 a full professor                      100
       101 a full professor                      550
       101 assistant professor                   100
       101 assistant professor                   380
       101 instructor                            270
       101 instructor                            100
       102 a full professor                      490
       102 assistant professor                   350
       102 instructor                            250
       103 a full professor                      530
       103 assistant professor                   330
       103 instructor                            290

    아래 예시에서 ROLLUP 함수는 GROUP BY 절에 주어진 주건으로 소계값을 구해줌. BUT, 각 부서별로 소계는 알수 있지만 교수가 몇 명 인지는 나오지 않음(즉 총 합계는 출력안됨) 그래서 전체 총 합계까지 출력하고 싶다면 ROLLUP 대신 CUBE 함수를 사용하면 됨

    SCOTT>SELECT deptno, position, SUM(pay)
  2  FROM professor2
  3  GROUP BY deptno, ROLLUP(position);

    DEPTNO POSITION                         SUM(PAY)
---------- ------------------------------ ----------
       101 instructor                            370
       101 a full professor                      650
       101 assistant professor                   480
       101                                      1500
       102 instructor                            250
       102 a full professor                      490
       102 assistant professor                   350
       102                                      1090
       103 instructor                            290
       103 a full professor                      530
       103 assistant professor                   330
       103                                      1150
       201 a full professor                      570
       201 assistant professor                   330
       201                                       900
       202 instructor                            260
       202 assistant professor                   310
       202                                       570
       203 a full professor                      500
       203                                       500
       301 instructor                            220
       301 assistant professor                   290
       301                                       510
    SCOTT>SELECT deptno, position,
  2          COUNT(*), SUM(pay)
  3  FROM professor
  4  GROUP BY CUBE(deptno, position);

    DEPTNO POSITION                         COUNT(*)   SUM(PAY)
---------- ------------------------------ ---------- ----------
                                                  16       5920
           instructor                              5       1290
           a full professor                        5       2640
           assistant professor                     6       1990
       101                                         3       1200
       101 instructor                              1        270
       101 a full professor                        1        550
       101 assistant professor                     1        380
       102                                         3       1090
       102 instructor                              1        250
       102 a full professor                        1        490
       102 assistant professor                     1        350
       103                                         3       1150
       103 instructor                              1        290
       103 a full professor                        1        530
       103 assistant professor                     1        330
       201                                         2        900
       201 a full professor                        1        570
       201 assistant professor                     1        330
       202                                         2        570
       202 instructor                              1        260
       202 assistant professor                     1        310
       203                                         1        500
       203 a full professor                        1        500
       301                                         2        510
       301 instructor                              1        220
       301 assistant professor                     1        290

  • GROUPING SETS() : 그룹핑 조건이 여러 개 일 경우 사용. 하나의 테이블에 대한 여러가지 그룹 함수가 사용이 될 때 활용.

    • ex1) 학년별 인원수 합계와 학과별 인원수 합계를 구해야 하는 경우 

      SCOTT>SELECT grade, deptno1, COUNT(*)
2  FROM student
3  GROUP BY GROUPING SETS(grade,deptno1);

  GRADE    DEPTNO1   COUNT(*)
---------- ---------- ----------
          102          4
          201          6
          301          2
          101          4
          202          2
          103          2
 1                     5
 2                     5
 4                     5
 3                     5

    • ex2) 학년별로 학과별로 인원수와 키의 합계, 몸무게의 합계를 동시에 출력

      SCOTT>SELECT grade, deptno1,
2          COUNT(*), SUM(height),
3          SUM(weight)
4  FROM student
5  GROUP BY GROUPING SETS(grade,deptno1);

    GRADE    DEPTNO1   COUNT(*) SUM(HEIGHT) SUM(WEIGHT)
---------- ---------- ---------- ----------- -----------
          102          4         683         257
          201          6        1037         402
          301          2         344         120
          101          4         688         240
          202          2         359         125
          103          2         331         103
 1                     5         852         312
 2                     5         878         337
 4                     5         879         341
 3                     5         833         257

  • LISTAGG() : 나열하는 함수. WITHIN GROUP 사이에 가로로 나열하고 싶은 규칙을 ORDER BY 로 적어주면 됨. 연결자를 설정해주지 않으면 모든 데이터가 한줄로 연결되어 보기 안좋음.(WITHIN GROUP 절에 아무 조건도 조지 않으면 에러발생, 연결자에 예약어 사용못함)

    SCOTT>SELECT deptno,
2          LISTAGG(ename, '->') WITHIN GROUP(ORDER BY hiredate) "LISTAGG"
3  FROM emp
4  GROUP BY deptno;

  DEPTNO       LISTAGG
---------- --------------------------------------------------
       10  CLARK->KING->MILLER
       20  SMITH->JONES->FORD->SCOTT
       30  ALLEN->WARD->BLAKE->TURNER->MARTIN->JAMES

  • PIVOT() : Row 단위를 Column 단위로 변경.(UNPIVOT()은반대로 변경)

    • ex1) 우선, pivot 기능 사용하지 않고 decode 함수 사용해 달력만들기( 달력만들기로 유명한 방법) 
      SCOTT>SELECT MAX(DECODE(day, 'SUN', dayno)) SUN,
2            MAX(DECODE(day, 'MON', dayno)) MON,
3            MAX(DECODE(day, 'TUE', dayno)) TUE,
4            MAX(DECODE(day, 'WED', dayno)) WED,
5            MAX(DECODE(day, 'THU', dayno)) THU,
6            MAX(DECODE(day, 'FRI', dayno)) FRI,
7            MAX(DECODE(day, 'SAT', dayno)) SAT
8  FROM cal
9  GROUP BY weekno
10 ORDER BY weekno;
      
SUN  MON  TUE  WED  THU  FRI  SAT
---- ---- ---- ---- ---- ---- ----
1    2    3    4    5    6    7
8    9    10   11   12   13   14
15   16   17   18   19   20   21
22   23   24   25   26   27   28
29   30   31

    • PIVOT 사용해 달력 만들기. (PIVOT 절에 MAX(dayno) 절은 DECODE 문장에서 사용되는 함수를 적으면 되고 FOR 절에는 화면에 집계될 그룹핑 할 컬럼을 적으면 됨. 아래의 예는 가상의 뷰를 하나 생성 한 후 pivot 기능을 활용. IN 연산자 뒤에는 서브쿼리를 사용할 수 없음)

      SCOTT>SELECT * FROM (SELECT weekno "WEEK", day, dayno
2                  FROM cal)
3  PIVOT
4  ( MAX(dayno) FOR day IN('SUN' AS "SUN" ,
5                          'MON' AS "MON" ,
6                          'TUE' AS "TUE" ,
7                          'WED' AS "WED" ,
8                          'THU' AS "THU" ,
9                          'FRI' AS "FRI" ,
10                          'SAT' AS "SAT")
11  )
12  ORDER BY "WEEK"; 
      
WEEK SUN  MON  TUE  WED  THU  FRI  SAT
---- ---- ---- ---- ---- ---- ---- ----
1    1    2    3    4    5    6    7
2    8    9    10   11   12   13   14
3    15   16   17   18   19   20   21
4    22   23   24   25   26   27   28
5    29   30   31

    • ex2) 부서별로 각 직급별 인원이 몇명인지 계산하기(decode/pivot 비교)

      SCOTT>SELECT deptno,
2          COUNT(DECODE(job, 'CLERK', '0'))   "CLERK",
3          COUNT(DECODE(job, 'MANAGER', '0')) "MANAGER",
4          COUNT(DECODE(job, 'PRESIDENT', '0')) "PRESIDENT",
5          COUNT(DECODE(job, 'ANALYST', '0')) "ANALYST",
6          COUNT(DECODE(job, 'SALESMAN', '0')) "SALESMAN"
7  FROM emp
8  GROUP BY deptno
9  ORDER BY deptno;
      
   DEPTNO      CLERK    MANAGER  PRESIDENT    ANALYST   SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
10          1          1          1          0          0
20          1          1          0          2          0
30          1          1          0          0          4
      

      
SCOTT>SELECT * FROM (SELECT deptno, job, empno
2                  FROM emp)
3  PIVOT
4  (
5    COUNT(empno) FOR job IN ('CLERK' AS "CLERK",
6                             'MANAGER' AS "MANAGER",
7                             'PRESIDENT' AS "PRESIDENT",
8                             'ANALYST' AS "ANALYST",
9                             'SALESMAN' AS "SALESMAN")
10  )
11  ORDER BY deptno ;
      
 DEPTNO      CLERK    MANAGER  PRESIDENT    ANALYST   SALESMAN
------- ---------- ---------- ---------- ---------- ----------
10          1          1          1          0          0
20          1          1          0          2          0
30          1          1          0          0          4

    • 조금 더 복잡하지만 PIVOT 부분에 아래와 같이 그룹핑 조건을 여러 개 사용해도 됨.(부서별 인원수와 각 인원수 별 급여 합계까지 함께 출력하는 예제)

      SCOTT>SELECT * FROM (SELECT deptno, job, empno, sal
2                  FROM emp)
3  PIVOT
4  (
5    COUNT(empno) AS COUNT,
6    SUM(NVL(sal,0)) AS SUM FOR job IN('CLERK' AS   "C",
7                                      'MANAGER' AS "M",
8                                      'PRESIDENT' AS "P",
9                                      'ANALYST' AS "A",
10                                      'SALESMAN' AS "S")
11  )
12  ORDER BY deptno;
      
DEPTNO C_COUNT    C_SUM  M_COUNT    M_SUM  P_COUNT    P_SUM  A_COUNT    A_SUM  S_COUNT    S_SUM
----- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
10        1     1300        1     2450        1     5000        0                 0
20        1      800        1     2975        0                 2     6000        0
30        1      950        1     2850        0                 0                 4     5600

    • UNPIVOT : 합쳐져 있는 결과를 다시 풀어줌.(테스트를 위해 아래와 같이 테이블생성 후 실습)

      SCOTT>CREATE TABLE upivot
2  AS SELECT * FROM (SELECT deptno, job, empno
3                    FROM emp)
4     PIVOT
5     (  COUNT(empno) FOR job IN('CLERK' AS "CLERK",
6                                'MANAGER' AS "MANAGER",
7                                'PRESIDENT' AS "PRESIDENT",
8                                'ANALYST' AS "ANALYST",
9                                'SALESMAN' AS "SALESMAN")
10     );
Table created.
      
SCOTT>SELECT * FROM upivot;
      
   DEPTNO      CLERK    MANAGER  PRESIDENT    ANALYST   SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30          1          1          0          0          4
20          1          1          0          2          0
10          1          1          1          0          0

      
SCOTT>SELECT * FROM upivot
2  UNPIVOT
3  (  empno FOR job IN (CLERK,MANAGER,PRESIDENT,ANALYST,SALESMAN)  );
      
 DEPTNO    JOB            EMPNO
---------- --------- ----------
30         CLERK              1
30         MANAGER            1
30         PRESIDENT          0
30         ANALYST            0
30         SALESMAN           4
20         CLERK              1
20         MANAGER            1
20         PRESIDENT          0
20         ANALYST            2
20         SALESMAN           0
10         CLERK              1
10         MANAGER            1
10         PRESIDENT          1
10         ANALYST            0
10         SALESMAN           0

  • LAG() / LEAD(): 이전/이후 행 값을 가져 올 때 사용하는 함수

  • RANK() : 순위계산. 같은 순위의 경우 같은 출력 값을 가지기 때문에 출력결과가 연속하지 않을 수 있다. 특정 순위만 볼수도 있고 전체 순위를 볼수도 있음.

    • 특정 데이터의 순위 확인하기 : RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ASC | DESC])

      SCOTT>SELECT RANK('SMITH') WITHIN GROUP (ORDER BY ename) "RANK"
2  FROM emp;
      
RANK
----------
11

    • 전체순위 확인하기 : RANK() OVER(ORDER BY 조건컬럼명 [ASC | DESC])

      SCOTT>SELECT empno, ename, sal,
2   RANK() OVER (ORDER BY sal) AS RANK_ASC,
3   RANK() OVER (ORDER BY sal DESC) AS RANK_DESC
4  FROM emp;
      
EMPNO ENAME           SAL   RANK_ASC  RANK_DESC
----- -------- ---------- ---------- ----------
7839 KING           5000         13          1
7788 SCOTT          3000         11          2
7902 FORD           3000         11          2
7566 JONES          2975         10          4
7698 BLAKE          2850          9          5
7782 CLARK          2450          8          6
7499 ALLEN          1600          7          7
7844 TURNER         1500          6          8
7934 MILLER         1300          5          9
7654 MARTIN         1250          3         10
7521 WARD           1250          3         10
7900 JAMES           950          2         12
7369 SMITH           800          1         13

    • ex) PARTITION BY 절 이후에 분류 조건 추가하기 

      SCOTT>SELECT empno, ename, sal, deptno,
2          RANK() OVER (PARTITION BY deptno      //사번, 이름, 급여, 부서번호, 부서별 급여순위를 출력
3                       ORDER BY sal DESC) "RANK"
4  FROM emp;

  EMPNO ENAME             SAL     DEPTNO       RANK
-------- ---------- ---------- ---------- ----------
7839     KING             5000         10          1
7782     CLARK            2450         10          2
7934     MILLER           1300         10          3
7788     SCOTT            3000         20          1
7902     FORD             3000         20          1
7566     JONES            2975         20          3
7369     SMITH             800         20          4
7698     BLAKE            2850         30          1
7499     ALLEN            1600         30          2
7844     TURNER           1500         30          3
7521     WARD             1250         30          4
7654     MARTIN           1250         30          4
7900     JAMES             950         30          6
      
SCOTT>SELECT empno, ename, sal, deptno,
2          RANK() OVER (PARTITION BY deptno, job  //부서번호 내에서 job 별로 RANK
3                       ORDER BY sal DESC) "RANK"    
4  FROM emp;
      
     EMPNO ENAME             SAL     DEPTNO       RANK
---------- ---------- ---------- ---------- ----------
7934 MILLER           1300         10          1
7782 CLARK            2450         10          1
7839 KING             5000         10          1
7788 SCOTT            3000         20          1
7902 FORD             3000         20          1
7369 SMITH             800         20          1
7566 JONES            2975         20          1
7900 JAMES             950         30          1
7698 BLAKE            2850         30          1
7499 ALLEN            1600         30          1
7844 TURNER           1500         30          2
7521 WARD             1250         30          3
7654 MARTIN           1250         30          3

  • SUM() OVER() : 누계

    • ex) panmae 테이블을 사용하여 1000 번 대리점의 판매 내역을 출력하되 판매일자, 제품코드, 판매량, 누적 판매금액을 출력 
      SCOTT>SELECT p_date, p_code, p_qty, p_total,
2          SUM(p_total) OVER(ORDER BY p_total) "TOTAL"
3  FROM panmae
4  WHERE p_store = 1000;
      
P_DATE       P_CODE      P_QTY    P_TOTAL      TOTAL
-------- ---------- ---------- ---------- ----------
20110103        100          2       1600       1600
20110102        102          2       2000       3600
20110101        100          3       2400       6000
20110102        105          2       3000       9000

      
SCOTT>SELECT p_date, p_code, p_qty, p_total,
2          SUM(p_total) OVER(PARTITION BY p_code ORDER BY p_total) "TOTAL"  //제품 코드별로 분류한 후 누계.
3  FROM panmae
4  WHERE p_store = 1000;
      
P_DATE       P_CODE      P_QTY    P_TOTAL      TOTAL
-------- ---------- ---------- ---------- ----------
20110103        100          2       1600       1600
20110101        100          3       2400       4000
20110102        102          2       2000       2000
20110102        105          2       3000       3000
      
SCOTT>SELECT p_code, p_store, p_date, p_qty, p_total,
2          SUM(p_total) OVER(PARTITION BY p_code, p_store ORDER BY p_date) "TOTAL"
3  FROM panmae;
      
 P_CODE P_STO P_DATE        P_QTY    P_TOTAL      TOTAL
------- ----- -------- ---------- ---------- ----------
100     1000  20110101          3       2400       2400
100     1000  20110103          2       1600       4000
100     1001  20110103          3       2400       2400
100     1002  20110104          2       1600       1600
100     1003  20110104          4       3200       3200
100     1004  20110103         10       8000       8000
100     1004  20110104          5       4000      12000
101     1001  20110101          5       4500       4500
101     1001  20110104          3       2700       7200
101     1002  20110104          4       3600       3600
101     1003  20110103          4       3600       3600
101     1003  20110104          3       2700       6300
102     1000  20110102          2       2000       2000
102     1001  20110104          4       4000       4000
102     1002  20110104          2       2000       2000
102     1003  20110101          2       2000       2000
103     1002  20110102          5       4500       4500
103     1003  20110104          2       1800       1800
103     1004  20110101          6       5400       5400
104     1002  20110102          3       2400       2400
105     1000  20110102          2       3000       3000

  • RATIO_TO_REPORT() : 비율출력

    • ex) 각 판매점 별 판매비중 구하기 
      SCOTT>SELECT p_code, SUM(SUM(p_qty)) OVER() "TOTAL_QTY",
2          SUM(SUM(p_total)) OVER() "TOTAL_PRICE", p_store, p_qty, p_total,
3          ROUND((RATIO_TO_REPORT(SUM(p_qty)) OVER())*100,2) "qty_%",
4          ROUND((RATIO_TO_REPORT(SUM(p_total)) OVER())*100,2) "total_%"
5  FROM panmae
6  WHERE pcode = 100
7  GROUP BY p_code, p_store, p_qty, p_total;
      
P_CODE  TOTAL_QTY TOTAL_PRICE P_STO      P_QTY    P_TOTAL      qty_%    total_%
------ ---------- ----------- ----- ---------- ---------- ---------- ----------
100         29       23200 1002           2       1600        6.9        6.9
100         29       23200 1001           3       2400      10.34      10.34
100         29       23200 1000           3       2400      10.34      10.34
100         29       23200 1000           2       1600        6.9        6.9
100         29       23200 1004           5       4000      17.24      17.24
100         29       23200 1004          10       8000      34.48      34.48
100         29       23200 1003           4       3200      13.79      13.79

  • LAG() 함수 이용해 차이 구하기 예제.

    • 전일 판매량과 금액을 구하여 당일과 전일 판매 수량과 금액의 차이를 보여주는 예제. 아래 코드에서 P_STORE 는 판매점 코드, P_DATE 는 판매날짜, P_QTY 는 판매수량 , D-1 QTY 는 전일 판매량 , DIFF-QTY 는 전일과 당일 판매량 차이 , P_TOTAL 은 당일판매금액 , D-1 PRICE 는 전일 판매금액 , DIFF_PRICE 는 전일과 당일 판매 금액 차이

      SCOTT>SELECT p_code, SUM(SUM(p_qty)) OVER() "TOTAL_QTY",
2          SUM(SUM(p_total)) OVER() "TOTAL_PRICE", p_store, p_qty, p_total,
3          ROUND((RATIO_TO_REPORT(SUM(p_qty)) OVER())*100,2) "qty_%",
4          ROUND((RATIO_TO_REPORT(SUM(p_total)) OVER())*100,2) "total_%"
5  FROM panmae
6  WHERE p_code = 100
7  GROUP BY p_code, p_store, p_qty, p_total;
      
P_CODE TOTAL_QTY TOTAL_PRICE   P_STO      P_QTY    P_TOTAL      qty_%    total_%
----- -------- -------------- ---------- ---------- ---------- ---------- ---------

100       29     23200         1002           2       1600        6.9        6.9
100       29     23200         1001           3       2400      10.34      10.34
100       29     23200         1000           3       2400      10.34      10.34
100       29     23200         1000           2       1600        6.9        6.9
100       29     23200         1004           5       4000      17.24      17.24
100       29     23200         1004          10       8000      34.48      34.48
100       29     23200         1003           4       3200      13.79      13.79

    • 모든 판매점을 판매점 별로 구분해서 다 출력하고 싶을 경우 

      SCOTT>SELECT p_store, p_date, p_code, p_qty,
2          LAG(p_qty,1) OVER(PARTITION BY p_store ORDER BY p_date) "D-1 QTY",
3          p_qty - LAG(p_qty,1) OVER(PARTITION BY p_store ORDER BY p_date) "DIFF-QTY",
4          p_total,
5          LAG(p_total,1) OVER(PARTITION BY p_store ORDER BY p_date) "D-1 PRICE",
6          p_total - LAG(p_total,1) OVER(PARTITION BY p_store ORDER BY p_date) "DIFF-PRICE"
7  FROM panmae;
      
P_STO P_DATE       P_CODE      P_QTY    D-1 QTY   DIFF-QTY    P_TOTAL  D-1 PRICE DIFF-PRICE
----- -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1000  20110101        100          3                             2400
1000  20110102        105          2          3         -1       3000       2400        600
1000  20110102        102          2          2          0       2000       3000      -1000
1000  20110103        100          2          2          0       1600       2000       -400
1001  20110101        101          5                             4500
1001  20110103        100          3          5         -2       2400       4500      -2100
1001  20110104        101          3          3          0       2700       2400        300
1001  20110104        102          4          3          1       4000       2700       1300
1002  20110102        104          3                             2400
1002  20110102        103          5          3          2       4500       2400       2100
1002  20110104        101          4          5         -1       3600       4500       -900
1002  20110104        100          2          4         -2       1600       3600      -2000
1002  20110104        102          2          2          0       2000       1600        400
1003  20110101        102          2                             2000
1003  20110103        101          4          2          2       3600       2000       1600
1003  20110104        103          2          4         -2       1800       3600      -1800
1003  20110104        100          4          2          2       3200       1800       1400
1003  20110104        101          3          4         -1       2700       3200       -500
1004  20110101        103          6                             5400
1004  20110103        100         10          6          4       8000       5400       2600
1004  20110104        100          5         10         -5       4000       8000      -4000

    • QUIZ

    1) emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우 , 평균 금액을 구하세요. 단 보너스가 없을 경우는 보너스를 0 으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요










    2) student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하세요.











    3) Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요. 단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요









    4) 먼저 emp 테이블에 아래의 두 건의 데이터를 입력 하신 후 작업하세요.

    Emp 테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.



    SCOTT>INSERT INTO emp (empno,deptno,ename,sal) 

            2 VALUES (1000,10,'Tiger',3600) ; 

    SCOTT> INSERT INTO emp (empno,deptno,ename,sal)

             2 VALUES (2000,30,'Cat',3000); 

    SCOTT> COMMIT ;











    5) emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요. 단 급여를 오름차순으로 정렬해서 출력하세요.











    6) fruit 테이블을 아래와 같은 형태로 출력하세요.










    7) student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요. (단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요)





    ratio_to_report도 사용가능








    8) emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요 단 부서번호로 오름차순 출력하세요.











    9) emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여총액에서 몇 %의 비율을 차지하는지 출력하세요. 단 급여 비중이 높은 사람이 먼저 출력되도록 하세요.










    10) emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요. 단 부서번호를 기준으로 오름차순으로 출력하세요.








    11) loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자,대출코드,대출금액,일자별 누적대출금액을 아래와 같이 출력하세요.










    12) loan 테이블을 사용하여 전체 지점의 대출코드 , 대출지점 , 대출날짜 , 대출건수 , 대출금액을 대출코드와 대출지점별로 누적 합계를 구하세요.










    13) professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요











    14) professor 테이블을 조회하여 학과번호 , 교수명 , 급여 , 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.





    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저


    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 5. DDL과 Data Dictionary  (773) 2016.09.15
    [SQL] 4. JOIN  (796) 2016.09.12
    [SQL] 2. 단일행함수(정규식표현)  (315) 2016.09.05
    [SQL] 2. 단일행함수(일반함수)  (11) 2016.09.02
    [SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수)  (993) 2016.08.30

    정규식(Regular Expression) 함수


    SQL 문장에서도 유닉스에서 사용하는 정규식을 사용해 다양한 검색을 할 수 있다. 정규식은 다양한 메타 문자들을 사용해 검색방법을 확장한것. 유닉스 버전마다 조금씩 다를 수 있지만 주로 사용하는 정규식 방법은 아래와 같다


    정규 표현식

    ^ (캐럿)

        해당 문자로 시작하는 line 출력

    ‘^pattern’

    $ (달러) 

        해당 문자로 끝나는 line 출력

    ‘pattern$’

    . 

        S로 시작하여 E으로 끝나는 line ( . =1 character)

    ‘S . . . .E ’

    *

        모든 이라는 뜻, 글자수가 0 일수도 있음

    ‘[a–z]*’

    [ ]

        해당 문자에 해당하는 한 문자

    ‘[Pp]attern’

    [ ^ ]

        해당 문자에 해당하지 않는 한 문자

    ‘[^a–m]attern’



    [:문자클래스:] : alpha, blank, cntrl, digit, graph, lower, print, space, uppper, xdigit




    연습용 테이블 확인

    SCOTT>SELECT * FROM t_reg;

TEXT
----------
ABC123
ABC 123
ABC  123
abc 123
abc  123
a1b2c3
aabbcc123
?/!@#$*&
\~*().,
123123
123abc
abc


    1. REGEXP_LIKE 함수

    like 함수처럼 특정 패턴과 매칭되는 결과를 검색


    사용예

  • 영문자가 들어가 있는 행만 출력하기

    • 소문자, 대문자, 대소문자가 들어가있는 행을 출력하는 예 
      SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[a-z]');
      
TEXT
----------
abc 123
abc  123
a1b2c3
aabbcc123
123abc
abc
      
SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[A-Z]');  // '[[:upper:]]' 와 같은결과
      
TEXT
----------
ABC123
ABC 123
ABC  123
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '[a-zA-Z]');
      
TEXT
----------
ABC123
ABC 123
ABC  123
abc 123
abc  123
a1b2c3
aabbcc123
123abc
abc

  • 영문자로 시작하고 공백을 포함하는 경우

    • 소문자로 시작, 뒤에 공백이 있는 경우 
      SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[a-z] '); //소문자로 시작, 뒤에 공백이 있는 모든 행을 출력
      
TEXT
----------
abc 123
abc  123
      
SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[a-z] [0-9]'); //소문자로 시작, 공백이 1칸있고 숫자로 끝나는 모든 행을 출력
      
TEXT
----------
abc 123
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '[[:space:]]'); //공백이 있는 데이터를 모두 출력
      
TEXT
----------
ABC 123
ABC  123
abc 123
abc  123

  • 연속적인 글자 수 지정하기

    • 대문자가 연속적으로 2글자 이상 오는 경우 출력

      SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '[A-Z]{2}'); 
      
TEXT
----------
ABC123
ABC 123
ABC  123

    • 영어대문자 다음에 숫자가 함께 오는데 영어대문자와 숫자가 각각 3글자 오는 행 출력 

      SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[A-Z][0-9]{3}');
      
TEXT
--------
ABC123
      
SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '[0-9][A-Z]{3}');
no rows selected

  • 특정 위치를 지정하여 출력

    • 시작되는 문자 지정 : ^(캐럿) cf.대괄호 안에 있는 캐럿은 NOT의 의미. 
      SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '^[A-Za-z]'); // 대문자나 소문자로 시작하는 행 출력 
      
TEXT
----------
ABC123
ABC 123
ABC  123
abc 123
abc  123
a1b2c3
aabbcc123
abc
      
SCOTT>SELECT * FROM treg
2  WHERE REGEXP_LIKE(text, '^[0-9A-Z]'); // 숫자나 대문자로 시작하는 행 출력
      
TEXT
----------
ABC123
ABC 123
ABC  123
123123
123abc
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '^[a-z]|^[0-9]'); // 소문자나 숫자로 시작하는 행 출력 (여러조건을 줄때 바(|) 기호사용해 연결가능)
      
TEXT
----------
abc 123
abc  123
a1b2c3
aabbcc123
123123
123abc
abc

    • 끝나는 문자 지정 : $(달러)

      SCOTT>SELECT name, id
2  FROM student
3  WHERE REGEXP_LIKE(id, '^M(a|o)'); // M으로 시작하고 두번째가 a나 o가 나오는 id 출력
      
NAME                           ID
------------------------------ --------------------
Demi Moore                     Moore
Steve Martin                   Martin
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '[a-zA-Z]$'); // 소문자나 대문자로 끝나는 행 출력 , = '[[:alpha:]]$'
      
TEXT
----------
123abc
abc

    • 대괄호안에 있는 ^(캐럿): NOT의 의미.

      SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '^[^a-z]'); // 소문자로 시작하지 않는 행을 출력
      
TEXT
----------
ABC123
ABC 123
ABC  123
?/!@#$*&
\~*().,
123123
123abc
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '^[^0-9a-z]'); // 숫자나 소문자로 시작하지 않는 행 출력
      
TEXT
----------
ABC123
ABC 123
ABC  123
?/!@#$*&
\~*().,
      
SCOTT>SELECT * FROM t_reg
2  WHERE REGEXP_LIKE(text, '[^a-z]'); //위치와 상관없이 소문자만 들어 있는 행을 제외하고 모두 출력
      
TEXT
----------
ABC123
ABC 123
ABC  123
abc 123
abc  123
a1b2c3
aabbcc123
?/!@#$*&
\~*().,
123123
123abc //t_reg 테이블의 맨 아랫줄 abc 행이 제외됨.
      
SCOTT>SELECT * FROM t_reg
2  WHERE NOT REGEXP_LIKE(text, '[a-z]'); // 소문자가 들어간 모든 행을 제거하고 모두 출력
      
TEXT
----------
ABC123
ABC 123
ABC  123
?/!@#$*&
\~*().,
123123

    • 다른여러 예들. 

      SCOTT>SELECT name, tel
2  FROM student
3  WHERE REGEXP_LIKE(tel, '^[0-9]{2}\)[0-9]{4}'); //지역번호가 2자리고 국번이 4자리가 나오는값 출력
      
NAME                           TEL
----------------------------- ---------------
Demi Moore                     02)6255-9875
Richard Dreyfus                02)6788-4861
Steve Martin                   02)6175-3945
Anthony Hopkins                02)6122-2345
      
SCOTT>SELECT name, id
2  FROM student
3  WHERE REGEXP_LIKE(id, '^...r..'); // 넷째 자리에 r이 있는 행을 출력
      
NAME                           ID
------------------------------ --------------------
James Seo                      75true
Bill Murray                    Murray
      
SCOTT>SELECT * FROM t_reg2
2  WHERE REGEXP_LIKE(ip, '^[172]{3}\.[16]{2}\.[168]{3}'); // 아래의 결과와 같이 대괄호 안의 숫자의 순서와는 상관없이 해당 숫자가 있는 행은 모두 출력됨.
      
NO IP
---------- --------------------
 4 172.61.186.2
 5 172.61.168.2

  • 특정 조건을 제외한 결과 출력하기

    • ex) 영문자(대소문자)를 포함하지 않는 모든행 출력 
      SCOTT>SELECT * FROM t_reg
2  WHERE NOT REGEXP_LIKE(text, '[a-zA-Z]');
      
TEXT
----------
?/!@#$&
\~().,
123123

  • 특수문자 찾기 : ? 나 * 이 포함된 행을 출력할경우 탈출문자(\)를 사용하면됨.



    • 2. REGEXP_REPLACE 함수

    Replace 함수를 확장한 개념으로 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 형태로 치환하는 함수.

     문법: REGEXP_REPLACE(source_char, pattern
                    [, replace_string
                    [, position
                    [, occurrence
                    [, match_param]]]]
                    )
  • Source : 원본 데이터, 컬럼명이나 문자열이 오고 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 가 올 수있다.
  • pattern : 찾고자 하는 패턴, 512byte까지의 정규 표현식을 사용, 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2 가 올 수 있다.
  • replace_string : 변환 하고자 하는 형태.
  • position : 검색 시작위치 (default:1)
  • occurrence : 패턴과 일치가 발생하는 횟수. 0은 모든 값을 대체하고 다른 n 이란 숫자를 주면 n번째 발생하는 문자열을 대입.
  • match_parameter : 기본값으로 검색되는 옵션을 바꿀수 있다.
    • -c: 대소문자 구분해서 검색
    • -i : 대소문자 구분않고 검색
    • -m : 검색조건을 여러줄로 줌.

    • 사용예

    • ex1) 예제 테이블 t_reg에서 숫자부분을 '*' 기호로 전부 변경
    SCOTT>SELECT text, REGEXP_REPLACE(text, '[[:digit:]]', '*') "NO -> CHAR"
  2  FROM t_reg;
    
TEXT       NO -> CHAR
---------- --------------------
ABC123     ABC***
ABC 123    ABC ***
ABC  123   ABC  ***
abc 123    abc ***
abc  123   abc  ***
a1b2c3     a*b*c*
aabbcc123  aabbcc***
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     ******
123abc     ***abc
abc        abc
    • ex2) 특정 패턴 찾아서 변경하기
    SCOTT>SELECT text,
  2          REGEXP_REPLACE(text, '([0-9])', '\1-*') "Add Char" //숫자뒤에 -* 붙이기
  3  FROM t_reg;
    

TEXT       Add Char
---------- ------------------------------
ABC123     ABC1-*2-*3-*
ABC 123    ABC 1-*2-*3-*
ABC  123   ABC  1-*2-*3-*
abc 123    abc 1-*2-*3-*
abc  123   abc  1-*2-*3-*
a1b2c3     a1-*b2-*c3-*
aabbcc123  aabbcc1-*2-*3-*
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     1-*2-*3-*1-*2-*3-*
123abc     1-*2-*3-*abc
abc        abc

    


SCOTT>SELECT text,
  2          REGEXP_REPLACE(text, '([0-9])', '-*') "Add Char"
  3  FROM t_reg;
    

TEXT       Add Char
---------- ------------------------------
ABC123     ABC-*-*-*
ABC 123    ABC -*-*-*
ABC  123   ABC  -*-*-*
abc 123    abc -*-*-*
abc  123   abc  -*-*-*
a1b2c3     a-*b-*c-*
aabbcc123  aabbcc-*-*-*
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     -*-*-*-*-*-*
123abc     -*-*-*abc
abc        abc

12 rows selected.

    

SCOTT>SELECT no, ip,
  2          REGEXP_REPLACE(ip, '\.', '') "Dot Remove"
  3  FROM t_reg2;
    

  NO IP                   Dot Remove
---- -------------------- --------------------
   1 10.10.0.1            101001
   2 10.10.10.1           1010101
   3 172.16.5.100         172165100
   4 172.61.186.2         172611862
   5 172.61.168.2         172611682
   6 255.255.255.0        2552552550

6 rows selected.

    


SCOTT>SELECT no, ip,
  2          REGEXP_REPLACE(ip, '\.', '/', 1,1) "REPLACE"
  3  FROM t_reg2;
    

  NO IP                   REPLACE
---- -------------------- --------------------
   1 10.10.0.1            10/10.0.1
   2 10.10.10.1           10/10.10.1
   3 172.16.5.100         172/16.5.100
   4 172.61.186.2         172/61.186.2
   5 172.61.168.2         172/61.168.2
   6 255.255.255.0        255/255.255.0
    • 사용자에게 입력받은 문자중 공백이 여러개 포함돼있을 경우 그 공백을 제거시키는 방법
    SCOTT>SELECT REGEXP_REPLACE('aaa     bbb', '( ){1,}','')
  2  FROM dual;
    

REGEXP
------
aaabbb

    위의 예제에서 {1,}부분을 {1}로 해도됨. { }내의 숫자는 앞문자가 나타나는 횟수 또는 범위를 의미함.

    ex) a{5} = 'a'의 5번 반복한 aaaaa 만을 의미
    ex) a{3,} = aaa, aaaa, aaaaa, .....
    ex) a{3,5} = aaa, aaaa, aaaaa
    ex) ab{2,3} = abb, abbb

    • 'abc bbb'에서 공백이 두칸이상 인 것만 공백을 제거해 출력
    SCOTT>SELECT REGEXP_REPLACE('aaa     bbb', '( ){1,}','')
  2  FROM dual;
    
REGEXP
------
aaabbb

    

SCOTT>SELECT REGEXP_REPLACE('aaa bbb', '( ){2,}', '') "ONE",
  2          REGEXP_REPLACE('aaa  bbb', '( ){2,}', '') "Two"
  3  FROM dual;
    

ONE     Two
------- ------
aaa bbb aaabbb

    

SCOTT>SELECT REGEXP_REPLACE('aaa bbb', '( ){2,}', '*') "ONE",
  2          REGEXP_REPLACE('aaa  bbb', '( ){2,}', '*') "Two",
  3          REGEXP_REPLACE('aaa   bbb', '( ){2,}', '*') "Three"
  4  FROM dual;
    

ONE     Two     Three
------- ------- -------
aaa bbb aaa*bbb aaa*bbb
    • 사용자가 입력한 단어에 공백 문자가 처음에 포함돼있고 중간에도 공백이 포함되어 있다고 가정하고 그 단어에서 모든 공백을 제거한 후 조회하는 예제.
    SCOTT>SELECT studno, name, id
  2  FROM student
  3  WHERE id = REGEXP_REPLACE('&id', '( ){1,}', '');
Enter value for id:      75    true
    

    STUDNO NAME                           ID
---------- ------------------------------ --------------------
      9411 James Seo                      75true

    

SCOTT>SELECT studno, name, id
  2  FROM student
  3  WHERE id = LOWER(REGEXP_REPLACE('&id', '( ){1,}', ''));   /소문자화
Enter value for id:      75    TRUE
    

    STUDNO NAME                           ID
---------- ------------------------------ --------------------
      9411 James Seo                      75true
    • 특정 문자열의 형태를 다른 형태로 바꿀 때
    SCOTT>SELECT REGEXP_REPLACE('20160905',
  2                         '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',
  3                         '\1-\2-\3')
  4  FROM dual;
    

REGEXP_REP
----------
2016-09-05


    3. REGEXP_SUBSTR 함수

    SUBSTR 함수의 확장판. 특정 패턴에서 주어진 문자를 추출해 내는 함수.



    사용예

    • 교수테이블(professor)테이블에서 홈페이지(hpage) 주소가 있는 교수들만 조사해서 아래의 화면처럼 나오게 출력하시오.
    SCOTT>SELECT name, LTRIM(REGEXP_SUBSTR(hpage, '/([[:alnum:]]+\.?){3,4}?'), '/') "URL"
  2  FROM professor
  3  WHERE hpage IS NOT NULL ;
    

NAME                 URL
-------------------- --------------------
Audie Murphy         www.abc.net
Angela Bassett       www.abc.net
Jessica Lange        www.power.com
Michelle Pfeiffer    num1.naver.com

    hpage 컬럼을 조회하여 ‘http://’ 부분을 제거하고 . 으로 구분되는 필드를 최소 3개에서 최대 4개까지 출력하라는 의미. 그 후에 왼쪽부분에 나오는 ‘/ ’ 기호를 LTRIM 함수로 제거함.

    • Professor 테이블에서 101번 학과와 201번 학과 교수들의 이름과 메일 주소의 도메인 주소를 출력하시오. 단 메일 주소는 @뒤에 있는 주소만 출력하시오.
    SCOTT>SELECT name, LTRIM(REGEXP_SUBSTR(email, '@([[:alnum:]]+\.?){3,4}?'), '@') domain
  2  FROM professor
  3  WHERE deptno IN (101,201) ;
    

NAME                 DOMAIN
-------------------- --------------------
Audie Murphy         abc.net
Angela Bassett       abc.net
Jessica Lange        power.com
Meryl Streep         daum.net
Susan Sarandon       def.com
    • 특정기호나 문자를 기준으로 데이터를 추출할 때
    SCOTT>SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb',
  2                        '[^:]+', 1, 3) result           // : 기호를 기준으로 3번재의 문자열을 추출
  3  FROM dual;
    

RESULT
----------
racdb



    
SCOTT>SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb',
  2                        '[^/:]+', 1, 2) result          // 슬래쉬를 기준으로 출력
  3  FROM dual;
    

RESULT
--------------
oracle@racdb


    4. REGEXP_COUNT 함수

    특정 문자의 개수를 세는 함수


    사용예

    • 주어진 문자열에서 대문자 'A'가 몇개인지 찾아 주는 예
    SCOTT>SELECT text, REGEXP_COUNT(text, 'A')
  2  FROM t_reg;
    

TEXT       REGEXP_COUNT(TEXT,'A')
---------- ----------------------
ABC123                          1
ABC 123                         1
ABC  123                        1
abc 123                         0
abc  123                        0
a1b2c3                          0
aabbcc123                       0
    • 검색 위치를 3으로 지정해서 3번째 문자 이후부터 해당 소문자 ‘c’ 가 나오는 개수를 세는 예
    SCOTT>SELECT text, REGEXP_COUNT(text, 'c', 3)
  2  FROM t_reg;
    

TEXT       REGEXP_COUNT(TEXT,'C',3)
---------- ------------------------
ABC123                            0
ABC 123                           0
ABC  123                          0
abc 123                           1
abc  123                          1
a1b2c3                            1
aabbcc123                         2
?/!@#$*&                          0
\~*().,                           0
123123                            0
123abc                            1
abc                               1

    

SCOTT>SELECT text, REGEXP_COUNT(text, 'c') "RESULT 1",
  2                REGEXP_COUNT(text, 'c', 1, 'i') "RESULT 2"   //대소문자 구분 없이 (즉 'C' 와 'c' 모두) 몇 개가 나오는 지 세어 출력
  3  FROM t_reg;
    

TEXT         RESULT 1   RESULT 2
---------- ---------- ----------
ABC123              0          1
ABC 123             0          1
ABC  123            0          1
abc 123             1          1
abc  123            1          1
a1b2c3              1          1
aabbcc123           2          2
?/!@#$*&            0          0
\~*().,             0          0
123123              0          0
123abc              1          1
abc                 1          1

    

SCOTT>SELECT text,
  2          REGEXP_COUNT(text, 'aa') RESULT1,
  3          REGEXP_COUNT(text, 'a{2}') RESULT2,
  4          REGEXP_COUNT(text, '(a)(a)') RESULT3
  5  FROM t_reg;
    

TEXT          RESULT1    RESULT2    RESULT3
---------- ---------- ---------- ----------
ABC123              0          0          0
ABC 123             0          0          0
ABC  123            0          0          0
abc 123             0          0          0
abc  123            0          0          0
a1b2c3              0          0          0
aabbcc123           1          1          1
?/!@#$*&            0          0          0
\~*().,             0          0          0
123123              0          0          0
123abc              0          0          0
abc                 0          0          0



    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 4. JOIN  (796) 2016.09.12
    [SQL] 3. 복수행함수(그룹함수)  (153) 2016.09.05
    [SQL] 2. 단일행함수(일반함수)  (11) 2016.09.02
    [SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수)  (993) 2016.08.30
    [SQL] 2. 단일행함수(문자함수)  (7) 2016.08.29

    일반함수

    함수의 입력되는 값이 숫자, 문자, 날짜 구분없이 모두 사용할 수 있는 함수. 일반적으로 많이 사용하는 함수 위주.



  • NVL(컬럼, 치환할 값) : 컬럼값이 null일 경우, 해당 값으로 치환

  • NVL2(Col1,Col2,Col3) : Col1의 값이 null이 아니면 Col2를, null이면 Col3 출력

    • ex) emp 테이블에서 deptno 가 30번 인 사람들의 empno , ename , sal , comm 값을 출력하되 만약 comm 값이 null 이 아니면 sal+comm 값을 출력하고 comm 값이 null 이면 sal*0 의 값을 출력

      SCOTT>SELECT empno, ename, sal, comm,
2          NVL2(comm,sal+comm,sal*0) "NVL2"
3  FROM emp
4  WHERE deptno = 30;

EMPNO ENAME             SAL       COMM       NVL2
---------- ---------- ---------- ---------- ----------
7499 ALLEN            1600        300       1900
7521 WARD             1250        500       1750
7654 MARTIN           1250       1400       2650
7698 BLAKE            2850                     0
7844 TURNER           1500          0       1500

  • DECODE() : 개발 언어에서 사용중인 IF 문을 오라클 SQL 안으로 가져온 함수. 오라클에서만 사용됨.

    • DECODE(A, B, '1', null) : A=B면 '1' 출력, 아니면 null출력 (null 생략가능)
    • DECODE(A, B, '1', '2') : A=B면 '1' 출력, 아니면 '2' 출력.

    • DECODE(A, B, '1', C, '2', '3') : A=B면 '1' 출력, A=C면 '2' 출력. 둘다아니면 '3' 출력.(조건과 결과 추가해 확장가능)

      • ex) Professor 테이블에서 교수의 이름과 학과 명을 출력하되 학과 번호가 101 번 이면 ‘Computer Engineering’ , 102 번이면 ‘Multimedia Engineering' , 103 번이면 ‘Software Engineering ‘ 나머지는 ‘ETC’ 로 출력 
        SCOTT>SELECT deptno, name, DECODE(deptno,101,'Computer Engineering',
2                                      102,'Multimedia Engineering',
3                                      103,'Software Engineering',
4                                          'ETC') "DNAME"
5  FROM professor;
DEPTNO NAME                      DNAME
------- --------------- -----------------

101 Audie Murphy              Computer Engineering
101 Angela Bassett            Computer Engineering
101 Jessica Lange             Computer Engineering
102 Winona Ryder              Multimedia Engineering
102 Michelle Pfeiffer         Multimedia Engineering
102 Whoopi Goldberg           Multimedia Engineering
103 Emma Thompson             Software Engineering
103 Julia Roberts             Software Engineering
103 Sharon Stone              Software Engineering
201 Meryl Streep              ETC
201 Susan Sarandon            ETC
202 Nicole Kidman             ETC
202 Holly Hunter              ETC

    • DECODE(A, B, DECODE(C,D, '1', null)) : A=B일 경우 C=D면 '1'출력, 아니면 null출력 (null생략가능)

      • ex) professor 테이블에서 교수의 이름과 부서번호를 출력하고 101 번 부서 중에서 이름이 "Audie Murphy" 교수에게 "BEST!" 라고 출력하고 101번 부서 중에서 이름이 "Audie Murphy" 교수가 아닌 나머지에는 NULL 값을 출력하시오. 만약 101 번 외 다른 학과에 "Audie Murphy" 교수가 있어도 "BEST!" 가 출력되면 안됨. 
        SCOTT>SELECT deptno, name, DECODE(deptno, 101, DECODE(name,'Audie Murphy','BEST!',NULL)) "decode"
2  FROM professor;
DEPTNO NAME                 decode
-------- ------------ --------


        101 Audie Murphy         BEST!
101 Angela Bassett
101 Jessica Lange
102 Winona Ryder
102 Michelle Pfeiffer
102 Whoopi Goldberg
103 Emma Thompson
103 Julia Roberts

    • DECODE(A, B, DECODE(C, D, '1', '2')) : A=B일 경우, C=D면 '1'출력, 아니면 '2'출력.

    • DECODE(A, B, DECODE(C, D, '1', '2'), '3') : A=B일경우, C=D면 '1'출력, 아니면 '2'출력, A=B아니면 '3'출력.
      • ex) professor 테이블에서 교수의 이름과 부서번호를 출력하고 101 번 부서 중에서 이름이 "Audie Murphy" 교수에게 비고란에 “BEST!” 이라고 출력하고 101번 학과의 "Audie Murphy" 교수 외에는 비고란에 “GOOD!”을 출력하고 101번 교수가 아닐 경우는 비고란에 "N/A" 을 출력. 
        SCOTT>SELECT deptno, name,
2          DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!', 'GOOD!'), 'N/A') "ETC"
3  FROM professor;
DEPTNO NAME                 ETC
---------- -------------------- -----
101 Audie Murphy         BEST!
101 Angela Bassett       GOOD!
101 Jessica Lange        GOOD!
102 Winona Ryder         N/A
102 Michelle Pfeiffer    N/A
102 Whoopi Goldberg      N/A

  • CASE() : DECODE 함수는 주로 = 인 값을 처리하는데 주로 사용되고, CASE문은 크거나 작은 조건을 처리할 경우 쉽게 처리할 수 있음. DECODE 함수와는 다르게 함수내부가 콤마(,)로 조건들이 구분되지 않는것을 주의해야함. 

     CASE(조건) WHEN 결과1 THEN 출력1
           [WHEN 결과2 THEN 출력2] 
            ELSE 출력3
 END "컬럼명"

    • ex1) decode와 동일하게 '='조건으로 사용되는 경우. (복잡해서 주로 decode로사용)

      SCOTT>SELECT name, tel,
2          CASE(SUBSTR(tel,1,INSTR(tel,')')-1)) WHEN '02' THEN 'SEOUL'
3                                               WHEN '031' THEN 'GYEONGGI'
4                                               WHEN '051' THEN 'BUSAN'
5                                               WHEN '052' THEN 'ULSAN'
6                                               WHEN '055' THEN 'GYEONGNAM'
7                                                          ELSE 'ETC'
8          END "LOC"
9  FROM student
10  WHERE deptno1 = 201;
NAME                           TEL             LOC
---------------- ----------- -----------

Demi Moore                     02)6255-9875    SEOUL
Macaulay Culkin                02)312-9838     SEOUL
Wesley Snipes                  053)736-4981    ETC
Steve Martin                   02)6175-3945    SEOUL
Sean Connery                   02)381-5440     SEOUL
Christian Slater               031)345-5677    GYEONGGI

    • ex2) 비교조건이 '='이 아닌경우 (Student 테이블의 jumin 컬럼을 참조하여 학생들의 이름과 태어난 달 , 그리고 분기를 출력하세요. 태어난 달이 01-03월 은 1/4, 04 – 06월 은 2/4 , 07 – 09 월 은 3/4 , 10 – 12 월은 4/4 로 출력)

      SCOTT>SELECT name, SUBSTR(jumin,3,2) "MONTH",
2          CASE WHEN SUBSTR(jumin,3,2) BETWEEN '01' AND '03' THEN '1/4'
3               WHEN SUBSTR(jumin,3,2) BETWEEN '04' AND '06' THEN '2/4'
4               WHEN SUBSTR(jumin,3,2) BETWEEN '07' AND '09' THEN '3/4'
5               WHEN SUBSTR(jumin,3,2) BETWEEN '10' AND '12' THEN '4/4'
6          END "Quarter"
7  FROM student;
NAME                           MONT Qua
------------------ --- ----

James Seo                      10   4/4
Rene Russo                     02   1/4
Sandra Bullock                 06   2/4
Demi Moore                     12   4/4
Danny Glover                   03   1/4
Billy Crystal                  01   1/4
Nicholas Cage                  04   2/4
Micheal Keaton                 09   3/4
Bill Murray                    01   1/4

    decode와 비슷하지만 더 넓은 범위에서 사용할 수 있어 sql 튜닝에서 요긴하게 많이 사용됨. 함수를 많이 알수록 성능 좋은 쿼리를 작성할 수 있음.



    • QUIZ1

    Professor 테이블에서 201번 학과 교수들의 이름과 급여, bonus , 총 연봉을 아래와 같이 출력하시오. 단 총 연봉은 (pay*12+bonus) 로 계산하고 bonus 가 없는 교수는 0으로 계산.




    QUIZ2

    아래 화면과 같이 emp 테이블에서 deptno 가 30 번인 사원들을 조회하여 comm 값이 있을 경우 'Exist' 을 출력하고 comm 값이 null 일 경우 'NULL' 을 출력하시오.




    QUIZ3

    Student 테이블을 사용하여 제 1 전공 (deptno1) 이 101 번인 학과 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호(jumin) 컬럼을 이용하여 7번째 숫자가 1일 경우 “ 남자” , 2일 경우 “여자 ” 로 출력하시오.




    QUIZ4

    Student 테이블에서 1 전공이 (deptno1) 101번인 학생의 이름과 연락처와 지역을 출력하시오. 단,지역번호가 02 는 "SEOUL" , 031 은 "GYEONGGI" , 051 은 "BUSAN" , 052 는 "ULSAN" , 055 는 "GYEONGNAM".




    QUIZ5

    emp 테이블을 조회하여 empno , ename , sal , LEVEL(급여등급)을 아래와 같이 출력하시오. 단 급여등급은 sal 을 기준으로 1 - 1000 이면 Level 1 , 1001 - 2000 이면 Level 2 , 2001 - 3000 이면 Level 3 , 3001 - 4000 이면 Level 4 , 4001 보다 많으면 Level 5 로 출력




    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 3. 복수행함수(그룹함수)  (153) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (315) 2016.09.05
    [SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수)  (993) 2016.08.30
    [SQL] 2. 단일행함수(문자함수)  (7) 2016.08.29
    [SQL] 1. 오라클 서버접속, 기본 SELECT 구문  (9) 2016.08.24

    숫자 함수

      함수명

     ROUND

     반올림 한 후 출력

     ROUND(12.345, 2)    ->  12.35

     TRUNC

     버림 한 후 출력

     TRUNC(12.345, 2)     ->  12.34 

     MOD

     나머지 값 출력

     MOD(12, 10)             ->  2

     CEIL

     가장 근접한 큰 정수 출력

     CEIL(12.345)             ->  13

     FLOOR

     가장 근접한 작은 정수 출력

     FLOOR(12.345)         ->  12 

     POWER

     제곱승 출력

     POWER(3, 2)            ->  9




  • ROUND(숫자, 자리수) : 주어진숫자를 반올림 후 출력. 소수점기준(자리수)
  • TRUNC(숫자, 자리수) : 버림.
  • MOD() : 나머지값 출력 /CEIL() : 가장 가까운 큰정수 출력 / FLOOR() : 가장 가까운 작은정수

    • ex) CEIL 함수의 경우 여러 데이터들을 하나의 기준으로 묶을 때 주로 사용함. 아래의 예는 emp 테이블의 13 건의 데이터를 출력하되 3 줄씩 나누어서 한 조로 만드는 예.

      SQL> SELECT rownum "ROWNO", CEIL(rownum/3) "TEAMNO", ename    //rownum은 출력 줄번호
2  FROM emp;

ROWNO     TEAMNO ENAME
----- --------- ----------
 1          1 SMITH
 2          1 ALLEN
 3          1 WARD
 4          2 JONES
 5          2 MARTIN
 6          2 BLAKE
 7          3 CLARK
 8          3 SCOTT
 9          3 KING
10          4 TURNER
11          4 JAMES
12          4 FORD
13          5 MILLER

  • POWER(2,3) : 2의 3승 값을 출력



    날짜 함수

  • SYSDATE : 현재 시스템의 시간을 출력
    • 윈도우와 유닉스(리눅스)의 날짜형식 다름 
      SQL> SELECT SYSDATE FROM dual;  //리눅스형식
SYSDATE
-----------
30-AUG-16
    • 윈도우 날짜형식으로 변환가능 
      SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD';
Session altered. 
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
  • MONTHS_BETWEEM() : 두날짜 사이의 개월수를 출력
    • 두 날짜중 큰 날짜를 먼저 써야 양수가 출력
    • 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 출력(같은 1개월이라도 29일,30일,31일 같은 경우마다 모두 결과값이 다를것)
  • ADD_MONTHS() : 주어진 날짜에 숫자만큼의 달을 추가
  • NEXT_DAY() : 주어진날짜 기준으로 돌아오는 가장 최근 요일의 날짜를 반환
  • LAST_DAY() : 주어진날짜가 속한 달의 가장 마지막 날을 출력
  • ROUND() : 정오를 넘겼을경우 다음 날짜로 출력하고 안넘겼을 경우는 당일로 출력
  • TRUNC() : 무조건 당일로 출력


    • 형변환 함수

  • 묵시적(자동) 형 변환 : 오라클이 자동으로 형을 바꿔줌. 튜닝에서 뜻하지 않게 속도가 느려지는 주범이 될 수 있으므로 조심할것 (속도가 느려지는 이유는 해당 컬럼에 인덱스가 생성되어 있을 경우 묵시적 형 변환이 생기면 그 인덱스를 못쓰게 되고 그렇게 되면 Index Suppressing error 발생) / 숫자처럼 생긴 문자만 변환해줌.

  • 명시적(수동) 형 변환 : 형변환 함수를 통해 수동으로 데이터 형을 바꾸는것. 



    형변환 함수는 위 그림처럼 TO_CHAR, TO_NUMBER, TO_DATE 세가지로 나뉘고, TO_CHAR 함수는 숫자와 날짜를 문자로 변환해주는 두가지 기능을 한꺼번에 가짐.


     데이터 타입

     CHAR(n)

     고정길이의 문자를 저장. 최대값 2000 bytes

     VARCHAR2(n)

     변하는 길이의 문자를 저장. 최대값 4000 bytes

     NUMBER(p,s)

     숫자 값을 저장. p 는 전체 자리수로 1-38 자리까지 가능하고 s는 소수점 이하 자리수로 -84 ~ 127 자리까지 가능

     DATE

     7Byte로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장.

     LONG

     가변 길이의 문자를 저장. 최대 2GB 까지 저장

     CLOB

     가변 길이의 문자를 저장. 최대 4GB 까지 저장 할 수 있음

     BLOB

     가변 길이의 바이너리 데이터를 최대 4GB 까지 저장 할 수 있음

     RAW(n)

     원시 이진 데이터로 최대 2000 bytes 까지 저장

     LONG RAW(n)

     원시 이진 데이터로 최대 2GB까지 저장
     BFILE

     외부 파일에 저장된 데이터로 최대 4GB 까지 저장


    • TO_CHAR(날짜 or 숫자,'원하는형태')
      • ex) TO_CHAR(sysdate,'RRRR-MM-DD:HH:MI:SS')
      • ex) TO_CHAR(12345, '99,999') : 12,345 
        SCOTT>SELECT empno, ename, sal, comm,
2          TO_CHAR((sal*12)+comm, '999,999') "SALARY"
3  FROM emp
4  WHERE ename = 'ALLEN';
EMPNO ENAME             SAL       COMM SALARY
---------- ---------- ---------- ---------- --------
7499 ALLEN            1600        300   19,500

    cf) 날짜와 숫자를 표현하는 방법

    [ 년도 ]
    ‘YYYY’ – 연도를 4자리로 표현.
    ‘RRRR’ – 2000년 이후에 Y2K 버그로 인해 등장한 날짜 표기법으로 연도 4자리 표기법.
    ‘YY’ – 연도를 끝의 2 자리만 표시. 예 : 14
    'RR' - 연도를 마지막 2 자리만 표시. 예: 14
    ‘YEAR’ – 연도의 영문 이름 전체를 표시.

    [ 월 ]
    MM – 월을 숫자 2자리로 표현. 예: 10
    MON – 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시. 예: OCT 윈도용 오라클일 경우는 MONTH 와 동일.
    MONTH – 월을 뜻하는 이름 전체를 표시

    [ 일 ]
    DD – 일을 숫자 2자리로 표시. 예: 26
    DAY – 요일에 해당하는 명칭을 표시하는데 유닉스용 오라클에서는 영문으로 나오고 윈도용 오라클에서는 한글로 나옴.
    DDTH – 몇 번째 날인지를 표시.

    [ 시간 ]
    HH24 – 하루를 24시간으로 표시.
    HH – 하루를 12 시간으로 표시.
    분 : MI 로 표시.
    초 : SS 로 표시.

    [숫자]
    9 의 개수만큼 자리수 TO_CHAR(1234,’99999’) 1234
    빈자리를 0으로 채움 TO_CHAR(1234,’099999’) 001234
    $ 표시를 붙여서 표시 TO_CHAR(1234,’$9999’) $1234
    소수점 이하를 표시 TO_CHAR(1234,’9999.99’) 1234.00
    천 단위 구분기호를 표시 TO_CHAR(12345,’99,999’) 12,345

    • TO_NUMBER('숫자처럼 생긴 문자')
    • ASCII('문자') : 문자를 아스키 코드 값으로 출력
    • TO_DATE('날짜처럼 생긴 문자')


    • QUIZ1

    Student 테이블의 birthday 컬럼을 사용하여 생일이 1월인 학생의 이름과 birthday 를 아래 화면과 같이 출력하시오.




    QUIZ2

    emp 테이블의 hiredate 컬럼을 사용하여 입사일이 1,2,3 월인 사람들의 사번과 이름, 입사일을 출력하시오.




    QUIZ3

    emp 테이블을 조회하여 comm 값을 가지고 있는 사람들의 empno , ename , hiredate , 총연봉,15% 인상 후 연봉을 아래 화면처럼 출력하세요. 단 총연봉은 (sal*12)+comm 으로 계산하고 아래 화면에서는 SAL 로 출력되었으며 15% 인상한 값은 총연봉의 15% 인상 값임. (HIREDATE 컬럼의 날짜 형식과 SAL 컬럼 , 15% UP 컬럼의 $ 표시와 , 기호 나오게)





    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 3. 복수행함수(그룹함수)  (153) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (315) 2016.09.05
    [SQL] 2. 단일행함수(일반함수)  (11) 2016.09.02
    [SQL] 2. 단일행함수(문자함수)  (7) 2016.08.29
    [SQL] 1. 오라클 서버접속, 기본 SELECT 구문  (9) 2016.08.24

    단일행 함수



    문자 함수

  • INITCAP(컬럼명 or '문자열') : 영어에서 첫 글자만 대문자로 출력(나머지는 소문자)
  • LOWER(컬럼명 or '문자열') / UPPER() : 입력되는 값을 전부 소문자/대문자로 출력
  • LENGTH(컬럼명 or '문자열') / LENGTHB() : 입력된 문자열의 길이/바이트수 를 계산
  • CONCAT('문자열1', '문자열2') = || 연산자와 동일
  • SUBSTR(컬럼명 or '문자열', 시작위치, 자릿수) : 주어진 문자열에서 특정길이의 문자만 추출/ SUBSTRB()의 자릿수에는 추출할 바이트수를 지정
    • 시작위치 숫자를 -(마이너스)로 지정할 경우 뒤에서부터(오른쪽) 세어 시작위치를 지정/ 자릿수는 left -> right 방향으로 동일
    • ex) Student 테이블에서 jumin 컬럼을 사용해서 1 전공이 101번인 학생들의 이름과 태어난 월일 , 생일 하루 전 날짜를 출력하시오 
      SQL> SELECT name, SUBSTR(jumin,3,4) "Birthday",
2               SUBSTR(jumin,3,4)-1 "Birthday - 1"
3  FROM student
4  WHERE deptno1 = 101;

NAME                 Birthday Birthday - 1
-------------------- -------- ------------
James Seo            1023             1022
Billy Crystal        0123              122
Richard Dreyfus      1129             1128
Danny Devito         0819              818

  • INSTR(컬럼명 or '문자열', 찾는글자, 시작위치(기준), 몇번째(default:1)) : 특정 글자의 위치를 추출

    • 시작 위치를 -(마이너스)로 지정할 경우 left <- right(오른쪽에서 왼쪽) 으로 검색해 시작위치지정
    • 시작위치와 찾으려는 위치 값은 생략가능하며 생략할 경우자동으로 1로 설정
    • ex) 문자열 A-B-C-D에서 오른쪽에서 6번째 문자를 기준으로 2번째'-'가 나오는 위치의 값을 출력하시오. 
      SQL> SELECT 'A-B-C-D' "ABCD", INSTR('A-B-C-D','-', -6, 2) "INSTR"
2  FROM dual;

ABCD         INSTR
----------- ---------
A-B-C-D             0
    • ex2) Student 테이블의 tel 컬럼을 사용하여 1 전공번호(deptno1)가 201번인 학생의 이름과 전화번호, ‘)‘ 가 나오는 위치를 출력하시오.

      SQL> SELECT name, tel, INSTR(tel, ')') "INSTR()"
 2  FROM student
3  WHERE deptno1 = 201;

NAME                 TEL                INSTR()
-------------------- --------------- ----------

Demi Moore           02)6255-9875             3
Macaulay Culkin      02)312-9838              3
Wesley Snipes        053)736-4981             4

  • LPAD(컬럼 or '문자열', 전체자리수, '채울문자') / RPAD() : 원래 문자열을 제외한 나머지 자리(왼쪽/오른쪽)에 문자를 채움.

    • ex) student 테이블에서 1 전공이 201 번인 학과 학생들의 id 를 총 10자리로 출력하되 왼쪽 빈 자리는 ‘*’ 기호로 채우세요 
      SQL> SELECT name, id, LPAD(id,10,'*') "LPAD()"
2  FROM student
3  WHERE deptno1 = 201;

NAME                 ID         LPAD()
-------------------- ---------- ------------------- 
Demi Moore           Moore      *****Moore
Macaulay Culkin      Culkin     ****Culkin
Wesley Snipes        Snipes     ****Snipes

  • LTRIM(컬럼 or '문자열', '제거할문자') / RTRIM() : 해당문자를 제거

    • ex) emp 테이블의 ename에서 마지막 글자가 'R'이 있을경우 제거함. 
      SQL> SELECT ename, RTRIM(ename, 'R') "RTRIM"
2  FROM emp
3  WHERE deptno = 10;

ENAME      RTRIM
------- --------
CLARK      CLARK
KING       KING
MILLER     MILLE

  • REPLACE(컬럼 or '문자열', '문자1', '문자2') : 주어진 문자열에서 문자1을 문자2로 바꿔 출력

    • ex) emp 테이블에서 이름의 첫 두글자를 *로 표시하시오. 
      SQL> SELECT ename, REPLACE(ename, SUBSTR(ename, 1,2), '**') "REPLACE"
2  FROM emp
3  WHERE deptno = 10;

ENAME      REPLACE
---------- --------------------
CLARK      **ARK
KING       **NG
MILLER     **LLER


    • QUIZ1

    Student 테이블을 참조해서 아래 화면과 같이 1 전공이(deptno1 컬럼) 201번인 학생의 이름과 전화번호와 지역번호를 출력하세요. 단 지역번호는 숫자만 나와야함. 

    QUIZ2

    아래 화면과 같이 emp 테이블에서 deptno 가 10번인 사원들의 이름을 총 9자리로 출력하되 오른쪽 빈자리에는 해당 자리 수에 해당되는 숫자가 출력되도록 하시오 


    QUIZ3

    Student 테이블에서 아래와 같이 deptno1 이 101 번인 학과 학생들의 이름과 전화번호와 전화번호에서 지역번호와 국번을 제외한 나머지 번호를 * 로 표시해서 출력하시오.





    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 3. 복수행함수(그룹함수)  (153) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (315) 2016.09.05
    [SQL] 2. 단일행함수(일반함수)  (11) 2016.09.02
    [SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수)  (993) 2016.08.30
    [SQL] 1. 오라클 서버접속, 기본 SELECT 구문  (9) 2016.08.24

    Numpy 시작하기

    import numpy

def matrix_tutorial():
    # 3×4 의 크기를 가진 2차원 배열 선언
    A = numpy.array([[1,4,5,8],
                     [2,1,7,3],
                     [5,4,5,9]])
    return A

print(matrix_tutorial())


    행렬변환

    
import numpy

def matrix_tutorial():
    A = numpy.array([[1,4,5,8], [2,1,7,3], [5,4,5,9]])

    # 1 A의 shape을 (3, 4)에서 (6, 2)로 변환
    B = A.reshape((6,2))
    # 2 B 밑에 행렬추가
    B = numpy.concatenate((B,[[2,2],[5,3]]), axis = 0)
    # 3 길이가 늘어난 B를 세로로 두 개로 쪼개고, 위-아래 순서대로 저장
    C = numpy.split(B, 2, axis = 0)[0]
    D = numpy.split(B, 2, axis = 0)[1]
    # 4 C와 D를 가로로 붙여 E생성
    E = numpy.concatenate((C,D), axis=1)
    # 5
    return E

print(matrix_tutorial())


    기초통계처리

    import numpy

def matrix_tutorial():
    A = numpy.array([[1,4,5,8], [2,1,7,3], [5,4,5,9]])
    B = A.reshape((6, 2))
    B = numpy.concatenate((B, numpy.array([[2, 2], [5, 3]])), axis = 0)
    (C, D) = numpy.split(B, 2, axis = 0)
    E = numpy.concatenate((C, D), axis = 1)

    # 1 E를 normalize(표준화)해 E안의 모든 원소 합이 1이되도록 함
    E = E / numpy.sum(E)
    # 2 E의 분산
    variance = numpy.var(E)
    return variance

print(matrix_tutorial())


    행렬데이터 입력 및 출력

    import numpy

def matrix_tutorial(A):

    # 2 A의 전치행렬 (transpose) B 를 생성
    B = A.transpose()

    try:
        #3 - 1 B의 역행렬을 구하여 C로 저장
        C = numpy.linalg.inv(B)
    except:
        #3 - 2 역행렬을 구하는 것이 불가능하다면 not invertible 을 리턴
        return "not invertible"

    # 4 C 안에 들어있는 0보다 큰 원소 (positive) 들의 개수를 리턴합니다.
    return numpy.sum(C > 0)

def get_matrix():
    # 텍스트 입력을 통해 행렬을 입력받아 A로 저장
    mat = [] # define mat variable

    first_line = input().strip() # receive first line
    first_line_splitted = first_line.split(" ") # split line by space " "
    n = int(first_line_splitted[0]) # convert to integer
    m = int(first_line_splitted[1]) # convert to integer

    for i in range(n):
        line = input().strip() # receive each line ...
        row = line.split(" ")  # ... and split
        for j in range(m):
            row[j] = int(row[j]) # convert to integer
        mat.append(row)

    return numpy.array(mat)

if __name__ == "__main__":
    A = get_matrix()
    print(matrix_tutorial(A))


    선형회귀법: X,Y 입력받기

    
import numpy

def main():
    (N, X, Y) = read_data()
    print(N)
    print(X)
    print(Y)

    
    
def read_data():
#입력       출력
#3          3
#1 5.1      [1.0, 1.7, 3.0]
#1.7 8      [5.1, 8.0, 10.4]
#3 10.4
    N = int(input())
    X = []
    Y = []
    for i in range(N) :
        line = input().strip().split(" ")
        X.append(float(line[0]))
        Y.append(float(line[1]))
    return (N, X, Y)

if __name__ == "__main__":
    main()
    저작자표시 비영리 변경금지

    'python' 카테고리의 다른 글

    1. 파이썬 기본 자료구조  (972) 2016.08.26
    [강의] 파이썬  (5) 2016.02.01

    함수와 변수


    #함수
def subtract(a,b):    
#2 subtract(a,b) = a-b
	return a-b
print(subtract(1, 10))

#변수
hello = "hello"
world = "world"
helloworld = hello + " " + world

print(helloworld)


    리스트와 튜플

    #리스트
def exercise(my_language):
    # 1
    programming_languages = ["C#", "Javascript","Java","Python","Matlab","R"]
    # 2
    del programming_languages[1] #Javascript 를 리스트에서 삭제
    # 3
    programming_languages.append("C++") #C++ 을 리스트의 끝에 추가
    # 4
    is_my_language = (my_language in programming_languages) 

    return is_my_language

print(exercise("R"))

#튜플
def exercise(my_language):
    # 1
    programming_languages = ("C#","Javascript","Java","Python","Matlab","R")
    # 2
    programming_languages = (programming_languages[2:5]) # Java부터 Matlab까지 3개의 원소만을 가지는 새로운 튜플을 만들고 다시할당
    # 3
    new_programming_languages = ("Swift","Go")
    # 4
    programming_languages = programming_languages + new_programming_languages 
    # 5
    is_my_language = (my_language in programming_languages)

    return is_my_language

print(exercise('R'))


    딕셔너리

    def exercise(my_language):
    # 1
    programming_languages_popularity = {
        'Java': 24.0,
        'Python':13.2,
        'PHP':10.4,
        'C#':9.0
    }
    # 2
    del programming_languages_popularity['PHP']
    programming_languages_popularity['Javascript'] = 7.6
    programming_languages_popularity['C++'] = 7.0
    # 3
    my_language_popularity = programming_languages_popularity[my_language]
    
    return my_language_popularity

print(exercise('Python'))


    조건문과 반복문

    def exercise(score):
    # 1
    if score < 50: 
        evaluation = "Below average"
    elif score < 70: 
        evaluation = "Average"
    elif score < 90: 
        evaluation = "Above average"
    else : 
        evaluation = "Excellent"
    return evaluation

print(exercise(80))


def exercise(big_list):
    # 1
    small_list = []
    # 2
    for big_list in big_list:
        small_list.append(big_list.lower()) #small_list 에 big_list 에 있는 모든 문자열들을 소문자화
           
        
    return small_list

print(exercise(['C#', 'JAVASCRIPT', 'JAVA', 'PYTHON', 'MATLAB', 'R']))


    함수구현

    #1
def sum_even_numbers(start, end):
#2
    sum_even = 0
    for x in range(start,end+1):
        if x % 2 == 0:
            sum_even += x          #start <= x <= end 인 모든 x 에 대해, 짝수인 x 를 모두 더한 값을 리턴
            
    return sum_even
        
print(sum_even_numbers(1, 10))


    저작자표시 비영리 변경금지

    'python' 카테고리의 다른 글

    2. Numpy 사용하기  (312) 2016.08.26
    [강의] 파이썬  (5) 2016.02.01

    새로운 저장소 만들기

    폴더를 하나 만들고, 그 안에서 아래 명령을 실행하세요.
    git init
    새로운 git 저장소가 만들어집니다.

    저장소 받아오기

    로컬 저장소를 복제(clone)하려면 아래 명령을 실행하세요.
    git clone /로컬/저장소/경로
    원격 서버의 저장소를 복제하려면 아래 명령을 실행하세요.
    git clone 사용자명@호스트:/원격/저장소/경로

    작업의 흐름

    여러분의 로컬 저장소는 git이 관리하는 세 그루의 나무로 구성돼있어요.
    첫번째 나무인 작업 디렉토리(Working directory)는 실제 파일들로 이루어져있고, 두번째 나무인 인덱스(Index)는 준비 영역(staging area)의 역할을 하며, 마지막 나무인 HEAD는 최종 확정본(commit)을 나타내요.


    로컬 저장소는 이렇게 생겼어요.

    추가와 확정(commit)
    변경된 파일은 아래 명령어로 (인덱스에) 추가할 수 있어요.
    git add <파일 이름>
    git add *
    이것이 바로 git의 기본 작업 흐름에서 첫 단계에 해당돼요.
    하지만 실제로 변경 내용을 확정하려면 아래 명령을 내려야 한답니다.
    git commit -m "이번 확정본에 대한 설명"
    자, 이제 변경된 파일이 HEAD에 반영됐어요.
    하지만, 원격 저장소에는 아직 반영이 안 됐답니다.



    변경 내용 발행(push)하기

    현재의 변경 내용은 아직 로컬 저장소의 HEAD 안에 머물고 있어요.
    이제 이 변경 내용을 원격 서버로 올려봅시다. 아래 명령을 실행하세요.
    git push origin master
    (다른 가지를 발행하려면 master를 원하는 가지 이름으로 바꿔주세요.)
    만약 기존에 있던 원격 저장소를 복제한 것이 아니라면,
    원격 서버의 주소를 git에게 알려줘야 해요.
    git remote add origin <원격 서버 주소>
    이제 변경 내용을 원격 서버로 발행할 수 있어요.

    가지(branch)치기

    가지는 안전하게 격리된 상태에서 무언가를 만들 때 사용해요.
    여러분이 저장소를 새로 만들면 기본으로 master 가지가 만들어집니다.
    이제 다른 가지를 이용해서 개발을 진행하고, 나중에 개발이 완료되면
    master 가지로 돌아와 병합하면 돼요.




  • 가지치기 예제

    • 아래 명령으로 "feature_x"라는 이름의 가지를 만들고 갈아탑니다.
    git checkout -b feature_x
    아래 명령으로 master 가지로 돌아올 수 있어요.
    git checkout master
    아래 명령으로는 가지를 삭제할 수 있어요.
    git branch -d feature_x
    여러분이 새로 만든 가지를 원격 저장소로 전송하기 전까지는 다른 사람들이 접근할 수 없어요.
    git push origin <가지 이름>

    갱신과 병합(merge)

    여러분의 로컬 저장소를 원격 저장소에 맞춰 갱신하려면 아래 명령을 실행하세요.
    git pull
    이렇게 하면 원격 저장소의 변경 내용이 로컬 작업 디렉토리에 받아지고(fetch), 병합(merge)된답니다.
    다른 가지에 있는 변경 내용을 현재 가지(예를 들면, master 가지)에 병합하려면 아래 명령을 실행하세요.
    git merge <가지 이름>
    첫번째 명령이든 두번째 명령이든, git은 자동으로 변경 내용을 병합하려고 시도해요.
    문제는, 항상 성공하는 게 아니라 가끔 충돌(conflicts)이 일어나기도 한다는 거예요.
    이렇게 충돌이 발생하면, git이 알려주는 파일의 충돌 부분을 여러분이 직접 수정해서 병합이 가능하도록 해야 하죠.
    충돌을 해결했다면, 아래 명령으로 git에게 아까의 파일을 병합하라고 알려주세요.
    git add <파일 이름>
    변경 내용을 병합하기 전에, 어떻게 바뀌었는지 비교해볼 수도 있어요.
    git diff <원래 가지> <비교 대상 가지>

    꼬리표(tag) 달기

    소프트웨어의 새 버전을 발표할 때마다 꼬리표를 달아놓으면 좋아요. (물론 꼬리표는 SVN 등에 이미 존재하는 기능이지요.)
    아래 명령을 실행하면 새로운 꼬리표인 1.0.0을 달 수 있어요.
    git tag 1.0.0 1b2e1d63ff
    위 명령에서 1b2e1d63ff 부분은 꼬리표가 가리킬 확정본 식별자입니다.
    아래 명령으로 확정본 식별자를 얻을 수 있어요.
    git log
    확정본 식별자의 앞부분 일부만 입력해도 꼬리표를 붙일 수 있지만, 그 일부분이 반드시 고유하다는 조건이 필요해요.

    로컬 변경 내용 되돌리기

    만약 여러분이 (물론 그럴 일은 없겠지만 ;) 실수로 무언가 잘못한 경우, 아래 명령으로 로컬의 변경 내용을 되돌릴 수 있어요.
    git checkout -- <파일 이름>
    위 명령은 로컬의 변경 내용을 변경 전 상태(HEAD)로 되돌려줘요.
    다만, 이미 인덱스에 추가된 변경 내용과 새로 생성한 파일은 그대로 남는답니다.

    만약, 로컬에 있는 모든 변경 내용과 확정본을 포기하려면, 아래 명령으로 원격 저장소의 최신 이력을 가져오고, 로컬 master 가지가 저 이력을 가리키도록 할 수 있어요.
    git fetch origin
    git reset --hard origin/master

    유용한 힌트

    git의 내장 GUI: gitk
    콘솔에서 git output을 컬러로 출력하기: git config color.ui true
    이력(log)에서 확정본 1개를 딱 한 줄로만 표시하기
    git config format.pretty oneline
    파일을 추가할 때 대화식으로 추가하기
    git add -i




    [출처]

    https://rogerdudler.github.io/git-guide/index.ko.html

    저작자표시 비영리 변경금지

    티스토리툴바