개발 다이어리

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)
    )

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

IN, EXISTS 

비슷한 듯, 동작방식이 다른 두 연산에 대해 알아보자.

select DISTINCT deptno from dept

select DISTINCT deptno from dept

select DISTINCT deptno from emp

IN 동작방식

근본적으로 IN 은 OR 동작과 같다.

select DISTINCT deptno
  from dept
 where deptno in (select DISTINCT deptno from emp)

위 쿼리에 서브 쿼리 결과는 아래와 같다.

select DISTINCT deptno from emp

즉, 위 쿼리는 아래와 같은 결과를 도출한다.

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

실행결과는 둘 다 같다.

EXISTS  

 select  *
  from dept d
 where  EXISTS  (select deptno from emp e WHERE e.DEPTNO = d.DEPTNO)

위 쿼리 또한 같은 결과를 도출한다.

IN과 차이는 EXISTS는 서브쿼리가 행을 반환하면 TRUE를 도출한다. 즉, 직접적으로 값 비교를 하지 않는다.

NOT IN, NOT EXISTS

단순히 위 쿼리 2개에 NOT만 붙이면 어떻게 될까?

select distinct deptno
  from dept
 where deptno not in (select distinct deptno from emp)

아마 위와 같은 결과를 기대했을 것이다.

하지만 아래와 같은 결과가 나온다.

 SELECT  DISTINCT DEPTNO
  FROM DEPT D
 WHERE NOT EXISTS  (SELECT DISTINCT DEPTNO FROM EMP E WHERE E.DEPTNO = D.DEPTNO)

NOT EXISTS는 정상적으로 기대한 값이 나온다. 

왜 이런 결과가 나오는 것일까?

 SELECT DISTINCT DEPTNO
  FROM DEPT
 WHERE DEPTNO NOT IN 
 (30
--,NULL
,20
,10)

위 쿼리에서 봤던 풀이에서 NULL만 주석 처리하고 결과를 보면 같은 결과가 나온다.

위 결과가 도출된 이유는 아래와 같다.

SELECT DISTINCT DEPTNO FROM DEPT;
SELECT DISTINCT DEPTNO FROM EMP;

각 테이블의 결과를 데카르트 곱을 하게 된다.

10을 예로 들면

(10=10, 10=20, 10=30, 10=NULL)

IN은 근본적으로 OR연산이니

(10=10 OR 10=20 OR 10=30 OR 10=NULL)

(TRUE OR FALSE OR FALSE  OR NULL)

OR연산에서 하나라도 TRUE가 있으면 FALSE가 몇 개던 TRUE이다.

(TURE OR NULL)

여기가 중요하다 OR은 NULL과 연산해도 TRUE를 도출한다.

(TURE)

즉, 각 값마다 데카르트 곱을 할 때 NULL이 껴 있느면 그냥 IN일때는 TRUE를 반환하지만

NOT IN 일 경우 TRUE에 NOT이 되어 전부 FALSE가 되는 것이다.

 SELECT  DISTINCT DEPTNO
  FROM DEPT D
 WHERE NOT EXISTS  (SELECT DISTINCT DEPTNO FROM EMP E WHERE E.DEPTNO = D.DEPTNO)

EXISTS의 경우에는 각 행 단위로 행 결과만 존재 여부로 TRUE, FALSE를 리턴하기 때문에 정상적으로 값이 도출되는 것이다.

이래나 저래나 당장 이해하기 버겁다면, NOT IN만 조심하면 된다는 것을 기억하면 된다.

아니 NOT IN에 NULL이 끼어있으면 값이 제대로 도출이 안된다는 것만 기억하면 된다.

이를 방지하기 위해선 위 처럼 NOT EXISTS를 사용하거나

기존 쿼리 구조를 변경하기 어려운 경우라면 NULL처리를 적당해 해주면 된다.

SELECT DISTINCT DEPTNO FROM DEPT
 WHERE DEPTNO NOT IN (SELECT DISTINCT  NVL(DEPTNO, 0) FROM EMP)

굳이 NVL아니더라도 DECODE, COALESCE.... 등 편한대로 처리하면 된다.

스패로우 같은 취약점 점검 툴을 점검 결과로 

제가 있는 사이트에 기존 sql mapper 파일에서 500개 정도되는 Mybatis Sql Injection 취약점이 나왔습니다.

원인은 ${} 사용 때문입니다. 

${}를 전부 #{}로 바꿔줬습니다.

SELECT '${BIND1}' FROM TEST
WHERE ${BIND2} BETWEEN '${BIND3}' AND '${BIND4}'

저같은 경우는 notepad++ 로 수정했습니다. 

이클립스나 STS도 당연히 정규식을 지원합니다.

\$\{[^{}]*\}  

일반적으로 ${}  안쓰는 것이 좋습니다.

성능면에서도 #{} 가 더 좋습니다. DB에 Library Cache를 찾아서 실행하기 때문입니다.

SELECT #{BIND1} 
FROM DUAL

실제 DB질의는 "SELECT ? FROM DUAL" 로 나가고 

파라미터로 #{BIND1} 값이 나갑니다. 

아마도 날짜로만 쿼리 포스트는 마지막일 듯 합니다.

SELECT 
	TO_DATE(:ST_YYYYMM, 'YYYYMM') "시작일"
	,ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1 "마지막일"
FROM DUAL;

시작점과 끝점 구하기

SELECT 
	TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL "DAY"
	,ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1 "마지막일"
	,COUNT(*) OVER()
FROM DUAL
CONNECT BY TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL <= ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1;

시작점과 끝점 사이 ROW값 구하기

SELECT 
	TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL "DAY"
	,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') "속한 요일"
	,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'D') "속한 주"
	,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'MM') "속한 달"
FROM DUAL
CONNECT BY TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL <= ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1;

필요한 컬럼 값 구하기

SELECT 
	DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 1, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "일"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 2, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "월"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 3, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "화"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 4, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "수"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 5, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "목"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 6, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "금"
	,DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 7, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')) "토"
	,TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL "DAY"
	,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') "속한 요일"
	,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'D') "속한 주"
	,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'MM') "속한 달"
FROM DUAL
CONNECT BY TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL <= ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1;

"DAY" 행을 DECODE로 강제로 주 단위로 찢기

SELECT 
	TO_CHAR(DECODE(MIN(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD')),1, MIN(TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'MM'))),'MM') "달"
	,RANK() OVER(PARTITION BY  TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'MM') ORDER BY MIN(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL)) || '주차' "주차"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 1, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "일"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 2, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "월"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 3, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "화"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 4, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "수"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 5, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "목"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 6, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "금"
	,SUM(DECODE( TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'D') , 7, TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL,'DD'))) "토"
FROM DUAL
CONNECT BY TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL <= ADD_MONTHS(TO_DATE(:ET_YYYYMM , 'YYYYMM'),1)-1
GROUP BY TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'MM'),TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL, 'D')
ORDER BY MIN(TO_DATE(:ST_YYYYMM, 'YYYYMM')-1+LEVEL)
;

찢은 컬럼을 GROUP BY 표현식으로 하나의 행으로 합침 

부가적으로  몇 월, 몇 주차 표시

마무리

날짜를 기준으로 그룹화를 하는 것은 꽤나 실전적인 쿼리입니다.

조금만 생각해보면 답이 나옵니다. 내가 쇼핑몰을 개발하던, 공장 재고관리를 하던, ... 고객이 원하는 대부분의 통계는 날짜 더나아가 시간 정보로 보길 원하거든요. 

이렇게 시간기준으로 집계한 원본 소스값에  WHERE 절을 추가해서 검색 조건을 달게 됩니다.

2022.05.27 - [개발/오라클 SQL] - 한달 달력 구해보기, 오라클에서 날짜계산, 바인딩 변수

이전 포스트 풀이과정입니다.

/*1차 필요한 컬럼 값 만들기*/
SELECT 
	LEVEL AS "DAY"
	,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'DD') AS "TO_CHAR" 
	,EXTRACT(DAY FROM TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1) AS "EXTRACT"
	,TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD') AS "마지막일" 
	,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') AS "요일 인덱스"
	,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') AS "내가 속한날 첫 일"
FROM DUAL
CONNECT BY LEVEL <=TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD');

"DAY", "TO_CHAR", "EXTRACT" 는 같은 값을 다르게 뽑는 방식입니다. 

추가로 오른쪽 정렬이 숫자, 왼쪽 정렬이 문자열을 의미합니다.

보통 모든 변환은 TO_CHAR를 제일 많이 사용합니다. 지원하는 기능도 많고 문자열이기 때문에 매개체 역할도 가능하기 때문입니다.

/*DECODE 사용한 피벗 */
SELECT
	"내가 속한날 첫 일" 
	,DECODE("요일 인덱스", 1 , "DAY") AS "일"
	,DECODE("요일 인덱스", 2 , "DAY") AS "월"
	,DECODE("요일 인덱스", 3 , "DAY") AS "화"
	,DECODE("요일 인덱스", 4 , "DAY") AS "수"
	,DECODE("요일 인덱스", 5 , "DAY") AS "목"
	,DECODE("요일 인덱스", 6 , "DAY") AS "금"
	,DECODE("요일 인덱스", 7 , "DAY") AS "토"
FROM
	(
	SELECT 
		LEVEL AS "DAY"
		,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'DD') AS "TO_CHAR" 
		,EXTRACT(DAY FROM TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1) AS "EXTRACT"
		,TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD') AS "마지막일" 
		,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') "요일 인덱스"
		,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') AS "내가 속한날 첫 일"
	FROM DUAL
	CONNECT BY LEVEL <=TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD')
	)
ORDER BY DAY
;

/*GROUP BY 내가 속한날 첫 일_ 로 컬럼 값 압축*/
SELECT
	"내가 속한날 첫 일"  
	,SUM(DECODE("요일 인덱스", 1 , "DAY")) AS "일"
	,MIN(DECODE("요일 인덱스", 2 , "DAY")) AS "월"
	,MAX(DECODE("요일 인덱스", 3 , "DAY")) AS "화"
	,SUM(DECODE("요일 인덱스", 4 , "DAY")) AS "수"
	,MIN(DECODE("요일 인덱스", 5 , "DAY")) AS "목"
	,MAX(DECODE("요일 인덱스", 6 , "DAY")) AS "금"
	,SUM(DECODE("요일 인덱스", 7 , "DAY")) AS "토"
FROM
	(
	SELECT 
		LEVEL AS "DAY"
		,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'DD') AS "TO_CHAR" 
		,EXTRACT(DAY FROM TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1) AS "EXTRACT"
		,TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD') AS "마지막일" 
		,TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') "요일 인덱스"
		,TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') AS "내가 속한날 첫 일"
	FROM DUAL
	CONNECT BY LEVEL <=TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD')
	)
GROUP BY "내가 속한날 첫 일"
ORDER BY MIN(DAY)
;

마지막으로 인라인뷰 없이 똑같은 결과를 만들기입니다. 

위 SQL을 기반으로 만들어 보기실 권장합니다.

*인라인뷰 사용 안한 최종 쿼리*/
SELECT 
	TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') AS "내가 속한날 첫 일"
	,SUM(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 1 , LEVEL)) AS "일"
	,MIN(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 2 , LEVEL)) AS "월"
	,MAX(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 3 , LEVEL)) AS "화"
	,SUM(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 4 , LEVEL)) AS "수"
	,MIN(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 5 , LEVEL)) AS "목"
	,MAX(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 6 , LEVEL)) AS "금"
	,SUM(DECODE(TO_CHAR(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D'), 7 , LEVEL)) AS "토"
FROM DUAL
CONNECT BY LEVEL <=TO_CHAR(TO_DATE(:ST_YYYYMM+1, 'YYYYMM')-1, 'DD')
GROUP BY TRUNC(TO_DATE(:ST_YYYYMM, 'YYYYMM')+LEVEL-1, 'D') 
ORDER BY MIN(LEVEL)
;