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

# Construction de requêtes DataStore

> Créez des requêtes de type SQL avec DataStore grâce au chaînage fluide de méthodes

DataStore fournit des méthodes de construction de requêtes de type SQL, compilées en requêtes SQL optimisées. Toutes les opérations sont différées tant que les résultats ne sont pas nécessaires.

<div id="overview">
  ## Vue d’ensemble des méthodes de requête
</div>

| Méthode            | Équivalent SQL  | Description                 |
| ------------------ | --------------- | --------------------------- |
| `select(*cols)`    | `SELECT cols`   | Sélectionner des colonnes   |
| `filter(cond)`     | `WHERE cond`    | Filtrer les lignes          |
| `where(cond)`      | `WHERE cond`    | Alias pour filter           |
| `sort(*cols)`      | `ORDER BY cols` | Trier les lignes            |
| `orderby(*cols)`   | `ORDER BY cols` | Alias pour sort             |
| `limit(n)`         | `LIMIT n`       | Limiter le nombre de lignes |
| `offset(n)`        | `OFFSET n`      | Ignorer des lignes          |
| `distinct()`       | `DISTINCT`      | Supprimer les doublons      |
| `groupby(*cols)`   | `GROUP BY cols` | Regrouper les lignes        |
| `having(cond)`     | `HAVING cond`   | Filtrer les groupes         |
| `join(right, ...)` | `JOIN`          | Joindre des DataStores      |
| `union(other)`     | `UNION`         | Combiner les résultats      |

***

<div id="selection">
  ## Sélection
</div>

<div id="select">
  ### `select`
</div>

Sélectionne des colonnes spécifiques à partir du DataStore.

```python theme={null}
select(*fields: Union[str, Expression]) -> DataStore
```

**Exemples :**

```python theme={null}
from chdb.datastore import DataStore
from pathlib import Path
Path("employees.csv").write_text("""\
name,age,city,salary,department,dept_id,status,email,manager_id,bonus
Alice,28,NYC,75000,Engineering,1,active,alice@company.com,3,5000
Bob,35,LA,85000,Engineering,1,active,bob@company.com,3,
Charlie,52,NYC,95000,Product,2,active,charlie@company.com,,10000
Diana,32,SF,70000,Design,3,active,diana@company.com,3,3000
Eve,23,LA,48000,Product,2,inactive,eve@company.com,2,
""")

ds = DataStore.from_file("employees.csv")

# Select by column names
result = ds.select('name', 'age', 'salary')

# Select all columns
result = ds.select('*')

# Select with expressions
result = ds.select(
    'name',
    (ds['salary'] * 12).as_('annual_salary'),
    ds['age'].as_('employee_age')
)

# Equivalent pandas style
result = ds[['name', 'age', 'salary']]
```

***

<div id="filtering">
  ## Filtrage
</div>

<div id="filter">
  ### `filter` / `where`
</div>

Filtrez les lignes selon des conditions. Les deux méthodes sont équivalentes.

```python theme={null}
filter(condition) -> DataStore
where(condition) -> DataStore  # alias
```

**Exemples :**

```python theme={null}
ds = DataStore.from_file("employees.csv")

# Single condition
result = ds.filter(ds['age'] > 30)
result = ds.where(ds['salary'] >= 50000)

# Multiple conditions (AND)
result = ds.filter((ds['age'] > 30) & (ds['department'] == 'Engineering'))

# Multiple conditions (OR)
result = ds.filter((ds['city'] == 'NYC') | (ds['city'] == 'LA'))

# NOT condition
result = ds.filter(~(ds['status'] == 'inactive'))

# String conditions
result = ds.filter(ds['name'].str.contains('John'))
result = ds.filter(ds['email'].str.endswith('@company.com'))

# NULL checks
result = ds.filter(ds['manager_id'].notnull())
result = ds.filter(ds['bonus'].isnull())

# IN condition
result = ds.filter(ds['department'].isin(['Engineering', 'Product', 'Design']))

# BETWEEN condition
result = ds.filter(ds['salary'].between(50000, 100000))

# Chained filters (AND)
result = (ds
    .filter(ds['age'] > 25)
    .filter(ds['salary'] > 50000)
    .filter(ds['city'] == 'NYC')
)
```

<div id="pandas-filtering">
  ### Filtrage façon Pandas
</div>

```python theme={null}
# Boolean indexing (equivalent to filter)
result = ds[ds['age'] > 30]
result = ds[(ds['age'] > 30) & (ds['salary'] > 50000)]

# Query method
result = ds.query('age > 30 and salary > 50000')
```

***

<div id="sorting">
  ## Tri
</div>

<div id="sort">
  ### `sort` / `orderby`
</div>

Trie les lignes par une ou plusieurs colonnes.

```python theme={null}
sort(*fields, ascending=True) -> DataStore
orderby(*fields, ascending=True) -> DataStore  # alias
```

**Exemples :**

```python theme={null}
ds = DataStore.from_file("employees.csv")

# Single column ascending
result = ds.sort('name')

# Single column descending
result = ds.sort('salary', ascending=False)

# Multiple columns
result = ds.sort('department', 'salary')

# Mixed order (use list for ascending parameter)
result = ds.sort('department', 'salary', ascending=[True, False])

# Pandas style
result = ds.sort_values('salary', ascending=False)
result = ds.sort_values(['department', 'salary'], ascending=[True, False])
```

***

<div id="limiting">
  ## Limites et pagination
</div>

<div id="limit">
  ### `limit`
</div>

Limitez le nombre de lignes retournées.

```python theme={null}
limit(n: int) -> DataStore
```

<div id="offset">
  ### `offset`
</div>

Ignore les n premières lignes.

```python theme={null}
offset(n: int) -> DataStore
```

**Exemples :**

```python theme={null}
ds = DataStore.from_file("employees.csv")

# First 10 rows
result = ds.limit(10)

# Skip first 100, take next 50
result = ds.offset(100).limit(50)

# Pandas style
result = ds.head(10)
result = ds.tail(10)
result = ds.iloc[100:150]
```

***

<div id="distinct">
  ## Distinct
</div>

<div id="distinct-method">
  ### `distinct`
</div>

Supprime les lignes dupliquées.

```python theme={null}
distinct(subset=None, keep='first') -> DataStore
```

**Exemples :**

```python theme={null}
from pathlib import Path
Path("events.csv").write_text("""\
user_id,event_type,timestamp
1,click,2024-01-15 10:30:00
2,view,2024-01-15 11:00:00
1,purchase,2024-01-15 11:30:00
3,click,2024-01-16 09:00:00
2,click,2024-01-16 10:00:00
""")

ds = DataStore.from_file("events.csv")

# Remove all duplicate rows
result = ds.distinct()

# Remove duplicates based on specific columns
result = ds.distinct(subset=['user_id', 'event_type'])

# Pandas style
result = ds.drop_duplicates()
result = ds.drop_duplicates(subset=['user_id'])
```

***

<div id="grouping">
  ## Regroupement
</div>

<div id="groupby">
  ### `groupby`
</div>

Regroupe les lignes par une ou plusieurs colonnes. Renvoie un objet `LazyGroupBy`.

```python theme={null}
groupby(*fields, sort=True, as_index=True, dropna=True) -> LazyGroupBy
```

**Exemples :**

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

ds = DataStore.from_file("sales.csv")

# Group by single column
by_region = ds.groupby('region')

# Group by multiple columns
by_region_product = ds.groupby('region', 'product')

# Aggregation after groupby
result = ds.groupby('region')['amount'].sum()
result = ds.groupby('region').agg({'amount': 'sum', 'quantity': 'mean'})

# Multiple aggregations
result = ds.groupby('category').agg({
    'price': ['min', 'max', 'mean'],
    'quantity': 'sum'
})

# Named aggregation
result = ds.groupby('region').agg(
    total_amount=('amount', 'sum'),
    avg_quantity=('quantity', 'mean'),
    order_count=('order_id', 'count')
)
```

<div id="having">
  ### `having`
</div>

Filtre les groupes après agrégation.

```python theme={null}
having(condition: Union[Condition, str]) -> DataStore
```

**Exemples :**

```python theme={null}
# Filter groups with total > 10000
result = (ds
    .groupby('region')
    .agg({'amount': 'sum'})
    .having(ds['sum'] > 10000)
)

# Using SQL-style having
result = (ds
    .select('region', 'SUM(amount) as total')
    .groupby('region')
    .having('total > 10000')
)
```

***

<div id="joining">
  ## Jointure
</div>

<div id="join">
  ### `join`
</div>

Effectuez une jointure entre deux DataStores.

```python theme={null}
join(right, on=None, how='inner', left_on=None, right_on=None) -> DataStore
```

**Paramètres :**

| Paramètre  | Type      | Valeur par défaut | Description                                          |
| ---------- | --------- | ----------------- | ---------------------------------------------------- |
| `right`    | DataStore | *obligatoire*     | DataStore de droite pour la jointure                 |
| `on`       | str/list  | `None`            | Colonnes sur lesquelles effectuer la jointure        |
| `how`      | str       | `'inner'`         | Type de jointure : 'inner', 'left', 'right', 'outer' |
| `left_on`  | str/list  | `None`            | Colonnes de jointure du côté gauche                  |
| `right_on` | str/list  | `None`            | Colonnes de jointure du côté droit                   |

**Exemples :**

```python theme={null}
from pathlib import Path
Path("departments.csv").write_text("""\
dept_id,department_name
1,Engineering
2,Product
3,Design
""")

employees = DataStore.from_file("employees.csv")
departments = DataStore.from_file("departments.csv")

# Inner join on single column
result = employees.join(departments, on='dept_id')

# Left join
result = employees.join(departments, on='dept_id', how='left')

# Join on different column names
result = employees.join(
    departments,
    left_on='department_id',
    right_on='id',
    how='inner'
)

# Pandas style merge
from chdb import datastore as pd
result = pd.merge(employees, departments, on='dept_id')
result = pd.merge(employees, departments, left_on='department_id', right_on='id')
```

<div id="union">
  ### `union`
</div>

Combine les résultats de deux DataStores.

```python theme={null}
union(other, all=False) -> DataStore
```

**Exemples :**

```python theme={null}
from pathlib import Path
Path("sales_2023.csv").write_text("""\
region,product,amount,date
East,Widget,1200,2023-06-15
West,Gadget,800,2023-09-20
North,Gizmo,600,2023-11-10
""")
Path("sales_2024.csv").write_text("""\
region,product,amount,date
East,Widget,1500,2024-03-10
North,Gizmo,900,2024-07-22
West,Gadget,1100,2024-05-05
""")

ds1 = DataStore.from_file("sales_2023.csv")
ds2 = DataStore.from_file("sales_2024.csv")

# UNION (removes duplicates)
result = ds1.union(ds2)

# UNION ALL (keeps duplicates)
result = ds1.union(ds2, all=True)

# Pandas style
from chdb import datastore as pd
result = pd.concat([ds1, ds2])
```

***

<div id="conditional">
  ## Expressions conditionnelles
</div>

<div id="when">
  ### `when`
</div>

Créer des expressions CASE WHEN.

```python theme={null}
when(condition, value) -> CaseWhenBuilder
```

**Exemples :**

```python theme={null}
ds = DataStore.from_file("employees.csv")

# Simple case-when
result = ds.select(
    'name',
    ds.when(ds['salary'] > 100000, 'High')
      .when(ds['salary'] > 50000, 'Medium')
      .otherwise('Low')
      .as_('salary_tier')
)

# With column assignment
ds['salary_tier'] = (
    ds.when(ds['salary'] > 100000, 'High')
      .when(ds['salary'] > 50000, 'Medium')
      .otherwise('Low')
)
```

***

<div id="raw-sql">
  ## SQL brut
</div>

<div id="run-sql">
  ### `run_sql` / `sql`
</div>

Exécute des requêtes SQL brutes.

```python theme={null}
run_sql(query: str) -> DataStore
sql(query: str) -> DataStore  # alias
```

**Exemples :**

```python theme={null}
from chdb.datastore import DataStore

# Execute raw SQL
result = DataStore().sql("""
    SELECT 
        department,
        COUNT(*) as count,
        AVG(salary) as avg_salary
    FROM file('employees.csv', 'CSVWithNames')
    WHERE status = 'active'
    GROUP BY department
    HAVING count > 5
    ORDER BY avg_salary DESC
    LIMIT 10
""")

# SQL on existing DataStore
ds = DataStore.from_file("employees.csv")
result = ds.sql("SELECT * FROM __table__ WHERE age > 30")
```

<div id="to-sql">
  ### `to_sql`
</div>

Afficher le SQL généré sans l’exécuter.

```python theme={null}
to_sql(**kwargs) -> str
```

**Exemples :**

```python theme={null}
ds = DataStore.from_file("employees.csv")

query = (ds
    .filter(ds['age'] > 30)
    .groupby('department')
    .agg({'salary': 'mean'})
    .sort('mean', ascending=False)
)

print(query.to_sql())
# Output:
# SELECT department, AVG(salary) AS mean
# FROM file('employees.csv', 'CSVWithNames')
# WHERE age > 30
# GROUP BY department
# ORDER BY mean DESC
```

***

<div id="chaining">
  ## Chaînage de méthodes
</div>

Toutes les méthodes de requête prennent en charge le chaînage fluide :

```python theme={null}
from chdb.datastore import DataStore

ds = DataStore.from_file("sales.csv")

result = (ds
    .select('region', 'product', 'amount', 'date')
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .groupby('region', 'product')
    .agg({
        'amount': ['sum', 'mean'],
        'date': 'count'
    })
    .having(ds['sum'] > 10000)
    .sort('sum', ascending=False)
    .limit(20)
)

# View SQL
print(result.to_sql())

# Execute
df = result.to_df()
```

***

<div id="aliasing">
  ## Alias
</div>

<div id="as">
  ### `as_`
</div>

Définir un alias pour une colonne ou une sous-requête.

```python theme={null}
as_(alias: str) -> DataStore
```

**Exemples :**

```python theme={null}
# Column alias
result = ds.select(
    ds['name'].as_('employee_name'),
    (ds['salary'] * 12).as_('annual_salary')
)

# Subquery alias
subquery = ds.filter(ds['age'] > 30).as_('senior_employees')
```