06-28(월) 변환조건그룹 함수, CASE, COALESCE

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

Sql  변환함수

TO_NUMBER   문자값->숫자값         ∴숫자
TO_DATE        문자값->날짜값         ∴날짜
TO_CHAR       숫자,날짜값->문자값   ∴문자
      TO_CHAR( 숫자날짜or컬럼 ,  '       ')   '  ' 이거 완전 필수!=>이거 실수 좀 심함
      TO_CHAR(      HIREDATE   , 'YYYY'  )   연도4자리로 문자형식
      TO_CHAR(     123456       , '999,999')   숫자3자리마다 ,로 문자형식

 

*TO_DATE는    ALTER SESSION 하면 시스템 날짜표기법 바꿔짐

*숫자형식 $  ,  L

  ex) TO_CHAR(SAL, '$999,999')  달러

       TO_CHAR(SAL, 'L999,999')  원화(지역통화)

 

---

Sql  조건함수

-IF A문장, T면 끝   F면B문장으로
B문장,                   T면 끝        F면B문장으로
C문장,                                       T면 끝     F면B문장으로
D문장                                                                     전부 F면 D문장으로

---

■ 조건함수 DECODE ★오라클 전용함수라 다른 DBMS에서 못씀!!! 얘는 , 콤마랑 )  잘 써야함★

SELECT * ,         컬럼나열할때도 ,콤마 잘적기
DECODE ( 컬럼 , 컬럼값이 A이면 , 결과1 ,
                       컬럼값이 B이면 , 결과2, 
                       다 아니면 마지막 기본) [별칭]  STH
FROM TABLE;
★★★완전 중요, DECODE 두번째엔 1개의 비교값 밖에 못들어간대
         그래서 두번째엔  컬럼IN(값1, 값2, 값3...) 이런건 못 넣는거임★★★

 

■ 조건함수 CASE  ★모든 DB에서 사용가능, 표현방법이 다양한데 THEN 뒤에 , 콤마 사용안함!!!!!★

① 조건이 일치하는 경우 =
SELECT * ,                   컬럼나열할때도 ,콤마 잘적기
CASE 컬럼  WHEN  비교값1  THEN 결과값1 
                WHEN  비교값2 THEN결과값2
                 다아니면 [ELSE결과값3]
END [별칭] 
FROM TABLE;

=이면
=아니면 < >

② 반드시 일치하지 않는 경우 =말고 < >
SELECT * ,                   컬럼나열할때도 ,콤마 잘적기
CASE   WHEN  컬럼  THEN 결과값1 
          WHEN 컬럼  THEN 결과값2
           다아니면 [ELSE결과값3]
END [별칭] 
FROM TABLE;

★(ANSI) 마지막에 책에 없는 내용 있으니까 책에 정리하기 ★

*CASE 하고 꼭 ELSE를 안 넣어도 된다함 =>이건 WHEN절에서 조건만 충족되면 되는건지, 아님 안되어도 되는건지 모르것네? 복습할때 실험해보기

 

 

■ [오라클 MySQL] COALESCE 함수

두 개 컬럼 중 존재하는 값으로 합치고 싶을 때, 컬럼 병합하는 함수, 여러 열 NULL아닌 컬럼 찾기 

(참고 블로그) https://jhnyang.tistory.com/387

 

SELECT COALESCE( 'A', 'B','C') AS COALESCE_TEST FROM DUAL UNION ALL
SELECT COALESCE(NULL, 'B','C')  FROM DUAL UNION ALL
SELECT COALESCE(NULL,NULL,'C')  FROM DUAL UNION ALL
SELECT COALESCE(NULL,NULL,NULL) FROM DUAL

---

*예제 더보기 누르기

1. 답 SELECT EMPNO, ENAME, HIREDATE

FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM')='12' ;

2. SELECT EMPNO, ENAME, LPAD(SAL, 10,'*') "급여"
FROM EMP;

3. SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') "입사일"
FROM EMP;

4. SELECT EMPNO, ENAME, COMM, 
CASE WHEN COMM IS NULL THEN '일반사원' 
     ELSE '영업사원'
END 사원종류
FROM EMP ;

5.내답)SELECT LOC,
CASE WHEN LOC IN('NEW YORK','BOSTON') THEN 'EAST'
     WHEN LOC IN('DALLAS','CHICAGO') THEN 'CENTER'
     ELSE 'WEST'
END "AREA"
FROM DEPT;
쌤답) SELECT LOC,
CASE LOC WHEN 'NEW YORK' THEN 'EAST'
                 WHEN 'BOSTEN' THEN 'EAST'
                 WHEN 'CHICAGO' THEN 'CENTER'
                 WHEN 'DALLAS' THEN 'CENTER'
                 ELSE 'ETC'
END "AREA"
FROM DEPT;
DECODE) 

SELECT EMPNO, ENAME, COMM, 
CASE WHEN COMM IS NOT NULL THEN   '영업사원'
     ELSE'일반사원'
END 사원종류
FROM EMP ;

6. 내답) 이거 왜 안돼애애앵ㄱ =>THEN뒤에 , 쓰면안돼...
SELECT ENAME, SAL,
CASE WHEN SAL BETWEEN 0 AND 1000 THEN 0
     WHEN SAL BETWEEN 1001 AND 1999 THEN 500
     ELSE 1000
END "BONUS"
FROM EMP ORDER BY 3 DESC;
=>휴 이거로 하면 뜬다야~
샘 답)
 select ename, sal ,
     CASE WHEN sal >= 2000 THEN 1000
          WHEN sal >= 1000 THEN 500
          ELSE 0
     END bonus
 from emp;

7. CASE내답)
SELECT EMPNO, ENAME, SAL, 
CASE WHEN SAL BETWEEN 0 AND 1000 THEN 'E'
     WHEN SAL BETWEEN 1001 AND 2000 THEN 'D'
     WHEN SAL BETWEEN 2001 AND 3000 THEN 'C'
     WHEN SAL BETWEEN 3001 AND 4000 THEN 'B'
     WHEN SAL BETWEEN 4001 AND 5000 THEN 'A'
END "등급"
FROM EMP ORDER BY 4 ;
샘 답)
SELECT EMPNO, ENAME, SAL, 
CASE WHEN SAL>=0 SAL<=1000 THEN 'E'
     WHEN SAL>1000 SAL<=2000 THEN 'D'
     WHEN SAL>2000 SAL<=3000 THEN 'C'
     WHEN SAL>3000 SAL<=4000 THEN 'B'
     WHEN SAL>4000 SAL<=5000 HEN 'A'
END "등급"
FROM EMP ORDER BY 4 ;

DECODE 답)
SELECT EMPNO, ENAME,SAL,
DECODE ( SIGN(SAL-4000), 1,'A',
        DECODE (SIGN(SAL-3000), 1,  'B',
        DECODE (SIGN(SAL-2000), 1,  'C',
        DECODE (SIGN(SAL-1000), 1,  'D', 'E')
        ))) "등급"
FROM EMP ORDER BY 4 ;

---

그룹함수

*기본적으로 NULL값 제외하고 계산, * COUNT는 NULL값 포함해서 계산

■ SELECT SUM , AVG , MAX,MIN
                                      함수(DISTINCT 컬럼)   ∴중복된 값 제외하고, 컬럼의 함수값
                                      함수(      컬럼       )    ∴중복된 값 포함하고, 컬럼의 함수값
ㄴ SELECT ENAME, AVG(~) FROM EMP => 오류남. ENAME은[ EMP의 ENAME 컬럼전체갯수]를 보여주고,
AVG는 [결과의 갯수 1개]만 보여주기 때문에, 갯수보여주기에 오류가 남 =>이건 AVG 뿐만 아니라 SUM MAX MIN 등등...

*MAX,MIN은 날짜값도 가능
■ SELECT COUNT(.....)
※ COUNT(*) =>원래 그룹함수는 널을 포함하지 않지만, COUNT 함수는 예외로 NULL도 포함해서 모든 갯수를 리턴

■ 그룹함수의 GROUP BY ,  HAVING

 SELECT 컬럼1, 컬럼2, .....
FROM 테이블1, 테이블2 ....
[WHERE 조건]
GROUP BY 단순컬럼
ㄴ SELECT 다음에 쓸 수 있는 컬럼은, GROUP BY뒤에 나오는 컬럼이나 그룹함수가 적용된 컬럼만! 가능
ㄴ 그래서 아무것도 없고 '~의 갯수', '~의 인원수'면 COUNT(단순컬럼 | *)만 써도 됨 
GROUP BY 정확한 컬럼명         
★절대 별칭만이나, 인덱스만으로는 안됨!!! 별칭만! 쓰는게 안된다는거지, [테이블별칭.컬럼 ] 이건 쌉가능!!!!

■ WHERE 절에는 그룹함수 못 씀
■ 책에 적었음. 단순컬럼과 그룹함수는 보여지는 갯수가 다르기 때문에 동시사용 못함

 

 

WHERE은  GROUP BY   하기 전에  골라내는거,

HAVING은 GROUP BY 하고 난 후에 골라내는거

 

*예시       더보기 열기

SELECT SUM(DISTINCT SAL), SUM(ALL SAL), SUM(SAL) FROM EMP;
SELECT SUM(SAL), AVG(SAL) FROM EMP;
SELECT ENAME, MIN(HIREDATE) FROM EMP;
SELECT COUNT (ENAME), COUNT(COMM) FROM EMP;
SELECT COUNT(JOB), COUNT(DISTINCT JOB)FROM EMP;
SELECT COUNT(*)FROM EMP;
SELECT COUNT(*) FROM EMP WHERE JOB LIKE 'SAL%';
SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
FROM EMP WHERE JOB LIKE 'SAL%';
SELECT COUNT(*) C_INWON, COUNT(COMM) C_COMM, AVG(COMM) A_COMM,
AVG(NVL(COMM,0)) N_COMM, COUNT(DEPTNO) C_DEPT,
COUNT(DISTINCT DEPTNO) C_DIS FROM EMP;

SELECT SUM(SAL) FROM EMP WHERE DEPTNO=10;
SELECT DEPTNO, SUM(SAL) FROM EMP 
GROUP BY DEPTNO ORDER BY 1;

SELECT DEPTNO 부서번호, TRUNC (AVG(SAL)) 평균월급
FROM EMP GROUP BY DEPTNO ORDER BY 1;

SELECT DEPTNO 부서번호, MAX(SAL) 최대월급, MIN(SAL) 최소월급
FROM EMP GROUP BY DEPTNO ORDER BY 1;

SELECT TO_CHAR(HIREDATE, 'YYYY')년,
       TO_CHAR(HIREDATE, 'MM') 월, SUM(SAL)
FROM EMP
GROUP BY  TO_CHAR(HIREDATE, 'YYYY'),
          TO_CHAR(HIREDATE, 'MM')
ORDER BY 년 ASC;

SLELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL)>9000;

SELECT DEPTNO, COUNT(*), TRUNC(AVG(SAL)), MIN(SAL), MAX(SAL),SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY SUM(sAL) DESC;