10월 12일(화) SQL More

남이섬·2021년 10월 13일

TIL sql 데이터베이스

[그림] 음원을 판매하는 사이트의 스키마 예시

SQL 내장함수

집합연산: 레코드를 조회하고 분류한 뒤, 특정 작업을 하는 연산

GROUP BY

데이터를 조회할 때 그룹으로 묶어서 조회

customers 테이블의 모든 레코드를 조회
SELECT * FROM customers;

이 쿼리를 주(state)에 따라 그룹으로 묶어 표현
SELECT * FROM customers
GROUP BY State;

GROUP BY 쿼리로 간단하게 State에 따라 그룹화할 수 있다
쿼리의 결과를 확인하면, 데이터가 중간에 비어있는 것을 확인할 수 있다
데이터베이스에서 데이터를 불러오는 과정에서 State에 따라 그룹을 지정했지만, 그룹에 대한 작업없이 조회만 했다, 그래서 쿼리의 결과로 나타나는 데이터는 각 그룹의 첫번째 데이터만 표현된다

HAVING

HAVING은 GROUP BY 로 조회된 결과를 필터링할 수 있다

invoices 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과를 조회
SELECT CustomerId, AVG(Total)
FROM invoices
GROUP BY CustomerId
HAVING AVG(Total) > 6.00

이 쿼리는 모든 고객의 주문서에서 가격의 평균을 구한 뒤에, 그 평균이 6.00을 넘는 결과만 조회한다

이렇게 GROUP BY로 그룹을 지은 결과에 필터를 적용할 때에는 HAVING을 사용할 수 있다

HAVING은 WHERE과는 적용하는 방식이 다르다
HAVING은 그룹화한 결과에 대한 필터이고, **WHERE는 저장된 레코드를 필터링한다** 따라서 실제로 그룹화 전에 데이터를 필터해야 한다면, WHERE을 사용한다

COUNT()

COUNT 함수는 레코드의 갯수를 헤아릴 때 사용한다

모든 레코드에 대한 COUNT 함수 사용
SELECT *, COUNT(*) FROM customers
GROUP BY State;
위 커맨드를 실제로 실행하면, 각 그룹의 첫번째 레코드와 각 그룹의 레코드 갯수를 집계하여 리턴한다 다음과 같이 변경하면, 그룹으로 묶인 결과의 레코드 갯수를 확인할 수 있다

각 State에 해당하는 레코드의 갯수를 확인하는 COUNT 함수
SELECT State, COUNT(*) FROM customers
GROUP BY State;

SUM()

SUM 함수는 레코드의 합을 리턴

SUM 함수 사용
SELECT InvoiceId, SUM(UnitPrice)
FROM invoice_items
GROUP BY InvoiceId;

위 커맨드는 invoice_items라는 테이블에서 InvoiceId 필드를 기준으로 그룹하고, UnitPrice 필드 값의 합을 구한다

AVG()

AVG 함수는 레코드의 평균값을 계산하는 함수

AVG 함수 사용
SELECT TrackId, AVG(UnitPrice)
FROM invoice_items
GROUP BY TrackId;

MAX(), MIN()

MAX 함수와 MIN 함수는 각각 레코드의 최대값과 최소값을 리턴

MIN 함수 사용
SELECT CustomerId, MIN(Total)
FROM invoices
GROUP BY CustomerId

커맨드에서 MIN을 MAX로 변경하면, 각 고객이 지불한 최대 금액을 리턴

SELECT 실행 순서

데이터를 조회하는 SELECT 문은 정해진 순서대로 동작

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

SELCT 문의 실행 순서
SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

위 쿼리문의 실행 순서