[SQL] CASE 문법과 IN 서브쿼리 문법 + 데이터베이스 보호 방법에 대한 개괄(SQLite 기반)

/* 칸 아카데미의 SQL 강의를 학습하고 정리한 내용입니다.

Intro to SQL: Querying and managing data | Khan Academy

Learn how to use SQL to store, query, and manipulate data. SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations.

https://www.khanacademy.org/computing/computer-programming/sql

*/

• 오늘의 개발 용어: PoC (Proof of Concept)

  : 새로운 프로제트가 실현 가능성이 있는지 검증하는 과정

  ex) 의약업계에서: 새롭게 개발한 신약의 유효성을 검증

  ex) 영화업계에서: 스토리가 CG로 재연가능한지 검증하는 과정

  ex) 로봇 자동화 업계에서: 설계 및 이론상으로 가능하다고 판단되는 자동화 공정에 대해서, 본격적으로 현장에 설치하기 전에 실험실에서 검증되는 과정. 로봇이 사용되는 한 지점을 구현해보고 시험해본다.

  ex) 데이터 분야에서는: 문제점 개선을 위한 성능 테스트(가능성 확인 및 개선점 도출)를 PoC라고 일컫기도 한다.

  ⇒ 한 마디로, 어떤 새롭고 흥미롭고 혁신적(이라는) 기술을 우리 회사에 도입할 수 있을지 알아보고 싶어서, “이렇게 동작하고 이런 것들이 처리될 수 있다” 라고 팀의 상황에 맞게 세팅해보고 논의하는 것. 이라고 볼 수 있겠다.

  (참고: https://www.myrobotsolution.com/contents/insight/81, https://pt.slideshare.net/LeeIGoo/ms-poc)

---

Select with Case

이렇게 생긴 테이블이 있고:

exercise_logs

id	type	minutes	calories	heart_rate
1	biking	30	100	110
2	biking	10	30	105
3	dancing	15	200	120
4	dancing	15	165	120
5	tree climbing	30	70	90
6	tree climbing	25	72	80
7	rowing	30	70	90
8	hiking	60	80	85

“심박수 - 자기 나이”가 안전한 최대 심박수라고 할 때, 심박수가 안전한지 범위를 나타내는 컬럼을 만들어 다음과 같은 결과가 나오도록 조회하기:

type	heart_rate	hr_zone
biking	110	within target
biking	105	within target
dancing	120	within target
dancing	120	within target
tree climbing	90	below target
tree climbing	80	below target
rowing	90	below target
hiking	85	below target

	-- hr_zone이라는 컬럼을 만들고
				-- 최대 심박수를 초과한 운동은 "above max"로 표기
				-- 최대 심박수의 50~100% 라면 "above target"으로 표기
				-- 최대 심박수의 20~50% 라면 "within target"으로 표기
				-- 나머지 경우엔 "below target"으로 표기하기

SELECT type, heart_rate,
    CASE 
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs;

⇒ 프로그래밍 문법의 if - else if - else 문법과 구조가 같다.

이를 바탕으로 각 운동 기록들이 어느 ‘심박수 범위’에 속하는지 통틀어 보기:

COUNT(*)	hr_zone
4	below target
4	within target

SELECT COUNT(*),
		CASE 
				WHEN heart_rate > 220 - 30 THEN "above max"
				WHEN heart_rate > ROUND(0.90 * (220 - 30)) THEN "above target"
				WHEN heart_rate > ROUND(0.50 * (220 - 30)) THEN "within target"
				ELSE "below target"
		END as "hr_zone"
FROM exercise_logs
GROUP BY hr_zone;

In with Subqueries

다음과 같은 2개 테이블이 있을 때,

exercise_logs

id	type	minutes	calories	heart_rate
1	biking	30	100	110
2	biking	10	30	105
3	dancing	15	200	120
4	tree climbing	30	70	90
5	tree climbing	25	72	80
6	rowing	30	70	90
7	hiking	60	80	85

drs_favorites

id	type	reason
1	biking	Improves endurance and flexibility.
2	hiking	Increases cardiovascular health.

보통 여러 조건 중 하나(만이라도)를 만족하는 행을 조회할 때 여러 OR을 합쳐서 하나의 IN 문으로 표현할 수 있다:

-- 여러 OR
SELECT * 
FROM exercise_logs 
WHERE type = "biking" 
		OR type = "hiking" 
		OR type = "tree climbing" 
		OR type = "rowing";
	
-- 하나의 IN 문으로 표현
SELECT * 
FROM exercise_logs 
WHERE type IN ("biking", "hiking", "tree climbing", "rowing");

만약 “의사가 추천하는 타입의 운동”만 exercise_logs에서 뽑아보고 싶다면 어떻게 할까? ‘의사가 추천하는’ drs_favorites 테이블도 계속 변동될 수 있다고 할 때 매번 drs_favorites에 존재하는 타입들만 정확히 가져오도록 말이다. 정답은 IN의 괄호 안에 또다른 쿼리문(서브쿼리)을 집어넣어 주는 것이다.

SELECT *
FROM exercise_logs 
WHERE type IN 
		(SELECT type FROM drs_favorites);

그러면 drs_favorites에 존재하는 biking과 hiking 타입에 해당하는 결과만 exercise_logs에서 뽑아오게 된다.

id	type	minutes	calories	heart_rate
1	biking	30	100	110
2	biking	10	30	105
7	hiking	60	80	85

‘의사가 추천하는 심혈관에 좋은 운동’을 exercise_logs 기록에서 검색할 수도 있다:

SELECT *
FROM exercise_logs
WHERE type IN
		(SELECT type 
		FROM drs_favorites 
		WHERE reason LIKE "%cardiovascular%");

id	type	minutes	calories	heart_rate
7	hiking	60	80	85

Update

UPDATE diary_logs SET content = "I had a horible fight with OhNoesGuy" WHERE id = 1;

조건절을 걸어주지 않으면 모든 행에 대해 업데이트가 일어나버린다.

Delete

DELETE FROM diary_logs WHERE id = 1;

많은 SQL 엔진이 실제로 해당 데이터를 지우기보다 “deleted=true”나 “deleted_at=2023-07-11”같은 컬럼을 truthy로 만드는 식으로 처리한다.

Table Update

ALTER TABLE diary_logs ADD emotion TEXT;

이후로 작성되는 데이터에는 emotion 컬럼을 포함하여 작성해 넣어야하고, 이전에 작성된 행들에 대해서는 emotion 컬럼에 Null이 일괄로 채워진 상태가 됨.

ALTER TABLE diary_logs ADD emotion TEXT DEFAULT "unknown";

Null 대신 다른 값을 넣어두고 싶다면 위와 같이 하면 된다.

결과 예시)

id	user_id	date	content	emotion
1	1	2015-04-02	OhNoesGuy and I made up and now we're best friends forever and we celebrated with a tub of ice cream.	unknown
2	1	2015-04-03	We went to Disneyland!	happy

Table Delete

DROP TABLE diary_logs;

---

내 데이터베이스 더 안전하게 만들기

1. 나쁜 update/delete을 피하자

• UPDATE를 실행하기 전에 똑같은 조건으로 SELECT을 실행해서 내가 수정하기 원하는 대상이 정확히 선택되는지 확인하기.

  예를 들어

  UPDATE users SET deleted = true WHERE id = 1;

  위와 같은 수정을 진행하기 전에 아래와 같이 확인해본다:

  SELECT id, deleted FROM users WHERE id = 1;

• UPDATE이나 DELETE을 실행할 때 LIMIT을 걸어주어 실수로 과도한 행을 수정하지 않도록 방지하기.

  UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

  DELETE users WHERE id = 1 LIMIT 1;

2. 트랜잭션을 활용하자

• 쿼리 여럿을 트랜잭션으로 묶어서 실행함으로써 중간에 실패한 작업이 데이터베이스에 반영되지 못하도록 하기.

  ⇒ 데이터베이스에 수정을 가하는 쿼리문(CREATE, UPDATE, INSERT, DELETE)을 실행시키면 자동으로 “트랜잭션”이라는 과정이 촉발된다. 트랜잭션은 하나의 논리적인 작업 조각으로 여겨지는 일련의 작업들을 말하는데, 데이터베이스를 조작할 때 한 트랜잭션을 신뢰할 만 하게 마치려면 ACID 원칙을 따라야 한다.

  하나의 ‘수정’ 쿼리문은 기본적으로 하나의 트랜잭션이지만, 여러 개를 합쳐 더 큰 트랜잭션을 만들 수도 있다. 예를 들어 첫 번째 UPDATE이 성공적으로 완수된 이후에만 두 번째 UPDATE을 하고 싶다고 할 때 이 둘을 한 트랜잭션으로 묶어서 명령할 수 있다. 원하는 쿼리문들을 BEGIN TRANSACTION과 COMMIT 명령어 사이에 넣으면 된다:

  BEGIN TRANSACTION;
  UPDATE people SET husband = "Winston" WHERE user_id = 1;
  UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
  COMMIT;

  도중에 에러가 발생하면 데이터베이스는 이 트랜잭션을 롤백시킨다. 그러면 그 때까지 성공적으로 완수됐던 중간과정을 포함하여 전체 트랜잭션이 없던 일이 된다.

• 다른 작업과 실행 순서가 섞이면 안되는 쿼리 여럿을 트랜잭션으로 묶어서 실행함으로써 작업이 끝날 때까지 순서와 대상 데이터 고정시켜두기.

  ⇒ 일련의 과정이 완료되기까지 다른 작업이 끼어들어 대상 데이터를 변경시키지 못하도록 만들고 싶을 때도 트랜잭션을 이용한다. 내 작업에 필요한 쿼리문(들)이 다른 사용자의 쿼리문과 동시에 실행됐을 때 이상한 결과를 낼 것 같은지를 파악하고, 만약 그렇다면 트랜잭션으로 묶어 실행해주도록 한다. 예를 들어 1번 유저가 얻은 새 배지를 기록하고 그 유저의 최근 활동을 새 배지를 얻었다는 내용으로 업데이트하려고 하는데,

  INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
  UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

  또 다른 데이터베이스 사용자가 곧바로 1번 유저에게 (또다른)새로운 배지를 추가하는 작업을 가할 수 있다:

  INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
  UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;

  그러면 이 두 작업이 뒤엉켜 다음과 같은 순서로 실행될 수가 있고:

  INSERT INTO user_badges VALUES (1, "SQL Master");
  INSERT INTO user_badges VALUES (1, "Great Listener");
  UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
  UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

  이 때 실제로 최신 획득 배지는 “Great Listener”인데도 불구하고 user 테이블의 최근 활동이 “SQL Master 배지 획득” 기록으로 남아있게 된다. 이런 상황이 바로 내 작업에 필요한 쿼리문들이 다른 사용자의 쿼리문과 동시에 실행됐을 때 이상한 결과를 내게 되는 경우이다. (내) 작업을 트랜잭션으로 감싸주면 실행 순서가 섞이지 않고 도중에 대상 데이터가 변경되지도 못하도록 할 수 있다:

  BEGIN TRANSACTION;
  INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
  UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
  COMMIT;

3. 백업을 만들자

위와 같은 방법들을 잘 지켜도 실수는 일어나게 마련이다. 데이터베이스의 백업을 만들자. 많은 회사들이 보유한 데이터베이스의 크기와 여유 공간에 따라 시간별, 일별, 주별 백업을 진행하고 있다. 실수가 발생하면, 이전(몇 시간 전 백업된 과거의) 데이터베이스에서 해당 테이블을 임포트하여 데이터의 영구 손실을 방지할 수 있다. 백업 주기만큼의 최신 데이터를 잃게 되겠지만, 완전히 잃는 것보다는 낫다.

4. 복사본을 유지하자

최신 데이터베이스의 복사본을 여러 장소에 나누어 유지하자. 같은 데이터베이스를 여럿 만들고 실시간으로 전송되는 데이터가 그 모두에 작성되도록 하는 방법이다. 백업이 데이터 관리와 복구에 어느 정도의 latency를 허용하는 개념이라면, 복사본을 유지하는 방법은 ‘즉각적이고 누락 없는’ 읽기와 쓰기의 유지를 목적으로 한다. 예를 들어 번개로 인해 복사본 중 한 데이터베이스가 다운됐을 때, 처방을 위해 환자의 알레르기 정보를 즉각 조회해야 하는 의사의 경우 다른 복사본 데이터베이스를 이용할 수 있다. 백업 시스템만 있는 경우라면 엔지니어가 백업에서 데이터를 복구하기까지 기다려야 했을 것이다.

그래서 데이터베이스 복사본을 만들고 유지하는 데 대한 비용과 속도 감소 측면(한 개의 데이터를 여러 데이터베이스에 넣어줘야하므로)을 감안하고도 복사본 유지 정책이 필요한지 비용과 효용을 잘 따져서 결정해야 한다.

5. 알맞은 사용자에게 알맞은 권한(만)을 부여하자

데이터베이스 시스템들은 보통 데이터베이스에 대해 ‘사용자(users)’와 ‘접근 권한(privileges)’을 책정할 수 있는 기능을 제공한다. 여러 사람이 한 데이터베이스를 사용하게 되는 경우라면 처음부터 어떤 사용자가 어떤 권한을 가질지 정해놓는 것이 중요하다. 보통은 백엔드 엔지니어와 같은 아주 소수의 사람들에게만 전체 접근 권한을 허용하는 것이 좋다.

‘super_admin’이라는 사용자에게 users 테이블 전체 권한 부여하기:

GRANT FULL ON TABLE users TO super_admin;

‘anaylizing_user’ 사용자에게 조회(SELECT) 권한만 부여하기:

GRANT SELECT ON TABLE users TO analyzing_user;

고객의 이름과 이메일 같은 신상 정보가 포함되는 테이블은 단순한 조회(SELECT) 권한을 주는 것조차 조심스럽다. 그래서 그러한 개인 정보를 들어낸 버전(anonymized version)으로 데이터베이스를 따로 만들어 접근권한을 주기도 한다.

Uploaded by N2T