오라클에서 조회된 데이터에서 중앙값(중간값)을 구하기 위해서는 MEDIAN 함수를 사용한다. 데이터의 중앙값이라고 생각해서 평균(AVG) 함수와 유사하다고 생각할 수 있지만, AVG 함수는 데이터 값의 평균값이고 MEDIAN 함수는 조회된 데이터의 값을 크기로 정렬 후 데이터 행의 중앙값이다. MEDAN 함수는 수치값과 날짜 형식에 사용할 수 있다. WITH temp AS ( SELECT 1 num FROM dual UNION ALL SELECT 2 num FROM dual UNION ALL SELECT 3 num FROM dual ) SELECT MEDIAN(num) FROM temp 위의 예제는 3개의 행이 조회되었고, 데이터 행의 중앙값은 2이다. WITH temp AS ( SELECT 1 num ..
오라클에서 XMLAGG 함수를 사용하여 여러 행의 칼럼을 하나의 문자열로 합칠 수 있다. XMLAGG 함수에서는 DISTINCT 연산자를 사용하여 중복을 제거할 수 없다. 합쳐진 문자열에서 중복된 값을 제거하기 위해서는 정규식 함수(REGEXP_REPLACE)를 사용하여 중복을 제거하면 된다. XMLAGG 함수 중복제거 방법 SELECT REGEXP_REPLACE( SUBSTR(XMLAGG(XMLELEMENT(job , ',' , job) ORDER BY job).EXTRACT('//text()').GETSTRINGVAL(), 2) , '([^,]+)(,\1)+', '\1') FROM emp WHERE deptno = 20 쉼표를 구분자로 사용하여 문자열을 합친다. 반드시 ORDER BY를 사용하여 합칠..
오라클에서 UNION, UNION ALL을 사용할 때 ORDER BY 절을 사용할 경우 정확한 방법을 숙지하지 않으면 오류가 발생할 수 있다. UNION 쿼리문이 모두 끝나고 마지막에 ORDER BY 절을 선언해야 하며, ORDER BY 절에 사용할 수 있는 칼럼은 UNION의 첫 번째 쿼리문의 별칭 또는 칼럼의 순번이다. ORDER BY 절을 잘못 사용했을 경우 아래와 같은 오류가 발생할 수 있다. ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다 00933. 00000 - "SQL command not properly ended" 목차 별칭(칼럼명)으로 정렬하는 방법 칼럼 순번으로 정렬하는 방법 서브쿼리를 사용하여 정렬하는 방법 별칭(칼럼명)으로 정렬하는 방법 SELECT empno A..
오라클 쿼리문에서 엔터 값(줄 바꿈)을 넣기 위해서는 CHR(13) || CHR(10)을 문자열 사이에 붙여주면 된다. 엔터 값을 제거하기 위해서는 REPLACE 함수를 사용하여 CHR(13)와 CHR(10)을 각각 공백으로 치환해 주면 된다. 엔터 값을 태그로 변환하기 위해서도 REPLACE 함수를 사용하여 치환해 주면 된다. 목차 엔터 값 넣는 방법 엔터 값 제거 방법 엔터 값 태그로 치환 방법 엔터 값 넣는 방법 SELECT 'Oracle Database' , 'Oracle' || CHR(13) || CHR(10) || 'Database' FROM dual 파이프 문자열 두 개를 사용하여 문자열 사이에 엔터 값을 붙여준다. 윈도우에서는 CHR(13) + CHR(10)을 엔터 값으로 사용한다. 특정 ..
오라클 SQL 쿼리툴 중에서 가장 많이 사용하는 무료툴 중 하나는 DBeaver이다. DBeaver는 오라클 외에도 PostgreSQL, MySQL, MongoDB 등 다양한 데이터베이스에서 사용할 수 있다. DBeaver를 무료로 사용하기 위해서는 Community 버전을 사용해야 하며, Pro 버전은 유료로 구매해야 사용할 수 있다. 오라클은 Community 버전만으로 충분히 사용할 수 있으며, Apache-2.0 라이선스가 적용되어 있어서 회사에서 설치 해도 문제가 되지 않는다. 목차 DBeaver 설치 파일 다운로드 DBeaver 오라클 연결 설정 DBeaver 설치 파일 다운로드 DBeaver Community 다운로드 Download DBeaver Community 23.1.5 Release..
오라클 쿼리문에서 문자열에서 특정 문자열의 위치를 찾을 때는 INSTR 함수와 오라클 10g부터 사용할 수 있는 REGEXP_INSTR 정규식 함수를 사용할 수 있다. INSTR 함수를 사용하여 문자열의 위치를 찾을때 동일한 문자열이 여러 개 존재해도 첫 번째 찾은 문자열의 위치를 반환하고 검색을 중지한다. 목차문자열 앞에서 부터 찾기 (INSTR 함수)문자열 찾은 횟수에 따른 위치 (INSTR 함수)문자열 뒤에서 부터 찾기 (INSTR 함수)정규식 함수로 문자열 위치 찾기 (REGEXP_INSTR 함수) 문자열 앞에서 부터 찾기 (INSTR 함수)SELECT INSTR('Oracle Database 21c Release', 'as') FROM dual INSTR("대상 문자열", "찾을 문자열")..
오라클 쿼리문에서 CASE 표현식을 사용할 때 조건절에 서브쿼리(Subquery)를 사용하여 조건을 부여할 수 있다. CASE WHEN 안에서 SELECT 문을 사용할 수 있으며, 조건절에서 서브쿼리를 사용할 때는 EXISTS 연산자, IN 연산자, 집계함수와 부등호 연산자를 주로 많이 사용한다. 목차 EXISTS 연산자와 서브쿼리 사용법 IN 연산자와 서브쿼리 사용법 집계함수와 서브쿼리 사용법 CASE WHEN~THEN 서브쿼리 사용법 EXISTS 연산자와 서브쿼리 사용법 SELECT a.empno , a.ename , a.deptno , a.mgr , CASE WHEN EXISTS(SELECT 1 FROM emp aa WHERE aa.empno = a.mgr AND aa.deptno = a.deptn..
오라클 쿼리문에서 CASE WHEN 표현식의 조건을 여러개 부여하기 위해서는 WHEN~THEN을 반복해서 사용할 수 있으며, 다중 조건을 부여할 수 있다. 하나의 조건절(WHEN~THEN)에서 여러개 칼럼을 조건으로 부여하기 위해서는 AND, OR 연산자를 사용하면 된다. 목차 CASE WHEN 조건 여러개 부여 (다중 조건) CASE WHEN 여러개 칼럼 조건 부여 (다중 칼럼) CASE WHEN 조건 여러개 부여 (다중 조건) SELECT ename , job , CASE WHEN job = 'ANALYST' THEN 'CASE 1' WHEN job = 'MANAGER' THEN 'CASE 2' WHEN job = 'SALESMAN' THEN 'CASE 3' END AS case_result FROM..
오라클 쿼리에서 월별 합계(월별 통계)를 구하기 위해서는 날짜 칼럼을 TO_CHAR 함수를 사용하여 년월까지만 잘라서 집계를 하면 쉽게 구할 수 있다. 월별 가로 합계를 구하기 위해서는 DECODE 함수를 사용하면 쉽게 구할 수 있지만, DECODE 함수 외에도 CASE 표현식이나 PIVOT 함수를 사용할 수도 있다. 목차 월별 합계 구하기 (가로 합계) 월별 합계 구하기 (세로 합계) 월별 합계 구하기 (가로 합계) SELECT SUM(DECODE(TO_CHAR(hiredate, 'MM'), '01', sal)) AS "01월" , SUM(DECODE(TO_CHAR(hiredate, 'MM'), '02', sal)) AS "02월" , SUM(DECODE(TO_CHAR(hiredate, 'MM'), '..
오라클 쿼리에서 가로 합계를 구하기 위해서는, 합계를 구할 각 칼럼을 SUM 함수를 사용하여 합산 후 모두 더하면 가로 합계가 계산된다. 오라클은 NULL과 수치 값을 더할 수 없기 때문에 가로 합계를 구할 때 계산 오류가 발생할 수 있으므로 아래의 주의 사항도 꼭 확인하기 바란다. 세로의 총합계를 구하기 위해서는 ROLLUP 함수를 사용하면 구할 수 있다. 목차 가로 합계 구하기 가로 합계 구할 때 주의 사항 GROUP BY 절과 가로 합계 구하기 가로 합계 구하기 WITH temp AS ( SELECT 150 AS col1, 80 AS col2, 200 AS col3 FROM dual UNION ALL SELECT 100 AS col1, NULL AS col2, 250 AS col3 FROM dual..