SQLite3로 가볍게 배우는 데이터베이스 4

A12 집계함수

Person 테이블을 가지고 집계(aggregation) 함수를 사용하기.

준비하기

SELECT Height FROM Person;

집계 함수를 쓰면 컬럼명이 자동으로 함수이름(컬럼명)으로 바뀐다.

 

✔count()

count() 함수는 행 수를 센다.

SELECT count(*) FROM Person;

특정 컬림에 데이터가 있는 행을 세려면 괄호 안에 컬럼명을 쓴다.

SELECT count(Height) FROM Person;

 

✔ max()

max()는 최댓값을 구한다.

SELECT max(Height) FROM Person;

 

✔ min()

min()은 최솟값을 구한다.

SELECT min(Height) FROM Person;

 

✔sum()

숫자 컬럼에 대해 sum()을 사용하여 쉽게 합계를 낼 수 있다.

SELECT sum(Height) FROM Person;

 

✔avg()

avg()는 평균값을 구한다.

SELECT avg(Height) FROM Person;

---

 

A13 그룹화

네 사람의 키(신장)가 다음과 같다고 한다.

SELECT Name, Height FROM Person;

 

✔GROUP BY

키를 반올림한 값이 같은 사람 수를 세어보자.

SELECT round(Height), count(*)

FROM Person

GROUP BY 1;

GROUP BY 절에 사용한 1은 첫 번째 컬럼, 즉 round(Height)을 가리킨다.

 

아래 질의도 같은 결과를 얻을 수 있다.

SELECT round(Height), count(*)

FROM Person

GROUP BY round(Height);

 

✔ HAVING

두 명 이상인 경우, 즉 count(\*)가 2인 경우만 조회해보자.

HAVING으로 조건 걸기

SELECT round(Height), count(*)

FROM Person

GROUP BY round(Height)

HAVING count(*) > 1;

HAVING 절에서 사용한 숫자 1은 리터럴 값임을 유의하자.

---

 

A14 OVER

over()를 이용해 평균에 대한 편차와 분산을 구하기.

 

1. 평균

Person 테이블에서 Height의 개별 값을 나열하며, 평균값도 함께 보여줌.

SELECT

Name AS 이름,

Height AS 키,

avg(Height) over() AS 평균

FROM Person;

Height열의 평균을 계산하고 over()은 특정 윈도우를 정의하지 않았으므로, 모든 행을 기준으로 평균을 계산하게 된다.

따라서 해당 쿼리는 테이블 전체 행을 대상으로 Height 열의 평균을 구한다.

 

2. 편차

Person 테이블에서 Height의 평균값과 편차를 구해보기.

SELECT

Name As 이름,

Height AS 키,

avg(Height) over() AS 평균,

round(Height - avg(Height) over(), 3) AS 편차

FROM Person;

 

편차를 모두 더하면 0이 나온다.

SELECT sum(편차)

FORM (

SELECT

round(avg(Height) over() - height, 3) AS 편차

FROM Person

)

 

3. 분산

SELECT avg(편차*편차) AS 분산

FROM (

SELECT

round(avg(Height) over() - height, 3) AS 편차

FROM Person

)

---

세 테이블의 조인

노래, 음반, 수록곡 테이블 만들기

1. 노래SQLCREATE TABLE 노래 (ID INTEGER NOT NULL PRIMARY KEY,제목 TEXT NOT NULL);
2. 음반SQLCREATE TABLE 음반 (ID INTEGER NOT NULL PRIMARY KEY,제목 TEXT NOT NULL,연도 INTEGER);
3. 수록곡SQLCREATE TABLE 수록곡(음반ID INTEGER NOT NULL,노래ID INTEGER NOT NULL);

• 데이터 넣기

INSERT INTO 노래 VALUES

(1, '갸우뚱'),

(2, 'Shuppy Shuppy'),

(3, 'Control'),

(4, '영러브'),

(5, '한번만 안아줘'),

(6, '반짝반짝'),

(7, '기대해'),

(8, 'I Don''t Mind'),

(9, 'Easy go'),

(10, '여자대통령');

INSERT INTO 음반 VALUES

(1, 'Girl''s Day Party #1', 2010),

(2, 'Everyday', 2011),

(3, 'Expectation', 2013),

(4, '여자대통령', 2013);

INSERT INTO 수록곡 VALUES

(1, 1),

(1, 2),

(1, 3),

(2, 4),

(2, 5),

(2, 6), -- Everyday - 반짝반짝

(3, 7),

(3, 8),

(3, 9),

(3, 6), -- Expectation - 반짝반짝

(3, 5),

(4, 10);

 

✔ 데이터를 조회해보자.

SELECT 음반.제목 앨범명, 음반.연도 발매년도, 노래.제목 곡명

FROM 수록곡

INNER JOIN 음반 ON 수록곡.음반ID = 음반.ID

INNER JOIN 노래 ON 수록곡.노래ID = 노래.ID

 

조인구문을 사용하지 않고 같은 결과 얻기.

SELECT 음반.제목 앨범명, 음반.연도 발매년도, 노래.제목 곡명

FROM 노래, 음반, 수록곡

WHERE 음반.ID = 수록곡.음반ID AND 노래.ID 수록곡.노래ID;

SELECT 음반.제목 앨범명, 음반.연도 발매년도, 노래.제목 곡명

FROM 수록곡

INNER JOIN 음반 ON 수록곡.음반ID = 음반.ID

INNER JOIN 노래 ON 수록곡.노래ID = 노래.ID

WHERE 음반.연도 = 2011;

 

 

조인 참고하기

https://etloveguitar.tistory.com/123

---

 

A18 UNION ALL과 UNION

합집합을 뜻하는 UNION 구문을 사용하면 복수의 질의문을 실행해서 얻은 결과를 하나로 합친 것과 같은 결과를 얻을 수 있다.

테이블의 내용이나 이름이 똑같지 않더라도 구조가 같다면 합칠 수 있음.

UNION과 비슷하되 중복 레코드를 제거하지 않는 UNION ALL도 있다.

 

✔ UNION ALL

음반 테이블과 노래 테이블의 제목을 한번에 조회해보기.

SELECT 제목 FROM 음반

UNION ALL

SELECT 제목 FROM 노래

컬럼명이 달라도 UNION ALL을 수행하는데 문제 없음.

 

아래처럼 바꿔도 같은 결과를 얻을 수 있다.

SELECT 제목 FROM 음반

UNION ALL

SELECT 제목 AS Title FROM 노래

데이터를 다시 보면 음반들의 제목이 먼저 나오고 그 뒤에 노래 제목들이 나온 것을 알 수 있음.

'여자대통령'이라는 노래는 동명의 음반이 실렸기 때문에 결과가 두번 나옴

 

✔ UNION

UNION구문은 UNION ALL과 비슷하지만, 중복되는 레코드를 제외한 결과를 돌려준 다는 점에서 차이가 있음.

SELECT 제목 FROM 음반

UNION

SELECT 제목 FROM 노래

여자대통령이 한번만 나오는 것을 확인할 수 있다.

UNION ALL과 UNION의 또다른 차이점으로, UNION은 질의 결과가 정렬된다는 점을 들 수 있다.

 

• 중복을 제거하기 위해 정렬을 먼저 수행하기 때문이다.

정렬과 중복 제거가 굳이 필요하지 않다면 UNION ALL을 사용하는 것이 메모리와 속도 측면에서 유리할 것이다.

RDBMS는 테이블 연산에 최적화 되어 있고 조건절은 부가적인 기능인 것 같다고 함.

그러므로 테이블간의 조인으로 계산할 수 있는 경우에는 조건절을 사용하는 것 보다 조인으로 처리하는 것이 좋음.

실행시간도 조건절을 사용했을 때 보다 이득을 볼 수 있음.

 

 

참고자료 : 
위키독스 SQLite로 가볍게 배우는 데이터베이스
https://wikidocs.net/book/1530