한 행만 보던 함수에게 옆줄을 볼 눈을 주는 일

예제 시나리오를 이어간다. 제3장 3.4.1절에서 다룬 34번 시스템의 데이터는 정합성에 문제가 좀 있었지만 스키마와 착상에 잘 맞았다. 목표는 15분 간격으로 측정값을 저장하는 것이었음을 기억해두자. 그런데 3.2.1절에서 함께 내려받은 다른 데이터셋을 열어보면, 15분이 아닌 간격으로 오는 것들이 눈에 띈다. 파일을 재빨리 들여다보는 한 방법은 tail 명령—파일의 마지막 n줄을 돌려준다—이고, head도 마찬가지로 쓸 수 있다.

해결의 도구는 time_bucket()이다. DuckDB가 날짜·시각·타임스탬프를 위해 제공하는 여러 함수 가운데 하나로, 타임스탬프를 주어진 간격으로 절단하고 선택적인 오프셋에 맞춰 정렬해 시간 바구니(time bucket)를 만든다. 시간 바구니는 센서 측정값 따위를 집계하는 강력한 기제다. GROUP BY와 집계 함수 avg를 함께 쓰면 요구에 맞게 데이터를 준비해 결국 수집할 수 있다. 15분 간격의 바구니를 만들고, 특정 바구니에 떨어지는 모든 측정값의 평균 발전량을 계산한다.

질의를 보면 CASE WHEN THEN ELSE END 구성물이 눈에 들어온다. if/else처럼 동작하는 CASE 문이다. 여기서 하는 일은 평균을 계산하기 전에 값이 0보다 작거나 값이 아예 없는 측정값을 0으로 바꾸는 것이다. 이 데이터셋의 별스러움 가운데 하나인데, 저자들이 붙인 설명이 담백해서 오래 기억에 남는다. 센서가 고장났는지, 아니면 네트워크가 말썽이었는지, 끝내 알 수 없다. 그래도 데이터는 다뤄야 한다. 여기서는 NULL 값을 음수값처럼 취급해 0으로 눌러두는 것이 괜찮다고 판단했다. 계산을 망가뜨리는 경우라면 집계에 FILTER를 걸어보는 편을 고려할 만하고, 그것은 4.6.3절에서 다룬다.

이 절은 판단의 문제로 맺는다. 수집을 아예 피하고 외부 파일에 기반해 모든 분석을 인메모리로 할지, 수집하는 동안 어느 정도 집계할지, 아니면 분석할 때만 집계할지. 이 결정은 통상 데이터셋의 크기, 장기 보관에 대한 목표, 이후의 처리 필요 사이의 절충으로 귀결된다. 그러므로 여기서 일반적으로 적용되는 해법을 만들려 하면 반드시 실패한다. 이 시나리오에서는 교육적 목적으로 수집과 집계를 모두 하기로 했고, 공유할 수 있을 만큼 데이터셋을 작게 유지하기로 했다.

새 데이터셋을 깊이 분석하기 전에 그 특성을 얼마간 알고 싶은 것이 보통이다. 값의 개수—예제에서는 측정값이 몇 개인가—, 수치의 분포와 크기, 그리고 시계열의 간격 크기 같은 것이다. 두 번째가 특히 중요하다. 와트를 다루는지 킬로와트를 다루는지 모른 채로는 보고서가 노골적으로 틀리게 된다. DuckDB에는 어떤 데이터셋에 대해서든 이 정보를 재빨리 내주는 고유 명령 SUMMARIZE가 있다.

관리하는 시스템들이 생산한 총 전력의 평균을 계산하고 싶다고 하자. 그러려면 집계 함수 둘, avg와 sum을 적용해야 한다. 그런데 둘을 중첩할 수 없다.

다음 질문으로 넘어간다. 어느 날, 어느 시스템에서 가장 많은 전력이 생산되었는가. 한 가지 해법은 WHERE 절의 비교에서 오른쪽 항으로 서브쿼리를 쓰는 것이다.

그러면 시스템별로 최대 발전량과 측정 시각을 알아내려면 어떻게 하는가. 원래의 서브쿼리로는 까다롭다. 그것은 전체 최대 발전량에 대한 값만 보여주기 때문이다. 서브쿼리가 행마다 다른 값을 돌려주어야 하며, 그러려면 바깥 질의의 필드를 안쪽에서 쓰는 상관 서브쿼리를 쓴다.

표현식으로 쓰인 서브쿼리는 조인으로 다시 쓸 수 있다. 중첩 집계의 계산만이 예외다. 방금의 예제라면 이렇게 된다.

그런데 DuckDB에서는 그 수고가 필요하지 않다. 이 절에서 가장 중요한 대목이므로 옮겨 적어둘 만하다. DuckDB는 서브쿼리 탈상관(decorrelation) 옵티마이저를 쓴다. 이 옵티마이저는 서브쿼리를 언제나 바깥 질의로부터 독립적으로 만든다. 그래서 사용자는 서브쿼리를 조인으로 손수 고쳐 쓸 걱정 없이 표현력 있는 질의를 자유롭게 쓸 수 있다.

더 나아간 사실이 있다. SQL을 고쳐 써서 어떤 서브쿼리를 손으로 탈상관하는 것이 언제나 가능한 것은 아니다. DuckDB는 내부적으로 모든 서브쿼리를 탈상관하는 특수한 종류의 조인을 쓴다. 실은 DuckDB에는 탈상관되지 않은 서브쿼리를 실행하는 기능 자체가 없다. 이것이 사용자에게는 이득이다. 질의의 가독성과 표현력, 그리고 풀려는 업무 문제에 집중할 수 있게 해주기 때문이다. 어떤 종류의 서브쿼리를 쓰는지 아예 걱정하지 않아도 된다는 것, 그것이 이 엔진이 주는 여유다.

표현식으로서의 서브쿼리 4.3.1

상관이든 비상관이든, JOIN에서 관계로 쓰이지 않는 모든 형태의 서브쿼리는 표현식이다. 따라서 다른 여러 연산자와 함께 쓸 수 있다. = 연산자와 부등 연산자 <, <=, >=, >는 서브쿼리가 정확히 한 행을 돌려주는 스칼라일 것을 요구한다. 스칼라와 비스칼라 서브쿼리를 함께 다룰 때는 추가 연산자를 쓸 수 있다. IN, EXISTS, ANY, ALL이며, 모두 집합 비교를 통해 작동한다.

이 절의 인공적인 예제들은 모두 v = 7을 돌려준다. 값이 같다는 것이 요점이 아니라, 같은 답에 이르는 네 가지 어법을 나란히 놓는 것이 요점이다.

어떤 데이터셋을 볼 때는 속성의 최소·최대값, 혹은 평균의 개요를 얻어두는 것이 늘 도움이 된다. 지워야 할 이상치가 섞여 있을 수도 있고, 단위를 잘못 다뤘을 수도 있다. 그것을 계산하는 가장 쉬운 방법은 GROUP BY 절 없이 한 질의에 몰아넣는 것이다. 그러면 집계가 바구니 하나—테이블 전체—에서 일어난다.

이제 시스템별 kWh와 측정 횟수를 본다. 여러 해를 15분 간격으로 절단해 들여왔으니 연간 대략 35,040개의 측정값이 있어야 한다. GROUP BY system_id, year가 그 가정을 확인해준다. 그런데 그다음 질문이 이 절의 본론이다. 합계는 어떻게 하는가. 연도별, 연도·시스템별, 그리고 전체의 측정 횟수와 총 발전량 말이다. 다시 말해 그룹마다 서로 다른 상세 수준을 보여주는 드릴다운 보고서를 만들 수 있는가.

숫자를 계산기에 하나씩 넣어 손으로 더하거나, 그룹 키 없는 집계 질의를 하나 더 쓸 수도 있다. 그러나 더 나은 선택이 있다. 그룹화 집합이다. 아래에서 네 어법을 갈아 끼워 결과 행이 어떻게 늘어나는지 확인할 수 있다.

그룹화 집합이 만든 바구니를 정리하면 이렇다.

• (year, system_id) — 두 값의 조합으로 정의되는 바구니. 예제에서는 여섯 가지 조합이므로 여섯 행이 나온다.
• (year) — 연도 하나로만 정의되는 바구니. 이 집합에는 없지만 다른 집합에는 있는 키에는 NULL 값이 채워진다. 여기서는 system_id가 그렇다.
• () — 빈 바구니, 또는 빈 그룹이라 할 수 있다. 다른 모든 키에 NULL 값이 채워진다.

같은 결과를 줄임 절 ROLLUP으로도 얻는다. ROLLUP 절은 앞서 논한 집합들을 n + 1개의 그룹화 집합으로 자동 생성하며, n은 ROLLUP 절의 항 수다. 모든 해에 걸친 시스템별 합계까지 보고 싶다면 GROUP BY CUBE를 쓴다. CUBE는 부분그룹이 아니라 실제 조합(2ⁿ개의 그룹화 집합)을 만든다. 예제에서는 (year, system_id), (year), (system), ()를 만든다.

저자들의 맺음이 이 절의 값어치를 정확히 짚는다. 여러 개의 질의가 아니라 하나의 간결한 질의로 발전량 측정값의 완전한 개요를 갖게 되었다. 오는 길에 덧붙인 모든 드릴다운을 그룹화 집합으로 표현할 수 있다. 최소·최대값은 목록을 읽기 좋게 하려고 생략했을 뿐이다.

창, 그리고 창 위에서 적용되는 함수는 현대 SQL과 분석의 본질적인 부분이다. 윈도 함수는 일반적으로 다른 행을 볼 수 있게 해준다. 통상 SQL 함수는 한 번에 현재 행만 볼 수 있고, 집계를 하는 경우가 예외인데 그 경우에는 행의 수가 줄어든다. 여기서 윈도 함수의 결정적 성질이 나온다. 보통의 집계 함수와 달리, 윈도 함수를 써도 행들이 하나의 출력 행으로 묶이지 않는다. 행들은 각자의 정체성을 유지한다.

창은 적용하려는 함수 뒤에 붙는 OVER() 절로 도입한다. 창 자체는 작업 대상이 되는 행들의 정의이며, 정해진 순서를 따라 정해진 크기의 행을 데이터셋 위로 움직여 가는 창이라고 생각하면 된다. 윈도잉이 하는 일은 셋으로 정리된다. 관계를 독립적인 파티션들로 쪼개고, 그 파티션을 선택적으로 정렬하고, 각 행마다 근처 값들의 함수로서 새 컬럼을 계산하는 것이다.

여기서 오해하기 쉬운 지점을 저자들이 못 박아둔다. 창의 크기는 파티션의 크기와 같지 않다. 둘은 독립적으로 정의할 수 있다. 종국에 창의 내용이 함수로 넘어가 새 값이 계산된다. 창의 맥락에서만 동작하는 전용 함수가 몇 개 있지만, 모든 보통의 집계 함수를 윈도 함수로 쓸 수 있다. 그래서 다음과 같은 쓰임이 열린다.

• 순위 매기기
• 창마다 독립적인 집계 계산
• 창마다 누적 합계 계산
• lag 또는 lead로 앞뒤 행에 접근해 변화량 계산

순진한 top-3의 함정

구체적인 예제를 본다. 가장 많은 전력이 생산된 시스템과 그 시각 상위 셋을 15분 단위로 얻고 싶다고 하자. 순진한 접근은 발전량으로 정렬해 3으로 제한하는 것이다.

OVER() 절 안의 창 정의에 들어가는 ORDER 절은 선택이며, 문장 끝의 ORDER BY 절과 달리 질의 결과를 정렬하지 않는다. OVER() 절의 일부로 쓰일 때 ORDER BY는 윈도 함수가 실행되는 순서를 정의한다. 생략하면 윈도 함수는 임의의 순서로 실행된다. 방금의 예제에서 생략하는 것은 무의미하다. 정렬되지 않은 밀집 순위는 언제나 1이 될 것이기 때문이다.

파티션 정의하기 4.5.1

앞의 순위 매기기는 나아졌지만 아직 특별히 도움이 되지 않는다. 시스템들의 발전량이 자릿수가 다를 만큼 차이가 나기 때문이다. 시스템을 구분하지 않고 순위를 계산하는 것은 원하는 바가 아닐 수 있다. 여기서 실제로 필요한 것은 시스템별 상위 세 측정값이며, 각 시스템이 데이터의 독자적인 파티션을 이룬다. 파티셔닝은 관계를 독립적이고 서로 무관한 조각들로 쪼개고, 그 안에서 윈도 함수가 적용된다.

PARTITION BY 절로 파티션이 어떻게 구성되는지 정의하지 않으면 관계 전체가 하나의 파티션으로 취급된다. 그리고 결정적인 제약이 하나 있다. 윈도 함수는 평가되고 있는 행이 담긴 파티션 밖의 값에 접근할 수 없다.

순위 매기기만 파티션 안에서 쓸 수 있는 것은 아니다. avg, sum, max, min 같은 집계 함수도 윈도잉 맥락에서 쓸 훌륭한 후보다. 차이는 그것들이 생산되는 행의 수를 바꾸지 않는다는 점이다. 시스템마다 하루의 발전량을 고르면서, 추가 컬럼에 그 시스템의 전체 평균 발전량을 함께 얻고 싶다고 하자. GROUP BY ROLLUP을 떠올릴 수 있고 그 생각이 틀리지도 않다. 그러나 그 그룹화 집합은 꽤 커지고(GROUP BY ROLLUP (system_id, day, kwh)), 평균값을 추가 컬럼이 아니라 추가 행으로 만들어낸다. 찾던 값—시스템별 전체 발전량—은 시스템 값이 있고 날짜 값이 없는 행에서 발견될 것이다.

추가 행을 다루지 않는 다른 방법은 자기 조인이다. 같은 테이블을 다시 조인해 원하는 집계를 키로 묶어 고르는 것이다. 원하는 결과는 나오지만 읽기 어렵고, 테이블 전체가 두 번 스캔되므로 성능도 좋지 않을 것이다. 파티션 창 맥락에서 avg를 쓰는 편이 훨씬 읽기 쉽고 성능도 좋다.

top-n 질의는 스트리밍 서비스를 운영하며 상위 n개 차트를 보여주고 싶을 때 요긴하다. 그러나 이 예제에서 더 흥미로운 질문은 이것이다. 시스템 전체에서 생산된 에너지의 7일 이동평균은 얼마인가. 이 질문에 답하려면 세 가지를 해야 한다.

• 15분 간격의 측정값을 일 단위로 집계한다 — 그룹화와 합산.
• 일자와 시스템으로 파티션을 나눈다.
• 7일 크기의 프레임을 만든다.

여기서 프레이밍이 등장한다. 프레이밍은 함수가 평가되는 각 행에 대해 상대적인 행의 집합을 지정한다. 현재 행으로부터의 거리는 현재 행에 선행하거나 후행하는 표현식으로 주어진다. 이 거리는 정수 개의 행으로 지정할 수도 있고, 정렬 표현식의 값으로부터의 범위 델타 표현식으로 지정할 수도 있다. 이 두 갈래가 곧 ROWS와 RANGE다.

가독성을 위해, 그리고 예제가 창 정의에 집중하도록, 제3장에서 정의한 뷰 v_power_per_day를 쓴다. 시스템별·일자별 kWh를 돌려주는 그 뷰다. CTE로 표현해도 마찬가지로 쉬웠을 것이다.

말로만 읽으면 프레임은 좀처럼 손에 잡히지 않는다. 아래는 원서 〈도판 4.3〉이 보여주는 10번 시스템의 2019년 1월 첫 15일을 그대로 옮겨놓고, 현재 행을 옮겨가며 프레임이 어디까지 걸치는지, 그 안의 평균이 어떻게 계산되는지 직접 확인할 수 있게 만든 장치다. 프레임 정의도 세 가지로 갈아 끼울 수 있다.

프레임의 성질 하나가 이 장치에서 곧바로 눈에 들어온다. 파티션의 시작과 끝에서는 프레임이 잘린다. 첫 행의 창에는 선행하는 3일이 없으므로 네 행만 들어가고, 그래서 첫 행의 평균은 뒤쪽 값들에 끌려간다. 이동평균 차트의 양 끝이 늘 수상해 보이는 이유가 여기 있다.

명명 윈도 4.5.3

범위를 가진 창을 논하며 방금 배운 대로, 창 정의는 꽤 복잡해질 수 있다. 파티션의 정의, 순서, 그리고 창의 실제 범위를 모두 담을 수 있기 때문이다. 그런데 주어진 창 위에서 집계 하나만 필요한 것이 아닐 때가 있다. 창 정의를 계속 되풀이하는 것은 고된 일이다.

이 도메인—태양광 시스템의 발전량 측정—에서라면 분위수를 써서 계절과 날씨를 함께 담는 보고서를 만들 수 있다. 월별로 7일 창의 분위수를 계산하는 것이다. 넓은 월별 평균으로도 충분할 때가 있지만, 그런 차트는 달마다 변하는 비교적 매끄러운 곡선만 보여준다. 한 주 동안 변하는 날씨를 거치며 발전량의 요동은 더 크다. 이상치와 튀는 값은 분위수로 잡아 표현하는 편이 낫다. 그 결과는 예컨대 움직이는 상자수염 그림을 만드는 데 쉽게 쓸 수 있다.

이상치를 잡고 분위수를 계산하려면 집계 셋(min, max, 분위수)이 필요하고, 창을 매번 정의하고 싶지는 않다. 목록 4.17의 정의를 그대로 가져와 측정값의 월을 파티션에 더한다. 그 밖에는 창 정의가 같다. 정의를 FROM 절 뒤로 옮겨 seven_days라는 이름을 준다. 필요한 만큼 여러 집계에서 참조할 수 있다.

파티션 안에서 앞뒤 행에 접근하기 4.5.4

순위 매기기는 이미 논했고 누적 합계의 예는 4.8절에서 보겠지만, 파티션 안에서 행 사이를 앞뒤로 뛰어다니는 능력은 아직 쓰지 않았다. 변화량을 계산해보는데, 요즘 시세만큼 좋은 예가 또 있겠는가.

제3장에서 만든 prices 테이블은 독일에서 에너지를 계통에 되팔 때의 판매 가격(ct/kWh)을 담고 있다. 그 판매 가격이 재생에너지 장려 정책의 유인 축소에 반응해 이후 하락했다고 하자. 이제 재생에너지에 대한 보상이 시간에 따라 얼마나 변했는지 알고 싶다. 차이를 계산하려면 n번째 행의 가격 값이 필요하고, 그것을 n-1번째 행의 값과 비교해야 한다. 테이블의 행은 본질적으로 한 행씩 고립되어 처리되므로 창 없이는 불가능하다. 그러나 정렬 가능한 아무 컬럼에나 창을 걸치면 lag()와 lead()로 현재 창 밖의 행에 접근할 수 있다. 어제의 가격을 골라 오늘 가격과 비교할 수 있게 되는 것이다.

lag 함수는 파티션 안에서 현재 행에 선행하는 행의 표현식 값을 주거나, 그런 행이 없으면 NULL을 준다. 파티션의 첫 행이 그 경우다. lead는 반대로 동작하니 파티션의 마지막 행에서 NULL을 돌려준다. DuckDB에서 두 함수는 몇 행을 뒤로 또는 앞으로 갈지뿐 아니라 기본값까지 지정할 수 있는 여러 오버로드를 갖는다. 그렇지 않다면 NULL 값이 실용적이지 않을 때 coalesce를 쓰는 것도 선택이 된다.

2019년 가격의 총 변화량을 계산하려면 CTE를 써야 한다. 윈도 함수 호출을 집계 함수 안에 중첩할 수 없기 때문이다. 4.3절에서 본 "집계는 중첩할 수 없다"의 사촌 격인 제약이다.

계산된 집계도, 윈도 함수의 결과도 표준 WHERE 절로는 걸러낼 수 없다. 그런데 그 필터링은 다음 같은 질문에 답하기 위해 필요하다. 집계값이 x를 넘는 그룹을 고르려면 HAVING 절을 써야 하고, 며칠의 범위에서 특정 값을 넘는 데이터를 고르려면 QUALIFY 절을 써야 한다. 여기에 더해, 값이 집계 함수에 애초에 들어가지 못하게 걸러내려면 FILTER 절이 필요하다.

HAVING 절 쓰기 4.6.1

발전량이 900kWh를 넘는 날을 모두 주시오! 제3장에서 WHERE 절과 GROUP BY의 작동을 배웠으니 그 둘을 조합해볼 만하다. 그런데 실패한다.

QUALIFY 절 쓰기 4.6.2

윈도 함수의 결과가 어떤 필터에 맞는 행만 돌려받고 싶다고 하자. 그 필터를 WHERE 절에 넣을 수 없다. 창에 포함되어야 할 행들을 걸러내버릴 것이고, 우리는 윈도 함수의 결과를 써야 하기 때문이다. 그런데 HAVING도 쓸 수 없다. 윈도 함수는 집계보다 먼저 평가되기 때문이다. 그래서 QUALIFY가 윈도 함수의 결과에 필터를 걸게 해준다.

FILTER 절 쓰기 4.6.3

집계나 평균, 값의 개수를 계산하려는데 포함하고 싶지 않은 행이 있다는 것을 알아차릴 때가 있다. 그런데 복잡한 질의에서는 다른 필드를 계산하기 위해 그 행들을 남겨두어야 할 수도 있다. 예컨대 이따금 음수로 나타나는 잘못된 측정값이 있다고 하자. 측정값의 총 개수와 센서의 평균 측정값을 함께 계산하고 싶다. 잘못된 측정값을 WHERE 절에서 걸러내면 총 개수를 계산할 수 없다. 그러나 모든 측정값을 그냥 평균 내면 잘못된 음수값이 섞여 들어간다. 이런 유형의 문제를 풀려면 집계의 일부로 FILTER 표현식을 쓴다.

4.1절로 돌아가 보면, 우리는 실은 NULL 값을 평균에 끌어들이는 바로 그 문제에 놓여 있었다. 대개 원하는 바가 아니다. NULL 값을 0으로 눌러두는 대신, 평균에서 아예 걸러낼 수 있다.

한 질의에 집계를 여럿 담을 수 있고, 그 각각에 개별적으로 필터를 걸 수 있다. 그러면 다음 같은 과제에 답할 수 있다. 시스템별·연도별 에너지 생산량 보고서를 원하는데, 연도는 컬럼이 되어야 한다! 시스템별 집계도 쉽고 연도별 집계도 쉽다. 두 키로 묶는 것도 어렵지 않다. 그러나 그렇게 하면 연도가 행으로 나오고 컬럼으로 나오지 않는다. 원하는 것은 세 행에 2019와 2020이라는 두 컬럼, 스프레드시트 프로그램에서 보던 그 모양이다. 그런 표를 재조직하는 과정을 피벗이라 부른다.

셀 값에 집계를 쓰는 경우, ON 절에 속하지 않은 모든 컬럼이 그 집계의 그룹 키로 쓰인다. 그런데 집계를 쓰지 않아도 된다. 그러면 흥미로운 일이 벌어진다. PIVOT v_power_per_day ON day는 1,382행 545컬럼의 결과를 만든다. 왜 그런가. v_power_per_day에는 (system_id, kWh)의 서로 다른 값이 1,382개 있어 그것이 행을 이루고, year(day)가 아니라 day로 컬럼을 만들라고 했으니 기록된 543개의 서로 다른 날이 컬럼이 되고, 여기에 system_id와 kWh 두 컬럼이 더해진다.

그러면 셀에는 무엇이 들어 있는가. 수많은 0과 몇 개의 1이다. USING 절이 없으면 DuckDB는 해당 값이 없던 날의 셀에 0을, 있던 날의 셀에 1을 채운다. 그러므로 모든 날을 표 형태로 보고 싶다면 first 집계를 쓰는 편이 좋다.

변동성 있는 상품을 하루 중 임의의 시각에 판다고 상상해보자. 가격은 어떤 간격—15분이라 하자—으로 예측할 수 있고, 그것이 얻을 수 있는 정밀도의 한계다. 그런데 사람들은 아무 때나 상품을 요구한다. 그래서 다음 같은 상황이 벌어진다. 가격 표에 한 시간짜리 항목 넷, 판매 표에 열두 항목이 있는데, 이를 순진하게 조인하면 열두 건의 판매 가운데 넉 건만 결과로 나온다.

ASOF JOIN은 주식 시세, 가격, IoT 센서처럼 시계열 데이터를 다룰 때 자주 쓰인다. 예제에서라면 변하는 판매 가격을 시스템의 측정값과 조인해 임의 시점의 가격을 계산하는 데 쓸 수 있다. 마지막 예제는 다시 태양광 예제 데이터를 쓰면서, ASOF JOIN이 이 장에서 배운 다른 구성물—가격이 서로 다른 판매 기간의 누적 수익을 구하는 창—과 함께 쓰일 수 있음을 보여준다.

SQL의 대부분 함수는 매개변수를 받아 하나의 값을 돌려준다. 반면 테이블 함수는 하나의 값이 아니라 행의 모음을 돌려준다. 그러므로 테이블이 나타날 수 있는 어느 자리에나 나타날 수 있다. 기능에 따라서는 파일이나 URL 같은 외부 자원에 접근해 그것을 표준 SQL 문장의 일부인 관계로 바꿔놓을 수 있다. 테이블을 만들어내는 함수라는 개념을 지원하는 관계형 데이터베이스가 DuckDB만은 아니지만, DuckDB는 많은 쓰임새를 감당하는 인상적인 테이블 함수 집합을 갖고 온다.

range(start, stop)와 generate_series(start, stop)는 대단히 요긴한 테이블 함수 둘이다. 둘 다 start와 stop 사이 범위의 값 목록을 만든다. 차이는 경계에 있다.

• start는 두 함수 모두 포함한다.
• stop은 range에서는 배제, generate_series에서는 포함이다.
• 둘 다 세 번째 매개변수 step으로 걸음 크기를 정하는 오버로드를 제공하며, 기본값은 1이다.
• stop만 받고 start를 0으로 기본 설정하는 변종도 있다.

보통 함수로 쓰이면 유용한 구성물이지만, 테이블처럼 질의할 때 훨씬 강력해진다. 1에서 5 사이 숫자 목록이 필요하고 하드코딩하고 싶지 않다면 SELECT generate_series(1, 5);를 쓴다. 숫자도 도움이 되지만 이 함수들은 시간 데이터에도 통한다. 다만 시간 데이터를 쓸 때는 start와 end 매개변수를 모두 지정해야 한다. 둘 다 합리적인 기본값이 없기 때문이다.

이제 실용으로 옮긴다. 예제 데이터의 측정값은 2020년 중반에 끝난다. 이에 기반한 보고서는 한 해 전체를 의도했다면 조기에 끊긴다.

이 착상을 한 걸음 더 밀면, 전년 같은 달의 값을 써서 생산량을 예측할 수 있다. 그러려면 v_power_per_day를 1년의 오프셋을 두고 한 번 더 조인한다.

4.3절에서 상관·비상관 서브쿼리를 배웠고, 목록 4.5는 비상관 서브쿼리가 바깥 질의와 한 번 조인될 수 있음을 보여주었다. 성능 관점에서는 그것이 이로울 수 있다. 서브쿼리는 한 번만 평가되고, 그다음 조인이 기억된 값들에 대해 다른 테이블의 행마다 수행되기 때문이다.

그런데 때로는 바깥 질의의 값마다 안쪽 질의를 정확히 평가하고 싶다. 여기서 LATERAL JOIN이 등장한다. 저자들이 준 비유가 명료하다. 바깥 질의를 제어 구조로 삼는 for 루프의 안쪽 블록이라고 생각하면 된다. 배열을 펼치는 일, 데이터를 부채처럼 넓히는 일 따위를 LATERAL로 다룰 수 있다.

예제의 소재가 이 장에서 가장 구체적이다. 태양의 세기, 구체적으로는 그 에너지가 하루의 특정 시각에 내 자리에 얼마나 도달하는가에 관심이 있다고 하자. 과거든 미래든. Open Meteo는 폭넓은 날씨 데이터를 제공하는 무료 API를 내놓고 있고, 그중에 전천 일사량(GHI, global horizontal irradiance)이 있다. 지면과 수평인 표면이 위에서 받는 단파 복사의 총량이며, 태양광 설비에 특히 관심 있는 값이고 W/m² 단위로 측정된다.

첫눈에는 SQL로 그 배열에서 아침, 정오, 저녁 시각을 골라내는 일이 벅찬 과제처럼 보인다. LATERAL이 이 과제를 어떻게 푸는지 보자. 제1장에서 DuckDB가 JSON을 처리할 수 있다고 읽었고 제5장에서 더 자세히 살펴볼 것이지만, 지금은 FROM 절에서 다른 어떤 테이블처럼 JSON 파일에서 고를 수 있다는 것만 알면 된다.

서브쿼리는 구동하는 바깥 테이블의 행마다 0개, 1개, 또는 더 많은 행을 만들어낼 수 있다. 앞의 예제에서는 바깥 행마다 한 행을 만들었다. 서브쿼리가 더 많은 행을 만들면 바깥 행의 값이 CROSS JOIN과 비슷한 방식으로 되풀이된다. 서브쿼리가 아무 값도 만들지 않으면 조인도 값을 만들지 않는다. 이 경우에는 OUTER JOIN을 적용해야 하며, 그 지점에서는 LATERAL 키워드만으로는 충분하지 않고 온전한 JOIN 문법을 써야 한다.

4.8절의 가격 문제도 서브쿼리와 LATERAL JOIN으로 풀 수 있다. 요컨대 서브쿼리는 판매 시점에 시간적으로 가장 가까운 유효 기간을 가진 가격 행을 돌려주어야 한다. 그것이 되려면 보통의 JOIN을 쓸 수 없다. 서브쿼리가 들어오는 날짜마다 다른 값을 만들어야 하기 때문이다. 따라서 보통이라면 JOIN의 일부였을 날짜 컬럼이 서브쿼리 안으로 옮겨가야 하고, 그럼으로써 조인된 서브쿼리는 상관되거나, 곧 바깥 질의에 lateral하게 조인된다.