[데이터베이스개론·SQL] 250115

이슬비·2025년 1월 15일

Database sql

데이터베이스개론·SQL

목록 보기

3/9

금융 시스템

자금의 흐름을 관리하고 지원하는 시스템이다.

주요 구성 요소는 다음과 같다.

은행 시스템 : 계자 관리, 대출, 결제 서비스.
증권 시스템 : 주식, 채권, 펀드 거래 및 관리.
보험 시스템 : 보험 가입, 계약 관리, 보상 처리.
결제 시스템 : 카드 결제, 온라인 송금, 전자 화폐.

금융 시스템의 역할은 다음과 같다.

자금의 원활한 흐름 지원.
금융 거래의 투명성과 신뢰성 확보.
고객과 금융 기관 간의 연결.
경제 성장을 위한 자금 조달 및 분배.

은행 시스템

고객 관리 시스템(CRM)
- 고객 정보, 계좌 관리.
대출 관리 시스템
- 대출 신청, 심사, 상환 관리.
결제 처리 시스템
- 송금, 카드 결제, 자동이체.
리스크 관리 시스템
- 신용 평가, 부도 예측, 규제 준수.

증권 시스템

거래 시스템
- 주식 및 채권 거래 처리.
결제 및 청산 시스템
- 거래 완료 후 자금 및 증권의 이동 관리.
포트폴리오 관리 시스템
- 투자자의 자산 구성 관리.
리스크 관리 시스템
- 시장 위험, 신용 위험 평가.

보험 시스템

보험 계약 관리
- 가입자 정보, 보험 상품 정보 관리.
청구 및 보상 시스템
- 사고 접수, 보상 처리.
고객 서비스 시스템
- 상담, 문의 처리, 계약 갱신 지원.
리스크 관리 시스템
- 재보험, 자산 및 부채 관리.

`ORDER BY`

SQL에서 데이터를 정렬하는 데 사용된다. 기본적으로 오름차순(ASC) 정렬이며 내림차순 정렬 시 DESC 키워드를 사용한다.

SELECT 컬럼명 FROM 테이블명 ORDER BY 컬럼명 [ASC | DESC];

--예시
SELECT NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC;

다중 컬럼 정렬

여러 컬럼을 기준으로 정렬 가능하다. 우선순위는 지정한 컬럼의 순서에 따른다.

SELECT NAME, DEPARTMENT_ID, SALARY FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC, SALARY DESC;

`ORDER BY`와 `NULL`

Oracle에서 NULL 값은 최댓값으로 간주한다. 이를 NULLS FIRST 또는 NULLS LAST로 명시적 설정이 가능하다.

SELECT NAME, SALARY FROM EMPLOYEES
ORDER BY SALARY ASC NULLS FIRST;

DBMS에 따라 차이가 있다. MySQL에서는 NULL 값을 최솟값으로 간주한다.

페이징 쿼리

페이징(Paging)

대량의 데이터에서 일정한 크기로 데이터를 나누어 보여주는 방법이다. 일반적으로 웹 애플리케이션에서 데이터 테이블, 검색 결과 등을 출력할 때 사용하며, 성능 최적화와 사용자 경험 개선을 위해 중요하다.

페이징 쿼리

ROWNUM : Oracle에서 결과 집합의 각 행에 부여되는 번호(Pseudo Column).
ROW_NUMBER() : Oracle의 윈도우 함수로, 정렬 기준에 따라 고유 번호를 부여.

SELECT * FROM (
  SELECT ROW_NUMBER() OVER
    (ORDER BY column_name) AS row_num, column_list
  FROM table_name
)
WHERE row_num BETWEEN :start_row AND :end_row;

SELECT ROWNUM, column_list FROM (
  SELECT column_list
  FROM table_name
  ORDER BY column_name
)
WHERE ROWNUM BETWEEN :start_row AND :end_row;

두 번째 코드는 정렬을 보장하지 않는다. 하위 쿼리를 정렬하여도 이를 상위 쿼리의 ROWNUM에 전달할 때 정렬 순서대로 값을 반환하지 않기 때문이다.

: : 바인드 변수로 입출력 매개변수로써 활용된다.

Oracle 12c부터는 OFFSET과 FETCH NEXT를 사용하여 페이징을 더 간단하고 효율적으로 구현 가능하다.
SELECT column_list
FROM table_name
ORDER BY column_name
OFFSET :start_row - 1 ROWS FETCH NEXT :page_size ROWS ONLY;

페이징 쿼리 작성 시 주의사항

정렬 기준
- ORDER BY는 페이징 쿼리에서 필수.
- 정렬 기준이 없으면 결과 순서가 비정상적으로 출력될 수 있음.
성능 고려
- 대규모 데이터셋에서 OFFSET 사용 시 성능 저하 가능.
- 인덱스를 적절히 설정하여 쿼리 성능 최적화.
다양한 페이지 크기 테스트
- 다양한 크기의 페이지를 고려하여 쿼리를 설계.

`DISTINCT`

중복된 데이터를 제거하여 고유한 값만 반환하는 키워드이다. SELECT 문과 함께 사용하여 특정 컬럼의 고유한 값만 조회할 수 있으며, 데이터의 요약 또는 집계에 유용하다.

SELECT DISTINCT 컬럼명
FROM 테이블명
WHERE 조건;

--예시
SELECT DISTINCT ACCOUNT_TYPE
FROM ACCOUNTS;

DISTINCT 키워드에 여러 컬럼을 적을 경우 해당 셋 또는 그룹을 기준으로 중복 없이 조회한다.
--고객ID와 계좌 타입 셋을 중복 없이 조회
SELECT DISTINCT CUSTOMER_ID, ACCOUNT_TYPE FROM ACCOUNTS;

`GROUP BY`

데이터 그룹핑

데이터베이스에서 데이터를 특정 기준으로 묶는 작업이다. 집계 함수와 함께 사용하여 그룹별 요약 정보를 제공한다.

`GROUP BY`

SELECT 문에서 데이터를 그룹으로 묶는 절이다. 그룹 별로 집계 결과를 반환하며 WHERE 절과 함께 사용 가능하다.

SELECT 컬럼, 집계함수 FROM 테이블
GROUP BY 컬럼;

--예시
SELECT DEPARTMENT_ID, AVB(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

집계 함수

그룹 별로 데이터 요약 정보를 제공한다.

COUNT() : 행의 개수 반환.
SUM() : 합계 반환.
AVG() : 평균 반환.
MAX() : 최댓값 반환.
MIN() : 최솟값 반환.

COUNT(*) = COUNT(1) : NULL 값까지 포함.

--부서별 평균 급여 계산
SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

--직책별 최대 급여와 최소 급여 계산
SELECT JOB_ID,
  MAX(SALARY) AS MAX_SALARY,
  MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEES
GROUP BY JOB_ID;

`GROUP BY`와 `HAVING`

WHERE 절은 GROUP BY 전에 조건을 필터링한다.

--50,000 이상 급여를 가진 직원만 그룹핑
SELECT DEPARTMENT_ID, COUNT(*) AS EMP_COUNT
FROM EMPLOYEES
WHERE SALARY >= 50000
GROUP BY DEPARTMENT_ID;

HAVING 절은 GROUP BY 이후 그룹별 조건을 필터링한다.

--평균 급여가 60,000 이상인 부서만 출력
--HAVING 후 SELECT가 실행되기 때문에 ALIAS 사용 불가능
SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) >= 60000;  --HAVING AVG_SALARY >= 6000; (X)

--계좌 유형별 평균 잔액 조회
--SELECT 후 ORDER BY가 실행되기 때문에 ALIAS 사용 가능
SELECT ACCOUNT_TYPE, AVG(BALANCE) AS AVG_BALANCE
FROM ACCOUNTS
GROUP BY ACCOUNT_TYPE
ORDER BY AVG_BALANCE DESC;  --ORDER BY 2 DESC;

--거래 유형별 총 거래 금액 조회
SELECT TRANSACTION_TYPE, SUM(AMOUNT) AS TOTAL_AMOUNT
FROM TRANSACTIONS
GROUP BY TRANSACTION_TYPE;

--고객당 계좌 수 및 평균 잔액을 구하고 평균 잔액이 50,000 이상인 고객만 출력
SELECT CUSTOMER_ID,
  COUNT(*) AS ACCOUNT_COUNT,
  AVG(BALANCE) AS AVG_BALANCE
FROM ACCOUNTS
GROUP BY CUSTOMER_ID
HAVING AVG(BALANCE) >= 50000;

함수

함수란

데이터를 처리하거나 계산하여 결과를 반환하는 사전 정의된 명령어이다. 데이터 검색, 변환, 요약 등을 자동화하여 효율성을 높이며, 단일 값 함수와 집계 함수로 구분한다.

단일 행 함수

한 행에 대해 하나의 결과를 반환한다.

문자 함수 : 문자열 반환 및 처리.
숫자 함수 : 수학적 계산.
날짜 함수 : 날짜 및 시간 처리.
변환 함수 : 데이터 형식 변환.
NULL 처리 함수 : NULL 값 처리.

집계 함수

여러 행을 그룹으로 묶어 하나의 결과를 반환한다.

COUNT() : 행의 개수 반환.
SUM() : 합계 함수.
AVG() : 평균 계산.
MAX() : 최댓값 반환.
MIN() : 최솟값 반환.

문자 함수

문자열을 다루기 위한 함수로, 문자열 반환, 검색, 길이 계산 등을 제공한다.

UPPER(), LOWER() : 대소문자 변환.
LENGTH(), SUBSTR() : 문자열 길이와 일부 추출.
INSTR(), TRIM(), RTRIM(), LTRIM() : 특정 문자열의 처음 위치 출력과 공백 제거.
LPAD(), RPAD() : 문자열 채움.
REPLACE(), CONCAT() : 문자열 대체와 두 문자열 연결.

CONCAT() 함수 대신 ||도 사용 가능하다.

숫자 함수

숫자를 다루기 위한 함수이다.

ROUND(), TRUNC() : 숫자 반올림과 버림.
CEIL(), FLOOR() : 숫자 올림과 내림.
ABS(), MOD() : 절댓값과 나머지 계산.

날짜 함수

날짜 및 시간 데이터를 다루기 위한 함수이다.

SYSDATE, CURRENT_DATE : 현재 날짜와 시간 반환.
ADD_MONTHS(), MONTHS_BETWEEN() : 날짜 계산.
NEXT_DAY(), LAST_DAY() : 지정된 요일의 다음 날짜와 해당 월의 마지막 날짜 반환.
EXTRACT(), TRUNC() : 날짜에서 특정 요소 추출과 날짜를 특정 단위로 자름.

변환 함수

데이터 타입을 다른 타입으로 변환하는 함수이다.

TO_CHAR(), TO_DATE(), TO_NUMBER() : 데이터 형식 변환.

`NULL` 처리 함수

NULL 값을 처리하는 함수이다.

NVL() : NULL 값을 대체.
NVL2() : NULL 여부에 따라 다른 값을 반환.
COALESCE() : NULL이 아닌 첫 번째 값 반환.
NULLIF() : 두 값이 같으면 NULL 반환.

`CASE WHEN`과 `DECODE`

`CASE WHEN`

조건에 따라 값을 반환하는 문법이다. SELECT, UPDATE, DELETE 문 등에서 사용 가능하며, 가독성이 뛰어나 복잡한 조건을 처리할 때 유용하다.

CASE
  WHEN 조건1 THEN 결과1
  WHEN 조건2 THEN 결과2
  ELSE 결과
END

CASE 컬럼명  --equal 조건
  WHEN 값 THEN 결과1
  WHEN 값 THEN 결과2
  ELSE 결과
END

CASE WHEN 예시는 다음과 같다.

SELECT EMPLOYEE_ID,
  CASE
    WHEN SALARY > 10000 THEN 'HIGH'
    WHEN SALARY BETWEEN 5000 AND 10000 THEN 'MEDIUM'
    ELSE 'NOW'
  END AS SALARY_LEVEL
FROM EMPLOYEES;

`DECODE`

특정 값에 따라 다른 값을 반환하는 함수이다. SELECT 문에서 주로 사용하며 CASE WHEN보다 간단한 조건 처리에 적합하다.

DECODE(표현식, 조건1, 결과1, 조건2, 결과2, ..., 기본값)

--DECODE 예제
SELECT EMPLOYEE_ID,
  DECODE(JOB_ID,
    'ADMIN', '관리자',
    'DEV', '개발자',
    'HR', '인사담당자',
    '기타') AS DEPARTMENT_NAME
FROM EMPLOYEES;

CASE WHEN vs DECODE

CASE WHEN DECODE
- 조건 처리에 유연함.
- 여러 조건 및 복잡한 로직 처리 가능. - 간단한 값 매핑에 적합.
- 특정 열의 값 기반으로 처리.

`CASE WHEN`	`DECODE`
- 조건 처리에 유연함. - 여러 조건 및 복잡한 로직 처리 가능.	- 간단한 값 매핑에 적합. - 특정 열의 값 기반으로 처리.

이슬비

이전 포스트

[데이터베이스개론·SQL] 250114

다음 포스트

[데이터베이스개론·SQL] 250115