반응형
- 조인과 집계 데이터
- 조인과 집계데이터= Grouping Set절
- 조인과 집계데이터 = Roll up 절
- 조인과 집계데이터 = Cube 절
- 조인과 집계데이터 = 분석 함수란
- 조인과 집계데이터 = AVG함수
- 조인과 집계데이터 = Row Number , Rank , Dense_Rank 함수
- 조인과 집계데이터 = First_Value , Last_Value함수
- 조인과 집계데이터 = Lag, Lead 함수
| 종류 | 설명 |
|---|---|
| INNER 조인 | 특정 컬럼을 기준으로 정확히 매칭된 집합을 출력한다. |
| OUTER 조인 | 특정 컬럼을 기준으로 매칭된 집합을 출력하지만 한쪽의 집합은 모두 출력하고 다른 한쪽의 집합은 매칭되는 컬럼의 값 만 |
| 을 출력한다. | |
| SELF 조인 | 동일한 테이블 끼리 의 특정 컬럼을 기준으로 매칭되는 집합을 출력한다. |
| FULL OUTER 조인 | INNER, LEFT OUTER, RIGHT OUTER 조인 집합을 모두 출력한다. |
| CROSS 조인 | Cartesian Product이라고도 하며 조인되는 두 테이블에서 곱집합을 반환한다. |
| NATURAL 조인 | 특정 테이블의 같은 이름을 가진 컬럼 간의 조인집합을 출력한다. |
| - LEFTOUTER조인 | |
| - 한쪽의 집합은 모두 출력하고 다른 한쪽의 집합은 매칭되는 컬럼의 값만 출력한다. | |
| - SELF 조인 | |
| - 예시 -> 조직에서 누군가는 누군가의 부하직원이면서 누군가는 누군가의 윗사람일때 | |
| - 이때 같은 테이블에 rank순으로 담겨 있으면 사용한다. | |
- ON M.EMPLOYEE_ID = E.MANAGER_ID 이런식으로 주어지면 직원 ID가 누군가의 매니저 ID가 되는데 그러면 각 매니저별로 나열할수있다 (ORDER BY 사용) |
|
| - 또다른 예시로는 아래와 같은 부분이 있다. |
> FILM 테이블과
FILM 테이블을 SELF 조인함
서로다른영화인집합을출력
영화의 상영 시간은 동일하면서
>
```sql
SELECT F1.title,
F2.title,
F1.length
FROM film F1
INNER JOIN film F2 -- f1 , f2 로 지정하고 합친다.
ON F1.film_id <> F2.film_id
AND F1.length = F2.length;
```Full outer 조인
아래 그림과 같다
.png)
.png)
- CROSS JOIN
.png)
- Natural JOIN
- 같은 컬럼 있으면 알아서 INNERJOIN
- NATURAL 조인은 INNER 조인의 또 다른 SQL 작성 방식이다. v 즉 조인 컬럼을 명시하지 않아도 된다.📌 집계 데이터
Grouping Set절
UNION ALL을 쓰지 않고 쉽게 그룹화 하고 싶다 - GROUPING SETS 절을 사용함
UNION ALL을 이용하여 (BRAND, SEGMENT 기준), (BRAND기준), (SEGMEN기준), (전체기준)으로 QUANTITY 합계의 값을 구할 수 있다
공식
SELECT c1 , c2 , 집계함수(c3) FROM table_name GROUP BY grouping sets ( (c1, c2) , (c1) , (c2) , ()SELECT brand, segment, Sum (quantity) FROM sales GROUP BY grouping sets ( ( brand, segment ), ( brand ), ( segment ), ( ) );
-- 위랑 같은 결과
1. BRAND, SEGMENT 컬럼 기준으로 합계를 구한다. 2. BRAND 컬럼 기준으로 합계를 구한다. 3. SEGMENT 컬럼 기준으로 합계를 구한다. 4. 테이블 전체를 기준으로 합계를 구한다.
SELECT **Grouping**(brand) GROUPING_BRAND,
**Grouping**(segment) GROUPING_SEGEMENT,
brand,
segment,
Sum (quantity)
FROM sales
GROUP BY grouping sets ( ( brand, segment ), ( brand ), ( segment ), ( ) )
ORDER BY brand,
segment;
-- 해당 컬럼이 집계에 사용 되었으면 0,
-- 그렇지 않으면 1을 리턴한다.SELECT CASE
WHEN Grouping(brand) = 0
AND Grouping(segment) = 0 THEN '브랜드별+등급별'
WHEN Grouping(brand) = 0
AND Grouping(segment) = 1 THEN '브랜드별'
WHEN Grouping(brand) = 1
AND Grouping(segment) = 0 THEN '등급별'
WHEN Grouping(brand) = 1
AND Grouping(segment) = 1 THEN '전체합계'
ELSE ''
END AS "집계기준",
brand,
segment,
Sum(quantity)
FROM sales
GROUP BY grouping sets ( ( brand, segment ), ( brand ), ( segment ), ( ) ).png)
- 조인과 집계데이터 = Roll up 절
- BRAND, SEGMENT 컬럼 기준으로 ROLL UP 한다.
- = BRAND 컬럼 기준으로 합계, BRAND 컬럼 , 기준으로 합계 전체 합계를 구한다.
SELECT brand,
segment,
Sum (quantity)
FROM sales
GROUP BY **rollup** ( brand, segment )
ORDER BY brand,
segment;- SEGMENT 기준으로 GROUP BY 한다.
- BRAND 컬럼 기준으로 ROLL UP 한다. SELECT segment,
brand,
Sum (quantity)
FROM sales
GROUP BY segment,
rollup ( brand )
ORDER BY segment,
brand;
-- 위랑 다른점은 Segment 기준으로 그룹화 하고 브랜드 기준으로 롤업함
-- 부분 ROLLUP 시 전체 합계는 구하지 않는다.조인과 집계데이터 = Cube 절
Grouping column의 다차원 소계를 생성한다.
SELECT c1, c2, c3, 집계함수(c4) FROM table_name GROUP BY cube ( c1, c2, c3 );- CUBE(C1,C2,C3)
- => GROUPING SETS ( (C1,C2,C3), 3개묶
- (C1,C2),(C1,C3),(C2,C3), 2개씩 묶
- (C1), (C2), (C3), 1개씩 묶
- () ) 여긴 아무것도 없음
- () ) 여긴 아무것도 없음
(2의 N승=소계의 수)
SELECT brand, segment, Sum (quantity) FROM sales GROUP BY cube ( brand, segment ) ORDER BY brand, segment;- 인자가 2개 이므로 총 4개의 경우에 수가 합계로 출력
- 인자가 2개 이므로 총 4개의 경우에 수가 합계로 출력
- 브랜드 컬럼 기준 2개
- 세그먼트 컬럼 기준 2개
- 합이 4개 + 전체 합계 1개 까지 해서 총 5개 합계가 뜬다.
즉 정리하자면 Grouping Set 은 UNION ALL과 같은 역할
Roll up 절은 자동으로 합계 구하기 (전체 합계는 구하지 않는다.)
조인과 집계데이터 = 분석 함수란
특정 집합 내에서 합계 및 카운트 계산
결과 건수의 변화 X
SELECT Count(*) OVER(), p.* FROM product p사용하고자 하는 분석함수를 쓰고 대상 컬럼을 기재 후
PARTITION BY에서 값을 구하는 기준 컬럼을 쓰고
ORDER BY에서 정렬 컬럼을 기재한다.
SELECT c1, 분석함수(c2, c3, ...) OVER (partition BY c4 ORDER BY c5) FROM table_name ;
조인과 집계데이터 = AVG함수
- 분석함수 중 1개
- 분석함수를 사용하여 결과집합을 그대로 출력하면서 GROUP_NAME 기준의 평균을 출력하였다.
SELECT A.product_name, A.price, B.group_name, Avg (A.price) OVER ( partition BY B.group_name) FROM product A INNER JOIN product_group B ON ( A.group_id = B.group_id );- 분석함수 중 1개
조인과 집계데이터 = Row Number , Rank , Dense_Rank 함수
Row Number
해당 집합내에서 순위를 구한다.
순위를 구할 때 GROUP_NAME 컬럼 기준으로
구하고 GROUP_NAME 기준의 각 순위는
PRICE 컬럼 기준으로 정렬한다.ROW_NUMBER 는 같은 순위가 있어도 무조건 순차적으로 순위를 매긴다.(1,2,3,4 순서)
SELECT A.product_name, B.group_name, A.price, Row_number () OVER ( partition BY B.group_name ORDER BY A.price) FROM product A INNER JOIN product_group B ON ( A.group_id = B.group_id );
- **Rank**
- RANK는 같은 순위가 있으면 동일 순위로 매기고 그 다음순위로건너뛴다.(1,1,3,4...순)
- 2가 없어질수도 있음
- **DENSE_RANK**
- DENSE_RANK는 같은 순위가 있으면 동일 순위로 매기고그다음순위를건너뛰지않는다.(1,1,2,3 ... 순으로 나간다.)
- 1,2,3은 있지만 중복 가능조인과 집계데이터 = First_Value , Last_Value함수
First_Value
SELECT A.product_name, B.group_name, A.price, First_value (A.price) OVER ( partition BY B.group_name ORDER BY A.price ) AS LOWEST_PRICE_PER_GROUP FROM product A INNER JOIN product_group B ON ( A.group_id = B.group_id );- GROUP_NAME 컬럼 기준으로 PRICE 컬럼으로 정렬한 값 중에서 가장 첫번째 나오는 PRICE 값을 출력한다.
Last_Value
LAST_VALUE함수에는
“RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (안묶인 그 다음) ” 를 추가
DEFAULT가
- ”RANGE BETWEEN UNBOUNDED PRECEDING (안묶인 이전) AND CURRENT ROW (현재 열)” 이기 때문이다.
SELECT A.product_name, B.group_name, A.price, Last_value (A.price) over ( PARTITION BY B.group_name ORDER BY A.price RANGE BETWEEN unbounded preceding AND unbounded following) AS HIGHEST_PRICE_PER_GROUP FROM product A inner join product_group B ON ( A.group_id = B.group_id );
조인과 집계데이터 = Lag, Lead 함수
Lag
이전 행의 값 찾기
없으면 NULL
SELECT A.product_name, B.group_name, A.price, **Lag** (A.price, 1) OVER ( partition BY B.group_name ORDER BY A.price ) AS PREV_PRICE, -- 현재행의 PRICE에서 이전행의 PRICE를 뺀다. A.price - **Lag** (price, 1) OVER ( partition BY group_name ORDER BY A.price ) AS CUR_PREV_DIFF FROM product A INNER JOIN product_group B ON ( A.group_id = B.group_id );
Lead
다음 행의 값 찾기
없으면 NULL
SELECT A.product_name, B.group_name, A.price, **Lead** (A.price, 1) OVER ( partition BY B.group_name ORDER BY A.price ) AS NEXT_PRICE, -- 현재행의 PRICE에서 다음행의 PRICE를 뺀다. A.price - **Lead** (price, 1) OVER ( partition BY group_name ORDER BY A.price ) AS CUR_NEXT_DIFF FROM product A INNER JOIN product_group B ON ( A.group_id = B.group_id );📌 CASE
파이썬의
IF ~ ELSE문 같은 조건문을 구현 할 수 있다.CASE 표현은 IF - THEN - ELSE 논리와 유사항 방식으로 사용할 수 있는 함수이다.
오라클에서는 DECODE 함수를 사용할 수도 있다.
SELECT CASE WHEN 조건식1 THEN 결과1 -- WHEN은 IF와 유사 WHEN 조건식2 THEN 결과2 -- WHEN은 ELSE IF와 유사 ELSE는 ELSE와 유사 ELSE 결과3 END;SELECT ENAME, -- 직원이름 CASE WHEN SAL > 2000 -- 급여가 2,000 초과면 THEN SAL -- 급여를 표시하고 ELSE 2000 -- 해당하지 않으면 2,000을 표시하라 END REVISED_SALARY -- CASE 행의 이름은 REVISED_SALARY 다 FROM EMP ;
📌 COALESCE
입력한 컬럼값 중에서 NULL 이 아닌 첫번째 값을 나타냄
SELECT product, ( price - COALESCE(discount, 0) ) AS NET_PRICE FROM tb_item_coalesce_test;순이익 NET_PRICE 를 계산하려면 가격 컬럼에 있는 값에서 할인 컬럼에 있는 값을 빼면 되는데
이때 Null 이 있으면 0을 리턴한다.
SELECT product, ( price - CASE WHEN discount IS NULL THEN 0 -- if discount == Null -- return 0 ELSE discount -- else return discount(x) :) END ) AS NET_PRICE FROM tb_item_coalesce_test;위의 코드랑 결과는 똑같다
📌 NULLIF
- 입력한 두개의 인자값이 동일하면 NULL 리턴
- 아니면 첫번째 인자값 리턴
- NULLIF(표현식1, 표현식2)
- 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.
- 특정 값을 NULL로 변경해야 할 때 유용하게 사용할 수 있다.
-- NULLIF (표현식1, 표현식2) : 표현식1 과 2과 같으면 NULL, 다르면 표현식 1 리턴
-- MGR 7698 이면 NULL로 표시한다.
SELECT ename,
empno,
mgr,
NULLIF(mgr, 7698) -- NUIF
FROM emp;📌 CAST
- CAST 함수를 사용하면 지정한 값을 다른 테이터 타입으로 변환할 수 있습니다.
- 형변환 함수
SELECT
CAST ('100' AS INTEGER);
==
SELECT
'100'::INTEGER;
-- 위 두개는 결과가 같다
-- '100'이라는 문자열을 정수형으로 형변환SELECT
CAST ('2015-01-01' AS DATE);
-- '2015-01-01' 이라는 문자열을 DATE 타입으로 형변환- CONVERT 함수도 지정한 값을 다른 테이터 타입으로 변환하고 싶을 때 사용합니다.
- expr에는 값을 지정
- type에는 변환하고 싶은 데이터 타입을 지정
CONVERT(expr, type)
- 지정 가능한 데이터 타입
📌 WITH
WITH tmp1 --WITH문을 이용해서 해당 집합을 TMP1으로 지정
AS (SELECT film_id,
title,
( CASE --영화의 상영시간별로 SHORT, MEDIUM, LONG
WHEN length < 30 THEN 'SHORT'
WHEN length >= 30
AND length < 90 THEN 'MEDIUM'
WHEN length > 90 THEN 'LONG'
END ) LENGTH
FROM film)
SELECT * -- SELECT문에서 TMP1을 조회
FROM tmp1
WHERE length = 'LONG'; --TMP1 집합에서 상영시간 구분이 LONG인 집합을 출력- with 문을 활용하면 select 문에서 리턴한 결과를 또 하나의 조건식으로 사용 가능합니다
📌 재귀쿼리
- RECURSIVE WITH 재귀 쿼리
-- 나라별 급여 평균의 평균보다 많이 급여를 주고 있는 나라와 나라의 평균 급여를 구하시오.
-- (나라명과 나라의 평균 급여만 출력)- ‘평균의 평균값’을 구하기 위해서는 쿼리A를 SUBQUERY로 사용해서 평균을 구한다
- 근데 이렇게 반보갷서 사용하면 퍼포먼스 저하 및 가독성 저하 문제 발생
- 이러한 문제를 해결하기 위해서 WITH절을 사용해서 쿼리문을 재사용할 수 있습니다.
WITH recursive tmp1 AS
(
**SELECT employee_id ,
manager_id ,
full_name ,
0 lvl
FROM
WHERE manager_id IS NULL -- 관리자가 없는 사람은 최상위 관리자**
UNION
**SELECT e.employee_id ,
e.manager_id ,
e.full_name ,
s.lvl + 1
FROM tb_emp_recursive_test e ,
tmp1 s tb_emp_recursive_test
WHERE s.employee_id = e.manager_id** -- 사원ID와 관리자 ID를 조인함
)
SELECT employee_id,
manager_id,
lpad(' ', 4 * (lvl))
|| full_name AS full_name
FROM tmp1;
-- 이전의 결과를 계속해서 인자로 값으로 넣어주고
-- 더 이상 넣어줄 인자가 없을 때 나머지 나머지 쿼리가 진행됩니다.- 제일 위의 관리자부터 시작해서
EMPLOYEE_ID1부터 계속 나열
이해가 잘 안가서 찾아본 재귀 쿼리
재귀 - 원래의 자리로 되돌아가거나 되돌아옴
같은걸 반복한다?
한 쿼리가 반복되어 실행된다.
a,b의 부모는 A, A,B의 부모는 AA
부모가 없는 최상위 AA 구조에서 아래와 같이 코드 작성WITH RECURSIVE cte. -- MYsql에서는 RECURSIVE 추가 AS (SELECT code, parent_code FROM code_table WHERE code = 'AA' -- AA 인 부분부터 시작 UNION ALL SELECT a.code, a.parent_code FROM code_table a INNER JOIN cte b ON a.parent_code = b.code) -- AA 를 부모로 가지는 A 랑 B -- A 또는 B를 부모로 가지는 a 와 b 까지 모두 select SELECT code, parent_code FROM cte
참고 https://allmana.tistory.com/134
📌 트랜잭션
- ‘트랜잭션’이란 '거래'라는 뜻으로 데이터베이스 내에서 하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 논리적인 작업 단위이다.
- 여러 단계의 처리를 하나의 처리처럼 다루는 기능
- 여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료된다.
- 하나의 명령어라도 잘못되면 전체 취소된다.
- 트랜잭션을 쓰는 이유는 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구하기 위함
❤️ 참고
- POSTGRESQL 은 DDL(data definition language : 데이터 조작어)도 커밋을 해야한다!
- 오라클은 DDL은 커밋을 할 필요가 없다.
📌 트랜잭션 필요성
(출처 : https://jerryjerryjerry.tistory.com/48)
- 트랜잭션은 '거래'라는 뜻으로, 은행에서 입금과 출금을 하는 그 거래를 뜻한다.
- A 은행에서 출금하여 B은행으로 송금한다고 가정하자.
- 송금하는 중에 알 수 없는 오류가 발생하여 A은행 계좌에서 돈이 빠져 나갔는데 B은행 계좌에 입금되지 않았다.
- 이때, 우리는 A은행 계좌의 출금을 취소하거나, 출금된 금액만큼 B은행 계좌로 다시 송금하면 된다.
- 하지만 이 방법은 번거롭고 더 심한 오류를 발생시킬수 있다.
- 그래서 생각해낸 해결책이, 거래가 성공적으로 모두 끝난 후에야 이를 완전한 거래로 승인하고, 거래 도중 뭔가 오류가 발생했을 때는 이 거래를 아예 처음부터 없었던 거래로 되돌리는 것이다.
- 이렇게 거래의 안전성을 확보하는 방법이 트랜잭션이다.
- Database에선 테이블에서 데이터를 읽어 온 후 다른 테이블에 데이터를 입력하거나 갱신, 삭제하는데 처리 도중 오류가 발생하면 모든 작업을 원상태로 되돌린다.
- 데이터베이스에선 처리 과정이 모두 성공했을 때만 최종적으로 데이터베이스에 반영한다.
📌 트랜잭션의 종류
트랜잭션에는 내가 적은 쿼리문과 데이터를 최종적으로 데이터베이스에 반영하는 COMMIT과 실패했을때 COMMINT 시점으로 다시 되돌아가는 ROLLBACK이 있다.
.png)
- 활동 : 트랜잭션이 실행 중에 있는 상태, 연산들이 정상적으로 실행 중인 상태
- 실패 : 트랜잭션이 실행에 오류가 발생하여 중단된 상태
- 철회 : 트랜잭션이 비정상적으로 종료되어 ROLLBACK 연산을 수행한 상태
- 부분적 완료 : COMMIT 연산이 실행되기 직전의 상태로 아직 작성한 것들을 저장하지 않은 상태
- 완료 : 트랜잭션이 성공적으로 종료되어 COMMIT 연산을 실행한 후의 상태
COMMIT
- 완전 저장
- COMMIT이란 모든 작업들을 정상적으로 처리하겠다고 확정하는 명령어로 처리과정을 DB에 영구 저장하는 것이다.
- COMMIT을 수행하면 하나의 트랜잭션 과정을 종료하는 것이다.
- COMMIT을 수행하면 이전 데이터가 완전히 UPDATE 된다.
- 위 그림에서 첫번째 COMMIT후 그 뒤에 UPDATE 문으로 데이터를 갱신하고(3), DELETE문으로 데이터를 삭제하고(4), INSERT 문을 사용해 데이터를 삽입(5)한다.
- 만약 이 모든 과정이 오류 없이 수행되었다면 지금까지 실행한 모든 작업(3,4,5)을 데이터베이스에 영구 저장하라는 명령으로 COMMIT을 수행한다.
ROLLBACK
- ROLLBACK은 작업 중 문제가 발생되어 트랜잭션의 처리과정에서 발생한 변경사항을 취소하는 명령어 이다.
- 트랜잭션이 시작되기 이전의 상태로 되돌린다. - 즉, 마지막 COMMIT을 완료한 시점으로 다시 돌아간다. - COMMIT하여 저장한 것만 복구한다.
- 위 그림에서 ROLLBACK 명령은 마지막으로 수행한 COMMIT 명령까지만 청상처리(1,2)된 상태로 유지한다.
- 그 이후에 수행했던 모든 DML 명령어 작업(3,4,5)들을 취소시켜 이전 상태로 원상 복귀 시킨다.
- 트랜잭션은 이렇든 ALL-OR-Nothing 방식으로 DML 명령어들을 처리한다.
- ALL-OR-Nothing이란 '모든것을 수행하던지 아무것도 하지말던지'라는 의미이다.
반응형
댓글