GroupBy Window¶
Source: src/memframe/wrappers/analytix/groupby_window.py
GroupByWindowStatsWrapper is the public partitioned-window interface
exposed through a ContextManager. It provides
pandas-groupby().rolling()-like analytics: rolling fixed-size windows
and expanding unbounded windows computed per group (PARTITION BY
the group columns), in-engine across DuckDB, PostgreSQL, and ClickHouse.
Each call writes a new transient table (<table>__op_<n>) and the source
upload table is never mutated in place.
Users normally call grouped window methods through the unified groupby
builder on a dataset context returned by an upload operation. There are
two equivalent entry points:
- a direct call —
dataset.groupby("region").arolling(...)— with the column, window, function, and group columns passed as arguments (async; sync viarolling(...)on the stats wrapper); - a pandas-style fluent builder —
dataset.groupby("region").rolling(3, order_by="month").mean("sales").
dataset = mf.upload_df(frame)
result = dataset.groupby("region").rolling(3, order_by="month").mean("sales")
result = dataset.groupby("region").expanding(order_by="month").sum("sales")
dataset = await mf.aupload_df(frame)
result = await dataset.groupby("region").arolling(
column="sales", window=3, func="mean",
group_cols="region", order_by="month",
)
Note
A flat dataset.rolling(...) resolves to the ungrouped
WindowWrapper.rolling(column, window, func, ...) (it is registered
first and takes no group columns). The grouped form above is only
reachable through the groupby(...) builder or
GroupByWindowStatsWrapper directly — the same arrangement as
grouped event_rate on the Stats page.
The lower-level files are implementation details:
src/memframe/core/analytix/groupby_window.pybuilds and executes backend-specific SQL in a singleGroupbyWindowOpsclass that extendsWindowOps(isinstancebranches for DuckDB / PostgreSQL / ClickHouse).src/memframe/core/orchestrator/analytix/groupby_window.pyresolves the active dataset context, detects the column dtype, maps requested functions to dtype-appropriate engine methods, chains multi-function requests into one transient table, and applies@record_call.src/memframe/wrappers/analytix/groupby_window.pyexposes the synchronous and asynchronous builder methods plus the direct API.src/memframe/wrappers/analytix/groupby.pyis the unifiedGroupByfacade that routesctx.groupby(*columns)calls to the stats, cumulative, and window builders.
Public API¶
Direct¶
| Synchronous | Asynchronous | Purpose |
|---|---|---|
rolling(column, window, func, group_cols, order_by=None, q=0.5) |
await arolling(...) |
Grouped rolling window; func is a string or list |
expanding(column, func, group_cols, order_by=None, q=0.5, min_periods=1) |
await aexpanding(...) |
Grouped expanding window |
ewm(column, group_cols, order_by=None, com/span/halflife/alpha, ...) |
await aewm(...) |
Grouped EWM (see note below) |
group_cols accepts a single column name or a list of names.
Builder¶
groupby(*columns) returns the unified GroupBy; .rolling(window,
order_by) / .expanding(min_periods, order_by) / .ewm(...) return
fluent builders. Each terminal method has a synchronous and asynchronous
form (mean / amean, …):
| Rolling / Expanding terminals | Purpose |
|---|---|
sum(column) / mean(column) |
Running sum / mean within each group |
min(column) / max(column) |
Running minimum / maximum |
count(column) |
Running non-null count |
std(column) / var(column) |
Running sample stddev / variance |
quantile(column, q=0.5) |
Running quantile |
sem(column) |
Running standard error |
rank(column) |
Running rank within the window |
nunique(column) |
Running distinct count |
first(column) / last(column) |
First / last value in the window |
agg(column, funcs) |
Multi-aggregation chained into one table |
Note
Grouped EWM (ewm) is currently unavailable: the engine path
depends on helpers (_ensure_row_id, _alpha_from_params,
_compute_ewm_array) that did not survive the window core split.
Rolling and expanding are fully supported; EWM needs an engine port.
Usage Overview¶
dataset = mf.upload_df(frame)
sample = dataset.groupby("region").rolling(3, order_by="month").mean("sales")
sample = dataset.groupby("region").expanding(order_by="month").sum("sales")
sample = dataset.groupby("region").rolling(3, order_by="month").agg("sales", ["sum", "mean"])
dataset = await mf.aupload_df(frame)
sample = await dataset.groupby("region").arolling(
column="sales", window=3, func="mean",
group_cols="region", order_by="month",
)
sample = await dataset.groupby("region").aexpanding(
column="sales", func="sum", group_cols="region", order_by="month",
)
order_by is optional — omit it to use physical row order:
sample = dataset.groupby("region").rolling(3).mean("sales")
sample = dataset.groupby("region").expanding().sum("sales")
Common Parameters¶
| Parameter | Type | Description |
|---|---|---|
column |
str |
Value column the window runs over. |
group_cols |
str or list[str] |
Column(s) partitioning the window. At least one is required. |
window |
int |
Rolling window size in rows (ROWS BETWEEN window-1 PRECEDING AND CURRENT ROW). |
func |
str or list[str] |
Aggregation(s): sum, mean/avg, min, max, count, std/stddev/stddev_samp, var/variance/var_samp, quantile, sem, rank, nunique, first/first_value, last/last_value. |
order_by |
str, list[str], or None |
Row ordering inside each group. None (default) uses physical row order. |
q |
float |
Quantile level for quantile (default 0.5). |
min_periods |
int |
Minimum rows before an expanding value is produced (default 1). |
new_table |
str or None |
Explicit name for the output table (chaining). Auto-generated as <table>__op_<n> when omitted. |
map_feature |
bool |
When True (default False), LEFT JOIN the new feature column(s) back onto the original table — matched on full row identity. No extra result table is created; the envelope gains mapped_table / mapped_columns. Rolling, expanding, and the specials; not EWM. |
With no order_by, rows accumulate in physical storage order
(PostgreSQL ctid, DuckDB rowid). ClickHouse has no stable row id, so
a synthetic _mf_row_num (ROW_NUMBER() OVER()) is generated in a
subquery to give the window a deterministic order.
Result columns are named
<column>_rolling_<stat>_w<window>_by_<groups> (rolling) or
<column>_expanding_<stat>_by_<groups> (expanding); duplicate requests
get a numeric suffix (_2, …).
Multicolumn Group-By¶
Pass several group columns to partition by their combination — the window restarts at every distinct key tuple:
result = dataset.groupby("region", "month").rolling(3, order_by="day").sum("sales")
# column: sales_rolling_sum_w3_by_region_month
result = await dataset.groupby("region", "month").aexpanding(
column="sales", func="mean",
group_cols=["region", "month"], order_by="day",
)
Direct calls accept the same shapes — a single name or a list:
result = dataset.arolling(
column="sales", window=3, func=["sum", "mean"],
group_cols=["region", "month"], order_by="day",
)
Multi-aggregation (func as a list, or builder .agg(column, funcs))
chains every requested function into one transient table, each
reading the previous call's output:
result = dataset.groupby("region").rolling(3, order_by="month").agg(
"sales", ["sum", "mean", "std"]
)
# columns: sales_rolling_sum_w3_by_region,
# sales_rolling_mean_w3_by_region,
# sales_rolling_std_w3_by_region
Rolling¶
Each rolling call computes AGG(col) OVER (PARTITION BY <groups> ORDER
BY <order> ROWS BETWEEN window-1 PRECEDING AND CURRENT ROW) and appends
the result column(s) to a copy of the source rows:
result = dataset.groupby("region").rolling(3, order_by="month").mean("sales")
result = dataset.groupby("region").rolling(7, order_by="month").quantile("sales", q=0.9)
func also accepts backend spellings directly (AVG, STDDEV_SAMP,
first_value, …); unknown names fail the whole call with is_error
True, while dtype-unsupported names are reported per-function in
skipped_funcs/failed_funcs with is_partial True.
Expanding¶
Expanding mirrors rolling with an unbounded frame (first row of each
group through the current row) plus min_periods:
result = dataset.groupby("region").expanding(order_by="month").sum("sales")
result = dataset.groupby("region").expanding(order_by="month", min_periods=3).mean("sales")
Specials: quantile, nunique, rank, sem¶
These use backend-specific execution but identical builder signatures:
quantile: DuckDB windowedQUANTILE_CONT; PostgreSQL and ClickHouse fall back to a correlated subquery over aROW_NUMBER()base (PERCENTILE_CONT … WITHIN GROUP/quantile(q)(…)).nunique: DuckDB windowedCOUNT(DISTINCT …); PostgreSQL and ClickHouse use the correlated-subquery form (countDistincton ClickHouse).rank: correlatedCOUNT(*)subquery on all backends.sem: windowedstddev / sqrt(count)(stddevSampon ClickHouse).
The orchestrator detects the value column dtype (numeric / datetime /
categorical) and maps each requested function to the dtype-appropriate
engine method; datetime columns support min/max/mean/median/
mode/count/nunique/rank/first/last via epoch conversion.
Map Feature Back (map_feature)¶
With map_feature=True, the new window column(s) are LEFT JOINed back
onto the original table — every original row gains its own running value
(one output row per input row, matched on full row identity). Only the
feature column(s) are added and no extra result table is created. Only
the window table is recorded by @record_call.
result = dataset.groupby("region").rolling(3, order_by="month").agg(
"sales", ["mean"], map_feature=True
)
# original table now has: region, month, sales, sales_rolling_mean_w3_by_region
Rules:
- A repeated identical call re-runs cleanly (its own column(s) are dropped and re-added). This also holds in non-deep mode, which saves nothing.
- If the column name already exists from anything else, the call fails with an error — drop or rename it first.
- Backend swaps keep the original table name: DuckDB
CREATE OR REPLACE, PostgreSQL create-swap-rename, ClickHouse atomicEXCHANGE TABLES. - Rows with
NULLkey columns match nothing (NULL = NULLis false in SQL) and keep aNULLfeature value.
Return Values and Errors¶
Calling the builder (or the wrapper class) directly returns the raw
operation envelope; single-function calls also carry new_column:
{
"is_error": False,
"message": "Rolling mean on 'sales' grouped by ['region'] (window=2)",
"result": <pandas.DataFrame>,
"new_table": "ab12CD__op_3",
"new_columns": ["sales_rolling_mean_w2_by_region"],
"new_column": "sales_rolling_mean_w2_by_region", # single-function calls only
"window": 2,
"group_cols": ["region"],
# multi-function calls also include:
# "successful_funcs", "skipped_funcs", "failed_funcs", "is_partial", "dtype"
}
On a ContextManager, public methods return the resulting DataFrame
directly; invalid operations surface as OperationError when unwrapped.
An unknown function, a function unsupported for the detected dtype, or a
missing column fails the call (message == "" on the raw envelope).
Generated Tables¶
Every grouped window operation is non-destructive to the source upload table. It creates a new transient table holding the source rows plus the generated column(s), then returns a preview of it:
- PostgreSQL / DuckDB:
CREATE TABLE … AS SELECT *, <window> AS <target> FROM …(quantile / nunique / rank use aROW_NUMBER()__basesubquery; no-order_bycalls stage a rowid table first). - ClickHouse:
CREATE TABLE … ENGINE = MergeTree() ORDER BY tuple() AS SELECT …— windows computed inline; withoutorder_bythe source is wrapped in a_mf_row_numsubquery whose helper column is excluded from the output.
Operations apply @record_call, so repeat calls are recorded in the
transient registry.
Backend Behavior¶
Grouped windows support DuckDB, PostgreSQL, and ClickHouse:
- Identifiers are sanitized and quoted before SQL is generated.
group_colsandorder_bymay each be a single column, a list, or (fororder_by) omitted for physical order.- Backend dialect differences (sample stddev/variance names, quantile/nunique/rank execution shapes, datetime epoch handling) are handled per backend.
- The per-backend SQL is locked by
tests/unit/test_groupby_window_sql_fingerprint.py(6 scenarios × 3 backends).
Errors¶
Grouped window methods raise OperationError (once unwrapped) for
validation or backend failures:
- No group-by column, no aggregation function, or an invalid function entry.
- Unknown rolling function, or a function unsupported for the detected dtype.
- Missing or renamed
column/order_by/ group column (raised out of dtype detection). - Missing
backend/data_idat the ops layer, or an unsupported backend.
API Reference¶
memframe.wrappers.analytix.groupby_window.GroupByWindowStatsWrapper
¶
Bases: GroupByWindowOrchestrator
Public wrapper with sync/async for the direct rolling, expanding, ewm methods.
arolling(column, window, func, group_cols, order_by=None, q=0.5, map_feature=False)
async
¶
Asynchronously run grouped rolling window operation directly.
rolling(column, window, func, group_cols, order_by=None, q=0.5, map_feature=False)
async
¶
Synchronously run grouped rolling window operation directly.
aexpanding(column, func, group_cols, order_by=None, q=0.5, min_periods=1, map_feature=False)
async
¶
Asynchronously run grouped expanding window operation directly.
expanding(column, func, group_cols, order_by=None, q=0.5, min_periods=1, map_feature=False)
async
¶
Synchronously run grouped expanding window operation directly.
aewm(column, group_cols, order_by=None, com=None, span=None, halflife=None, alpha=None, adjust=True, ignore_na=False, min_periods=0, func='mean')
async
¶
Asynchronously run grouped EWM operation directly.
ewm(column, group_cols, order_by=None, com=None, span=None, halflife=None, alpha=None, adjust=True, ignore_na=False, min_periods=0, func='mean')
async
¶
Synchronously run grouped EWM operation directly.
groupby(*columns)
¶
Create grouped window builder for one or more group-by columns.
memframe.wrappers.analytix.groupby_window.GroupByWindowWrapper
¶
Bases: GroupByWindow
Returns the wrapped builders so sync/async methods are available.
rolling(window, order_by=None)
¶
Create grouped rolling window builder.
expanding(min_periods=1, order_by=None)
¶
Create grouped expanding window builder.
ewm(order_by=None, com=None, span=None, halflife=None, alpha=None, adjust=True, ignore_na=False, min_periods=0)
¶
Create grouped exponentially weighted window builder.
memframe.wrappers.analytix.groupby_window.GroupByRollingWrapper
¶
Bases: GroupByRolling
Fluent grouped rolling window builder with async/sync operations.
__init__(group_window, window, order_by)
¶
Initialize rolling builder with parent group window and ordering.
aagg(column, funcs, **kwargs)
async
¶
Asynchronously compute grouped rolling multi-aggregation.
acount(column)
async
¶
Asynchronously compute grouped rolling count.
afirst(column)
async
¶
Asynchronously compute grouped rolling first value.
agg(column, funcs, **kwargs)
async
¶
Synchronously compute grouped rolling multi-aggregation.
alast(column)
async
¶
Asynchronously compute grouped rolling last value.
amax(column)
async
¶
Asynchronously compute grouped rolling maximum.
amean(column)
async
¶
Asynchronously compute grouped rolling mean.
amin(column)
async
¶
Asynchronously compute grouped rolling minimum.
anunique(column)
async
¶
Asynchronously compute grouped rolling unique count.
aquantile(column, q=0.5)
async
¶
Asynchronously compute grouped rolling quantile.
arank(column)
async
¶
Asynchronously compute grouped rolling rank.
asem(column)
async
¶
Asynchronously compute grouped rolling standard error.
astd(column)
async
¶
Asynchronously compute grouped rolling standard deviation.
asum(column)
async
¶
Asynchronously compute grouped rolling sum.
avar(column)
async
¶
Asynchronously compute grouped rolling variance.
count(column)
async
¶
Synchronously compute grouped rolling count.
first(column)
async
¶
Synchronously compute grouped rolling first value.
last(column)
async
¶
Synchronously compute grouped rolling last value.
max(column)
async
¶
Synchronously compute grouped rolling maximum.
mean(column)
async
¶
Synchronously compute grouped rolling mean.
min(column)
async
¶
Synchronously compute grouped rolling minimum.
nunique(column)
async
¶
Synchronously compute grouped rolling unique count.
quantile(column, q=0.5)
async
¶
Synchronously compute grouped rolling quantile.
rank(column)
async
¶
Synchronously compute grouped rolling rank.
sem(column)
async
¶
Synchronously compute grouped rolling standard error.
std(column)
async
¶
Synchronously compute grouped rolling standard deviation.
sum(column)
async
¶
Synchronously compute grouped rolling sum.
var(column)
async
¶
Synchronously compute grouped rolling variance.
memframe.wrappers.analytix.groupby_window.GroupByExpandingWrapper
¶
Bases: GroupByExpanding
Fluent grouped expanding window builder with async/sync operations.
__init__(group_window, min_periods, order_by)
¶
Initialize expanding builder with parent group window and ordering.
aagg(column, funcs, **kwargs)
async
¶
Asynchronously compute grouped expanding multi-aggregation.
acount(column)
async
¶
Asynchronously compute grouped expanding count.
afirst(column)
async
¶
Asynchronously compute grouped expanding first value.
agg(column, funcs, **kwargs)
async
¶
Synchronously compute grouped expanding multi-aggregation.
alast(column)
async
¶
Asynchronously compute grouped expanding last value.
amax(column)
async
¶
Asynchronously compute grouped expanding maximum.
amean(column)
async
¶
Asynchronously compute grouped expanding mean.
amin(column)
async
¶
Asynchronously compute grouped expanding minimum.
anunique(column)
async
¶
Asynchronously compute grouped expanding unique count.
aquantile(column, q=0.5)
async
¶
Asynchronously compute grouped expanding quantile.
arank(column)
async
¶
Asynchronously compute grouped expanding rank.
asem(column)
async
¶
Asynchronously compute grouped expanding standard error.
astd(column)
async
¶
Asynchronously compute grouped expanding standard deviation.
asum(column)
async
¶
Asynchronously compute grouped expanding sum.
avar(column)
async
¶
Asynchronously compute grouped expanding variance.
count(column)
async
¶
Synchronously compute grouped expanding count.
first(column)
async
¶
Synchronously compute grouped expanding first value.
last(column)
async
¶
Synchronously compute grouped expanding last value.
max(column)
async
¶
Synchronously compute grouped expanding maximum.
mean(column)
async
¶
Synchronously compute grouped expanding mean.
min(column)
async
¶
Synchronously compute grouped expanding minimum.
nunique(column)
async
¶
Synchronously compute grouped expanding unique count.
quantile(column, q=0.5)
async
¶
Synchronously compute grouped expanding quantile.
rank(column)
async
¶
Synchronously compute grouped expanding rank.
sem(column)
async
¶
Synchronously compute grouped expanding standard error.
std(column)
async
¶
Synchronously compute grouped expanding standard deviation.
sum(column)
async
¶
Synchronously compute grouped expanding sum.
var(column)
async
¶
Synchronously compute grouped expanding variance.
memframe.wrappers.analytix.groupby_window.GroupByEWMWrapper
¶
Bases: GroupByEWM
Fluent grouped exponentially weighted window builder.
__init__(*args, **kwargs)
¶
Initialize grouped exponentially weighted window builder.
aagg(column, funcs)
async
¶
Asynchronously compute grouped EWM multi-aggregation.
agg(column, funcs)
async
¶
Synchronously compute grouped EWM multi-aggregation.
amean(column)
async
¶
Asynchronously compute grouped exponentially weighted mean.
astd(column)
async
¶
Asynchronously compute grouped exponentially weighted stddev.
asum(column)
async
¶
Asynchronously compute grouped exponentially weighted sum.
avar(column)
async
¶
Asynchronously compute grouped exponentially weighted variance.
mean(column)
async
¶
Synchronously compute grouped exponentially weighted mean.
std(column)
async
¶
Synchronously compute grouped exponentially weighted stddev.
sum(column)
async
¶
Synchronously compute grouped exponentially weighted sum.
var(column)
async
¶
Synchronously compute grouped exponentially weighted variance.