1. MooseStack
  3. Data Types
  5. Aggregate Types

Aggregate Types

SimpleAggregateFunction stores pre-aggregated values that ClickHouse automatically merges when combining rows with the same primary key. Use with AggregatingMergeTree tables.

SimpleAggregateFunction

Define columns that automatically aggregate during merges:

import {   SimpleAggregated,   OlapTable,   ClickHouseEngines,   Key,   DateTime } from "@514labs/moose-lib"; interface DailyStats {  date: DateTime;  userId: string;  totalViews: number & SimpleAggregated<"sum", number>;  maxScore: number & SimpleAggregated<"max", number>;  minLatency: number & SimpleAggregated<"min", number>;  lastSeen: DateTime & SimpleAggregated<"anyLast", DateTime>;} const statsTable = new OlapTable<DailyStats>("daily_stats", {  engine: ClickHouseEngines.AggregatingMergeTree,  orderByFields: ["date", "userId"],});

Supported Aggregate Functions

FunctionDescriptionTypical Use
sumSum of valuesTotals, counts
maxMaximum valuePeak metrics
minMinimum valueMinimum thresholds
anyAny single valueNon-deterministic pick
anyLastLast inserted valueLatest timestamp, status
argMaxValue at max of another columnValue when metric was highest
argMinValue at min of another columnValue when metric was lowest

How It Works

  1. Insert: Data is written with initial aggregate values
  2. Background Merge: ClickHouse periodically merges rows with the same ORDER BY key
  3. Automatic Aggregation: SimpleAggregateFunction columns are combined using their specified function
// Two rows with same (date, userId):{ date: "2025-01-01", userId: "u1", totalViews: 10 }{ date: "2025-01-01", userId: "u1", totalViews: 15 } // After merge:{ date: "2025-01-01", userId: "u1", totalViews: 25 }  // sum
When to use SimpleAggregateFunction

Use when you need real-time aggregations on high-volume data. Instead of storing every event and aggregating at query time, pre-aggregate during ingestion for faster queries.

Requires AggregatingMergeTree

SimpleAggregateFunction only works with AggregatingMergeTree or ReplicatedAggregatingMergeTree table engines. The table's ORDER BY clause defines which rows get merged.

See Also

  • ClickHouse SimpleAggregateFunction docs
  • Table Engines for configuring AggregatingMergeTree

On this page

SimpleAggregateFunctionSupported Aggregate FunctionsHow It WorksSee Also
FiveonefourFiveonefour
Fiveonefour Docs
MooseStackTemplatesGuides
Release Notes
Source523
  • Overview
Build a New App
  • 5 Minute Quickstart
  • Browse Templates
  • Existing ClickHouse
Add to Existing App
  • Next.js
  • Fastify
Fundamentals
  • Moose Runtime
  • MooseDev MCP
  • Data Modeling
Moose Modules
  • Moose OLAP
  • Moose Streaming
  • Moose Workflows
  • Moose APIs & Web Apps
Deployment & Lifecycle
  • Moose Migrate
  • Moose Deploy
Reference
  • API Reference
  • Data Types
    • Strings
    • LowCardinality
    • Integers
    • Floats
    • Decimals
    • Booleans
    • Date & Time
    • Network
    • Arrays
    • Maps
    • Nested
    • Tuples
    • Enums
    • Geometry
    • JSON
    • Nullable
    • Aggregates
  • Table Engines
  • CLI
  • Configuration
  • Observability Metrics
  • Help
  • Release Notes
Contribution
  • Documentation
  • Framework
import {   SimpleAggregated,   OlapTable,   ClickHouseEngines,   Key,   DateTime } from "@514labs/moose-lib"; interface DailyStats {  date: DateTime;  userId: string;  totalViews: number & SimpleAggregated<"sum", number>;  maxScore: number & SimpleAggregated<"max", number>;  minLatency: number & SimpleAggregated<"min", number>;  lastSeen: DateTime & SimpleAggregated<"anyLast", DateTime>;} const statsTable = new OlapTable<DailyStats>("daily_stats", {  engine: ClickHouseEngines.AggregatingMergeTree,  orderByFields: ["date", "userId"],});