Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

SQL para usuarios de pandas

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

Querypython
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())
Responsesql
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))
})
Navigation