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

### F with Window Functions[​](#f-window "Direct link to F with Window Functions")

```
# Define window frame
window = F.window(
    partition_by='category',
    order_by='date',
    rows_between=(-7, 0)  # Current row and 7 preceding
)

ds['rolling_avg'] = F.avg(Field('price')).over(window)
```

---

## Common Aggregation Patterns[​](#patterns "Direct link to Common Aggregation Patterns")

### Top N per Group[​](#top-n "Direct link to Top N per Group")

```
# Top 3 products per category by sales
result = (ds
    .assign(rank=F.row_number().over(
        partition_by='category',
        order_by=('sales', 'desc')
    ))
    .filter(ds['rank'] <= 3)
)
```

### Running Total[​](#running-total "Direct link to Running Total")

```
# Running total of sales
ds['running_total'] = F.sum('amount').over(
    order_by='date',
    rows_between=(None, 0)  # All rows up to current
)
```

### Moving Average[​](#moving-avg "Direct link to Moving Average")

```
# 7-day moving average
ds['ma_7'] = F.avg('price').over(
    order_by='date',
    rows_between=(-6, 0)
)
```

### Year-over-Year Comparison[​](#yoy "Direct link to Year-over-Year Comparison")

```
# YoY comparison
ds['prev_year_sales'] = F.lag('sales', 12).over(
    partition_by='product_id',
    order_by='month'
)
ds['yoy_growth'] = (ds['sales'] - ds['prev_year_sales']) / ds['prev_year_sales']
```

### Percentile Ranking[​](#percentile "Direct link to Percentile Ranking")

```
# Rank customers by total spend
ds['spend_percentile'] = F.percent_rank().over(order_by='total_spend')
```

---

## Aggregation Methods Summary[​](#summary "Direct link to Aggregation Methods Summary")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Category Methods|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Basic** `sum`, `mean`, `count`, `min`, `max`, `median`| **Statistical** `std`, `var`, `quantile`, `skew`, `kurt`, `corr`, `cov`| **Conditional** `sum_if`, `count_if`, `avg_if`, `min_if`, `max_if`| **Collection** `group_array`, `group_uniq_array`, `group_concat`, `top_k`| **Ranking** `row_number`, `rank`, `dense_rank`, `ntile`, `percent_rank`| **Value** `lag`, `lead`, `first_value`, `last_value`, `nth_value`| **Cumulative** `cumsum`, `cummax`, `cummin`, `cumprod`, `diff`, `pct_change`| **Rolling** `rolling().mean/sum/std/...`, `expanding().mean/sum/...` | | | | | | | | | | | | | | | | | |

- [Basic Aggregations](#basic)
  - [Built-in Methods](#builtin)- [GroupBy Aggregations](#groupby)
    - [Single Aggregation](#single-agg)- [Multiple Aggregations](#multi-agg)- [Named Aggregations](#named-agg)- [Multiple GroupBy Keys](#multi-groupby)- [Statistical Aggregations](#statistical)- [Conditional Aggregations](#conditional)- [Collection Aggregations](#collection)- [Window Functions](#window)
            - [Ranking Functions](#ranking)- [Value Functions](#value-functions)- [Cumulative Functions](#cumulative)- [Rolling Windows](#rolling)- [F Namespace](#f-namespace)
              - [Import](#f-import)- [Using F Functions](#f-usage)- [F with Window Functions](#f-window)- [Common Aggregation Patterns](#patterns)
                - [Top N per Group](#top-n)- [Running Total](#running-total)- [Moving Average](#moving-avg)- [Year-over-Year Comparison](#yoy)- [Percentile Ranking](#percentile)- [Aggregation Methods Summary](#summary)

Was this page helpful?