Visualizando o SQL gerado
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)
)
# Visualizar o 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
Mapeamento das operações básicas
Filtragem (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 |
Seleção (SELECT)
| pandas | SQL |
|---|---|
df['col'] | SELECT col |
df[['a', 'b', 'c']] | SELECT a, b, c |
df.head(10) | LIMIT 10 |
df.tail(10) | Mais complexo (ORDER BY … DESC LIMIT 10) |
df.drop_duplicates() | SELECT DISTINCT * |
Ordenação (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 e agregação
GroupBy básico
| 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 |
Funções de agregação
| 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() |
Várias agregações
# 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
Cláusula HAVING
# estilo pandas
df.groupby('city')['sales'].sum().query('sales > 10000')
# estilo 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
Junções
| 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 |
Exemplo de JOIN
# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')
# Equivalente em SQL
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
Operações com strings
| 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) |
Operações com 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) |
Operações aritméticas
| 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) |
Tratamento de NULL
| pandas | SQL |
|---|---|
df['col'].isna() | isNull(col) |
df['col'].notna() | isNotNull(col) |
df.dropna() | WHERE col IS NOT NULL (para cada coluna) |
df.fillna(0) | ifNull(col, 0) |
df.fillna({'a': 0, 'b': 'x'}) | ifNull(a, 0), ifNull(b, 'x') |
Exemplo completo
Código em pandas
import pandas as pd
df = pd.read_csv("sales.csv")
result = (df
[df['date'] >= '2024-01-01'] # Filtrar
[df['amount'] > 100] # Filtrar
[['region', 'category', 'amount']] # Selecionar colunas
.groupby(['region', 'category']) # Agrupar
.agg({
'amount': ['sum', 'mean', 'count']
})
.reset_index() # Nivelar
.query('amount_sum > 10000') # HAVING
.sort_values('amount_sum', ascending=False) # Ordenar
.head(20) # Limitar
)
SQL equivalente
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
Código do 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)
)
# Visualizar o SQL gerado
print(result.to_sql())
Resumo das palavras-chave SQL
| Operação do pandas | Cláusula 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 |
Dicas para usuários de pandas
1. Pense em operações em SQL
# Se quiser: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# Escreva:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)
2. Aprenda com to_sql()
# Veja como seu código pandas se torna SQL
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())
3. Aproveite os recursos do SQL
# Funções de janela
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')
# Agregação condicional
ds.groupby('region').agg({
'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})