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

# بناء الاستعلامات في DataStore

> أنشئ استعلامات على نمط SQL باستخدام DataStore عبر ربط استدعاءات الأساليب

يوفر DataStore أساليب لبناء استعلامات على نمط SQL تُحوَّل إلى استعلامات SQL مُحسّنة. جميع العمليات مؤجَّلة التنفيذ حتى تصبح النتائج مطلوبة.

<div id="overview">
  ## نظرة عامة على طرق الاستعلام
</div>

| الطريقة            | مكافئ SQL       | الوصف                  |
| ------------------ | --------------- | ---------------------- |
| `select(*cols)`    | `SELECT cols`   | اختيار الأعمدة         |
| `filter(cond)`     | `WHERE cond`    | تصفية الصفوف           |
| `where(cond)`      | `WHERE cond`    | اسم مستعار لـ `filter` |
| `sort(*cols)`      | `ORDER BY cols` | فرز الصفوف             |
| `orderby(*cols)`   | `ORDER BY cols` | اسم مستعار لـ `sort`   |
| `limit(n)`         | `LIMIT n`       | تقييد عدد الصفوف       |
| `offset(n)`        | `OFFSET n`      | تخطي الصفوف            |
| `distinct()`       | `DISTINCT`      | إزالة التكرار          |
| `groupby(*cols)`   | `GROUP BY cols` | تجميع الصفوف           |
| `having(cond)`     | `HAVING cond`   | تصفية المجموعات        |
| `join(right, ...)` | `JOIN`          | ربط كائنات DataStore   |
| `union(other)`     | `UNION`         | دمج النتائج            |

***

<div id="selection">
  ## التحديد
</div>

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

حدِّد أعمدة معيّنة من DataStore.

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

**أمثلة:**

```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">
  ## التصفية
</div>

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

صفِّ الصفوف استنادًا إلى الشروط. الطريقتان متكافئتان.

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

**أمثلة:**

```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">
  ### التصفية بأسلوب 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">
  ## الترتيب
</div>

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

افرز الصفوف حسب عمود واحد أو أكثر.

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

**أمثلة:**

```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">
  ## التحديد والترقيم الصفحي
</div>

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

حدِّد الحد الأقصى لعدد الصفوف المُعادة.

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

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

تخطَّ أول n صفوف.

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

**أمثلة:**

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

يزيل الصفوف المكررة.

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

**أمثلة:**

```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">
  ## التجميع
</div>

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

تجميع الصفوف حسب عمود واحد أو أكثر. ويُرجع كائن `LazyGroupBy`.

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

**أمثلة:**

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

تصفية المجموعات بعد التجميع.

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

**أمثلة:**

```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">
  ## الربط
</div>

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

ربط DataStoreين.

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

**المعلمات:**

| المعلمة    | النوع     | الافتراضي | الوصف                                        |
| ---------- | --------- | --------- | -------------------------------------------- |
| `right`    | DataStore | *مطلوب*   | كائن DataStore الأيمن المراد ربطه            |
| `on`       | str/list  | `None`    | الأعمدة المطلوب الربط بناءً عليها            |
| `how`      | str       | `'inner'` | نوع الربط: 'inner', 'left', 'right', 'outer' |
| `left_on`  | str/list  | `None`    | أعمدة الربط في الجهة اليسرى                  |
| `right_on` | str/list  | `None`    | أعمدة الربط في الجهة اليمنى                  |

**أمثلة:**

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

ادمج نتائج اثنين من DataStore.

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

**أمثلة:**

```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">
  ## التعبيرات الشرطية
</div>

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

أنشئ تعبيرات CASE WHEN.

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

**أمثلة:**

```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 الخام
</div>

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

نفِّذ استعلامات SQL الخام.

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

**أمثلة:**

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

اعرض عبارة SQL المُولَّدة دون تنفيذها.

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

**أمثلة:**

```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">
  ## ربط استدعاءات الأساليب
</div>

تدعم جميع أساليب الاستعلام الربط المتسلسل بسلاسة:

```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">
  ## الأسماء البديلة
</div>

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

عيّن اسمًا مستعارًا لعمود أو لاستعلام فرعي.

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

**أمثلة:**

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