CMU Database Fall 2022 를 듣고 정리한 글입니다.

---

SQL

SQL은 아래 세 개의 combination으로 생각할 수 있습니다.

• Data Manipulation Language (DML): 데이터를 read, write, modify, retrieve 할 수 있음
• Data Definition Language (DDL): 데이터가 어떻게 생겼는지 정의할 수 있음
• Data Control Language (DCL): 누가 데이터를 읽고 쓸 수 있는지 결정할 수 있음

지난 강의에서 다룬 Relational Algebra는 Set Theory에 기반했는데, SQL은 Bag에 기반. 여기서 Set과 Bag의 차이는 Bag은 Set과 달리 중복을 허용한다는 것.

중복을 허용하면 이후에 다룰 여러 연산에서 효율적이다.

오늘 다를 데이터베이스는 이렇게 생김.

Aggregates

Aggregator는 Tuple의 bag으로부터 하나의 값을 반환하는 함수이다.

ex) AVG, MIN, MAX, SUM, COUNT ...

여기서 Aggregate function은 거의 항상 SELECT output list에서 사용한다. 결국 이 함수들은 데이터를 요약하는 역할을 하기 때문.

SELECT COUNT(login) as CNT

여러개 한번에 사용할수도 있다.

SELECT CNT(sid), AVG(gpa)
FROM student
WHERE login LIKE '%@cs'

여기서 % 는 와일드카드로 아무 character를 의미

COUNT, SUM, AVG 는 DISTINCT 를 지원한다.

SELECT COUNT(DISTINCT login)
FROM STUDENT WHERE login LIKE '%cs'

With GROUP BY

Aggregate function과 함께 Aggregate의 대상이 아닌 column을 지정할 수 없다.

GPA는 전체 tuple에 대해 계산된 값인데, 과연 어떤 course의 id를 가져와야 할 지 알 수가 없다. 아마 의도는, 전체 데이터셋을 부분부분으로 나눈 다음 각 부분에 대해서 궁금한 값을 구하는 것일 것. 따라서 GROUP BY를 사용하여 특정 기준으로 row를 나누어 준다.

SELECT AVG(s.gpa), e.cid
FROM enrolled as e JOIN student as s
ON e.sid = s.sid
GROUP BY e.cid

그러면 나뉜 그룹별로 Aggregation이 진행된다.

이렇게 GROUP BY 와 Aggregate function을 같이 사용할 때에는, SELECT 문에 사용한 값들이 GROUP BY에도 포함되어 있어야 한다.

SELECT AVG(s.gpa), e.cid, s.name
FROM enrolled AS e JOIN student as s
ON e.sid = s.sid
GROUP BY e.cid, s.name

이러면 course id + student name 별로 group by를 수행하므로 aggregate를 하는 큰 의미는 없겠지만 쿼리는 잘 돌아갈 것이다.

HAVING

SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled as e JOIN student as s
WHERE e.sid = s.sid
AND avg_gpa > 3.9
GROUP BY e.cid

HAVING 은 GROUP BY 에서 WHERE 처럼 작동한다. WHERE을 바로 사용할 수 없는 이유는 쿼리의 실행 순서 상, WHERE 절을 처리하는 시점에 Aggregation 된 결과를 가져올 수 없기 때문이다.

SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled as e JOIN student as s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING AVG(s.gpa) > 3.9

String Operations

SQL 구현마다 casing과 사용하는 quote가 다르다.

LIKE

LIKE 는 string matching 에 사용된다.

• %: empty string을 포함한 모든 substring
• _: 임의의 문자 하나

SELECT * FROM enrolled as e
WHERE e.cid LIKE '15-%'

LIKE 이외에도 SUBSTRING, UPPER, CONCAT 등 다양한 string function이 있다. 이 함수들은 DBMS 마다 존재..

Output Redirection

쿼리 결과를 다른 (정의되지 않은) 테이블에 저장할 수 있다.

CREATE TABLE CourseIds AS
SELECT DINCT cid
FROM enrolled

DBMS마다 문법은 조금씩 다르다. 위는 MySQL.

또, 쿼리 결과를 이미 정의된 테이블에 새 row로 삽입할 수도 있다. 당연히 column은 동일해야 한다.

INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled);

Output Control

ORDER BY

ORDER BY 는 column을 ASC 혹은 DESC 로 정렬한다.

SELECT sid, grade FROM enrolled
WHERE cid = '15-721'
ORDER BY grade

column index를 넣거나, 여러 개를 동시에 적용할 수도 있다.

ORDER BY 1

ORDER BY grade DESC, 1 ASC

LIMIT

LIMIT 은 아웃풋의 row 개수를 제한한다. OFFSET 으로 offset 을 지정할 수도 있다.

SELECT sid, name FROM student
WHERE login LIKE '%@cs'
LIMIT 20 OFFSET 10

Nested Queries

한 쿼리가 다른 쿼리를 포함하는 것을 Nested Query라고 한다. 거의 모든 곳에 Inner Query가 존재할 수 있으며 최적화하기 상당히 어렵다고 한다.

SELECT name FROM student
WHERE sid IN (SELECT sid FROM enrolled)

'15-445'를 듣는 모든 학생들의 이름을 가져온다고 할 때,

SELECT name FROM student
WHERE ...

... 에는 '15-445를 듣는 학생들' 이 들어가야 한다. 이를 sql로 옮기면

SELECT sid from enrolled
WHERE cid = '15-445'

여기서 sid 를 select 했으므로,

SELECT name FROM student
WHERE sid IN (
  SELECT sid FROM enrolled
  WHERE cid = '15-445'
)

여기서 IN Operator가 쓰였는데, 비슷한 용례를 보이는 operator는 다음이 있다.

1. ALL: sub-query의 모든 row가 expression을 만족
2. IN: ANY: 적어도 한개의 row가 expression을 만족
3. EXISTS: 적어도 한 개의 row가 return 됨

다른 예시, 현재 수업을 듣고 있는 학생 중 가장 큰 sid 를 가진 학생의 이름과 sid를 가져와 보자.

SELECT name, sid FROM student
WHERE ...

여기서 ... 에 들어갈 표현은 'enrolled 의 sid 중 가장 큰 sid` 이다.

SELECT name, sid FROM student
WHERE sid IN (
	SELECT MAX(sid) FROM enrolled
)

혹은 역순으로 정렬한 다음 한개만 가져와도 된다.

SELECT name, sid FROM student
WHERE sid IN (
	SELECT sid FROM enrolled
    ORDER BY sid DESC LIMIT 1
)

또 다른 예시로, 아무도 수강하지 않는 모든 수업을 가져와 보자.

SELECT * FROM course
WHERE ...

... 에 들어갈 말은 어떤 학생도 수강하지 않는 = enrolled table에 cid가 없는 이므로

SELECT * FROM course
WHERE cid NOT EXISTS (
	SELECT * FROM enrolled
    WHERE course.cid = enrolled.cid
)

Window Function

Window Function은 관계된 몇 개의 tuple에 걸쳐서 'sliding' calculation을 한다. Aggregation과 비슷하지만, 결과값이 한개가 아니다.

문법은 아래와 같다.

SELECT ... FUNC(...) OVER (...)
FROM tablename

Function에는 앞에서 다뤘던 Aggregation Function
뿐 아니라, window functions들이 올 수 있다.

• ROW_NUMBER: 각 row가 몇 번째인지
• RANK: 특정 column을 기준으로의 order position
• FIRST_VALUE, LAST_VALUE, ...

SELECT *, ROW_NUMBER() OVER () AS row_num
FROM enrolled

이 때 OVER 에는 window function을 계산할 때 tuple을 어떻게 grouping 할 것인지를 지정할 수 있다.

SELECT cid, sid, ROW_NUMBER() OVER (PARTITIONED BY cid)
FROM enrolled
ORDER BY cid

각 코스에서 2번째로 성적이 높은 학생들을 찾는 예시를 생각해 보자. RANK() 를 이용하면 각 그룹에서 2번째 요소를 쉽게 찾을 수 있으므로

SELECT * FROM (
  SELECT *, RANK() OVER (PARTITIONED BY cid ORDER BY grade ASC) AS rank
) AS ranking
WHERE ranking.rank = 2

CTE (Common Table Expressions)

CTE는 복잡한 쿼리를 보조하기 위해 사용되며, 마치 쿼리에서 사용할 임시 테이블을 만드는 것 처럼 이해할 수 있다.

WITH cteName AS (
 SELECT 1
 )
 
 SELECT * FROM cteName

AS 커맨드를 사용하면 column에 이름을 지정할 수 있다.

WITH cteName (col1, col2) AS (
	SELECT 1, 2
)
SELECT col1 + col2 FROM cteName

적어도 한 강의를 수강하는 학생 중 가장 큰 id 값을 가진 학생의 이름을 가져오는 예시를 생각해 보자.

WITH cteSource (maxId) AS (
  SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId

Recursive CTE

WITH RECURSIVE 는 Recursive CTE 를 만들기 위해 사용된다. Recursive CTE는 정의에서 자기 자신을 참조하는 CTE로, tree-like data structure에서 일반적으로 사용된다.

Recursive CTE 의 구조는 Base Result set을 정의하는 Anchor Member와 CTE 자신을 참조해서 기존의 결과로부터 다음 결과를 만들어내는 Recursive member 로 구분된다.

1에서 10까지의 순서를 차례로 만드는 CTE는 아래와 같다.

WITH RECURSIVE cteSource (counter) AS (
  SELECT 1
  UNION ALL
  SELECT counter + 1 FROM cteSource
  WHERE counter < 10
)

여기서 anchor member는 SELECT 1 로, CTE를 초깃값 1로 초기화한다.

recursive member는 SELECT counter + 1 FROM cteSource WHERE counter < 10 으로 이전 counter 값을 가져와서 1을 더한다. 10이 되면 멈춘다.

그리고 이 둘을 UNION ALL로 결합한다.