[SQL] 활용 - 함수, 집합 연산자, 서브/계층쿼리 및 뷰

is Yoon·2023년 11월 20일

SQL

목록 보기
5/6

함수

  • 단일행 함수 : 문자형 함수, 숫자형 함수, 날짜형 함수, 변환형 함수, NULL 함수
  • 다중행 함수 : 집계 함수, 그룹 함수, 윈도우 함수

◾ 단일행 함수 (문자, 숫자, 날짜, 변환, NULL)

문자형 함수

SELECT 문자형함수 FROM 테이블;

CHR(ASCII 코드) - 매핑되는 문자 반환
LOWER(문자열) - 문자열을 소문자로 변환
UPPER(문자열) - 문자열을 대문자로 변환
LTRIM(문자열[, 특정 문자]) - 문자열 왼쪽 공백 제거

  • []는 옵션 (SQL Server 미지원, 공백 제거만 가능)
  • 특정 문자 입력 시, 일치하면 삭제하고 미일치하면 중단

RTRIM(문자열[, 특정 문자]) - 문자열 오른쪽 공백 제거

  • []는 옵션 (SQL Server 미지원, 공백 제거만 가능)
  • 특정 문자 입력 시, 일치하면 삭제하고 미일치하면 중단

TRIM([위치][특정 한 글자][FROM]문자열) - 문자열 양옆 공백 제거

  • []는 옵션 (SQL Server 미지원, 공백 제거만 가능)
  • [위치] : 왼쪽부터 LEADING or 오른쪽부터 TRAILING or 양쪽 BOTH
  • 특정 한 글자 입력 시, 일치하면 삭제하고 미일치하면 중단

SUBSTR(문자열, 시작점/기준[, 길이]) - 문자열의 원하는 부분만 잘라서 반환. 시작점을 기준으로 길이(디폴트 끝)까지 혹은 기준점으로 나눠서 1, .. -1 등

  • SQL Server의 경우, SUBSTR(문자열)

SQL Server, SUBSTRING_INDEX(문자열, 기준, 인덱스) - 문자열응 기준으로 잘라서 인덱스에 해당하는 내용 반환

LENGTH(문자열) - 문자열의 길이 반환

  • SQL Server의 경우, LEN(문자열)

REPLACE(문자열, 변경 전 문자열[, 변경 후 문자열]) - 문자열에서 변경 전 문자열을 변경 후 문자열로 변환, 변경 후 문자열이 명시되지 않으면 제거


숫자형 함수

NULL 값을 제외하고 계산한다.

SELECT 숫자형함수 FROM 테이블;

ABS(수) - 절대값 반환
SIGN(수) - 부호 반환 (양수=1, 음수=-1, 0=0)
ROUND(수[, 자릿수] - 소수점 자릿수까지 반올림하여 반환
TRUNC(수[, 자릿수]) - 소수점 자릿수까지 버림하여 반환

  • 자릿수 default = 0이므로 반올림/버림된 정수로 반환
  • 자릿수가 음수일 경우, 지정된 정수부를 반올림/버림하여 반환

CELL(수) - 소수점 이하의 수를 올림한 정수 반환

  • SQL Server의 경우, CEILING(수)

FLOOR(수) - 소수점 이하의 수를 버림한 정수 반환
MOD(수1, 수2) - 수1을 수2로 나눈 나머지를 반환

  • 수2 = 0 : 수1 반환
  • 수1, 수2 = 음수 : 나머지 값 = 음수

날짜형 함수

SELECT 날짜형함수 FROM 테이블;

SYSDATE - 현재의 YYYY-MM-DD HH:MI:SS 반환

  • SQL Server의 경우, GERDATE()

EXTRACT(특정_단위 FROM 날짜_데이터) - 날짜 데이터에서 특정 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만 반환

ADD_MONTHS(날짜 데이터, 특정 개월 수) - 날짜 데이터에서 특정 개월 수를 더한 날짜 반환

  • SQL Server의 경우, DATEADD(MONTH, 특정 개월 수, 날짜 데이터)
  • 날짜의 이전 달/다음 달에 기준 날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자를 반환

변환형 함수

  • 명시적 형변환 : 변환 함수를 사용해 데이터 유형 변환을 명시적으로 나타냄
  • 암시적 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함
SELECT 변환형함수 FROM 테이블;

TO_NUMBER(문자열) - 문자열을 숫자형으로 변환
TO_CHAR(수 or 날짜[, 포맷]) - 수, 날짜형을 포맷 형식의 문자형으로 변환
TO_DATE(문자열, 포맷) - 포맷 형식의 문자형을 날짜형으로 변환

SQL Server의 경우, CONVERT or CAST 함수 이용

CAST (값 AS 데이터_형식 [길이])
CONVERT (값, 데이터_형식 [길이])
-- 둘 다 같은 내용
-- 데이터 형식 : CHAR, SIGNED (부호가 있는 정수), UNSIGNED, DATE, TIME, DATETIME 등

NULL 관련 함수

SELECT 컬럼, NULL함수 FROM 테이블;

NVL(인수1, 인수2) - 인수1 값이 NULL이면 인수2 반환, NULL이 아니면 인수1 반환

  • SQL Server의 경우, ISNULL(인수1, 인수2)

NULLIF(인수1, 인수2) - 인수1 = 인수2면 NULL, 같지 않으면 인수1 반환
COALESCE(인수1, 인수2, ..) - NULL이 아닌 최초의 인수를 반환



◾ CASE 구문

경우에 따라 원하는 값 출력하기

CASE [컬럼] WHEN __ THEN __ [ELSE _(default = NULL)_] END

단일행 CASE 표현의 종류는 3가지가 있다. (모두 같은 값을 도출한다.)

  • CASE SIMPLE_CASE_EXPRESSION 조건 ELSE 표현절 END
  • CASE SERCHED_CASE_EXPRESSION 조건 ELSE 표현절 END
  • DECODE(표현식, 기준값1, 값1, 디폴트값) : Oracle에만 존재
SELECT 컬럼,
        CASE 컬럼
            WHEN 'a' THEN 'A'
            WHEN 'b' THEN 'B'
            ELSE 'C'
        END AS 별명
  FROM 테이블;
SELECT 컬럼,
        CASE WHEN 컬럼 = 'a' THEN 'A'
             WHEN 컬럼 = 'b' THEN 'B'
             ELSE 'C'
        END AS 별명
  FROM 테이블;
SELECT 컬럼,
		DECODE(컬럼, 'a', 'A', 'b', 'B', 'C') AS 별명
  FROM 테이블;

# ELSE 값인 'C'가 지정되지 않으면 NULL 출력



◾ 다중행 함수

AGGREGATE FUNCTION : COUNT, SUM, MAX, MIN 외 각종 집계 함수
GROUP FUNCTION : ROLLUP, CUBE, GROUPING SETS
WINDOW FUNCTION : 분석 함수, 순위 함수

➀ 집계 함수 : COUNT, SUM, AVG, MIN, MAX 등

SELECT, HAVING, ORDER BY 에서 사용

SELECT 집계 함수 FROM 테이블;

COUNT(*) - NULL 값을 포함한 행(row) 반환
COUNT(컬럼) - NULL 값을 제외한 행(row) 반환
COUNT(DISTINCT 컬럼) - NULL 값을 제외하고 중복을 제외한 행(row) 반환
SUM(컬럼) - 컬럼값들의 합계 반환
AVG(컬럼) - 컬럼값들의 평균 반환
MIN(컬럼) - 컬럼값들의 최소값 반환
MAX(컬럼) - 컬럼값들의 최대값 반환
STDDEV(컬럼) - 컬럼값들의 표준편차 반환
VARIAN(컬럼) - 컬럼값들의 분산 반환



➁ 그룹 함수 : ROLLUP, CUBE, GROUPING SETS

집계 함수 외 소계(총계) 함수 : ROLLUP, CUBE, GROUPING SETS 등

SELECT * FROM table_name GROUP BY 그룹함수();

ROLLUP

소그룹 간의 소계 및 총계를 계산하는 함수

  • ROLLUP(A) - A로 그룹핑, 총합계
  • ROLLUP(A, B) - A,B로 그룹핑, A로 그룹핑, 총합계
  • ROLLUP(A, B, C) - A,B,C로 그룹핑, A,B로 그룹핑, A로 그룹핑, 총합계

CUBE

소그룹 간의 소계 및 총계를 다차원적으로 계산하는 함수

  • CUBE(A) - A로 그룹핑, 총합계
  • CUBE(A, B) - A,B로 그룹핑, A로 그룹핑, B로 그룹핑, 총합계

GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수
인자값으로 ROLLUP이나 CUBE를 사용할 수도 있다.

  • GROUPING SETS(A, B) - A로 그룹핑, B로 그룹핑
  • GROUPING SETS(A, B, ()) - A로 그룹핑, B로 그룹핑, 총합계
  • GROUPING SETS(A, ROLLUP(B)) - A로 그룹핑, B로 그룹핑, 총합계

GROUPING : ROLLUP, CUBE, GROUPING SETS와 함께 쓰이며, 소계를 나타내는 Row를 구분할 수 있게 해주는 함수

SELECT column1, GROUPING(column1) FROM table_name GROUP BY ROLLUP(column1);



➂ 윈도우 함수 : 그룹 내 순위, 집계, 행 순서, 비율 함수

OVER 키워드와 함께 사용
OVER(PARTITION BY 나누는 기준)

그룹 내 순위 함수 - RANK, DENSE_RANK, ROW_NUMBER
그룹 내 집계 함수 - SUM, MAX, MIN, AVG, COUNT
그룹 내 행 순서 함수 (Oracle만 지원) - FIRST_VALUE, LASE_VALUE, LAG, LEAD
그룹 내 비율 함수 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

순위 함수

SELECT columns1, 
	   순위함수() OVER(__) 
  FROM table_name

RANK - 특정 항목(칼럼)에 대한 순위를 구하는 함수. 동일한 값 = 동일한 순위
DENSE_RANK - 동일한 순위를 하나의 건수로 취급
ROW_NUMBER - 동일한 값이라도 고유한 순위 부여

집계 함수

SELECT columns1, 
	   집계함수(colums2) OVER(__) 
  FROM table_name
  • SUM 에서 OVER 절에 ORDER BY 쓰면 누적합 집계함

행 순서 함수

SELECT columns1, 
	   FIRST/LAST_VALUE(colums2) OVER(__) 
  FROM table_name
  
SELECT columns1, 
	   LAG/LEAD(colums2, 인자값) OVER(__) 
  FROM table_name

FIRST_VALUE - 파티션별 윈도우에서 가장 먼저 나온 값을 구함. 처음 나온 행만 처리함.
LAST_VALUE - 파티션별 윈도우에서 가장 나중에 나온 값을 구함. 공동 등수 미인정.
LAG - 파티션별 윈도우에서 이전 몇 번째 행의 값 가져오기. 인자값 생략 시 default 1
LEAD - 파티션별 윈도우에서 이후 몇 번째 행의 값 가져오기. 인자값 생략 시 default 1

비율 함수 (Oracle)

SELECT columns1, 
	   비율함수(colums2) OVER(__) 
  FROM table_name

RATIO_TO_REPORT - 파티션 별 합계에서 차지하는 비율. 칼럼 값에 대한 백분율을 소수점으로 구함 (Oracle만 지원)
PERCENT_RANK - 행의 순서에 대한 백분율. 제일 먼저 나오는 것은 0, 제일 늦게 나오는 것은 1 (Oracle만 지원)
CUME_DIST - 해당 파티션에서의 누적 백분율. 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한 누적백분율 구함 (1/전체건수) (Oracle만 지원)
NTILE - 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과 구함






집합 연산자

각 쿼리의 결과 집합을 가지고 연산을 하는 명령어
헤더 값은 첫 번째 쿼리를 따라간다.

UNION : 합집합. 중복 행은 한 줄로 출력
UNION ALL : 합집합. 중복 행도 그대로 결과로 표시.
INTERSECT : 교집합. 중복 행은 한 줄로 출력
MINUS / EXCEPT : 차집합. 중복 행은 한 줄로 출력

SELECT * FROM table1;
집합 연산자
SELECT * FROM table2;






계층 쿼리 & 셀프 조인

테이블에 계층 구조를 이루는 컬럼이 존재하는 경우 계층 쿼리를 이용하여 데이터 출력 가능
Depth가 깊어질수록 셀프 조인이 반복되는데, 계층 쿼리를 이용하면 좀 더 간단하게 쿼리를 작성할 수 있다.
보통 순방향 : 루트(상위)-리프(하위)로 내려가는 구조

BEFORE :

cTYPEcNAMEPARENT
대컴/디/가NULL
중컴퓨터컴/디/가
중디지털컴/디/가
중가전컴/디/가
소노트북/PC컴퓨터
소모니터/프린터컴퓨터
소모바일/태블릿디지털
소음향가전가전

AFTER :

# Self Join
SELECT A.cTYPE, A.cNAME,
	   B.cTYPE, B.cNAME,
	   C.cTYPE, C.cNAME
FROM CATEGORY A, CATEGORY B, CATEGORY C
WHERE A.cNAME = B.PARENT AND B.C_cNAME = C.PARENT
cTYPEcNAMEcTYPEcNAMEcTYPEcNAME
대컴/디/가중컴퓨터소노트북/PC
대컴/디/가중컴퓨터소모니터/프린터
대컴/디/가중디지털소모바일/태블릿
대컴/디/가중가전소음향가전
# 계층 쿼리
SELECT LEVEL,
	   SYS_CONNECT_BY_PATH('['||cTYPE||']'|| cNAME, '-') AS PATH
  FROM CATEGORY
 START WITH PARENT IS NULL
CONNECT BY PRIOR NAME = PARENT
LEVELPATH
1-[대]컴/디/가
2-[대]컴/디/가-[중]가전
2-[대]컴/디/가-[중]디지털
2-[대]컴/디/가-[중]컴퓨터
3-[대]컴/디/가-[중]가전-[소]음향가전
3-[대]컴/디/가-[중]디지털-[소]모바일/태블릿
3-[대]컴/디/가-[중]컴퓨터-[소]노트북/PC
3-[대]컴/디/가-[중]컴퓨터-[소]모니터/프린터

LEVEL - 현재의 DEPTH를 반환. 루트 노드는 1이 된다.
SYS_CONNECT_BY_PATH(컬럼, 구분자) - 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수
START WITH - 경로가 시작되는 루트 노드를 생성해주는 절
CONNECT BY - 루트로부터 자식 노드를 생성해주는 절. 조건에 만족하는 데이터가 없을 때까지 노드를 생성
PRIOR - 바로 앞에 있는 부모 노드의 값을 반환

CONNECT_BY_ROOT 컬럼 - 루트 노드의 주어진 컬럼 값을 반환
CONNECT_BY_ISLEAF - 가장 하위 노드인 경우 1 반환, 그 외 0 반환






서브 쿼리 Subquery (+뷰)

하나의 쿼리 안에 존재하는 또 다른 커리
괄호로 감싸서 사용
메인 쿼리의 컬럼 모두 사용 가능 (메인 쿼리는 서브 쿼리의 컬럼 사용 불가능)

위치에 따른 분류 :
SELECT 절 - 스칼라 서브쿼리 (Scalar Subquery)
FROM 절 - 인라인 뷰 (Inline View), 동적 뷰 (Dynamic View)
WHERE 절, HAVING 절 - 중첩 서브쿼리 (Nested Subquery)

  • 메인 쿼리와의 관계에 따라 비연관 서브쿼리, 연관 서브쿼리로 분류
  • 반환하는 데이터 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리로 분류

그 외 UPDATE 문 > SET 절, INSERT 문 > VALUES 절에 사용하는 서브쿼리

스칼라 서브쿼리

주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용 가능
컬럼 대신 사용하므로 반드시 하나의 값만 반환해야 하며, 그렇지 않은 경우 에러 발생

인라인 뷰

FROM 절 등 테이블명이 올 수 있는 위치에 사용 가능

뷰 View
실제 데이터를 가지고 있지 않는 가상테이블
특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트

  • 특징 :
    독립성 - 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
    편리성 - 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성 가능하다.
    보안성 - 숨기고 싶은 정보가 존재한다면, 뷰 생성 시 해당 칼럼을 빼고 생성하여 사용자에게 정보를 감출 수 있다.

중첩 서브쿼리

동작하는 방식에 따른 분류 :
비연관 서브쿼리 : 메인쿼리 컬럼을 갖고 있지 않는 형태. 메인쿼리에 값(서브쿼리 실행 결과) 제공을 위해 사용
연관 서브쿼리 : 메인쿼리 컬럼을 갖고 있는 형태. 메인쿼리에서 읽혀진 데이터를 서브에서 조건 맞는지 확인할 때 사용

반환되는 데이터 형태에 따른 분류 :
단일행 서브쿼리 : 실행 결과가 항상 1건 이하. 단일행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용.
다중행 서브쿼리 : 실행 결과가 여러 건. 다중행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 사용.
다중칼럼 서브쿼리 : 실행 결과로 여러 칼럼 반환. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교 가능.


길어질 경우, WITH로 서브쿼리를 따로 분류하여 사용 가능

WITH (SELECT*FROM table_name) as ex1
	 (SELECT*FROM table_name) as ex1

SELECT * FROM ~
profile
is Yoon
planning design development with data
이전 포스트

[SQL] 기본 - SELECT, JOIN

다음 포스트

[SQL] 데이터베이스 개체 - 뷰, 인덱스, 스토어드 프로시저

0개의 댓글