GROUP BY clause

​

Overview

You specify how Horizon AI Query should dissect the selected data through a Group By clause. The Aggregate expressions you specify would then be applied to the resulting groups.

Your Group By clause can contain one or more of the following expressions:

• a Date Truncate expression

  • A synchronous Query can have at most one Date Truncate expression
  • A Continuous Query must have exactly one Date Truncate expression.

• a Date Part expression

• a Table Column expression

• a Source Column expression

You can mix and match any expression types, and organize them in any order. Group Key Column expression tracks your declaration order.

​

Group by a truncated datetime

Group the input rows based on the specified column expression truncated to the specified granularity. This is similar to the DATE_TRUNC function in SQL.

import vitalx.aggregation as va

# Truncate to every 1 day.
va.select(...).group_by(
    va.date_trunc(va.Sleep.index(), 1, "day"),
)

# Truncate to every 3 months.
va.select(...).group_by(
    va.date_trunc(va.Sleep.index(), 3, "month"),
)

​

Group by a date or time component

Group the input rows based on a specific date or time component extracted from the specified column expression. This is similar to the DATE_PART function in SQL.

import vitalx.aggregation as va

# Extract the weekday
va.select(...).group_by(
    va.date_part(va.Sleep.index(), "weekday"),
)

# Extract the day-of-month
va.select(...).group_by(
    va.date_part(va.Sleep.index(), "day")
)

​

Group by a Table Column expression

Group the input rows based on a Table Column expression. This is similar to the generic GROUP BY clause in SQL.

import vitalx.aggregation as va

# Group by calendar date and Stand Hour type (idle vs stand)

va.select(
    va.group_key("*"),
    va.Timeseries.col("stand_hour").field("value").sum()
).group_by(
    va.date_trunc(va.Timeseries.index(), 1, "day"),
    va.Timeseries.col("stand_hour").field("type")
)

​

Group by a Source Column expression

Group the input rows based on a Source Column expression. This is similar to a generic GROUP BY clause in SQL.

import vitalx.aggregation as va

# Group by the source provider and source type
va.select(...).group_by(
    va.date_trunc(va.Sleep.index(), 1, "week"),
    va.Source.col("source_provider"),
    va.Source.col("source_type")
)