> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

<div id="viewing-sql">
  ## Ver el SQL generado
</div>

```python title="Query" theme={null}
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())
```

```sql title="Response" theme={null}
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
```

***

<div id="basic">
  ## Correspondencia de operaciones básicas
</div>

<div id="filtering-where">
  ### Filtrado (WHERE)
</div>

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

<div id="selection-select">
  ### Selección (SELECT)
</div>

| 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 *` |

<div id="sorting-order-by">
  ### Ordenar (ORDER BY)
</div>

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

***

<div id="groupby">
  ## GroupBy y agregación
</div>

<div id="basic-groupby">
  ### GroupBy básico
</div>

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

<div id="aggregation-functions">
  ### Funciones de agregación
</div>

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

<div id="multiple-aggregations">
  ### Agregaciones múltiples
</div>

```python theme={null}
# 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
```

<div id="having-clause">
  ### Cláusula HAVING
</div>

```python theme={null}
# 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
```

***

<div id="joins">
  ## Joins
</div>

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

<div id="join-example">
  ### Ejemplo de join
</div>

```python theme={null}
# 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
```

***

<div id="string">
  ## Operaciones con cadenas
</div>

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

***

<div id="datetime">
  ## Operaciones de fecha y hora
</div>

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

***

<div id="arithmetic">
  ## Operaciones aritméticas
</div>

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

***

<div id="null">
  ## Tratamiento de NULL
</div>

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

***

<div id="example">
  ## Ejemplo completo
</div>

<div id="pandas-code">
  ### Código en pandas
</div>

```python theme={null}
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
)
```

<div id="equivalent-sql">
  ### SQL equivalente
</div>

```sql theme={null}
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
```

<div id="datastore-code">
  ### Código de DataStore
</div>

```python theme={null}
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())
```

***

<div id="summary">
  ## Resumen de palabras clave de SQL
</div>

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

***

<div id="tips">
  ## Consejos para usuarios de pandas
</div>

<div id="think-in-sql">
  ### 1. Piensa en operaciones SQL
</div>

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

```python theme={null}
# Si quieres: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# Escribe:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)
```

<div id="use-to-sql">
  ### 2. Aprende con to\_sql()
</div>

```python theme={null}
# 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())
```

<div id="leverage-sql-features">
  ### 3. Aprovecha las capacidades de SQL
</div>

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

```python theme={null}
# 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))
})
```