> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL for pandas users

> Understanding how pandas operations map to SQL in DataStore

DataStore compiles pandas-style operations into optimized SQL. This guide helps pandas users understand the SQL behind their operations.

<h2 id="viewing-sql">
  Viewing Generated SQL
</h2>

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

# View the 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
```

***

<h2 id="basic">
  Basic Operations Mapping
</h2>

<h3 id="filtering-where">
  Filtering (WHERE)
</h3>

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

<h3 id="selection-select">
  Selection (SELECT)
</h3>

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

<h3 id="sorting-order-by">
  Sorting (ORDER BY)
</h3>

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

***

<h2 id="groupby">
  GroupBy and Aggregation
</h2>

<h3 id="basic-groupby">
  Basic GroupBy
</h3>

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

<h3 id="aggregation-functions">
  Aggregation Functions
</h3>

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

<h3 id="multiple-aggregations">
  Multiple Aggregations
</h3>

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

<h3 id="having-clause">
  HAVING Clause
</h3>

```python theme={null}
# pandas style
df.groupby('city')['sales'].sum().query('sales > 10000')

# DataStore style
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
```

***

<h2 id="joins">
  Joins
</h2>

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

<h3 id="join-example">
  Join Example
</h3>

```python theme={null}
# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')

# SQL equivalent
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
```

***

<h2 id="string">
  String Operations
</h2>

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

***

<h2 id="datetime">
  DateTime Operations
</h2>

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

***

<h2 id="arithmetic">
  Arithmetic Operations
</h2>

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

***

<h2 id="null">
  NULL Handling
</h2>

| pandas                          | SQL                                    |
| ------------------------------- | -------------------------------------- |
| `df['col'].isna()`              | `isNull(col)`                          |
| `df['col'].notna()`             | `isNotNull(col)`                       |
| `df.dropna()`                   | `WHERE col IS NOT NULL` (for each col) |
| `df.fillna(0)`                  | `ifNull(col, 0)`                       |
| `df.fillna({'a': 0, 'b': 'x'})` | `ifNull(a, 0), ifNull(b, 'x')`         |

***

<h2 id="example">
  Complete Example
</h2>

<h3 id="pandas-code">
  pandas Code
</h3>

```python theme={null}
import pandas as pd

df = pd.read_csv("sales.csv")

result = (df
    [df['date'] >= '2024-01-01']              # Filter
    [df['amount'] > 100]                      # Filter
    [['region', 'category', 'amount']]        # Select columns
    .groupby(['region', 'category'])          # Group
    .agg({
        'amount': ['sum', 'mean', 'count']
    })
    .reset_index()                            # Flatten
    .query('amount_sum > 10000')              # Having
    .sort_values('amount_sum', ascending=False)  # Sort
    .head(20)                                 # Limit
)
```

<h3 id="equivalent-sql">
  Equivalent SQL
</h3>

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

<h3 id="datastore-code">
  DataStore Code
</h3>

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

# View the generated SQL
print(result.to_sql())
```

***

<h2 id="summary">
  SQL Keywords Summary
</h2>

| pandas Operation       | SQL Clause    |
| ---------------------- | ------------- |
| `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`      |

***

<h2 id="tips">
  Tips for pandas Users
</h2>

<h3 id="think-in-sql">
  1. Think in SQL Operations
</h3>

When writing DataStore code, think about what SQL you'd want:

```python theme={null}
# If you want: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# Write:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)
```

<h3 id="use-to-sql">
  2. Use to\_sql() to Learn
</h3>

```python theme={null}
# See how your pandas code becomes SQL
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())
```

<h3 id="leverage-sql-features">
  3. Leverage SQL Features
</h3>

DataStore gives you SQL power with pandas syntax:

```python theme={null}
# Window functions
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')

# Conditional aggregation
ds.groupby('region').agg({
    'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})
```