SQL 베이직 5차시

TCL 구문

트랜잭션을 제어하는 영역

 

트랜잭션이란 쪼갤 수 없는 업무처리 단위인데, 무조건 한 세트로 묶여야하는 논리적 업무 단위라고 생각할 수 있습니다.

예를 들어 ‘계좌이체’라는 작업이 일어날 때 돈을 보내는 A라는 사람의 계좌에서 돈이 차감된다, 돈을 받는 B라는 사람의 계좌에 돈이 증가한다.

 

이 두 가지 액션은 하나로 묶여서 한 번에 동작 되어야 합니다. 둘 중에 하나의 액션만 동작이 되면 안 되니까요.

이렇게 무조건 한 세트로 묶여야하는 액션들을 트랜잭션이라고 부릅니다. 

 

트랜잭션 내에서 벌어진 것들은 커밋이 실행되기 전까지 다른 세션에서 보이지 않음.

 

세션: 각 데이터베이스 클라이언트는 독립적인 세션을 가짐. 세션 간에는 트랜잭션이 격리되어 있으며, 한 세션에서의 트랜잭션 변경 사항은 COMMIT이 실행되기 전까지 다른 세션에서 보이지 않음.

 

다른 세션: 다른 쿼리창에서 실행되는 SELECT 문은 별도의 세션에서 실행됨. 다른 세션에서는 첫 번째 세션에서 실행된 INSERT 문이 COMMIT되지 않았기 때문에 해당 데이터가 보이지 않음.

 

COMMIT: INSERT/UPDATE/DELETE 명령어를 확정/반영하는 명령어.

첫번째 세션에서 커밋을 하면 INSERT 명령어가 확정 되기 때문에 두 번째 세션에서도 확인 가능합니다.

COMMIT;

 

ROLLBACK: insert/update/delete 문은 commit 만 하지 않았다면 되돌릴 수 있어요.

직전에 했던 COMMIT으로 돌아간다.

ROLLBACK

 

ROLLBACK은 INSERT/UPDATE/DELETE 를 취소하는 명령어이지 commit을 취소하는 명령어는 아니기 때문에 지금 이 상황에서 아무리 rollback을 해도 commit 이전으로 돌아가지 않아요! 그러니 commit 할 때는 조심해주세요!!!

 

첫 번째 세션에서 아래 Insert 문을 실행하고(그럼 ‘르탄이’라는 이름을 가진 데이터가 두 줄이 되겠죠?), commit 전에 Rollback을 실행하면 다시 ‘르탄이’라는 이름을 가진 데이터가 한 줄이 됩니다.

 

SAVEPOINT: ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어.

그 지점까지만 데이터가 복구 됨.

 

SAVEPOINT 세이브포인트명 (ex. SAVEPOINT A)
ROLLBACK TO 세이브포인트명 (ex. ROLLBACK TO A)

 

3. DDL구문

 

데이터를 정의하는 명령어.

 

1) CREATE

 

CREATE TABLE STUDENT(
STD_NO INT NOT NULL,
STD_NAME VARCHAR(50) NOT NULL,
STD_YN VARCHAR(2) DEFAULT 'N');

SELECT * FROM STUDENT;

 

DEFAULT 값을 설정해 놓으면 Insert 시 해당 컬럼의 값을 따로 명시하지 않았을 때 Default 값이 할당 됨.

(Insert 시 NULL로 명시해 놓았으면 default 값이 아닌 Null값이 들어감.)

 

테이블을 만들 때 주의해야할 점

• 테이블 명은 고유해야하고 한 테이블 내에서 각 컬럼의 명도 고유해야합니다.
• 테이블명과 컬럼명은 숫자로 시작될 수 없습니다.

 

2) ALTER

 

ALTER TABLE STUDENT ADD STD_SCORE FLOAT; -- 컬럼 추가

ALTER TABLE STUDENT DROP COLUMN STD_SCORE; -- 컬럼 삭제

ALTER TABLE STUDENT MODIFY STD_NAME VARCHAR(100); -- 컬럼 변경

ALTER TABLE STUDENT RENAME COLUMN STD_NO TO STD_NUMBER; -- 컬럼명 변경

 

3) RENAME

 

RENAME STUDENT TO STD -- 테이블 명 변경
RENAME STD TO STUDENT -- 테이블 명 변경

 

4) TRUNCATE TABLE

 

TRUNCATE TABLE STUDENT  -- 테이블 내 저장되어 있는 모든 데이터 제거. 
-- delete 와 유사하지만 roll back 불가능함으로 조심 ! 

 

5)DROP

 

3. ROLLUP

 

• Rollup함수는 SQL에서 집계 데이터를 생성할 때 유용한 기능입니다.
• Rollup을 사용하여 여러 수준의 집계 데이터를 “한 번의 쿼리”로 “쉽게” 생성할 수 있습니다.
• 예를 들어 월 별, 분기 별, 연도 별 총계의 데이터를 구해야할 때나 제품 별, 카테고리 별, 전체 매출의 총계를 구할 때 효율적으로 쓸 수 있겠죠!?
• 복잡한 집계 쿼리를 간단하게 적성할 수 있어 쿼리의 가독성을 높이고 유지보수를 용이하게 하는 장점이 있습니다.

4. WINDOW

• SQL WINDOW 함수는 현재 행과 관련된 일련의 행에서 계산을 수행하는 함수 클래스입니다. (이 말이 이해가 되지 않아도 괜찮아요! 이따 실제 데이터를 보면서 함께 익혀보아요!)
• OVER() 절과 함께 사용되며 결과 집합의 특정 행 WINDOW를 기반으로 계산을 수행할 수 있습니다.

 

-- [] 안에 있는 내용은 옵션으로, 필수적으로 작성해야하는 것이 아니라 필요에 따라 작성하면 됩니다. 
SELECT 윈도우함수([대상]) OVER ([PARTITION BY 컬럼] -- 연산할 그룹 묶기
							[ORDER BY 컬럼 ASC|DESC]  -- 정렬 순서 지정
							[ROW|RANGE BETWEEN A AND B]) -- 연산범위 설정.

 

역할에 따라 다음과 같이 나눌 수 있습니다.

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MAX, MIN, AVG, COUNT
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE

 

4.1 순위 함수

• 그 전에 orders 테이블의 특정 쿼리 실행 결과를 다른 테이블로 저장해봅시다.
  • 위와 같이 날짜 별 판매량의 총 합을 확인할 수 있습니다.
  • 이제부터 이렇게 새로 만든 테이블을 대상으로 순위 함수 실습을 해보도록 하겠습니다.
• CREATE TABLE new_orders_summary AS SELECT ORDER_DT, COUNT(*) as cnt FROM orders o GROUP BY ORDER_DT ORDER BY ORDER_DT; select * from new_orders_summary;
• 대표적인 순위 함수는 아래와 같습니다. 숫자가 조금씩 차이가 나는 게 보이시나요? 똑같은 데이터를 대상으로 Rank(순위)를 매겼음에도 rank에는 3등과 6등이 생략되어 있고 rownumber는 중복된 순위가 없습니다.1. RANK 1, 2, 2, 4, 5, 5, 7

  2. DENSE_RANK	1, 2, 2, 3, 4, 4, 5
  3. ROW_NUMBER	1, 2, 3, 4, 5, 6, 7

• (1) RANK()
  • 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너 뜀.
  • 문법: rank() over (order by 정렬기준)
    • 예시1 (날짜마다 cnt를 기준으로 순위를 매겨보도록 하겠습니다.)
      • 위와 같이 하루에 6개 팔린 날짜가 3번 있기 때문에 3개를 공동 1등으로 지정하고, 그 다음에 나오는 행은 4등을 부여합니다.
    • -- 판매수가 많은 순서대로 랭킹 지정하기 SELECT *, RANK() OVER(ORDER by cnt DESC) AS rnk FROM new_orders_summary as s order by rnk;
• (2) DENSE_RANK()
  • 순위를 매기면서 같은 순위가 존재하더라도 다음순위를 건너 뛰지 않고 이어서 매깁니다.
  • ‘dense’가 ‘밀집한’이라는 뜻을 가지고 있기 때문에 ‘순위가 밀집되어 있다’ 라고 이해해 주세요.
    • 위와 같은 결과를 확인할 수 있습니다.
  • SELECT *, DENSE_RANK() OVER(ORDER by cnt DESC) AS rnk FROM new_orders_summary as s order by rnk;
• (3) ROW_NUMBER()
  • 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여합니다.
    • 동일하게 6개가 팔렸음에도 다른 순위가 부여되었습니다.
    • 중복값이 발생하면 안 되는 컬럼을 만들어야할 때 사용합니다.
  • SELECT *,row_number() OVER(ORDER by cnt DESC) AS rnk FROM new_orders_summary as s order by rnk;

4.2 집계 함수

• 그 전에 orders 테이블의 특정 쿼리 실행 결과를 다른 테이블로 저장해봅시다.
  • 날짜 별, 직원별, 판매량의 합계를 구했습니다.
• CREATE TABLE new_orders_summary2 AS SELECT ORDER_DT, REG_NAME, COUNT(*) AS CNT FROM ORDERS GROUP BY ORDER_DT, REG_NAME ORDER BY ORDER_DT; select * from new_orders_summary2;
• (1) SUM: 데이터의 합계를 구하는 함수.
  • 일반적인 집계 함수의 사용
    • 일반적인 집계함수를 이용해서 cnt의 총합을 구해 봅시다.
    • new_orders_summary2 테이블의 CNT컬럼의 총합인 100이라는 숫자가 반환됩니다. (총 100개가 판매되었으니까요.)
    • 그룹 (계산 직원 이름) 별로 몇개를 판매했는지는 아래와 같이 확인할 수 있습니다.
    • SELECT REG_NAME, SUM(CNT) FROM new_orders_summary2 GROUP BY REG_NAME;
  • select sum(cnt) from new_orders_summary2;
  • 이번엔 sum + over 절을 써서 데이터의 누적값을 구해보겠습니다. (=개인 별 총 판매량을 구해보겠습니다.)

    SELECT
    	REG_NAME,
    	ORDER_DT,
    	cnt,
    	SUM(CNT) OVER(PARTITION BY #파티션의 기준이 될 컬럼#
    ORDER BY
    	#정렬의 기준이될 컬럼#) AS TOTAL_CNT
    FROM
    	new_orders_summary2;
    

    • ‘개인 별’ 총 판매량을 구하고 ‘날짜’ 기준으로 정렬해서 볼 것임.

    SELECT
    	REG_NAME,
    	ORDER_DT,
    	cnt,
    	SUM(CNT) OVER(PARTITION BY REG_NAME
    ORDER BY
    	ORDER_DT) AS TOTAL_CNT
    FROM
    	new_orders_summary2;
    

    • 실행 결과는 아래와 같습니다. 날짜 별로 개인 별 판매량의 누계를 알 수 있습니다.
      • 쿼리를 뜯어보면
        • 먼저 sum(cnt)를 구하는데, over(partition by ~ order by ~)구문을 이용해 파티션 별로 작은 집합을 만들겁니다.
        • 저희는 PARTITION BY REG_NAME 이라고 했기 때문에 REG_NAME 별로 작은 집합이 만들어집니다.
        • 그리고 ORDER BY ORDER_DT 라고 되어 있기 때문에 만들어진 작은 집합 안에서 order_dt 별로 정렬을 합니다.
        • 즉, “로이”라는 reg_name을 가진 데이터만 추출해서 작은 집합을 만들고, 그렇게 만들어진 집합에 order_dt 별로 정렬을 합니다. 그리고 SUM(CNT) 쿼리에 의해 누적 합이 계산 됩니다.
  • SELECT REG_NAME, ORDER_DT, CNT FROM NEW_ORDERS_SUMMARY2;
• (2) MAX: 데이터의 최댓값을 구하는 함수.
  • max라는 집계함수의 sum + over 절은 어떻게 작동할까요?
  • SELECT REG_NAME, ORDER_DT, cnt, max(CNT) OVER(PARTITION BY REG_NAME ORDER BY ORDER_DT) AS TOTAL_CNT FROM new_orders_summary2;
• (3) min, avg, count 도 동일한 방식으로 동작합니다.
  • MIN: 데이터의 최솟값을 구하는 함수.
  • AVG: 데이터의 평균값을 구하는 함수
  • COUNT: 데이터의 건수를 구하는 함수.

  -- count 예시
  SELECT REG_NAME, ORDER_DT, COUNT(*) OVER (PARTITION BY REG_NAME ORDER BY ORDER_DT) FROM new_orders_summary2;
  

• (4. 심화) window 함수의 집계 범위: 집계 연산 시 행의 범위 설정 가능.
  • 1. ROWS와 RANGE
    • ROWS: order by의 기준이된 컬럼의 값이 같더라도 한 행 씩 연산.
    • RANGE: order by의 기준이된 컬럼의 같은 값의 경우 하나의 range 로 묶어서 동시에 연산. (default)
    1. 시작점과 마지막 시점 정의
    • A) 시작점 정의
      • CURRENT ROW: 현재행부터
      • UNBOUNDED PRECEDING: 처음부터(default)
      • N PRECEDING: N 이전부터
    • B) 마지막 시점 정의
      • CURRENT ROW: 현재행까지(default)
      • UNBOUNDED FOLLOWING: 마지막까지
      • N FOLLOWING: N 이후까지
  • 예제) 범위 설정에 따른 누적합.
    • RANGE 범위 전달(default): 값이 같을 경우 같은 범위로 취급하여 ‘동시’ 연산.
    • cnt가 1인 행이 동시에 연산되고, 이후 더해질 때도 같은 값끼리 동시에 연산되어 같은 값이 출력되는 것을 확인할 수 있음.

    SELECT 	REG_NAME,
    	ORDER_DT,
    	cnt,
    	SUM(CNT) OVER(PARTITION BY REG_NAME ORDER BY CNT ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
    FROM new_orders_summary2;
    

    • ROWS 범위전달: 각 행별로 연산 수행.

    SELECT 	REG_NAME,
    	ORDER_DT,
    	cnt,
    	SUM(CNT) OVER(PARTITION BY REG_NAME ORDER BY CNT ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
    FROM new_orders_summary2;
    

  • SELECT REG_NAME, ORDER_DT, cnt, SUM(CNT) OVER(PARTITION BY REG_NAME ORDER BY CNT) -- partition 은 그대로 REG_NAME로 나누되, cnt 기준으로 정렬을 한 뒤 -- cnt의 누적합을 구해보겠습니다. FROM new_orders_summary2;
• -- [] 안에 있는 내용은 옵션으로, 필수적으로 작성해야하는 것이 아니라 필요에 따라 작성하면 됩니다. SELECT 윈도우함수([대상]) OVER ([PARTITION BY 컬럼] -- 연산할 그룹 묶기 [ORDER BY 컬럼 ASC|DESC] -- 정렬 순서 지정 [ROWS|RANGE BETWEEN A AND B]) -- 연산범위 설정.

•  

•  

•