[데이터 엔지니어링] SQL (02)

트랜잭션

정보의 교환이나 DB갱신등 일련의 작업에 대한 연속처리단위

1. 즉, DB상태를 변화시키는 작업의 모음이다.

2. 주로 상태를 변화시키는 DML과 같이 사용된다. (상태가 변화된 것은 데이터베이스의 내용이 변화됨을 의미)

3. 하나의 특정작업으로 시작해 묶인 모든 작업을 완료해야 끝난다. (하나라도 실패하면 트랜잭션도 실패한다.)

4. COMMIT
   1) 트랜잭션은 실행한 뒤 확정신호를 보내야 메모리에 저장된 SQL 구문이 데이터베이스에 반영된다.
   2) 확정 신호를 Commit이라고한다.
   3) 지금가지 테이블이 생성되고 "INSERT, UPDATE, DELETE"되는 것은 AUTO-COMMIT이 실행되고 있기 때문이다.
   4) 원래는 과정을 진행한후 COMMIT; 를 작성해주어야한다.

5. ROLLBACK
   1) Commit과 반대되는 개념, 트랜잭션 수행 중 지금까지 수행한 내용을 취소한다.
   2) 깃에서도 Reset과 Revert으로 되돌리기를 할 수 있다.

ACID

Atomicity, Consistency, Isolation, Durability 를 의미한다. (트랜잭션의 안전성을 보장하기 위해 필요하다.)

1. Atomicity (원자성)
   1)하나의 트랜잭션을 구성하는 작업들은 전부 성공하거나 전부 실패해야한다.

2. Consistency (일관성)
   1) 트랜잭션 이전과 이후의 데이터베이스의 상태는 이전과 같이 유효해야한다.
   2) 예를 들어 id가 꼭있어야한다와 같은 제약이 있을 시 일관되게 id가 있어야한다.

3. Isolation (고립성)
   1) 하나의 트랜잭션이 다른 트랜잭션과 독립되어야 한다.
   2) 각 트랜잭션이 다른 연산을 방해하면 안된다.
   3) 동시에 실행되더라도 연속으로 실행될때의 데이터베이스 상태 동일해야한다.

4. Durability (지속성)
   1) 하나의 트랜잭션이 성공적으로 수행된다면 로그가 남는다.
   2) 오류가 발생해도 기록은 영구적이다.

5. RDB를 제작할것이 아니면 ACID를 고려하지 않아도 괜찮다.

6. RDB는 강한 규칙,제약을 가진다.

SQL 문법 +

레코드들을 조회하고 분류한 뒤 특정 작업을 하는 집합연산의 과정을 정리했다.

1. GROUP BY
   1) 데이터를 묶어서 조회하게 해준다.
   2) Source - Split - Apply - Combine의 순서대로 진행된다.
   3) 다음 코드를 사용할시 각 그룹의 첫번째 레코드가 조회된다.

SELECT * FROM customers
GROUP BY State
ORDER BY CustomerId;

2. HAVING
   1) GROUP BY 로 조회된 결과에 대한 필터다.
   2) HAVING 은 그룹화한 결과에 대한 필터이고 WHERE 는 그룹화하기 전에 조회되는 레코드를 필터링한다.

3. COUNT()
   1) 몇 개인지 값을 리턴한다.

4. SUM()
   1) 조회된 값들에 대한 합을 구해 리턴

5. AVG()
   1) 평균값을 구해주는 함수

6. MAX(), MIN()
   1) 최대값, 최소값을 구하는 함수
   2) 위의 계산함수들은 GROUP BY가 아니더라도 SELECT문과 같은 부분에서 이용가능하다.

7. SELECT 실행 순서
   1) FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY 순으로 이루어진다.

8. CASE 사용하기
   1) 프로그래밍의 if문과 같은 기능이다.
   2) 아래와 같은 형태로 이루어진다.

SELECT CASE
WHEN CustomerId <= 25 THEN 'GROUP 1'
WHEN CustomerId <= 50 THEN 'GROUP 2'
ELSE 'GROUP 3'
END
FROM customers

9. SUBQUERY
   1) 쿼리문을 작성할 때 다른 쿼리문을 포함한다.
   2) JOIN과 유사하다 (두개의 테이블을 결합한다는 점에서)
   3) 소괄호로 감싸져 있으며, 개별값이나 레코드 리스트를 돌려준다.
   4) 서브쿼리의 결과를 하나의 칼럼으로 활용가능하다.
   5) 아래와 같은 형태로 이루어진다.

SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6

10. IN, NOT IN
    1) 특정값이 서브쿼리에 있는지 확인한다.
    2) 아래와 같은 형태로 이루어진다.

SELECT *
FROM customers
WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)

11. FROM
    1) 서브쿼리는 FROM에서도 사용가능하다.
    2) 아래와 같은 형태로 이루어진다.
    3) 즉, 서브쿼리는 SELECT WHERE FROM 에서 다 사용가능하다.

SELECT *
FROM (
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)

조인 VS 서브쿼리

동일한 결과를 얻을 수 있지만 상황별로 효율이 다르다.

1. 서브쿼리 : 복잡한 SQL쿼리문에 많이 사용된다. 보통 외부쿼리 내에 내부쿼리가 있는 구조를 가진다.

2. 조인 : 여러개의 쿼리를 필요로하지 않는다.

3. 서브쿼리를 조인으로 대체할 수 없는 경우
   1) GROUP BY 를 사용한 서브 쿼리가 FROM 절에 들어 있을 때
   2) 집계된 값을 반환하는 서브쿼리가 WHERE 절에 있을 때
   3) 서브쿼리가 ALL 연산자 안에 들어있을 때

SQL VS Pandas

1. 판다스의 concat
   1) SQL에서는 합치는 두 데이터프레임의 열이 같아야한다. 다를 경우 에러발생
   2) UNION ALL을 이용하면 중복을 제거하지 않고 concat한다.

2. 판다스의 drop_duplicates()
   1) SQL에서는 UNION ALL 이 아닌 UNION 을 이용하면 된다.