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.