DataStore Aggregation Functions

**Examples:**

```
from chdb.datastore import F, Field

# Sum only high value orders
high_value_sum = F.sum_if(Field('amount'), Field('amount') > 1000)

# Count active users
active_count = F.count_if(Field('status') == 'active')

# In groupby context
result = ds.groupby('region').agg({
    'total': ('amount', 'sum'),
    'high_value': ('amount', F.sum_if(Field('amount') > 1000)),
})
```

---

## Collection Aggregations[​](#collection "Direct link to Collection Aggregations")

ClickHouse-specific functions that collect values.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Function ClickHouse Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `group_array()` `groupArray()` Collect into array|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `group_uniq_array()` `groupUniqArray()` Collect unique into array|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `group_concat(sep)` `groupConcat()` Concatenate strings|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `top_k(n)` `topK(n)` Top K frequent values|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `any()` `any()` Any value|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `any_last()` `anyLast()` Last value|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `first_value()` `first_value()` First value in order|  |  |  | | --- | --- | --- | | `last_value()` `last_value()` Last value in order | | | | | | | | | | | | | | | | | | | | | | | | | | |

**Examples:**

```
from chdb.datastore import F, Field

# Collect all tags per category
result = ds.groupby('category').agg({
    'all_tags': ('tag', F.group_array()),
    'unique_tags': ('tag', F.group_uniq_array())
})

# Get top 5 products per region
result = ds.groupby('region').agg({
    'top_products': ('product_id', F.top_k(5))
})
```

---

## Window Functions[​](#window "Direct link to Window Functions")

### Ranking Functions[​](#ranking "Direct link to Ranking Functions")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Function SQL Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `row_number()` `ROW_NUMBER()` Sequential row number|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `rank()` `RANK()` Rank with gaps|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `dense_rank()` `DENSE_RANK()` Rank without gaps|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ntile(n)` `NTILE(n)` Divide into n buckets|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `percent_rank()` `PERCENT_RANK()` Percentile rank (0-1)|  |  |  | | --- | --- | --- | | `cume_dist()` `CUME_DIST()` Cumulative distribution | | | | | | | | | | | | | | | | | | | | |

**Examples:**

```
from chdb.datastore import F, Field

# Add row number
ds['row_num'] = F.row_number().over(order_by='date')

# Rank within groups
ds['rank'] = F.rank().over(
    partition_by='category',
    order_by='sales'
)

# Dense rank (no gaps)
ds['dense_rank'] = F.dense_rank().over(
    partition_by='region',
    order_by=('revenue', 'desc')
)

# Divide into quartiles
ds['quartile'] = F.ntile(4).over(order_by='score')
```

### Value Functions[​](#value-functions "Direct link to Value Functions")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Function SQL Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `lag(n)` `LAG(col, n)` Previous row value|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `lead(n)` `LEAD(col, n)` Next row value|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `first_value()` `FIRST_VALUE()` First value in window|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `last_value()` `LAST_VALUE()` Last value in window|  |  |  | | --- | --- | --- | | `nth_value(n)` `NTH_VALUE(col, n)` Nth value in window | | | | | | | | | | | | | | | | | |

**Examples:**

```
# Previous and next value
ds['prev_price'] = F.lag('price', 1).over(order_by='date')
ds['next_price'] = F.lead('price', 1).over(order_by='date')

# First and last in partition
ds['first_order'] = F.first_value('amount').over(
    partition_by='customer_id',
    order_by='date'
)
```

### Cumulative Functions[​](#cumulative "Direct link to Cumulative Functions")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Method Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `cumsum()` Cumulative sum|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `cummax()` Cumulative maximum|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `cummin()` Cumulative minimum|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `cumprod()` Cumulative product|  |  |  |  | | --- | --- | --- | --- | | `diff(n)` Difference from n rows back|  |  | | --- | --- | | `pct_change(n)` Percent change from n rows back | | | | | | | | | | | | | |

**Examples:**

```
# Cumulative calculations
ds['running_total'] = ds['amount'].cumsum()
ds['running_max'] = ds['amount'].cummax()

# With grouping
ds['group_cumsum'] = ds.groupby('category')['amount'].cumsum()

# Period over period
ds['daily_diff'] = ds['sales'].diff(1)
ds['pct_change'] = ds['sales'].pct_change(1)
```

### Rolling Windows[​](#rolling "Direct link to Rolling Windows")

```
# Rolling window aggregations
ds['rolling_avg'] = ds['price'].rolling(window=7).mean()
ds['rolling_sum'] = ds['amount'].rolling(window=30).sum()
ds['rolling_std'] = ds['value'].rolling(window=10).std()

# Expanding windows
ds['expanding_max'] = ds['price'].expanding().max()
ds['expanding_sum'] = ds['amount'].expanding().sum()
```

---

## F Namespace[​](#f-namespace "Direct link to F Namespace")

The `F` namespace provides access to ClickHouse functions.

### Import[​](#f-import "Direct link to Import")

```
from chdb.datastore import F, Field
```

### Using F Functions[​](#f-usage "Direct link to Using F Functions")

```
# Aggregations
F.sum(Field('amount'))
F.avg(Field('price'))
F.count(Field('id'))

# Statistical
F.quantile(Field('value'), 0.95)
F.stddev_pop(Field('score'))
F.corr(Field('x'), Field('y'))

# Conditional
F.sum_if(Field('amount'), Field('status') == 'completed')
F.count_if(Field('is_active'))

# String
F.length(Field('name'))
F.upper(Field('text'))

# Date/Time
F.to_year(Field('date'))
F.date_diff('day', Field('start'), Field('end'))

# Array
F.array_sum(Field('values'))
F.array_avg(Field('scores'))

# Math
F.abs(Field('delta'))
F.round(Field('price'), 2)
F.floor(Field('value'))
F.ceil(Field('value'))
```