SQL para usuarios de pandas

Cómo se traducen las operaciones de pandas a SQL en DataStore

DataStore compila las operaciones al estilo de pandas en SQL optimizado. Esta guía ayuda a los usuarios de pandas a entender el SQL que hay detrás de sus operaciones.

​

Ver el SQL generado

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)
)

# Ver el 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

---

​

Correspondencia de operaciones básicas

​

Filtrado (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

​

Selección (SELECT)

pandas	SQL
df['col']	SELECT col
df[['a', 'b', 'c']]	SELECT a, b, c
df.head(10)	LIMIT 10
df.tail(10)	Complejo (ORDER BY ... DESC LIMIT 10)
df.drop_duplicates()	SELECT DISTINCT *

​

Ordenar (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 y agregación

​

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

​

Funciones de agregación

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()

​

Agregaciones múltiples

# 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

---

​

Joins

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

​

Ejemplo de join

# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')

# Equivalente en SQL
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

---

​

Operaciones con cadenas

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)

---

​

Operaciones de fecha y hora

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)

---

​

Operaciones 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)

---

​

Tratamiento de NULL

pandas	SQL
df['col'].isna()	isNull(col)
df['col'].notna()	isNotNull(col)
df.dropna()	WHERE col IS NOT NULL (para cada columna)
df.fillna(0)	ifNull(col, 0)
df.fillna({'a': 0, 'b': 'x'})	ifNull(a, 0), ifNull(b, 'x')

---

​

Ejemplo completo

​

Código en 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']]        # Seleccionar columnas
    .groupby(['region', 'category'])          # Agrupar
    .agg({
        'amount': ['sum', 'mean', 'count']
    })
    .reset_index()                            # Aplanar
    .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 de 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)
)

# Ver el SQL generado
print(result.to_sql())

---

​

Resumen de palabras clave de SQL

Operación de 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

---

​

Consejos para usuarios de pandas

​

1. Piensa en operaciones SQL

Al escribir código con DataStore, piensa en qué consulta SQL te gustaría usar:

# Si quieres: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# Escribe:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)

​

2. Aprende con to_sql()

# Observa cómo tu código de pandas se convierte en SQL
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())

​

3. Aprovecha las capacidades de SQL

DataStore te ofrece la potencia de SQL con la sintaxis de pandas:

# Funciones de ventana
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')

# Agregación condicional
ds.groupby('region').agg({
    'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})