SQL 소개 (SQL Introduction)
SQL 소개 (SQL Introduction)
이 페이지는 SQL에서 간단한 연산을 어떻게 수행하는지 개요를 다룹니다. 이 튜토리얼은 여러분에게 소개를 해 주는 것일 뿐이며, SQL에 대한 완전한 튜토리얼은 결코 아닙니다. 이 튜토리얼은 PostgreSQL 튜토리얼에서 각색한 것입니다.
DuckDB의 SQL 방언은 PostgreSQL 방언의 관례를 밀접하게 따릅니다. 그 예외 몇 가지는 PostgreSQL 호환성 페이지에 나열되어 있습니다.
아래 예제들은 DuckDB 커맨드 라인 인터페이스(CLI) 셸을 설치했다고 가정합니다. CLI 설치 방법은 설치 페이지를 참고하세요.
팁: SQL에 대한 포괄적인 소개가 필요하다면 'Tabular Database Systems' 강좌의 슬라이드 덱을 확인해 보세요.
개념 (Concepts)
DuckDB는 관계형 데이터베이스 관리 시스템(RDBMS)입니다. 다시 말하면, relation에 저장된 데이터를 관리하는 시스템이라는 뜻입니다. relation은 본질적으로 table(테이블)을 가리키는 수학 용어입니다.
각 테이블은 이름이 붙은 행(rows)의 모음입니다. 주어진 테이블의 각 행은 동일한 이름의 열(columns) 집합을 가지며, 각 열은 특정한 데이터 타입을 가집니다. 테이블 자체는 schema 안에 저장되고, 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 명령 안에서 자유롭게 사용할 수 있습니다. 즉 위와 다르게 정렬해서, 심지어 한 줄에 전부 몰아서 입력해도 됩니다. 대시(dash) 두 개(--)는 주석을 나타냅니다. 그 뒤에 따라오는 내용은 줄 끝까지 무시됩니다. SQL은 키워드와 식별자에 대해 대소문자를 구분하지 않습니다. 식별자를 반환할 때는 원래의 대소문자가 보존됩니다.
SQL 명령에서 우리는 먼저 수행하려는 명령의 종류를 지정합니다: CREATE TABLE. 그 다음에 명령에 대한 파라미터들이 따라옵니다. 먼저 테이블 이름 weather가 오고, 그다음 열 이름과 열 타입이 옵니다.
city VARCHAR는 테이블에 city라는 이름의 타입이 VARCHAR인 열이 있다는 것을 지정합니다. 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)
테이블에서 데이터를 가져오려면 테이블을 질의(query)합니다. 이를 위해 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 절을 추가해 '한정(qualified)'할 수 있습니다. WHERE 절은 불리언(참/거짓) 표현식을 포함하며, 불리언 표현식이 참인 행만 반환됩니다. 흔한 불리언 연산자(AND, OR, NOT)는 qualification에서 사용할 수 있습니다. 예를 들어 다음은 비 오는 날의 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 질의라고 부릅니다. 예를 들어 모든 날씨 기록을 해당 도시의 위치와 함께 나열하고 싶다고 가정해 봅시다. 그러려면 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에 대한 일치 항목이 없어, join이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 |
열 이름이 모두 달랐기 때문에 파서는 각 열이 어느 테이블에 속하는지 자동으로 찾아냈습니다. 만약 두 테이블에 중복된 열 이름이 있다면, 다음과 같이 열 이름을 한정해 어느 것을 의미하는지 표시해야 합니다:
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;
join 질의에서는 모든 열 이름을 한정하는 게 좋은 스타일로 널리 여겨집니다. 그래야 나중에 어느 한 테이블에 중복된 열 이름이 추가되어도 질의가 실패하지 않습니다.
지금까지 본 종류의 join 질의는 다음의 대안 형식으로도 쓸 수 있습니다:
SELECT *
FROM weather
INNER JOIN cities ON weather.city = cities.name;
이 문법은 위의 것만큼 흔히 쓰이지는 않지만, 다음 내용을 이해하는 데 도움이 되도록 여기서 보여드리겠습니다.
이제 Hayward 기록을 다시 가져오는 방법을 알아보겠습니다. 우리가 질의에 원하는 것은 weather 테이블을 훑으며 각 행에 대해 일치하는 cities 행을 찾는 것입니다. 일치하는 행이 없으면 cities 테이블의 열에 '빈 값'이 대신 들어가길 원합니다. 이런 종류의 질의를 outer join이라고 합니다. (지금까지 본 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이라 부르는 이유는, 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 절이 집계 계산에 포함될 행을 결정하기 때문입니다. 그래서 당연히 집계 함수가 계산되기 전에 평가되어야 합니다.
하지만 흔히 그렇듯 이 질의는 원하는 결과를 얻도록 다시 쓸 수 있는데, 여기서는 서브쿼리(subquery)를 사용합니다:
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에 적용할 수 있습니다. 집계가 필요하지 않기 때문입니다. 이는 WHERE 검사에 실패하는 모든 행에 대해 그룹화와 집계 계산을 하지 않으므로, 제한을 HAVING에 추가하는 것보다 더 효율적입니다.
갱신 (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를 실행하면 주어진 테이블의 모든 행이 제거되어 테이블이 비게 됩니다. 시스템은 이 작업 전에 확인을 요청하지 않습니다.