SQL) 산술연산자, 집계 함수, AS(Alias), BETWEEN

 이 게시글은 이틀 동안 DataCamp의 Introduction to SQL 코스의 내용을 제가 기억할 수 있도록 정리한 것으로, 아주 기초적인 내용으로 구성되어 있습니다.

---

SQL에서의 산술 연산

 SQL에서 기본적인 연산을 위해서는 산술연산자라고 불리우는 기호를 사용할 수 있다. 여기에는 '+', '-', '*', '/' 가 해당된다. 이 때 정수로 나누면 정수값이 반환되며, 실수로 나누면 실수값이 나오므로, 보다 정확한 값(실수)을 구해야할 때는 이를 유의해야 한다.

SELECT (4 * 3); --> 반환값 : 12
SELECT (4 / 3); --> 반환값 : 1
SELECT (4.0 / 3.0); --> 반환값 : 1.33333333

* 연산식에 실수가 존재할 경우에는 그 순서에 유의해야 한다. 예를 들어 '45 / 10 * 100.0'의 경우 45 / 10 가 '4'의 결과를 반환하고, 결국 400.0의 값이 반환된다. 정확한 값을 위해서는 45 * 100.0 / 10 의 순서로 계산하는 것이 옳다. (결과 : 450.0)

 

ex. 전체 사람 수 대비 살아있는 사람의 비율을 구하되, 결과값 컬럼명을 percentage_dead로 출력하기

SELECT 
(SELECT COUNT(*) FROM people WHERE deathdate IS NOT NULL) * 100.0 / COUNT(*) 
AS percentage_dead
FROM people;

 

ex. 가장 최신 영화와 가장 오래된 영화 사이의 수를 구하되, 결과값 컬럼명을 difference로 출력하기

SELECT (MAX(release_year) - MIN(release_year)) AS difference 
FROM films;

 

ex. films 테이블이 다루는 기간(10년 단위)을 구하고, 결과값 컬럼명을 number_of_decades로 출력하기

SELECT (MAX(release_year) - MIN(release_year)) / 10.0 AS number_of_decades 
FROM films;

---

집계 함수

 SQL에서는 데이터 베이스의 데이터 계산을 위해 몇 가지 집계 함수를 제공한다. 집계 함수의 종류에는 대표적으로 SUM, AVG, MIN 등이 있다.

SUM 함수

 해당 조건의 열들의 숫자 값들을 더한 결과를 반환한다. 아래의 쿼리는 모든 영화들의 예산을 더한 값을 반환한다.

SELECT SUM(budget) 
FROM films;

 

AVG 함수

 해당 조건의 열들의 숫자 값들을 더해 그 평균값을 결과로 반환한다. 아래의 쿼리는 모든 영화의 평균 예산을 반환한다.

SELECT AVG(budget) 
FROM films;

 

MAX 함수

 해당 조건의 열들의 숫자 값 중 가장 큰 값을 결과로 반환한다. 아래의 쿼리는 모든 영화 중에서 가장 예산이 큰 영화의 예산을 반환한다.

SELECT MAX(budget) 
FROM films;

 

MIN 함수

 해당 조건의 열들의 숫자 값 중 가장 작은 값을 결과로 반환한다. 아래의 쿼리는 모든 영화 중에서 가장 예산이 적은 영화의 예산을 반환한다.

SELECT MIN(budget) 
FROM films;

집계 함수 + WHERE절

 집계 함수는 WHERE절과 함께 사용하면서 더 구체적이고 명확한 데이터를 얻을 수 있다. 예를 들어 아래의 쿼리를 통해 2010년 또는 그 이후에 개봉한 영화들의 예산의 합을 구할 수 있다.

SELECT SUM(budget) 
FROM films 
WHERE release_year>=2010;

 

 ex. 제목이 'A'로 시작하는 영화들의 평균 수익 구하기

SELECT AVG(gross) 
FROM films 
WHERE title LIKE 'A%';

 

 ex. 2000년, 2012년에 개봉된 영화들 중 수익이 가장 높은 영화의 수익 구하기

SELECT MAX(gross) 
FROM films 
WHERE release_year BETWEEN 2000 AND 2012;

 

---

Alias

 SELECT문을 통해 데이터 조회시 반환되는 컬럼명을 변경할 수도 있고, 연산 등을 통해 생성한 값에 임시로 컬럼명을 부여할 수도 있다. 이 때 사용되는 것이 Alias이다. 코드의 가독성을 향상시킬뿐만 아니라 SQL 성능 향상에도 영향을 미친다.

*옵티마이저(Optimizer) : DBMS에서 쿼리를 가장 빠르고 효율적으로 실행시킬 최적(최저 비용)의 경로를 생성해내는 내부 엔진. 규칙기반 옵티마이저, 비용기반 옵티마이저로 나뉜다.

*AS 키워드를 사용함으로써 같은 집계 함수를 사용해도 다른 컬럼명을 가지도록 할 수 있다. 예를 들어 MAX(budget), MAX(duration) 두 가지를 조회하고자 할 때 결과 테이블은 MAX라는 컬럼명을 두 개 가진다. 이 때는 어떤 값인지 식별하기 어려워진다. 다음과 같이 쿼리를 작성하면 두 개의 컬럼은 같은 집계 함수를 쓰지만 각각 다른 컬럼명으로 반환된다.

SELECT MAX(budget) AS MAX_BUDGET, MAX(duration) AS MAX_DURATION 
FROM films;

 

ex. 모든 영화들의 이름과 순이익(총 수익- 예산)을 조회하되, 순이익의 결과값 컬럼명을 net_profit으로 출력하기

SELECT title, (gross - budget) AS net_profit 
FROM films;

 

ex. 모든 영화들의 제목과 상영시간을 조회하되, 상영시간(분)을 시간 단위로 나타내고 결과값 컬럼명을 duration_hours로 출력하기

SELECT title, (duration/60.0) AS duration_hours 
FROM films;

 

ex. 모든 영화들의 평균 상영시간을 조회하되, 상영시간(분)을 시간 단위로 나타내고, 그 결과값 컬럼명을 avg_duration_hour로 출력하기

SELECT AVG(duration) / 60.0 AS avg_duration_hours 
FROM films;

 

---

BETWEEN

 특정 집합에서 어떠한 컬럼의 값이 특정 범위안에 들어가는 집합을 출력하기 위한 연산자이다. 아래와 같은 형식으로 사용한다. (Fastcampus의 SQL 강의 내용)

SELECT [컬럼 리스트]
FROM [테이블명]
WHERE [컬럼명] BETWEEN 값1 AND 값2;

 

 이는 아래의 쿼리와 같은 의미가 된다.

SELECT [컬럼 리스트]
FROM [테이블명]
WHERE [컬럼명] >= 값1 AND [컬럼명] <= 값2;

 

 BETWEEN 연산자를 사용하면 범위 양 끝의 값을 모두 포함한다. TIMESTAMP 타입의 데이터의 범위를 지정할 때는 다음 두 가지 방법 중 하나를 택해 범위를 올바르게 지정할 수 있다.

- TIMESTAMP 타입의 데이터 범위를 지정할 경우, 범위 끝 값에 시, 분, 초를 함께 명시해준다.

-  DATE 타입으로 변환해 사용한다.

ex. rental_date(TIMESTAMP 타입)가 2005년 5월 25일부터 2005년 5월 27일까지인 rental 기록을 조회하고자 할 때, 아래와 같은 쿼리는 26일까지만 조회한다.

SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-25' AND '2005-05-27'
ORDER BY rental_date;

 

 이를 해결하기 위해 시, 분, 초를 명시해주거나 DATE 타입으로 변환하여 28일이 되기 전의 모든 값을 조회한다.

 -- 시, 분, 초 명시
SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-25' AND '2005-05-27 23:59:59'
ORDER BY rental_date;

-- DATE 타입으로 변경
	-- (1) CAST함수 사용
SELECT *
FROM rental
WHERE CAST(rental_date AS DATE) BETWEEN '2005-05-25' AND '2005-05-27'
ORDER BY rental_date ;

	-- (2) '::' 사용(PostgreSQL 문법)
SELECT *
FROM rental
WHERE rental_date::DATE BETWEEN '2005-05-25' AND '2005-05-27'
ORDER BY rental_date;

	-- (3) 문자열로 변경
SELECT *
FROM rental
WHERE TO_CHAR(rental_date, 'YYYY-MM-DD') BETWEEN '2005-05-25' AND '2005-05-27'
ORDER BY rental_date;

 

 특정 집합에서 어떠한 컬럼의 값이 특정 범위안에 들어가지 않는 집합을 출력하기 위해 NOT 연산자와 함께 사용할 수 있다. 아래 쿼리는 payment 테이블에서 amount가 8부터 9까지가 아닌 레코드들을 조회한다.

SELECT *
FROM payment
WHERE amount NOT BETWEEN 8 AND 9;