Group functions (g_functions)

Group functions, or g_functions, are used to perform operations, such as summarizations, on rows that have the same values in a set of given columns.

Group functions allow you to define the order and partitioning of a table on the fly. There are some restrictions that apply to these functions, but they can be used in a wide array of applications and provide an unmatched level of flexibility and performance. These functions provide similar functionality as tabulations, but are often faster.

Group functions take a column of data in a table, break the column up into different groups, and then compute summary information on each of those groups.

Under the hood, g_functions offer a lot of performance benefits, because like all other computed columns, results are only calculated as they are needed. As-needed means required in a later tabulation, such as linking in another worksheet on the computed column with the g_function. Plus, g_functions can be used to create results without any data aggregation. You can then create values that contain summary information (such as total sales per store per day) and then have other columns in the original table that refer to them.

Each function description contains the syntax, input values, return values, examples, and additional information related to that function.

Group functions and segmentation

Group functions require that the data be arranged in a certain way. The data must be contained in one of two ways:
  • In a single-segment table (approximately 8 million rows or fewer)
  • In a multi-segment table that is already segmented by the column you want to group by

If your table is larger than 8 million rows, the Tabulate operation is the most common way to segment your data by the desired column.

When to use g_functions

You can use the Tabulate operation to accomplish many of the same tasks as g_functions. However, group functions can be performed up to ten times faster. You can use separate g_functions to aggregate data at different levels. In addition, g_functions allow you to view both summarized and detailed information in one place.