지붕 위 태양광 계량기가 관계 대수를 가르친다

용어의 층위를 먼저 정리한다. 질의(query)는 여러 문(statement)으로 이루어지고, 문은 다시 절(clause)로 이루어진다. 그리고 CLI나 지원되는 다른 클라이언트에 제출된 질의를 명령(command)이라 부른다. DuckDB CLI에서 명령은 세미콜론으로 끝낸다.

공백은 마음대로 써도 된다. 명령을 아름답게 정렬해도 되고 한 줄에 몰아서 타이핑해도 되며, 어느 쪽을 골라도 상관없다. 그리고 SQL은 키워드와 식별자 모두에 대해 대소문자를 구분하지 않는다. 이 관대함이 SQL을 40년 넘게 살아남게 한 미덕 가운데 하나다.

대부분의 문은 동작을 바꾸는 여러 절을 지원한다. 그중 가장 두드러진 셋이 다음과 같다.

• WHERE — 최종 결과에 어떤 행을 포함할지에 대한 조건을 붙인다.
• GROUP BY — 하나 이상의 키로 정의되는 바구니에 여러 값을 집계해 담는다.
• ORDER BY — 돌려받을 결과의 순서를 지정한다.

이제 이 문들과 절들을 실제 사례로 시연한다. 소재는 태양광 발전이다. 개념마다 구체적인 세부를 붙여, 독자 자신의 작업에 어떻게 적용되는지 감을 갖고 떠나게 하는 것이 이 예제의 목적이다.

에너지 소비와 생산은 이미 오래전부터 OLAP 분석의 대상이었다. 15분 간격으로 소비를 측정하는 스마트 계량기는 금속 가공이나 대형 생산 공장 같은 여러 산업에서 진작부터 쓰여 이제 상당히 표준이 되었다. 이 측정값으로 소비 에너지의 값을 매기고, 소비를 예측하고, 그 밖의 일을 한다.

스마트 모니터링 시스템이 퍼지면서 이제 개인 가정에서도 상세한 에너지 검침값을 얻을 수 있게 되었고, 해마다 더 흔해지고 있다. 집에 태양광 발전 설비와 스마트 계량기를 설치했다고 상상해보자. 대기업이 하듯 전기 사용을 얼마간 계획하고 설비의 투자 회수를 예측하고 싶어진다. 그러려면 본격적인 시계열 데이터베이스와 실시간 대시보드까지 갖출 필요는 없다. DuckDB와 이 장의 예제가 자신에게 쓸모 있는 보고서를 만드는 좋은 출발점이 되기를 저자들은 바란다. 이 대목이 이 책 전체의 태도를 압축한다. 큰 장비를 세우지 않고도 할 수 있는 일이 생각보다 많다는 것이다.

데이터셋 내려받기 3.2.1

CSV 파일을 거치지 않고 데이터를 적재하기 위해 DuckDB의 httpfs 확장을 쓴다. CLI에서 install httpfs; load httpfs;를 실행해 설치한다. 다룰 데이터 파일은 두 종류다.

• systems.csv — PVDAQ가 측정하는 모든 태양광 시스템의 목록이다.
• 10번·34번·1200번 시스템의 2019년과 2020년 측정값 — URL은 모두 같은 형태를 따르며, system_id와 year 질의 문자열 매개변수를 바꿔가며 받는다. 접근하려면 API 키가 필요하고, 여기서는 DEMO_KEY를 쓴다.

이 데이터셋을 고른 이유가 분명하다. 영역이 파악하기 쉬우면서도 여러 분석 개념을 도입할 만큼 복잡하고, 실제 현장의 필요가 뒷받침되어 있기 때문이다. 그리고 어떤 분석 과정에서나 그렇듯, 결국 일관되지 않은 데이터를 만나게 된다. 이 데이터셋의 일부 계열도 그렇다. 저자들이 이 사실을 미리 밝혀두는 것은 뒤에 나올 CASE 표현식과 DELETE 문의 예고이기도 하다.

목표 스키마 3.2.2

DuckDB는 관계형 데이터베이스 관리 시스템(RDBMS)이다. 곧 관계(relation)에 저장된 데이터를 관리하는 시스템이라는 뜻이며, 관계란 본질적으로 테이블을 가리키는 수학 용어다. 각 테이블은 이름이 붙은 행의 모음이다. 한 테이블의 각 행은 이름이 붙은 같은 컬럼 집합을 가지고, 각 컬럼은 특정 데이터 타입을 갖는다. 테이블 자체는 스키마 안에 저장되며, 스키마의 모음이 접근할 수 있는 데이터베이스 전체를 이룬다.

이 데이터셋의 스키마는 몇 개의 테이블로 이루어진다(〈도판 3.1〉). 지원되는 조인을 쉽게 시연할 수 있도록 정규화해두었다. 다룰 세 테이블은 다음과 같다.

• systems — 발전량을 읽어 들이는 대상 시스템들을 담는다.
• readings — 그 시스템들에서 실제로 측정한 값을 담는다.
• prices — 에너지를 판매하는 가격을 담는다. 예제의 가격은 킬로와트시당 유로센트(cents/kWh)로 측정하지만, 킬로와트시당 어떤 단위를 써도 무관하다.

키 설계의 세 갈래를 한 문단으로 정리하면 이렇다. systems 테이블은 CSV 집합에 정의된 ID를 그대로 쓰며, 이를 외부에서 생성된 대리 키로 취급한다. prices 테이블은 SEQUENCE를 쓴다. readings 테이블은 복합 자연 키—측정된 시스템의 ID에 측정 시각을 붙인 것—를 쓴다. 같은 스키마 안에서 세 가지 키 전략을 나란히 보여주는 설계이니, 교재로서는 잘 짜인 배치다.

테이블을 담은 스키마를 먼저 만들지 않고도 여러 원천에 질의할 수 있다는 것은 이미 보았다. 그러나 DuckDB는 온전한 RDBMS이며, 데이터셋을 수집하기 전에 목표 스키마를 만들기 위해 데이터 정의어(DDL) 질의를 쓴다. 새 테이블은 CREATE TABLE 문으로 만들고, 기존 테이블은 ALTER TABLE 문으로 바꾼다. 테이블이 더 필요하지 않으면 DROP TABLE을 쓴다.

CREATE TABLE 문 3.3.1

감시할 시스템을 담을 테이블부터 만든다. 만들 테이블의 이름과 컬럼 목록은 반드시 지정해야 하고, 문장 전체에 붙는 수정자 같은 나머지 옵션은 선택이다. 컬럼 목록은 컬럼의 이름 다음에 타입, 그리고 선택적인 컬럼 제약을 붙여 정의한다.

여기서, 그리고 이 테이블을 토대로 삼는 이후의 예제에서 저자들은 여러 제약을 활용한다. 기본 키와 유일 키, 그리고 외래 키다. CREATE 문의 선택지를 시연하기 위해서만이 아니라, 데이터 정합성에 마음을 쓰기 때문이라고 밝혀두었다.

다만 대가가 있다. DuckDB에서도—거의 모든 다른 데이터베이스와 마찬가지로—제약은 대량의 데이터를 적재할 때 성능에 대체로 부정적으로 작용한다. 그 제약을 뒷받침하는 인덱스를 다시 만들거나 갱신해야 하고, 규칙도 검사해야 하기 때문이다. 정합성 검사가 필요하지 않다면 그 제약들을 생략하라는 것이 저자들의 조언이다. 원칙과 실용의 저울을 감추지 않고 보여주는 문장이다.

readings 테이블의 정의는 모양이 조금 다르다. 복합 기본 키를 쓴다. systems 테이블을 가리키는 참조 컬럼 system_id와, 값이 측정된 날짜와 시각을 담은 타임스탬프 컬럼으로 이루어진 키다. 이런 기본 키 제약은 컬럼 하나에 직접 붙일 수 없고 컬럼 목록 바깥으로 나간다.

마지막은 prices 테이블이다. 이 스크립트에는 실은 명령이 둘 들어 있다. 증가하는 숫자 값을 대리 기본 키로 쓸 것이기 때문이다. nextval() 함수 호출을 담은 DEFAULT 선언으로 이를 구현한다. 이 함수는 시퀀스의 이름을 입력으로 받는다. 시퀀스는 테이블 정의 바깥에 데이터베이스에 저장되는 숫자 값이며, CREATE SEQUENCE로 만든다.

그런데 왜 valid_from을 기본 키로 쓰지 않았는가. 저자들의 설명이 이 절에서 가장 읽어둘 만한 대목이다. 초기 응용에서는 판매 가격만 다루겠지만, 앞으로는 구매 가격도 다루게 될 수 있다. 그것을 모델링하는 방법은 여럿이다. 테이블을 하나 더 두거나, prices 테이블에 어떤 값이 판매가인지 구매가인지 명시하는 유형 컬럼을 도입하는 식이다. 그런데 valid_from을 기본 키로 쓰면 유형이 다른 두 가격이 같은 날짜부터 유효할 수 없게 된다. 그러면 단순 기본 키를 복합 키로 바꿔야 한다. 다른 데이터베이스는 기본 키와 유일 키를 삭제하고 다시 만드는 것을 허용할지 모르지만 DuckDB는 허용하지 않으므로, 이 경우 더 큰 이관 작업을 거쳐야 한다.

게다가 기본 키의 값을 갱신하는 일은 그 자체로 비싸다. 인덱스 관점에서만이 아니라 조직적 관점에서도 그렇다. 예컨대 해당 컬럼이 이미 외래 키의 참조 컬럼으로 쓰이고 있는 경우가 그렇다. 모든 제약은 인덱스로 뒷받침되며, 값이 바뀌면 그 인덱스를 재편해야 하는 일이 잦고, 그것은 느리고 비쌀 수 있다. 한 트랜잭션에서 여러 테이블을 갱신하는 것은 흔한 오류의 원천이며, 자주 불일치로 이어진다. 그 위험은 readings 테이블에는 없다. 타임스탬프 컬럼을 기본 키로 삼았는데, 측정값은 본질적으로 불변이기 때문이다.

ALTER TABLE 문 3.3.2

스키마를 정의하는 일은 복잡한 과제이고, 조직들은 보통 여기에 많은 공을 들인다. 그러나 스키마가 모든 만일의 사태를 아우르고 처음부터 완전히 옳은 경우는 좀처럼 만나지 못한다. 요구는 늘 변한다. 예컨대 가격의 유효 기간을 담아야 한다는 요구가 생기면 컬럼이 하나 더 필요해진다. 그런 경우 ALTER TABLE 문을 쓴다.

CREATE VIEW 문 3.3.3

CREATE VIEW 문은 질의에 대한 뷰를 정의한다. 본질적으로 그 질의를 표현하는 문장을 모든 조건과 변환을 포함해 저장하는 것이다. 질의될 때 뷰는 다른 어떤 테이블이나 관계와 똑같이 동작하며, 추가 조건과 변환을 얹을 수 있다.

어떤 데이터베이스는 뷰를 실체화(materialize)하고 어떤 데이터베이스는 하지 않는다. DuckDB는 뷰를 질의하면 그 밑에 깔린 문장을 실행한다. 성능 문제에 부딪히면 CTAS 문으로 뷰의 데이터를 임시 테이블에 실체화하는 것을 고려할 만하다. 그리고 뷰를 질의할 때 WHERE 절에 쓰는 추가 술어는 흔히 푸시다운 술어로 활용된다. 그 술어가 뷰를 정의하는 밑단 질의에 더해지며, 데이터를 적재한 뒤에 걸러내는 필터로 쓰이지 않는다는 뜻이다. 뷰가 게으른 껍데기가 아니라 최적화의 통로가 된다는 점을 짚어둘 만하다.

이 시나리오에서 요긴한 뷰는 시스템별·일별로 생산된 에너지를 kWh로 알려주는 뷰다. 이 뷰가 그 값을 계산하는 논리를 필요한 그룹화 문장과 함께 감싸준다. 저자들은 뷰의 값어치를 이렇게 정리한다. 뷰는 데이터베이스 안에 API를 만드는 훌륭한 방법이다. 그 API는 즉석 질의와 응용 프로그램을 모두 상대할 수 있고, 밑단 계산이 바뀌어도 같은 구조로 뷰를 다시 만들면 바깥의 어떤 응용에도 영향을 주지 않는다.

GROUP BY 절은 관계형 세계에서 없이 지내기 어려운 절 가운데 하나이며, 왜 그렇게 중요한지는 이 장 뒤에서 살펴본다. 이 예제에서는 GROUP BY 절이 시스템과 일자별로 생산된 총 전력을 계산한다는 것만 이해하면 된다. 선택 목록에 쓰인 sum 함수는 이른바 집계 함수로, 한 그룹에 속한 값들을 집계한다.

DESCRIBE 문 3.3.4

거의 보편적으로, 관계형 데이터베이스는 데이터베이스 스키마를 질의하기 위해 DESCRIBE 문을 지원한다. 가장 기본적인 구현에서는 보통 테이블과 뷰에 대해 동작한다. 그런데 DuckDB의 DESCRIBE는 관계인 모든 것에 대해 동작한다. 뷰, 질의, 집합, 그 밖의 것까지 모두 해당한다.

데이터베이스의 맥락에서 데이터를 삽입하고 삭제하고 수정하고 읽는 모든 문장을 데이터 조작어(DML)라 부른다. 읽는 것까지 조작에 넣는다는 점이 처음에는 낯설지만, 관계를 새 관계로 변환하는 일이 곧 조작이라고 보면 자연스럽다. 이 절은 데이터 질의로 들어가기 전에 INSERT와 DELETE 문을 먼저 다룬다. UPDATE 문은 여기서 자세히 파지 않는다.

그럴 수 있는 이유가 있다. SQL 질의의 아름다움은 그것들이 아주 자연스럽게 조합된다는 데 있다. 예컨대 WHERE 절에 대해 배운 모든 것은 INSERT, DELETE, UPDATE, SELECT 문에서 쓰이는 그 절에도 그대로 적용된다.

INSERT 문 3.4.1

데이터를 만들 때는 INSERT 문을 쓴다. 데이터 삽입은 단순한 "쏘고 잊는" 문장에서부터 충돌을 완화하고 높은 데이터 품질을 보장하는 복잡한 문장까지 폭이 넓다. 저자들은 단순하고 순진한 쪽에서 출발한다. INSERT 문은 어디에 넣을지를 먼저 지정하고 무엇을 넣을지를 그다음에 지정한다. 어디는 테이블 이름이고, 무엇은 컬럼 값의 목록일 수 있지만 테이블의 컬럼 타입과 순서에 맞아야 한다.

스키마가 주어진 이상 충돌 자체를 막을 수는 없었지만, 표준이 아닌 ON CONFLICT 절을 써서 아무것도 하지 않게 완화할 수 있다. DO NOTHING 절은 기본적으로 기본 인덱스—여기서는 id 컬럼—를 대상으로 삼는다. 여전히 취약하지만 최소한 이제 이 문장은 멱등해진다.

다만 저자들은 여기에 정직한 경고를 붙인다. 이 경우 멱등성은 생각만큼 유용하지 않을 수 있다. 오류는 나지 않지만, 기대한 결과도 얻지 못할 가능성이 크기 때문이다. 오류를 삼키는 것이 문제 해결과 같지 않다는 이 지적은 새겨둘 만하다. 더 나은 해법은 넣고 싶은 컬럼을 모두 명시하고 ID에 명시적 값을 쓰지 않는 것이다. 이미 시퀀스와 기본값을 정의해두었으니 ID는 알아서 생성된다.

물론 SELECT 문의 결과를 INSERT 문의 입력으로 쓸 수도 있다. 이 문장을 INSERT 절로 들어가는 파이프라인이라고 생각하면 된다.

이제 systems 테이블을 채우고 첫 측정값 묶음을 적재한다. INSERT 문을 제대로 쓰려면 CSV 데이터가 어떻게 생겼는지부터 알아야 한다. 여기서 앞 절의 교훈—DESCRIBE는 관계인 모든 것에 통한다—이 그대로 쓰인다. 이 경우 그 관계는 CSV 파일을 읽는 것으로 정의된다.

그런데 이 파일에는 중복이 있어서 삽입이 실패한다. 중복을 걸러내는 가장 쉬운 방법은 SELECT 문의 컬럼 절에 DISTINCT 키워드를 적용하는 것이다. 이렇게 하면 선택한 모든 컬럼에 걸쳐 유일한 집합이 보장된다.

3.2.1절에서 시스템을 고른 데에는 이유가 있었다. 34번 시스템의 데이터셋으로 시작하는 것은 그것이 15분 간격 측정이라는 요건에 맞기 때문이다. 다만 다뤄야 할 흠이 있다. 출력 전력이 때때로 NULL이거나 음수다. 빠진 값을 0으로 대체하기 위해 CASE 표현식을 쓴다. 그리고 이 URL은 .csv나 .parquet처럼 낯익은 확장자를 쓰지 않아 어떤 종류의 파일이나 구조가 뒤에 있는지 DuckDB에게 분명하게 알려주지 못한다. 그래서 read_csv_auto 함수를 써야 한다. 제2장의 짧은 링크 문제가 여기서 실전으로 되돌아온다.

2020년치 질의는 URL 매개변수만 빼면 사실상 같다. 그렇다면 인라인 테이블 구실을 하는 range 함수로 파일 이름 목록을 생성하면 되지 않을까. 저자들도 그 생각을 해보고, 되지 않는다고 솔직하게 적었다.

데이터 병합 3.4.2

중복이 있거나 이미 데이터베이스에 있는 항목을 담은 데이터셋을 마주하는 일이 잦다. 새 데이터를 다듬고 정리하는 것이 유일한 목적이라면 충돌을 무시해도 되지만, 때로는 새 데이터를 기존 데이터에 병합하고 싶어진다. 이 목적을 위해 DuckDB는 ON CONFLICT DO UPDATE 절을 제공한다. 다른 데이터베이스에서는 MERGE INTO로 알려진 그것이다.

예제의 상황은 이렇다. 같은 시스템에 대해 서로 다른 계량기에서 온 측정값이 여럿 있을 수 있고, 그 평균 측정값을 계산하고 싶다. 충돌 시 아무것도 하지 않는 대신 DO UPDATE를 쓴다.

DELETE 문 3.4.3

쓰고 있는 데이터 원천에는 이상치가 몇 개 있다. 시각의 서로 다른 분(minute)에 측정된 측정값 무리를 들여왔는데, 그것들을 데이터셋에 두고 싶지 않다. 가장 쉬운 처리는 DELETE 문을 적용해 없애버리는 것이다.

이 절은 SELECT 문과, 수집한 데이터를 질의하는 일에 집중한다. 이 문장은 데이터베이스에서 데이터를 행으로 가져오거나, 중첩해 쓰이면 덧없는(ephemeral) 관계를 만든다. 그 관계는 다시 질의할 수도 있고, 앞에서 본 것처럼 데이터를 삽입하는 데 쓸 수도 있다.

SELECT 절과 FROM 절

데이터를 읽는 모든 표준 SQL 문은 SELECT 절로 시작한다. SELECT 절은 종국에 행으로 돌아올 컬럼이나 표현식을 정의한다. 원천 테이블에서 모든 것을 가져오려면 *를 쓴다.

두 절은 서로를 보완하므로 어느 쪽을 먼저 설명해도 좋고 함께 설명해도 좋다. FROM 절은 나머지 질의가 작동할 데이터의 원천을 지정하며, 대부분의 질의에서 그것은 하나 이상의 테이블이다. FROM 절에 테이블이 둘 이상 나열되거나 추가로 JOIN 절이 쓰이면, 그것을 테이블을 서로 조인한다고 말한다.

여기서 LIMIT 절이 등장한다. 돌려받을 행의 수를 제한한다. 밑에 깔린 데이터셋을 모를 때는 돌려받는 데이터의 양을 제한하는 것이 흔히 현명하다. 대량의 네트워크 트래픽을 일으키거나 클라이언트가 응답하지 않는 상태에 빠지는 일을 피할 수 있다.

WHERE 절

WHERE 절은 질의에 조건을 붙여 데이터를 걸러낸다. 그 조건은 하나 이상의 표현식으로 짜인다. SELECT, DELETE, UPDATE 문으로 선택한 데이터는 그 술어에 맞아야 연산에 포함된다. 논리적으로 WHERE 절은 FROM 절 바로 뒤, 또는 앞서는 DELETE·UPDATE 문 바로 뒤에 적용된다.

GROUP BY 절

하나 이상의 컬럼으로 그룹을 만들면 그 컬럼들의 유일한 값마다 출력 행이 하나 생긴다. 그 필드들이 일치하는 모든 행을 함께 묶는 것이다. 이어서 묶인 값들이 count, sum, avg, min, max 같은 집계 함수로 집계되어, 그 그룹에 대한 단 하나의 값이 만들어진다. 하루 평균 측정 횟수나 주별 고객 합계 같은 것을 계산할 때 쓸모가 있다.

한 가지 짚어둘 규칙이 있다. GROUP BY 절이 지정되면, 선택 목록에 집계가 하나도 없어도 그 질의는 언제나 집계 질의다. 그리고 DuckDB에는 집계 함수의 일부가 아닌 모든 컬럼으로 그룹을 만들어주는 편리한 확장 GROUP BY ALL이 있다.

고를 수 있는 집계 함수는 많다. 앞서 다룬 비교적 표준적인 것들에 더해, 저자들이 자주 요긴하다고 꼽는 것들은 다음과 같다.

• list — 각 그룹의 모든 값을 리스트 구조로 집계한다.
• any_value — 그룹화에 쓰이지 않은 컬럼에서 아무 값이나 하나 고른다.
• first, last — 결과가 정렬된 경우 그룹화에 쓰이지 않은 컬럼에서 첫 값이나 마지막 값을 고른다.
• arg_max, arg_min — 어떤 표현식이 최대 또는 최소 값을 갖는 행에서 그 표현식의 값을 찾는, 흔한 과제를 해결한다.
• bit_and, bit_or, bit_xor 등 — 집합에 대해 동작하는 비트 연산이다.
• median, 분위수 계산, 공분산 계산, 일반 회귀 — 통계 집계의 빠짐없는 집합이다.

이제 가격 예제로 돌아간다. 앞에서는 WHERE 절로 한 해의 가격을 찾았다. 그것도 흥미롭지만, 연도별 최저·최고 가격을 알아내는 것은 어떤가.

VALUES 절

VALUES 절은 정해진 개수의 행을 지정하는 데 쓴다. 데이터를 삽입할 때 이미 보았으며, 그것이 상당히 흔한 사용처다. 그러나 DuckDB에서는 다른 몇몇 데이터베이스보다 훨씬 다재다능하다. 독립된 문장으로도, FROM 절의 일부로도, 행과 컬럼의 개수에 제한 없이 쓸 수 있다. 조건에 넣을 씨앗 데이터를 마련하는 것 같은 몇 가지 상황에서 요긴하다.

단일 Parquet 파일이나 CSV 파일을 분석할 때는 JOIN 절 없이도 넘어갈 수 있다. 그러나 이 절은 건너뛰지 말라고 저자들은 당부한다. 조인은 두 테이블 또는 관계를 잇는 데 쓰이는 관계형 연산의 근본이다. 두 관계를 조인의 왼쪽과 오른쪽이라 부르며, 먼저 나열된 테이블이 왼쪽이다. 이 연결은 그전까지 이어져 있지 않던 정보를 결합한 새 관계를 표상하며, 그로써 새로운 통찰을 준다.

본질적으로 조인은 양쪽에서 짝이 맞는 행의 쌍을 만든다. 짝짓기는 보통 왼쪽 테이블의 키 컬럼이 오른쪽 테이블의 컬럼과 같은지에 기반한다. 테이블을 조인하기 위해 외래 키 제약이 필요하지는 않다. 저자들은 JOIN .. ON보다 SQL 표준 정의인 JOIN .. USING에 기반한 조인을 선호하며, 이 책 나머지에서도 그렇게 쓴다. 그렇다고 그것만 되는 것은 아니다. FROM 절에 테이블을 그냥 나열하고 WHERE 절에서 키 컬럼을 비교하는 것으로도 조인을 표현할 수 있다.

아래는 그 설명을 손으로 확인해보는 장치다. 왼쪽과 오른쪽 관계는 목록 3.12–3.14가 쓴 값을 그대로 옮겼다. 조인 종류를 갈아 끼우면 결과 행이 어떻게 달라지는지, 그리고 NULL이 어느 자리에 채워지는지 눈으로 좇을 수 있다.

두 관계에 이름이 같은 컬럼이 있으면 USING 절로 그것을 지정할 수 있다. USING 절은 지정한 컬럼을 두 관계에서 찾아내며, ON 절로 직접 ON tab1.col = tab2.col이라 쓰는 것과 똑같이 동작한다. 목록 3.12–3.14의 세 문장은 JOIN, LEFT OUTER JOIN, FULL OUTER JOIN만 바뀌고 나머지는 같다.

외부 조인은 지정한 쪽의 행 가운데 상대에 짝이 없는 것에 대해 NULL 값을 보충해준다. 데이터베이스에 발전 시스템이 여럿 있다고 생각해보자. 어떤 것에는 다른 테이블에 공급업체 정보를 저장해두었고 어떤 것에는 없다. 모든 시스템의 목록을 공급업체가 있으면 그것과 함께, 없으면 빈 컬럼과 함께 내놓아야 하는 과제라면 외부 조인을 쓴다.

왼쪽 외부 조인과 오른쪽 외부 조인은 모두 세 행을 돌려준다. 네 행을 되찾으려면 완전 외부 조인을 써야 한다. 그렇다고 언제나 외부 조인을 쓰라고 권하는 것은 잘못이다. 외부 조인은 내부 조인이 줄 행을 언제나 포함하지만, 내부 조인은 다른 테이블에 짝이 없는 행을 걸러내며 그것이 요구 사항인 경우가 잦다. 외부 조인은 보통 반드시 있어야 하는 데이터를 선택적인 데이터로 살찌우고 싶을 때 적절하다. 두 조인의 용도를 이보다 간결하게 정리하기 어렵다.

예제의 스키마에서는 사정이 조금 다르다. systems 테이블에는 id 컬럼을 정의했고 readings의 외래 키 컬럼은 system_id이니, 이름이 다르므로 ON 절을 써야 한다. 그 컬럼으로 조인하면 조인은 언제나 짝이 맞는 행을 만들어낸다. 조인 컬럼(id)이 system_id에 정의한 외래 키가 참조하는 컬럼이기 때문이다. 곧 systems에 대응 항목이 없는 readings 행은 존재할 수 없다.

조인 종류는 더 있다. 모든 튜플의 데카르트 곱을 만드는 CROSS JOIN이 있고, 유효 기간이 제한된 가격을 다룰 때 요긴해지는 ASOF(as of) 조인이 있다. ASOF 조인은 시간적 유효성—사실은 <=, <, >, >= 같은 부등 조건을 갖는 무엇이든—에 기반해 한 테이블의 행을 다른 테이블의 행과 맞춰준다. 자세한 내용은 다음 장에서 다룬다.

경고 — 키가 중복될 때

저자들은 이 절을 경고에 가까운 이야기로 맺는다. 내부 조인과 외부 조인의 예제에서는 키 컬럼의 값이 다른 테이블에서 발견되지 않을 때 무슨 일이 생기는지만 논했다. 그러면 조인 컬럼 가운데 하나가 같은 값을 여러 번 담고 있을 때는, 그것도 한쪽에서 또는 양쪽에서 그럴 때는 어떻게 되는가. 위의 탐색기에서 〈중복 키 데이터로 바꾸기〉를 눌러보면 직접 확인할 수 있다. 왼쪽에서 id가 2인 행이 둘, 오른쪽에서 3인 행이 둘이다.

이것은 스키마를 정의할 때 대비할 수 있는 일이다. 통상 조인은 미리 알고 있는 컬럼에서 일어난다. 예제라면 systems 테이블의 id와 readings의 system_id 쌍이 그렇다. systems 테이블에서 그 컬럼은 기본 키로 정의되어 있으므로 언제나 유일한 값이며, 따라서 그 테이블에 한 번만 나타날 수 있다. readings 테이블에서는 외래 키로 정의되어 있으므로 다른 쪽에 반드시 존재해야 한다. 외래 키는 보통 데이터베이스에 이른바 인덱스를 만들며, 모든 행을 훑지 않고 빠르게 조회할 수 있게 해 조인의 성능을 좋게 한다.

그리고 외래 키는 유일하지 않으며 대부분의 모델에서 유일할 필요도 없다. 예제에서 시스템이 readings 테이블—오른쪽—에 여러 번 나타나는 것은 당연히 기대되는 바다. 발전 설비가 평생 한 번만 전기를 만들기를 바라는 것이 아니라면 말이다. 마지막 한 문장의 능청스러움이 이 책의 성격을 잘 보여준다.

WITH 절은 공통 테이블 표현식(CTE)이라는 이름으로도 알려져 있다. CTE는 본질적으로 특정 질의 안으로 범위가 제한된 뷰다. 뷰처럼, 질의 논리의 일부를 독립된 문장으로, 적어도 더 큰 질의 안의 격리된 부분으로 감싸두고 싶을 때 쓴다. 뷰를 만들어도 전혀 문제는 없지만, 그 결과가 더 큰 질의의 특정 문맥에서만 필요하다면 뷰까지 만들고 싶지는 않을 것이다.

게다가 CTE에는 뷰에 없는 특성이 하나 있다. 뷰는 다른 뷰를 참조할 수 있지만 중첩될 수는 없다. CTE는 같은 WITH 절에 정의된 다른 CTE를 참조할 수 있다. 그로써 질의 논리를 점진적으로 쌓아 올릴 수 있다.

WITH 절은 FROM 절에 서브쿼리를 정의하는 반패턴을 막아준다. FROM 절의 원천 관계로 놓인 서브쿼리는 구문적으로도 의미적으로도 타당하다. 그 결과가 그 자체로 관계이기 때문이다. 그러나 읽기가 어려운 일이 잦다. 게다가 중첩된 서브쿼리는 자기 자신을 참조할 수 없다.

그렇다면 시스템별·시간별 평균 발전량의 최대치를 묻는다면 어떤가. max와 avg 같은 집계 함수는 중첩할 수 없다. 곧 avg(max(v))라고 쓸 수 없으므로 집계를 따로따로 써야 한다. 여기서 CTE가 참으로 필요해진다.

그 전에 함수 둘을 짚어둔다. 어떤 컬럼의 최소·최대 값을 담은 행이 무엇인지 묻는 일은 아주 흔한 과제이므로, DuckDB에는 그것을 수행하는 내장 함수 arg_max와 arg_min이 있다. 두 번째 매개변수의 최소 또는 최대 값이 처음 나타나는 행에서, 첫 번째 매개변수로 정의된 표현식을 계산해준다.

이제 두 겹의 집계다. 먼저 시스템과 시간별 평균 발전량으로 측정값을 묶는 복잡한 논리—첫 번째 집계—를 per_hour라 이름 붙인 CTE에 감싸고, 그 CTE를 받아 두 번째 집계를 계산한다.

RECURSIVE — 뷰도 서브쿼리도 못 하는 일

CTE는 뷰와 서브쿼리가 하지 못하는 멋진 일을 하나 더 할 수 있다. WITH 절에는 RECURSIVE라는 추가 키워드가 있어서, CTE를 뒤따르는 다른 CTE와 FROM 절에서만이 아니라 자기 자신 안에서도 참조할 수 있게 해준다. 재귀 CTE를 작동시키려면 재귀의 초기 씨앗이 있어야 한다. 트리 구조라면 쉽다. 부모 행이 없는 행을 가져와 UNION 절의 한쪽 잎으로 삼으면 된다.

DuckDB를 만든 이들의 목표 가운데 하나는 SQL을 더 접근하기 쉽고 사람에게 친절하게 만드는 것이다. 그 방법의 하나로, 흔한 과제를 쉽게 처리하게 해주는 추가 문법을 자신들의 SQL 구현에 더해왔다. 이 절이 그 추가분을 소개한다.

SELECT를 다루는 법 3.5.1

SELECT *는 양날의 검이다. 적어내기 쉽고, 결과 튜플에는 실제로 필요한 것이 웬만하면 다 들어 있다. 관계의 모든 컬럼을 선택하는 데 딸린 문제로 저자들은 넷을 든다.

• 결과 튜플의 불안정성 — 테이블 정의가 바뀔 수 있다. 컬럼이 더해지거나 없어지면 결과의 모양이 함께 바뀐다.
• 메모리 압박 — 데이터베이스 서버나 프로세스에 더 큰 부담을 준다.
• 네트워크 트래픽 — DuckDB는 임베디드 데이터베이스라 네트워크가 개입하지 않지만, 임베디드가 아닌 데이터베이스에서는 스타 셀렉트가 트래픽을 늘린다.
• 인덱스 온리 스캔의 무산 — 인덱스 온리 스캔은 질의가 인덱스를 쓸 수 있고 그 인덱스에 있는 컬럼만 돌려주어 다른 입출력을 모두 피할 수 있을 때 일어난다. 대부분의 경우 바람직한 거동인데, 스타 셀렉트는 그것을 막아버릴 수 있다.

그러니 SELECT * 질의를 너무 많이 하지 않는 것이 좋지만, 때로는 필요하다. 그래서 DuckDB는 키워드 둘을 더해 그것을 더 안전하게 쓸 수 있게 했다. EXCLUDE와 REPLACE다. 그리고 정말로 모든 컬럼을 원하는 것이 확실하다면, SELECT 절을 아예 생략하고 FROM 절로 시작하는 단순화된 형태를 쓸 수 있다.

COLUMNS — 컬럼을 동적으로 사영하고 걸러내기

prices 테이블에는 가격의 유효성에 관한 정보를 담은 컬럼이 둘 있다. COLUMNS 표현식은 정규식에 기반해 하나 이상의 컬럼을 사영하고, 걸러내고, 집계하는 데 쓸 수 있다.

이름으로 삽입 3.5.2

목록 3.6을 기억할 것이다. 거기서는 INSERT INTO target(col1, col2) SELECT a, b FROM src 꼴의 문장으로 systems 테이블을 채웠다. 작동은 하지만 유지하기에 취약하다. INSERT 문은 선택된 컬럼이 대상 테이블에 정의된 것과 같은 순서이기를 요구하거나, 컬럼 이름을 두 번—INTO 절에 한 번, 선택 목록에 한 번—되풀이하기를 요구하기 때문이다.

DuckDB는 이 문제를 풀기 위해 BY NAME 절을 제공한다. 원천의 컬럼 이름에서 대상의 컬럼 이름으로 가는 대응 관계를 한자리에 모아둘 수 있다.

어디서나 별칭에 접근하기 3.5.3

알아채지 못했을 수도 있지만, 앞의 여러 예제는 표준이어야 마땅한데 표준이 아닌 어떤 성질의 혜택을 이미 받고 있었다. 컬럼에 별칭을 도입하는 순간, 뒤따르는 절들에서 그 별칭에 접근할 수 있다는 성질이다.

관련된 모든 컬럼으로 묶고 정렬하기 3.5.4

GROUP BY 절을 다룬 절에서 논한 대로, 집계가 아닌 모든 컬럼은 GROUP BY 절에 열거되어야 한다. 비집계 컬럼이 많으면 이는 고통스러운 경험이 되는데, DuckDB는 GROUP BY ALL을 허용해 그 고통을 덜어준다. 정렬에도 비슷한 개념이 있다. ORDER BY ALL은 포함된 컬럼들로 왼쪽에서 오른쪽으로 결과를 정렬한다. 스타 셀렉트의 경우 컬럼의 순서는 물론 테이블이나 뷰의 정의가 정한다.

데이터 표본 추출 3.5.5

큰 데이터셋을 다룰 때는 전부를 훑는 대신 표본을 얻고 싶은 일이 잦다. 34번 시스템의 측정값만 들여왔다 해도 데이터베이스에는 5만 건이 넘는 레코드가 있다. 0이 아닌 발전량 측정값의 개요를 n 퍼센트 또는 n개 행의 표본으로 얻을 수 있다.

표본 추출 방법의 선택에 관한 설명이 이 절의 알맹이다. 세 가지가 있다.

선택적 매개변수를 갖는 함수 3.5.6

DuckDB의 몇몇 함수—예컨대 read_json_auto—는 필수 매개변수와, 합리적인 기본값을 갖는 선택적 매개변수 하나 이상을 함께 갖는다. 방금 언급한 함수는 매개변수가 열일곱 개다. 그 목록은 다음 질의로 얻는다. 오버로드가 여러 개 있어 타입이 다른 것들이 섞이므로 DISTINCT를 쓴다.