Skip to main content

Aggregation

Overview​

The aggregation filter is a dynamic feature in SQL queries used for grouping data based on time intervals. In a query, it is represented as {column_name:aggregation}. This filter allows users to dynamically aggregate data by time periods like minutes, hours, days, weeks, months, quarters, or years.

Functionality​

The filter {column_name:aggregation} is used to dynamically aggregate data by time intervals such as minutes, hours, days, weeks, months, quarters, or years. The actual aggregation period is determined at compile time, replacing aggregation with the chosen interval.

Syntax​

SELECT {column_name :aggregation} as period, count (*) FROM [table] GROUP BY period;
  • {column_name:aggregation}: Applies the dynamic aggregation filter to column_name, where :aggregation will be replaced at compile time with the chosen time interval.
  • [table]: The table from which data is selected and grouped.

Examples​

Assuming :aggregation is replaced at compile time based on user settings or context:

Example 1: Aggregation Set to Month​

Query:

SELECT {birthday:aggregation} as period, count(*) FROM students GROUP BY period;

Behavior: :aggregation is replaced with month.

Final SQL: SELECT date_trunc('month', birthday) as period, count(*) FROM students GROUP BY period.