생성된 SQL 확인
Query
from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")
from chdb import datastore as pd
ds = pd.read_csv("sales.csv")
query = (ds
.filter(ds['amount'] > 1000)
.groupby('region')
.agg({'amount': ['sum', 'mean']})
.sort('sum', ascending=False)
.head(10)
)
# SQL 확인
print(query.to_sql())
Response
SELECT region, SUM(amount) AS sum, AVG(amount) AS mean
FROM file('sales.csv', 'CSVWithNames')
WHERE amount > 1000
GROUP BY region
ORDER BY sum DESC
LIMIT 10
기본 작업 대응표
필터링 (WHERE)
| pandas | SQL |
|---|---|
df[df['age'] > 25] | WHERE age > 25 |
df[df['city'] == 'NYC'] | WHERE city = 'NYC' |
df[(df['x'] > 10) & (df['y'] < 20)] | WHERE x > 10 AND y < 20 |
df[(df['a'] == 1) | (df['b'] == 2)] | WHERE a = 1 OR b = 2 |
df[~(df['status'] == 'inactive')] | WHERE NOT status = 'inactive' |
df[df['col'].isin([1, 2, 3])] | WHERE col IN (1, 2, 3) |
df[df['val'].between(10, 20)] | WHERE val BETWEEN 10 AND 20 |
df[df['name'].str.contains('John')] | WHERE position('John' IN name) > 0 |
선택 (SELECT)
| pandas | SQL |
|---|---|
df['col'] | SELECT col |
df[['a', 'b', 'c']] | SELECT a, b, c |
df.head(10) | LIMIT 10 |
df.tail(10) | 복잡함 (ORDER BY ... DESC LIMIT 10) |
df.drop_duplicates() | SELECT DISTINCT * |
정렬 (ORDER BY)
| pandas | SQL |
|---|---|
df.sort_values('col') | ORDER BY col ASC |
df.sort_values('col', ascending=False) | ORDER BY col DESC |
df.sort_values(['a', 'b']) | ORDER BY a ASC, b ASC |
df.sort_values(['a', 'b'], ascending=[True, False]) | ORDER BY a ASC, b DESC |
df.nlargest(10, 'col') | ORDER BY col DESC LIMIT 10 |
df.nsmallest(5, 'col') | ORDER BY col ASC LIMIT 5 |
GroupBy와 집계
기본적인 GroupBy
| pandas | SQL |
|---|---|
df.groupby('city')['sales'].sum() | SELECT city, SUM(sales) FROM ... GROUP BY city |
df.groupby('city')['sales'].mean() | SELECT city, AVG(sales) FROM ... GROUP BY city |
df.groupby('city').size() | SELECT city, COUNT(*) FROM ... GROUP BY city |
df.groupby(['a', 'b'])['c'].sum() | SELECT a, b, SUM(c) FROM ... GROUP BY a, b |
집계 함수
| pandas | SQL |
|---|---|
sum() | SUM() |
mean() | AVG() |
count() | COUNT() |
min() | MIN() |
max() | MAX() |
std() | stddevPop() |
var() | varPop() |
median() | MEDIAN() |
nunique() | COUNT(DISTINCT col) |
first() | any() |
last() | anyLast() |
다중 집계
# pandas
df.groupby('city').agg({
'sales': ['sum', 'mean'],
'quantity': 'sum'
})
# SQL
SELECT city,
SUM(sales) AS sales_sum,
AVG(sales) AS sales_mean,
SUM(quantity) AS quantity_sum
FROM data
GROUP BY city
HAVING 절
# pandas 스타일
df.groupby('city')['sales'].sum().query('sales > 10000')
# DataStore 스타일
ds.groupby('city').agg({'sales': 'sum'}).having(ds['sum'] > 10000)
# SQL
SELECT city, SUM(sales) AS sum
FROM data
GROUP BY city
HAVING sum > 10000
조인
| pandas | SQL |
|---|---|
pd.merge(df1, df2, on='id') | JOIN df2 ON df1.id = df2.id |
pd.merge(df1, df2, on='id', how='left') | LEFT JOIN df2 ON ... |
pd.merge(df1, df2, on='id', how='right') | RIGHT JOIN df2 ON ... |
pd.merge(df1, df2, on='id', how='outer') | FULL OUTER JOIN df2 ON ... |
pd.merge(df1, df2, left_on='a', right_on='b') | JOIN df2 ON df1.a = df2.b |
조인 예시
# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')
# SQL에 해당하는 표현
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
문자열 연산
| pandas | SQL |
|---|---|
df['col'].str.upper() | upper(col) |
df['col'].str.lower() | lower(col) |
df['col'].str.len() | length(col) |
df['col'].str.strip() | trim(col) |
df['col'].str.contains('x') | position('x' IN col) > 0 |
df['col'].str.startswith('x') | startsWith(col, 'x') |
df['col'].str.endswith('x') | endsWith(col, 'x') |
df['col'].str.replace('a', 'b') | replace(col, 'a', 'b') |
df['col'].str[:5] | substring(col, 1, 5) |
DateTime 연산
| pandas | SQL |
|---|---|
df['date'].dt.year | toYear(date) |
df['date'].dt.month | toMonth(date) |
df['date'].dt.day | toDayOfMonth(date) |
df['date'].dt.hour | toHour(date) |
df['date'].dt.dayofweek | toDayOfWeek(date) |
df['date'].dt.quarter | toQuarter(date) |
산술 연산
| pandas | SQL |
|---|---|
df['a'] + df['b'] | a + b |
df['a'] - df['b'] | a - b |
df['a'] * df['b'] | a * b |
df['a'] / df['b'] | a / b |
df['a'] // df['b'] | intDiv(a, b) |
df['a'] % df['b'] | a % b |
df['a'] ** 2 | pow(a, 2) |
df['a'].abs() | abs(a) |
df['a'].round(2) | round(a, 2) |
NULL 처리
| pandas | SQL |
|---|---|
df['col'].isna() | isNull(col) |
df['col'].notna() | isNotNull(col) |
df.dropna() | WHERE col IS NOT NULL (각 열에 대해) |
df.fillna(0) | ifNull(col, 0) |
df.fillna({'a': 0, 'b': 'x'}) | ifNull(a, 0), ifNull(b, 'x') |
전체 예시
pandas 코드
import pandas as pd
df = pd.read_csv("sales.csv")
result = (df
[df['date'] >= '2024-01-01'] # 필터
[df['amount'] > 100] # 필터
[['region', 'category', 'amount']] # 컬럼 선택
.groupby(['region', 'category']) # 그룹
.agg({
'amount': ['sum', 'mean', 'count']
})
.reset_index() # 평탄화
.query('amount_sum > 10000') # Having
.sort_values('amount_sum', ascending=False) # 정렬
.head(20) # 제한
)
이에 해당하는 SQL
SELECT
region,
category,
SUM(amount) AS amount_sum,
AVG(amount) AS amount_mean,
COUNT(amount) AS amount_count
FROM file('sales.csv', 'CSVWithNames')
WHERE date >= '2024-01-01'
AND amount > 100
GROUP BY region, category
HAVING amount_sum > 10000
ORDER BY amount_sum DESC
LIMIT 20
DataStore 코드
from chdb import datastore as pd
ds = pd.read_csv("sales.csv")
result = (ds
.filter(ds['date'] >= '2024-01-01')
.filter(ds['amount'] > 100)
.select('region', 'category', 'amount')
.groupby('region', 'category')
.agg({'amount': ['sum', 'mean', 'count']})
.having(ds['sum'] > 10000)
.sort('sum', ascending=False)
.head(20)
)
# 생성된 SQL 확인
print(result.to_sql())
SQL 키워드 요약
| pandas 연산 | SQL 절 |
|---|---|
df[condition] | WHERE |
df[['a', 'b']] | SELECT a, b |
df.groupby('x') | GROUP BY x |
.agg({'col': 'sum'}) | SUM(col) |
.sort_values('x') | ORDER BY x |
.head(n) | LIMIT n |
pd.merge() | JOIN |
.drop_duplicates() | DISTINCT |
.having() | HAVING |
pandas 사용자를 위한 팁
1. SQL 연산 단위로 생각하기
# 원하는 쿼리: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# 작성 방법:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)
2. to_sql()로 익히기
# pandas 코드가 SQL로 어떻게 변환되는지 확인하세요
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())
3. SQL 기능 활용하기
# 윈도우 함수
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')
# 조건부 집계
ds.groupby('region').agg({
'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})