개발 다이어리

당신은 월에 속한 주차를 구해야한다.

이때 고객사에선 대금결제일이 목요일이라 목요일이 속한 월의 주를 기준으로 주차를 정했으면 좋겠다고 한다.

한 주 단위는 월요일부터 시작 일요일까지다

위 규칙에 따라 1월30일은 2월 1주차이다.

1월29일은 2월 4주차이다.

SELECT TO_DATE(:YYYYMMDD,  'YYYYMMDD' )  /*바인딩변수 받는곳*/                A
     , TO_DATE(:YYYYMMDD,  'YYYYMMDD' )-1                                     B
     , TRUNC(TO_DATE(:YYYYMMDD,  'YYYYMMDD' )-1,'WW')                         C
     , TRUNC(TO_DATE(:YYYYMMDD,  'YYYYMMDD' )-1,'WW')+4  /*목요일보정*/       D
     , TO_CHAR(TRUNC(TO_DATE(:YYYYMMDD,  'YYYYMMDD' )-1,'WW')+4,'W')||'주차'  E
  FROM DUAL;

다른 달 검증

4월1일, 4월 2일은 5주차가 나와야한다. 4월 3일부터 1주차가 나와야한다.

TRUNC나 TO_CHAR로 날짜를 다루는 것은 굉장히 중요하다. (ROUND도 날짜를 다룰 수 있는데 거의 안씀)

주차를 구하는 것도 별것 아닌 것처럼 보이는데 막상해보면 막힐 수 있다.

날짜를 다루는데 도움되는 이전 포스트로 마무리!

https://rkwhr0010.tistory.com/8

SELECT (SELECT MAX(sal) FROM emp) AS MAX
      ,(SELECT MIN(sal) FROM emp) AS MIN
      ,(SELECT AVG(sal) FROM emp) AS AVG
      ,(SELECT SUM(sal) FROM emp) AS SUM
  FROM dual;
-- 위는 극단적인 대조를 위한 예시
SELECT DISTINCT  MAX(sal) OVER()  AS MAX   
     , MIN(sal) OVER()  AS MIN   
     , AVG(sal) OVER()  AS AVG   
     , SUM(sal) OVER()  AS SUM   
  FROM EMP;

결과는 같다. 다만 결과를 산출하는 방식에 차이가 있다.

윈도우 함수는 한 번 참조한 테이블을 버퍼로 활용한다.

SELECT DISTINCT DEPTNO 
     , MAX(sal) OVER(PARTITION BY DEPTNO)  AS MAX   
     , MIN(sal) OVER(PARTITION BY DEPTNO)  AS MIN   
     , AVG(sal) OVER(PARTITION BY DEPTNO)  AS AVG   
     , SUM(sal) OVER(PARTITION BY DEPTNO)  AS SUM   
  FROM EMP;
 
SELECT DEPTNO
      ,MAX(SAL) AS MAX
      ,MIN(SAL) AS MIN
      ,AVG(SAL) AS AVG
      ,SUM(SAL) AS SUM
  FROM EMP
 GROUP BY DEPTNO;

GROUP BY 사용 시 해당 절에 열거된 컬럼만 사용이 가능하다. 

이런 제약에서 자유롭기 때문에 좀 더 유연하게 결과를 도출할 수 있다.

SELECT NUM
     , VALUE
FROM (
      SELECT TRUNC(DBMS_RANDOM.VALUE(1,1000)) NUM 
           , DECODE(MOD(TRUNC(DBMS_RANDOM.VALUE(1,1000)) ,2),0,NULL 
                   ,TRUNC(DBMS_RANDOM.VALUE(1,1000))) VALUE  --짝수면 NULL
        FROM DUAL
      CONNECT BY LEVEL <=10
     );

짝수면 NULL이 나오게 했다.

SELECT 
     DISTINCT AVG(VALUE) OVER() AVG1  --NULL포함 안함
     , AVG(NVL(VALUE,0)) OVER() AVG2  -- NULL포함
     , SUM(VALUE) OVER() / COUNT(VALUE) OVER() AVG3 --NULL포함 안함
     , SUM(VALUE) OVER() / COUNT(*) OVER()     AVG4 --카운트만 NULL포함
     , SUM(VALUE) OVER() / COUNT(NVL(VALUE,0)) OVER() AVG5 -- 카윤트만 NULL 포함
     , COUNT(VALUE) OVER() CN1
     , COUNT(1) OVER() CN2
FROM (
      SELECT TRUNC(DBMS_RANDOM.VALUE(1,1000)) NUM 
           , DECODE(MOD(TRUNC(DBMS_RANDOM.VALUE(1,1000)) ,2),0,NULL 
                   ,TRUNC(DBMS_RANDOM.VALUE(1,1000))) VALUE  --짝수면 NULL
        FROM DUAL
      CONNECT BY LEVEL <=10
     );

기본적으로 집계함수는 NULL 값을 제외한다. 따라서 AVG함수나, COUNT함수는 주의해서 사용해야 한다.

NULL인 값을 포함해서 계산을 하고 싶은 경우도 많기 때문이다.

추가로 집계함수의 NULL 처리 방식을 이해하면 아래 중 무엇이 잘못된 사용 방식인지 판별할 수 있다.

SELECT SUM(NVL(123,0)) FROM DUAL;
SELECT NVL(SUM(123), 0) FROM DUAL;

1번 째의 경우 나름 생각한다고 NULL처리를 했지만 DB에 불필요한 부하를 주게 된다.

내부적으로 NULL을 처리하는데 개발자가 NVL을 또 주었으니 불필요한 연산이 들어가게 된다.

이게 행이 많고, 집계함수를 몇 십개 쓴다고 생각해보자. 

WITH TMP AS
(
SELECT WORD
       ,SUBSTR(WORD, ROWNUM, 1) COL_TO_ROWS
FROM(
    SELECT 'ADSFGWEQWE' WORD FROM DUAL
    )
CONNECT BY LEVEL <= LENGTH(WORD)
)
, TMP2 AS
(
SELECT DECODE(ROWNUM,1,COL_TO_ROWS) "1"
      ,DECODE(ROWNUM,2,COL_TO_ROWS) "2"
      ,DECODE(ROWNUM,3,COL_TO_ROWS) "3"
      ,DECODE(ROWNUM,4,COL_TO_ROWS) "4"
      ,DECODE(ROWNUM,5,COL_TO_ROWS) "5"
      ,DECODE(ROWNUM,6,COL_TO_ROWS) "6"
      ,DECODE(ROWNUM,7,COL_TO_ROWS) "7"
      ,DECODE(ROWNUM,8,COL_TO_ROWS) "8"
      ,DECODE(ROWNUM,9,COL_TO_ROWS) "9"
      ,DECODE(ROWNUM,10,COL_TO_ROWS) "10"
  FROM TMP
)
--SELECT * FROM TMP2;
SELECT MAX("1")||MAX("2")||MAX("3")||MAX("4")||MAX("5")||
       MAX("6")||MAX("7")||MAX("8")||MAX("9")||MAX("10") ROWS_TO_COL
FROM TMP2;

TMP2 결과

TMP2 합친 결과

컬럼을 행으로 변환할 때는 행을 반복적으로 실행시켜 나눌 수 있지만 본문 "COL_TO_ROWS"

반대로 합칠 때는 수동으로 입력하는 수 밖에 없다. 본문 "ROWS_TO_COL"

WITH R10 AS --미리 만들어둔 반복용 테이블이라 가정
(
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 8 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL UNION ALL
SELECT 10 AS NUM FROM DUAL
)
, TMP AS
(
SELECT WORD
       ,SUBSTR(WORD, ROWNUM, 1) COL_TO_ROWS
FROM(
    SELECT 'ADSFGWEQWE' WORD FROM DUAL
    )
    , R10 --데카르트 곱으로 활용
 WHERE R10.NUM <= LENGTH(WORD)  
--CONNECT BY LEVEL <= LENGTH(WORD)

)
, TMP2 AS
(
SELECT DECODE(ROWNUM,1,COL_TO_ROWS) "1"
      ,DECODE(ROWNUM,2,COL_TO_ROWS) "2"
      ,DECODE(ROWNUM,3,COL_TO_ROWS) "3"
      ,DECODE(ROWNUM,4,COL_TO_ROWS) "4"
      ,DECODE(ROWNUM,5,COL_TO_ROWS) "5"
      ,DECODE(ROWNUM,6,COL_TO_ROWS) "6"
      ,DECODE(ROWNUM,7,COL_TO_ROWS) "7"
      ,DECODE(ROWNUM,8,COL_TO_ROWS) "8"
      ,DECODE(ROWNUM,9,COL_TO_ROWS) "9"
      ,DECODE(ROWNUM,10,COL_TO_ROWS) "10"
  FROM TMP
)
--SELECT * FROM TMP2;
SELECT MAX("1")||MAX("2")||MAX("3")||MAX("4")||MAX("5")||
       MAX("6")||MAX("7")||MAX("8")||MAX("9")||MAX("10") ROWS_TO_COL
FROM TMP2;

CONNECT BY 를 꼭 안쓰더라도 위 처럼 사용하는 경우도 많다.

	SELECT 
	       MONTH 
	     , EXTRACT (MONTH FROM MONTH) /3 M1
	     , CEIL(EXTRACT (MONTH FROM MONTH) /3) M2
	     , MOD(EXTRACT (MONTH FROM MONTH) ,3) M3
	 FROM(
		SELECT ADD_MONTHS(TRUNC(SYSDATE ,'YYYY'), LEVEL-1) MONTH  
		 FROM DUAL
		CONNECT BY LEVEL <= 12
	     )

리포트나 통계를 낼 때 그룹핑을 위한 값을 생성할 때 숫자함수가 유용하다.

SELECT M2
	  ,M3
	  ,DECODE(M3, 1, M2||'분기') M4
	  ,MONTH
 FROM(
	SELECT 
	       MONTH 
	     , EXTRACT (MONTH FROM MONTH) /3 M1
	     , CEIL(EXTRACT (MONTH FROM MONTH) /3) M2
	     , MOD(EXTRACT (MONTH FROM MONTH) ,3) M3
	 FROM(
		SELECT ADD_MONTHS(TRUNC(SYSDATE ,'YYYY'), LEVEL-1) MONTH  
		 FROM DUAL
		CONNECT BY LEVEL <= 12
	     )
     )
;

--올림, 내림, 반올림
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)
    )

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