개발 다이어리

--올림, 내림, 반올림
SELECT 
    CEIL(10.3) C -- 정수만 가능
    ,FLOOR(10.7) F  -- 정수만 가능
    ,ROUND(10.77, 1) R  -- 유일하게 소수점 가능
    ,ROUND(10.77, -2) R -- 유일하게 소수점 가능
    ,ROUND(10.34,1) R
FROM DUAL;
-- SIGN 부호  -1, 0, 1 만 표시  DECODE는 동등비교만 된다
-- SIGN과 같이 사용 시 크기 비교가 가능해진다.
SELECT 
    SIGN(-10)
    ,SIGN(0)
    ,SIGN(20)
FROM DUAL;
SELECT 
    SIGN(-10)
    ,SIGN(0)
    ,SIGN(20)
FROM DUAL;

-- 0으로 만든다.
SELECT TRUNC(123.934, -2) 
    ,TRUNC(SYSDATE)
    ,ROUND(SYSDATE)
    ,ROUND(TO_DATE('20220109 11:59:59', 'YYYYMMDD HH24:MI:SS')) R1
    ,ROUND(TO_DATE('20220109 12:59:59', 'YYYYMMDD HH24:MI:SS')) R2 -- 정오 기준 다음날 아님 오늘
    ,TRUNC(TO_DATE('20220109 11:59:59', 'YYYYMMDD HH24:MI:SS')) T1  --무조건 자른다
FROM DUAL;

-- 월 다루기 -도 가능
SELECT 
    ADD_MONTHS(SYSDATE, 3)
    ,ADD_MONTHS(SYSDATE, -3)
FROM DUAL;

-- TO_DATE
-- DB 기준 DB가 가진 현재 시간을 리턴
SELECT 
    CURRENT_DATE
    ,SYSDATE 
FROM DUAL;

-- 그리드에서 정렬이 오른쪽이면 숫자, 왼쪽이면 문자열
SELECT EXTRACT(YEAR FROM SYSDATE)A 
     , EXTRACT(MONTH FROM SYSDATE)B
     , EXTRACT(DAY FROM SYSDATE)C -- 리턴 타입이 숫자
     , TO_CHAR(SYSDATE,'YYYY')D --리턴 타입이 문자
FROM DUAL;

--해당 달의 마지막 날 반환
SELECT LAST_DAY(SYSDATE) D1
     , ADD_MONTHS(TRUNC(SYSDATE, 'MM'),1)-1  D2
FROM DUAL;

SELECT NEXT_DAY(SYSDATE,6) --인덱스로 따짐 단 1부터 일요일 돌아오는 날짜
FROM DUAL;



SELECT 
    ABS(-5) COL1 -- 부등호 제외한 
    ,ABS(5) COL2 -- 절대값
    ,FLOOR(10.5) COL3 -- 내림
    ,CEIL(10.5) COL4 -- 올림
    ,POWER(2,5) COL5 -- 제곱
    ,SQRT(2) COL6 --루트
    ,ROUND(10.4) COL7 -- 반올림인데 소수점 가능
    ,ROUND(10.55,1) COL8 -- 2번 째 인자는 소수점 몇번째를 기준으로 할지
    ,TRUNC(10.4) COL9 -- 버림
    ,TRUNC(10.5) COL10 --무조건 버림
    ,TRUNC(11.5, -1) COL11 -- 정수 1의자리 버림
    ,ADD_MONTHS(TO_DATE('20180101','YYYYMMDD'),3) COL12 -- 날짜로 이후 3달 더하기
    ,CURRENT_DATE COL13 -- DB기준 현재시간
    ,EXTRACT(YEAR FROM SYSDATE) COL14 -- 년도 추출
    ,EXTRACT(MONTH FROM SYSDATE) COL15 -- 월 추출
    ,EXTRACT(DAY FROM SYSDATE) COL16 -- 일 추출
    ,NEXT_DAY('20180206',2) COL18 -- 1부터 일요일 돌아오는 그 요일의 날짜 반환
    ,NEXT_DAY('20180206','월') COL19 -- 월 화 수 목 금 토 일 가능
    ,SYSDATE COL20 -- DB기준 현재시간
FROM DUAL;

SELECT 'INSERT INTO EMP VALUES('||DEPTNO||','''||DNAME||''','''||LOC||''')' FROM DEPT;

실제로 이렇게 쓰는 경우가 간혹있다.

DELETE FROM DEPT
 WHERE DEPTNO NOT IN ( SELECT MIN(DEPTNO)/*KEY값*/
                         FROM DEPT
                        GROUP BY LOC/*중복제거 컬럼*/);

기본키는 유일하기 때문에 키값을 기준으로 잡고 중복을 제거하고 싶은 컬럼을 선정한다.

그리고 집계함수를 사용하면 반드시 1개의 값만 리턴하게 된다.

위 예제는 편의상 DEPT 테이블의 DEPTNO을 사용했지만, 원래는 지워도 되는 행인지 명확히 판별해야 한다.

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,NULL)

SELECT * FROM DEPT WHERE DEPTNO NOT IN (10,20,NULL);

결과가 하나도 안나왔다. 

아마도 대부분의 사람들은 다음과 같은 기대값을 생각했을 것이다.

IN은 근본적으로 OR연산과 같다. 따라서 다음과 같이 표현될 수 있다.

--IN 테이블
SELECT 
	*	
FROM DEPT 
WHERE  (DEPTNO = 10 
     OR DEPTNO = 20
     OR DEPTNO = NULL)
     
     ----------------
--NOT IN 테이블
SELECT 
	*	
FROM DEPT 
WHERE NOT (DEPTNO = 10 
        OR DEPTNO = 20
        OR DEPTNO = NULL)

DEPT 의 DEPTNO 이 10일 때 어떻게 필터링 되는지 보자

DEPTNO = 10 

IN 테이블

(DEPTNO = 10 , DEPTNO = 20 , DETPNO = NULL) 

(TRUE, FALSE, NULL)   NULL과의 연산은 NULL을 리턴한다.

(TRUE, NULL)      OR은 하나라도 TRUE이면 TRUE를 리턴하므로 FALSE를 제거해봤다.

TRUE     OR연산 시 TRUE는 NULL과의 연산에도 TRUE를 리턴한다.

NOT IN 테이블

마지막 결과에 부정 연산자를 사용해보자 

NOT(TRUE)  

FALSE

DEPTNO = 40 

NOT IN 테이블

(DEPTNO = 10 , DEPTNO = 20 , DETPNO = NULL) 

(FALSE, FALSE, NULL)

(FALSE, NULL)  

(NULL)   OR 연산 시 FALSE는 NULL과 연산 결과로 NULL을 반환한다. 

따라서 하나라도 TRUE가 있는 경우 NOT 결과로 FALSE를 반환한다.

전부 FALSE일 때는 NULL이 있어 NULL을 반환하기 때문에 아무 결과도 나오지 않게 된다.

IN 연산자 안에 NULL값이 들어가게 되면 TRUE를 리턴한 때는 정상동작하지만

FALSE의 경우 항상 NULL과의 연산에서 NULL을 리턴하게 되므로 

NOT IN 은 NULL이 하나라도 들어가면 모든 결과가 부정된다. 

해법

SELECT * FROM DEPT WHERE DEPTNO NOT IN (10,20,DECODE(30,NULL,'9999',30)) --NVL 등 방법은 여러 가지

SELECT 
	*	
FROM DEPT A
WHERE NOT EXISTS (SELECT 1 
                    FROM DEPT B 
                   WHERE DEPTNO IN(10, 20, NULL)
                     AND A.DEPTNO = B.DEPTNO)

NULL일 가능성이 있는 컬럼을 보정한다.

또는 EXISTS를 사용한다.

SELECT * FROM DEPT;

SELECT *
FROM(
	SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
	UNION ALL
	SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
	INTERSECT 
	SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)
    )
;

SELECT *
FROM(
	SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
	UNION ALL
	SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
	MINUS  
	SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)
    )

말그대로 집합 특성을 지니기에 중복이 제거된 상태로 결과를 도출한다.

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION ALL 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50);

SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)

----------------------------------------------

WITH TMP AS
(
SELECT * FROM DEPT WHERE DEPTNO IN (10,20,30)
UNION ALL 
SELECT * FROM DEPT WHERE DEPTNO IN (30,40,50)
)
SELECT DISTINCT * FROM TMP ORDER BY DEPTNO

차이점은 중복 제거 여부다. 

따라서 나온 결과에 DISTINCT 를 먹인 것과 같다.

만약 중복제거가 불필요하다면 UNION ALL을 사용해 불필요한 연산을 피하는 것이 좋다.

SELECT ROWNUM, A.* FROM EMP A;

여기서 'SCOOT'를 1번 자리에, 'KING' 2번 자리에 위치시키고 싶다.

SELECT ROWNUM   R1
     , A.* 
  FROM EMP A
 ORDER BY DECODE(ENAME, 'SCOTT',1,'KING',2,ROWNUM+2)

ORDER BY 절에 DECODE 뿐만 아니라 FUNCTION도 사용 가능하다.

왜 ROWNUM 뒤에 +2를 줬을까?

SELECT ROWNUM   R1
     , DECODE(ENAME, 'SCOTT',1,'KING',2,ROWNUM+2) R2
     , DECODE(ENAME, 'SCOTT',1,'KING',2,ROWNUM) R3
     , A.* 
  FROM EMP A
 ORDER BY 2

ORDER BY 는 SELECT 절 이후에 판단되기 때문에 구조상 SELECT 절에서 사용하는 모든 값을 사용할 수 있다. 

WITH VIEW_TMP AS
(
SELECT NULL AS V1 FROM dual
)
SELECT CASE WHEN V1 = NULL THEN 'NULL입니다' END AS T1
     , CASE WHEN V1 IS NULL THEN 'NULL입니다' END AS T2
     , CASE V1 WHEN  NULL THEN 'NULL입니다' END AS T3
--     , CASE V1 WHEN IS NULL THEN 'NULL입니다' END AS T4  문법 오류
     , DECODE(V1, NULL, 'NULL입니다') AS T5
--     , DECODE(V1, IS NULL, 'NULL입니다') AS T6     문법 오류
  FROM VIEW_TMP
UNION ALL
SELECT CASE WHEN V1 != NULL THEN 'NULL아닙니다' END AS T1
     , CASE WHEN V1 IS NOT NULL THEN 'NULL아닙니다' END AS T2
     , CASE V1 WHEN /*NOT*/ NULL THEN 'NULL아닙니다' END AS T3 --문법 오류
--     , CASE V1 WHEN IS NULL THEN 'NULL아닙니다' END AS T4  문법 오류
     , DECODE(V1,/*NOT*/ NULL, 'NULL입니다') AS T5 --문법 오류
--     , DECODE(V1, IS NULL, 'NULL아닙니다') AS T6     문법 오류
  FROM VIEW_TMP;

CASE WHEN 과 DECODE 가 NULL 다루는 방법이 다르다.

CASE WHEN은 키워드를 통한 NULL처리가 아닌 연산자를 통한 연산 시 흔히 생각하는 값이 안나온다.

CASE WHEN에서 NULL을 다룰 때는 무조건 키워드로 다뤄야 한다는 것만 기억하면 된다.

통계 쿼리를 낼 때 NULL을 다루다 위를 차이점을 모르면 전혀 다른 결과를 낼 수 있기 때문에 주의해야한다.

See the Pen Untitled by rkwhr0010 (@rkwhr0010) on CodePen.

See the Pen Untitled by rkwhr0010 (@rkwhr0010) on CodePen.

그리드가 따로 존재하고 거기서 명확히 어떤 행이 중복인지 알려주는 코드,

단순히 중복만 판단하는 것은 더 간단하다.

어떤 행이 중복인지 알아야하는 이유는 해당 그리드 행으로 포커스를 가도록 처리하는 경우가 많기 때문

다음에는 이 코드를 함수화.