https://2stndard.tistory.com/notice/203
[발간예정][EPL과 유튜브로 배우는 DuckDB] 실습 코드와 데이터
EPL과 유튜브 데이터로 배우는 DuckDB에서 사용되는 실습 데이터와 코드를 제공합니다. EPL_DATA&samplefile.zip : 책에서 사용하는 영국 프리미어리그 데이터 셋과 샘플로 사용하는 파일espn.duckdb.zip : 책
2stndard.tistory.com
DuckDB의 뛰어난 안정성과 성능에 대해 들어보셨을 겁니다. 한번 사용해 보고 싶으실 수도 있겠지만, 아마도 데이터 과학자로서 SQL보다는 pandas에 더 익숙하실 겁니다. SELECT, JOIN, GROUP BY 명령어는 사용할 수 있겠지만 그 이상은 어렵고, 이런 의문이 들 수도 있습니다. 과연 SQL을 사용해서 다음 작업을 할 수 있을까요?
- 변수를 중심화(즉, 평균을 뺀 값)하는 것?
- 시간별로 재표본 추출하는 것?
- 이동 평균 통계를 계산하는 것?
이 모든 것이 가능할 뿐만 아니라, 아주 쉽습니다. SQL에서 데이터프레임의 기본 기능을 구현하는 방법을 배워봅시다!
하지만 우선, 왜일까요?
애초에 왜 DuckDB나 SQL을 사용해야 할까요? 어차피 데이터프레임 API가 더 가독성이 좋고 표현력이 뛰어나지 않나요? 어느 정도는 그렇다고 할 수 있습니다. 그럼에도 불구하고, 가능하다면 DuckDB SQL 솔루션을 구현해야 할 아주 타당한 이유가 몇 가지 있다고 생각합니다.
- 안정성: 데이터프레임 API는 API 개선을 위해 주기적으로 기능이 사용 중단되는 경향이 있습니다. 오늘 데이터프레임 솔루션을 작성한다고 해도, 5년 후에도 여전히 작동하지 않을 수 있습니다. 하지만 SQL은 아마도 작동할 것입니다.
- 이식성: SQL은 표준이 존재하기 때문에 구현상의 차이는 있더라도 각 DB의 전용 SQL 간의 마이그레이션은 데이터프레임 API 간 마이그레이션보다 수월할 것입니다.
- 보편성: 다양한 산업 분야의 분석가, 엔지니어, 데이터 과학자들은 모두 SQL에 익숙할 가능성이 높습니다. 모두가 SQL을 가장 선호하는 언어로 꼽지는 않겠지만, 특히 LLM의 도움을 받으면 누구나 읽을 수 있을 것입니다.
- 견고성: sqllogictest와 같은 포괄적인 SQL 테스트 프레임워크가 이미 개발되어 있으므로, DuckDB는 이를 활용해 버그가 있는 쿼리 결과를 방지할 수 있습니다.
또한, 기존 SQL에는 다소 성가신 규칙(예: "SELECT 문에서 마지막 표현식 뒤에는 쉼표를 붙이지 마라!")이 있는 경향이 있지만, DuckDB는 'Friendly SQL'을 통해 구문 측면에서 매우 편리하게 사용합니다.
이제 일반적인 데이터프레임 작업을 SQL로 변환하는 방법을 살펴보겠습니다.
평균 빼기(Subtracting the mean)
“중심화(centering)”라고도 불리는 평균을 빼는 작업은, 전통적인 회귀 모델을 적합하기 전에 수행되는 일반적인 데이터 과학 기법입니다. pandas나 Polars에서는 이 작업이 매우 간단합니다.
data = {"a": [1, 3, -1, 8]}
# pandas
import pandas as pd
df_pd = pd.DataFrame(data)
df_pd["a_centered"] = df_pd["a"] - df_pd["a"].mean()
# Polars
import polars as pl
df_pl = pl.DataFrame(data)
df_pl.with_columns(a_centered=pl.col("a") - pl.col("a").mean()) a a_centered
0 1 -1.75
1 3 0.25
2 -1 -3.75
3 8 5.25
하지만 이를 무턱대고 SQL로 변환하려고 하면 오류가 발생합니다:
import duckdb
duckdb.sql(
"""
SELECT *, a - MEAN(a) AS a_centered
FROM df_pl
"""
)BinderException: Binder Error: column "a" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(a)" if the exact value of "a" is not important.
SQL에서는 집계 함수를 사용하여 열을 비교할 수 없습니다. 이를 위해서는 각 행에 대해 값을 산출하는 함수인 윈도우 함수를 사용해야 합니다. 'a' 열 전체에 대해 평균을 구하므로 다음과 같이 작성합니다:
duckdb.sql(
"""
SELECT *, a - MEAN(a) OVER () AS a_centered
FROM df_pl
"""
)┌───────┬────────────┐
│ a │ a_centered │
│ int64 │ double │
├───────┼────────────┤
│ 1 │ -1.75 │
│ 3 │ 0.25 │
│ -1 │ -3.75 │
│ 8 │ 5.25 │
└───────┴────────────┘
Resampling: 주간 평균
다음과 같이 시간적 간격이 불규칙한 데이터가 있다고 가정해 보겠습니다.
from datetime import datetime
dates = [
datetime(2025, 1, 1), # Wednesday
datetime(2025, 1, 7), # Tuesday
datetime(2025, 1, 8), # Wednesday
datetime(2025, 1, 9), # Thursday
datetime(2025, 1, 16), # Thursday
datetime(2025, 1, 17), # Friday
]
sales = [1, 5, 0, 4, 3, 6]
data = {"date": dates, "sales": sales}
주간 평균 판매량을 구해야 한다면, 여기서는 한 주를 수요일부터 화요일까지로 봐야합니다. pandas의 resample을 다음과 같이 사용합니다.
# pandas
import pandas as pd
df_pd = pd.DataFrame(data)
df_pd.resample("1W-Wed", on="date", closed="left", label="left")["sales"].mean()date
2025-01-01 3.0
2025-01-08 2.0
2025-01-15 4.5
Freq: W-WED, Name: sales, dtype: float64
DuckDB에서 이와 같은 리샘플링을 하려면 전처리 단계가 필요합니다. 월요일부터 일요일까지인 주의 시작일인 월요일로 각 날짜를 자르기 위해 DATE_TRUNC('week', date) 함수를 사용합니다. 한 주의 시작일을 수요일로 설정하려면 먼저 2일을 빼고, 자른 다음, 다시 2일을 더해야 합니다. DATE_TRUNC('week', date - INTERVAL 2 DAYS) INTERVAL 2 DAYS AS week_start 와 같이 사용합니다.
import duckdb
duckdb.sql(
"""
SELECT
DATE_TRUNC('week', date - INTERVAL 2 DAYS) + INTERVAL 2 DAYS AS week_start,
AVG(sales) AS sales
FROM df_pd
GROUP BY week_start
ORDER BY week_start
"""
)┌─────────────────────┬────────┐
│ week_start │ sales │
│ timestamp │ double │
├─────────────────────┼────────┤
│ 2025-01-01 00:00:00 │ 3.0 │
│ 2025-01-08 00:00:00 │ 2.0 │
│ 2025-01-15 00:00:00 │ 4.5 │
└─────────────────────┴────────┘
이동 평균
금융 데이터 분석에서는 이동 평균이 매우 중요한 분석 데이터입니다.
from datetime import datetime
dates = [
datetime(2025, 1, 1),
datetime(2025, 1, 2),
datetime(2025, 1, 3),
datetime(2025, 1, 4),
datetime(2025, 1, 5),
datetime(2025, 1, 7),
]
sales = [2.0, 4.6, 1.32, 1.11, 9, 8]
data = {"date": dates, "sales": sales}
최근 3개 데이터의 이동 평균을 사용하여 '판매량' 데이터를 평활화할 수 있습니다. 데이터프레임을 사용하면 이 작업이 간단합니다.
# pandas
import pandas as pd
df_pd = pd.DataFrame(data)
df_pd["sales_smoothed"] = df_pd["sales"].rolling(3).mean() date sales sales_smoothed
0 2025-01-01 2.00 NaN
1 2025-01-02 4.60 NaN
2 2025-01-03 1.32 2.640000
3 2025-01-04 1.11 2.343333
4 2025-01-05 9.00 3.810000
5 2025-01-07 8.00 6.036667
이 데이터가 'date'(날짜)별로 정렬되어 있습니다. pandas를 사용할 때는 데이터가 특정 방식으로 정렬되어 있다는 사실을 이미 알고 있는 경우가 많고, 연산 과정에서도 그 순서가 유지되는 경우가 많습니다. 따라서 정렬을 전제로 이동 평균(rolling mean)을 계산하는 것이 괜찮습니다.
하지만 SQL 엔진에서는 일반적으로 행의 순서가 정의되어 있지 않습니다. (물론 DuckDB가 순서 유지를 보장하는 몇몇 예외적인 경우가 있긴 합니다.)
이에 대한 해결책은 윈도우 함수(window function) 내부에 'ORDER BY'를 명시하는 것입니다. 이렇게 하면 이동 평균을 계산할 때 어떤 컬럼을 기준으로 순서를 결정할지 엔진에 직접 알려줄 수 있습니다.
import duckdb
duckdb.sql(
"""
SELECT
*,
MEAN(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sales_smoothed_DuckDB
FROM df_pd
"""
)┌─────────────────────┬────────┬────────────────────┬───────────────────────┐
│ date │ sales │ sales_smoothed │ sales_smoothed_DuckDB │
│ timestamp_ns │ double │ double │ double │
├─────────────────────┼────────┼────────────────────┼───────────────────────┤
│ 2025-01-01 00:00:00 │ 2.0 │ NULL │ 2.0 │
│ 2025-01-02 00:00:00 │ 4.6 │ NULL │ 3.3 │
│ 2025-01-03 00:00:00 │ 1.32 │ 2.64 │ 2.64 │
│ 2025-01-04 00:00:00 │ 1.11 │ 2.3433333333333333 │ 2.3433333333333333 │
│ 2025-01-05 00:00:00 │ 9.0 │ 3.81 │ 3.81 │
│ 2025-01-07 00:00:00 │ 8.0 │ 6.036666666666666 │ 6.036666666666666 │
└─────────────────────┴────────┴────────────────────┴───────────────────────┘
이 방식은 pandas/Polars의 결과와 비슷해 보이지만, 완전히 동일하지는 않습니다. 데이터프레임 방식에서는 윈도우 크기(이 경우 3) 이상의 데이터가 확보될 때까지 첫 두 행을 null로 처리하는 반면, SQL(DuckDB) 방식은 모든 윈도우에 대해 평균을 계산하기 때문에 첫 부분도 값이 채워집니다.
이 차이를 해결하기 위해 CASE 문을 사용할 수 있습니다. (가독성을 위해 별도의 윈도우 함수 이름을 정의하는 방식도 함께 사용합니다.)
import duckdb
duckdb.sql(
"""
SELECT
*,
CASE WHEN (COUNT(sales) OVER w) >= 3
THEN MEAN(sales) OVER w
ELSE NULL
END AS sales_smoothed_DuckDB
FROM df_pd
WINDOW w AS (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
"""
)┌─────────────────────┬────────┬────────────────────┬───────────────────────┐
│ date │ sales │ sales_smoothed │ sales_smoothed_DuckDB │
│ timestamp_ns │ double │ double │ double │
├─────────────────────┼────────┼────────────────────┼───────────────────────┤
│ 2025-01-01 00:00:00 │ 2.0 │ NULL │ NULL │
│ 2025-01-02 00:00:00 │ 4.6 │ NULL │ NULL │
│ 2025-01-03 00:00:00 │ 1.32 │ 2.64 │ 2.64 │
│ 2025-01-04 00:00:00 │ 1.11 │ 2.3433333333333333 │ 2.3433333333333333 │
│ 2025-01-05 00:00:00 │ 9.0 │ 3.81 │ 3.81 │
│ 2025-01-07 00:00:00 │ 8.0 │ 6.036666666666666 │ 6.036666666666666 │
└─────────────────────┴────────┴────────────────────┴───────────────────────┘
"DuckDB를 엔진으로 사용하고 싶지만 파이썬 API를 선호한다면, 다음과 같은 방법을 사용할 수 있습니다."
- SQLFrame: PySpark API를 DuckDB를 포함한 다양한 백엔드로 변환해 줍니다.
- DuckDB's Python Relational API: 매우 엄격하고 견고하지만, 문서화가 다소 부족합니다. 특히 윈도우 함수(window expressions)는 아직 지원되지 않지만 로드맵에 포함되어 있습니다.
- Narwhals: Polars API를 다양한 백엔드로 변환합니다. DuckDB의 경우 'Python Relational API'를 사용하기 때문에, 역시 아직 윈도우 함수를 지원하지 않습니다.
- Ibis: 자체적인 API를 다양한 백엔드로 변환하여 실행합니다.
특히, DuckDB는 메모리에 있는 pandas나 Polars 데이터프레임을 대상으로 직접 쿼리를 실행할 수 있습니다. 도구들을 혼합해서 사용하는 것은 전혀 문제가 되지 않습니다. 사실 한 가지 도구만 고집하며 모든 것을 해결하려 하는 것보다, 여러 도구를 적재적소에 섞어 쓰는 것이 훨씬 더 효과적일 것입니다.
<출처: https://labs.quansight.org/blog/duckdb-when-used-to-frames>
'EPL과 유튜브 데이터로 배우는 DuckDB' 카테고리의 다른 글
| 2026년의 파이썬 데이터 분석 스택: DuckDB, Polars, 그리고 Pandas의 종말? (0) | 2026.05.15 |
|---|---|
| 모든 데이터 엔지니어가 더 이상 믿어서는 안 될 DuckDB 성능에 관한 7가지 오해 (0) | 2026.05.15 |
| DuckDB의 오브젝트 스토리지 캐싱(Object-Store Caching) (0) | 2026.05.13 |
| DuckDB와 다른 DB 비교해보기 (0) | 2026.05.09 |
| SQL 효율을 높이는 DuckDB 전용 SQL - Part 4 (0) | 2026.05.06 |
댓글