SQL 소개
SQL 소개 (SQL Introduction)
이 페이지는 SQL에서 간단한 연산을 수행하는 방법에 대한 개요를 제공해요. 이 튜토리얼은 입문을 위한 것이며 SQL에 대한 완전한 튜토리얼은 아니에요. PostgreSQL 튜토리얼에서 각색했어요.
출처: 문서
본문
DuckDB의 SQL 방언은 PostgreSQL 방언의 관례를 밀접하게 따르지만, 그 예외 몇 가지는 PostgreSQL 호환성 페이지에 나열되어 있어요.
이어지는 예시들은 DuckDB Command Line Interface (CLI) 셸이 설치되어 있다고 가정해요. CLI 설치 방법은 설치 페이지에서 확인할 수 있어요.
팁: 포괄적인 SQL 입문을 찾고 있다면 "Tabular Database Systems" 강좌의 슬라이드 덱을 확인해 보세요.
개념 (Concepts)
DuckDB는 관계형 데이터베이스 관리 시스템(RDBMS)이에요. 즉, 관계(relation)에 저장된 데이터를 관리하는 시스템이에요. 관계는 기본적으로 테이블의 수학적 용어예요.
각 테이블은 이름이 붙은 행의 집합이에요. 주어진 테이블의 각 행은 같은 이름의 컬럼 집합을 가지며, 각 컬럼은 특정 데이터 타입이에요. 테이블 자체는 스키마(schema) 안에 저장되고, 스키마의 모음이 접근할 수 있는 전체 데이터베이스를 구성해요.
새 테이블 만들기 (Creating a New Table)
테이블 이름과 모든 컬럼 이름 및 타입을 지정하면 새 테이블을 만들 수 있어요.
CREATE TABLE weather (
city VARCHAR,
temp_lo INTEGER, -- minimum temperature on a day
temp_hi INTEGER, -- maximum temperature on a day
prcp FLOAT,
date DATE
);
이것을 줄바꿈을 포함해 셸에 입력할 수 있어요. 명령은 세미콜론에 도달하기 전까지 종료되지 않아요.
SQL 명령에서 공백(스페이스, 탭, 줄바꿈)은 자유롭게 사용할 수 있어요. 즉, 위와 다르게 정렬하거나 한 줄에 모두 입력할 수 있어요. 두 개의 대시(--)는 주석을 시작해요. 그 뒤의 내용은 줄 끝까지 무시돼요. SQL은 키워드와 식별자에 대해 대소문자를 구분하지 않아요. 식별자를 반환할 때 원래 대소문자가 보존돼요.
SQL 명령에서는 먼저 수행할 명령 타입을 지정해요: CREATE TABLE. 그 다음 명령의 파라미터가 따라와요. 먼저 테이블 이름 weather가 주어지고, 그 다음 컬럼 이름과 컬럼 타입이 따라와요.
city VARCHAR는 테이블에 타입이 VARCHAR인 city라는 컬럼이 있다는 뜻이에요. VARCHAR는 임의 길이의 텍스트를 저장할 수 있는 데이터 타입이에요. 온도 필드는 INTEGER 타입(소수점 없는 정수)으로 저장돼요. FLOAT 컬럼은 단정밀도 부동소수점 숫자(소수점이 있는 숫자)를 저장해요. DATE는 날짜(년, 월, 일 조합)를 저장해요. DATE는 그 날짜와 연관된 시간은 저장하지 않고 특정 날짜만 저장해요.
DuckDB는 표준 SQL 타입 INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, CHAR(n), VARCHAR(n), DATE, TIME, TIMESTAMP를 지원해요.
두 번째 예시는 도시와 그 지리적 위치를 저장해요.
CREATE TABLE cities (
name VARCHAR,
lat DECIMAL,
lon DECIMAL
);
마지막으로, 테이블이 더 이상 필요 없거나 다르게 다시 만들고 싶다면 다음 명령으로 제거할 수 있어요.
DROP TABLE ⟨tablename⟩;
행으로 테이블 채우기 (Populating a Table with Rows)
테이블을 행으로 채우려면 insert 문을 사용해요.
INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
숫자가 아닌 상수(예: 텍스트와 날짜)는 예시에서처럼 단일 따옴표('')로 감싸야 해요. date 타입의 입력 날짜는 'YYYY-MM-DD' 형식이어야 해요.
cities 테이블에도 같은 방식으로 삽입할 수 있어요.
INSERT INTO cities
VALUES ('San Francisco', -194.0, 53.0);
지금까지의 문법은 컬럼의 순서를 기억해야 해요. 대안 문법으로 컬럼을 명시적으로 나열할 수 있어요.
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
원하면 컬럼을 다른 순서로 나열하거나 일부 컬럼(예: prcp가 알려지지 않은 경우)을 생략할 수 있어요.
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
팁: 많은 개발자가 컬럼을 명시적으로 나열하는 것이 순서에 암시적으로 의존하는 것보다 더 좋은 스타일이라고 생각해요.
이어지는 섹션에서 작업할 데이터가 있도록 위의 모든 명령을 입력해 주세요.
또는 COPY 문을 사용할 수 있어요. COPY 명령은 대량 로딩에 최적화되어 INSERT보다 유연성은 떨어지지만, 대량의 데이터를 다룰 때 더 빠르답니다. weather.csv를 사용한 예시는 다음과 같아요.
COPY weather
FROM 'weather.csv';
소스 파일의 파일 이름은 프로세스를 실행하는 머신에서 사용 가능해야 해요. DuckDB에 데이터를 로드하는 다른 방법은 많아요. 자세한 내용은 해당 문서 섹션을 참고해요.
테이블 쿼리하기 (Querying a Table)
테이블에서 데이터를 검색하려면 테이블을 쿼리해요. 이를 위해 SQL SELECT 문을 사용해요. 이 문은 select 리스트(반환할 컬럼을 나열한 부분), 테이블 리스트(데이터를 검색할 테이블을 나열한 부분), 선택적 자격(qualification, 제한을 지정하는 부분)으로 나뉘어요. 예를 들어 weather 테이블의 모든 행을 검색하려면:
SELECT *
FROM weather;
여기서 *는 "모든 컬럼"의 축약이에요. 그래서 다음으로도 같은 결과를 얻을 수 있어요.
SELECT city, temp_lo, temp_hi, prcp, date
FROM weather;
출력은 다음과 같아야 해요.
| city | temp_lo | temp_hi | prcp | date |
|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| San Francisco | 43 | 57 | 0.0 | 1994-11-29 |
| Hayward | 37 | 54 | NULL | 1994-11-29 |
select 리스트에 단순한 컬럼 참조뿐 아니라 표현식을 쓸 수 있어요. 예를 들어:
SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg, date
FROM weather;
결과는 다음과 같아요.
| city | temp_avg | date |
|---|---|---|
| San Francisco | 48.0 | 1994-11-27 |
| San Francisco | 50.0 | 1994-11-29 |
| Hayward | 45.5 | 1994-11-29 |
AS 절이 출력 컬럼에 레이블을 다시 붙이는 데 사용되는 점을 주목하세요. (AS 절은 선택 사항이에요.)
쿼리는 원하는 행을 지정하는 WHERE 절을 추가해 "자격(qualify)"을 부여할 수 있어요. WHERE 절은 불리언(진리값) 표현식을 포함하며, 불리언 표현식이 참인 행만 반환돼요. 일반적인 불리언 연산자(AND, OR, NOT)를 자격에서 사용할 수 있어요. 예를 들어 다음은 비 오는 날의 San Francisco 날씨를 검색해요.
SELECT *
FROM weather
WHERE city = 'San Francisco'
AND prcp > 0.0;
결과:
| city | temp_lo | temp_hi | prcp | date |
|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
쿼리 결과가 정렬된 순서로 반환되도록 요청할 수 있어요.
SELECT *
FROM weather
ORDER BY city;
| city | temp_lo | temp_hi | prcp | date |
|---|---|---|---|---|
| Hayward | 37 | 54 | NULL | 1994-11-29 |
| San Francisco | 43 | 57 | 0.0 | 1994-11-29 |
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
이 예시에서 정렬 순서는 완전히 지정되지 않아서 San Francisco 행이 어느 순서로든 나올 수 있어요. 하지만 다음처럼 하면 항상 위의 결과를 얻을 수 있어요.
SELECT *
FROM weather
ORDER BY city, temp_lo;
쿼리 결과에서 중복 행을 제거하도록 요청할 수 있어요.
SELECT DISTINCT city
FROM weather;
| city |
|---|
| San Francisco |
| Hayward |
여기서도 결과 행 순서가 달라질 수 있어요. DISTINCT와 ORDER BY를 함께 사용하면 일관된 결과를 보장할 수 있어요.
SELECT DISTINCT city
FROM weather
ORDER BY city;
테이블 간 조인 (Joins between Tables)
지금까지 쿼리는 한 번에 하나의 테이블만 접근했어요. 쿼리는 여러 테이블을 동시에 접근하거나, 같은 테이블의 여러 행을 동시에 처리하도록 같은 테이블에 접근할 수 있어요. 같은 또는 다른 테이블의 여러 행을 한 번에 접근하는 쿼리를 조인 쿼리(join query)라고 해요. 예를 들어 모든 날씨 레코드를 해당 도시의 위치와 함께 나열하고 싶다고 해 볼게요. 그러려면 weather 테이블 각 행의 city 컬럼을 cities 테이블 모든 행의 name 컬럼과 비교하고, 값이 일치하는 행 쌍을 선택해야 해요.
이것은 다음 쿼리로 수행돼요.
SELECT *
FROM weather, cities
WHERE city = name;
| city | temp_lo | temp_hi | prcp | date | name | lat | lon |
|---|---|---|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | -194.000 | 53.000 |
| San Francisco | 43 | 57 | 0.0 | 1994-11-29 | San Francisco | -194.000 | 53.000 |
결과 집합에서 두 가지를 관찰해요.
- Hayward 도시에 대한 결과 행이 없어요. 이는
cities테이블에 Hayward에 대한 일치 항목이 없어서, 조인이weather테이블에서 일치하지 않는 행을 무시하기 때문이에요. 곧 이 문제를 어떻게 고칠 수 있는지 볼 거예요. - 도시 이름을 담은 컬럼이 두 개 있어요. 이는
weather와cities테이블의 컬럼 목록이 연결되기 때문이에요. 실제로는 바람직하지 않으므로*대신 출력 컬럼을 명시적으로 나열하고 싶을 거예요.
SELECT city, temp_lo, temp_hi, prcp, date, lon, lat
FROM weather, cities
WHERE city = name;
| city | temp_lo | temp_hi | prcp | date | lon | lat |
|---|---|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 | 53.000 | -194.000 |
| San Francisco | 43 | 57 | 0.0 | 1994-11-29 | 53.000 | -194.000 |
컬럼 이름이 모두 달랐기 때문에 파서가 자동으로 어떤 테이블에 속하는지 찾았어요. 두 테이블에 중복 컬럼 이름이 있다면 아래처럼 컬럼 이름을 한정(qualify)해서 어느 것을 의미하는지 보여줘야 해요.
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.lon, cities.lat
FROM weather, cities
WHERE cities.name = weather.city;
조인 쿼리에서 모든 컬럼 이름을 한정하는 것은 널리 좋은 스타일로 간주돼요. 나중에 중복 컬럼 이름이 테이블 중 하나에 추가돼도 쿼리가 실패하지 않기 때문이에요.
지금까지 본 종류의 조인 쿼리는 다음 대안 형식으로도 쓸 수 있어요.
SELECT *
FROM weather
INNER JOIN cities ON weather.city = cities.name;
이 문법은 위의 것만큼 흔히 쓰이지는 않지만, 다음 주제를 이해하는 데 도움이 되도록 여기서 보여드려요.
이제 Hayward 레코드를 다시 가져오는 방법을 알아볼게요. 쿼리가 weather 테이블을 스캔하면서 각 행에 대해 일치하는 cities 행을 찾길 원해요. 일치하는 행이 없으면 cities 테이블의 컬럼에 "빈 값"을 대체하길 원해요. 이런 쿼리를 외부 조인(outer join)이라고 해요. (지금까지 본 조인은 내부 조인(inner join)이에요.) 명령은 다음과 같아요.
SELECT *
FROM weather
LEFT OUTER JOIN cities ON weather.city = cities.name;
| city | temp_lo | temp_hi | prcp | date | name | lat | lon |
|---|---|---|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | -194.000 | 53.000 |
| San Francisco | 43 | 57 | 0.0 | 1994-11-29 | San Francisco | -194.000 | 53.000 |
| Hayward | 37 | 54 | NULL | 1994-11-29 | NULL | NULL | NULL |
이 쿼리를 왼쪽 외부 조인(left outer join)이라고 해요. 조인 연산자 왼쪽에 있는 테이블의 각 행이 출력에 적어도 한 번은 나타나고, 오른쪽 테이블은 왼쪽 테이블의 어떤 행과 일치하는 행만 출력되기 때문이에요. 오른쪽 테이블과 일치하지 않는 왼쪽 테이블 행을 출력할 때는 오른쪽 테이블의 컬럼 대신 빈(null) 값을 대체해요.
집계 함수 (Aggregate Functions)
대부분의 다른 관계형 데이터베이스 제품처럼 DuckDB도 집계 함수를 지원해요. 집계 함수는 여러 입력 행에서 단일 결과를 계산해요. 예를 들어 행 집합에 대해 count, sum, avg(평균), max(최대), min(최소)을 계산하는 집계가 있어요.
예를 들어 어디서든 가장 높은 최저온도를 찾을 수 있어요.
SELECT max(temp_lo)
FROM weather;
| max(temp_lo) |
|---|
| 46 |
그 측정이 어떤 도시에서 발생했는지 알고 싶다면 시도해 볼 수 있어요.
SELECT city
FROM weather
WHERE temp_lo = max(temp_lo);
그러나 이것은 집계 max를 WHERE 절에서 사용할 수 없으므로 동작하지 않아요.
Binder Error:
WHERE clause cannot contain aggregates!
이 제한이 존재하는 이유는 WHERE 절이 집계 계산에 포함될 행을 결정하기 때문이에요. 분명히 집계 함수가 계산되기 전에 평가되어야 해요. 하지만 자주 그렇듯 쿼리는 원하는 결과를 얻도록 다시 표현할 수 있는데, 여기서는 서브쿼리를 사용해요.
SELECT city
FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
| city |
|---|
| San Francisco |
이것은 괜찮아요. 서브쿼리가 외부 쿼리에서 일어나는 것과 별도로 자신의 집계를 독립적으로 계산하기 때문이에요.
집계는 GROUP BY 절과 결합할 때도 매우 유용해요. 예를 들어 각 도시에서 관찰된 최고 최저온도를 얻을 수 있어요.
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
| city | max(temp_lo) |
|---|---|
| San Francisco | 46 |
| Hayward | 37 |
도시마다 출력 행이 하나씩 생겨요. 각 집계 결과는 그 도시와 일치하는 테이블 행들에 대해 계산돼요. HAVING으로 이 그룹화된 행들을 필터링할 수 있어요.
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
| city | max(temp_lo) |
|---|---|
| Hayward | 37 |
이것은 모든 temp_lo 값이 40 미만인 도시에 대해서만 같은 결과를 줘요. 마지막으로 이름이 S로 시작하는 도시만 신경 쓴다면 LIKE 연산자를 사용할 수 있어요.
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' -- (1)
GROUP BY city
HAVING max(temp_lo) < 40;
LIKE 연산자에 대한 자세한 내용은 패턴 매칭 페이지에서 확인할 수 있어요.
집계와 SQL의 WHERE, HAVING 절 사이의 상호작용을 이해하는 것이 중요해요. WHERE와 HAVING의 근본적인 차이는 이것이에요: WHERE는 그룹과 집계가 계산되기 전에 입력 행을 선택해요(따라서 어떤 행이 집계 계산에 들어갈지 제어해요). 반면 HAVING은 그룹과 집계가 계산된 후에 그룹 행을 선택해요. 그래서 WHERE 절은 집계 함수를 포함하면 안 돼요. 집계를 사용해 어떤 행이 집계의 입력이 될지 결정하려는 것은 말이 안 되거든요. 반면 HAVING 절은 항상 집계 함수를 포함해요.
이전 예시에서 도시 이름 제한을 WHERE에 적용할 수 있어요. 집계가 필요 없기 때문이에요. 이는 HAVING에 제한을 추가하는 것보다 더 효율적이에요. WHERE 검사에 실패하는 모든 행에 대해 그룹화와 집계 계산을 피하기 때문이에요.
갱신 (Updates)
UPDATE 명령으로 기존 행을 갱신할 수 있어요. 11월 28일 이후의 온도 측정이 모두 2도씩 틀렸다는 것을 발견했다고 해 볼게요. 데이터를 다음과 같이 고칠 수 있어요.
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
데이터의 새 상태를 보세요.
SELECT *
FROM weather;
| city | temp_lo | temp_hi | prcp | date |
|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| San Francisco | 41 | 55 | 0.0 | 1994-11-29 |
| Hayward | 35 | 52 | NULL | 1994-11-29 |
삭제 (Deletions)
DELETE 명령으로 테이블에서 행을 제거할 수 있어요. Hayward의 날씨에 더 이상 관심이 없다고 해 볼게요. 그러면 테이블에서 해당 행들을 삭제할 수 있어요.
DELETE FROM weather
WHERE city = 'Hayward';
Hayward에 속한 모든 날씨 레코드가 제거돼요.
SELECT *
FROM weather;
| city | temp_lo | temp_hi | prcp | date |
|---|---|---|---|---|
| San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| San Francisco | 41 | 55 | 0.0 | 1994-11-29 |
다음 형태의 문을 발행할 때는 주의해야 해요.
DELETE FROM ⟨table_name⟩;
경고: 자격(qualification) 없이
DELETE는 주어진 테이블의 모든 행을 제거해 테이블을 비워요. 시스템은 이 작업 전에 확인을 요청하지 않아요.