Aggregate

Projection – Abstract

Purpose: Enables simple calculations for individual property values and aggregations for lists of property values, which can optionally be formed by grouping as subsets from a basic set defined by the search.

Tooltip

• Usage: The selection for the Type of Aggregate determines whether a single value or a list of values determined by grouping is expected. The Projection defines the origin of the individual value or the values to be aggregated.

• Parameter:

  • The Name optionally defines the title of an output column (if relevant). By default, the internal name for the Type is linked to the applicable name for the Projection via the static text 'of '.

  • A selection must be made from the following options for the Type:

    • Aggregation functions for any data types: Count, Count (distinct), Min, Max, Least

    • Aggregation functions for numerical data types: Sum, Average

    • Calculation functions for numerical values: Absolute, Negate, Square root

    • Calculation functions for Boolean values: Not

    • Calculation functions for text: Length, Lower case (letters), Upper case (letters), Trim

  • The Projection determines the source from which the individual value or the values to be aggregated are obtained

• Note: Aggregation functions ignore $null values from the Projection. Calculation functions return $null as the result, for $null as the value of the Projection.

The Aggregate projection type enables various simple calculations (for individual property values) and aggregations for lists of property values, which can optionally be formed by grouping as subsets from a basic set defined by the search.

The following table divides the options for the Type of calculation/aggregation into categories and specifies for each Type which data type is supported per Projection.

• Only the types summarized here as aggregation functions can process several return values from the Projection and, if necessary, take into account subsets from a grouping in the context of the search.

• The other calculation functions enable simple calculations or 'conversions' of individual values of a specific data type from the Projection.

Configuration

Examples of the conversion of individual values

Arithmetic – Absolute (ABS): Absolute value of a numerical field as a restriction projection

The default property 'Last modifier ID' (lastModifierId) for entities usually contains a positive Long value that corresponds to the 'ID' (id) of a user account (see Users).

However, if the last change to an entity was made in the context of a guest user account (see Guest users), its 'ID' (id) is entered as a negative value in the lastModifierId property to ensure differentiation from users with the same ID.

In the data grid of an overview, the 'Last modifier ID' column appears empty by default if this property references a guest user account, because looking up the user name (see Subsearch projection), which is implemented as a column projection by default, is 'unproductive' for negative references.

Regardless of the display, the filter function for this column should be adapted so that the filter criteria for the positive Long value of an account ID can be entered, regardless of whether it is a Users or Guest users.

Configuration:

The settings for the 'Last modifier ID' column (lastModifierId) must be displayed in the data grid settings for the relevant overview.

For this column, there is no configuration for a projection in the tab for defining the Restriction projection by default. This means that by default the Projection is also a Restriction projection.

If you configure a Restriction projection as shown on the right, it overrides the default behaviour as follows:

• Instead of the text value tailored to Users (scheme: {id} - {username}), an Aggregate with the Type 'Absolute' (ABS) is used as a compare value for the filter, which is used here to remove any minus sign (for Guest users) from the Long value read from the Property projection for the lastModifierId property.

• The positive ID values for Users return the Aggregate with the Type 'Absolute' (ABS) unchanged.

• The characteristic of the filter adapts to the numerical data type.

►NOTE◄ Of course, it is not ideal that this Restriction projection cannot distinguish between a user account and a guest user account with the same ID. However, if overlaps between the IDs used are relevant in practice, it is still possible to adapt the Projection so that Users and Guest users can still be clearly distinguished from each other.

Runtime example:

Arithmetic – Negation (NEG): Negating numerical values (sign reversal)

The line items of a user-defined entity type 'Account' (see Custom type definitions) are used to record incoming and outgoing payments, which are always maintained as positive numerical values in a 'payment value' property (payment.value), regardless of their type. An associated text property 'payment type' (paymentType) is used to further classify the transaction.

By convention, payouts should be identified by key values for the 'payment type' (paymentType) that begin with the letter 'R'.

Using a tuple search that directly addresses the item level (AccountLineItem), the balances for each 'account' instance are to be calculated in such a way that the total of all payments out is deducted from the total of all payments in.

Configuration:

Arithmetic – Square root (SQRT): Square root

A tuple search for Shipments should provide an indication of the complexity of a shipment in a COMPLEXITY column, the square root of the number of different values for the 'parent line item', in addition to other key figures.

Configuration:

ID,VOLUME,COMPLEXITY

8455,1,2.0
7955,10,1.7320508075688772
7951,,1.7320508075688772
4006,2,1.7320508075688772
9251,4,1.4142135623730951
9252,4,1.0

Logic – Not (NOT): Logical negation

In a tuple search for Users, the Boolean property 'Active' should be logically reversed so that with the column labeling such as DEACTIVATED, the value $true appears for the Users for which the 'Active' property contains the value $false and vice versa.

Configuration:

►NOTE◄

• The 'Active' property for users is 'not nullable' by definition, so the return value of the Aggregate will be $true or $false in all cases.

• If the return value of the Projection can also be $null in addition to $true and $false, the Aggregate with the Type 'Not' (NOT) returns the return value $null if the original value is $null.

String processing – Length (LENGTH): Text length as filter condition

In the address property 'Account number' (address.accNumber) for Companies/Clients, a nine-digit character string is expected by convention.

A search should return a list of all Companies/Clients for which this convention for the address property 'Account number' is not fulfilled.

Configuration:

Within an OR conjunction, two instances of the Field restriction must be used to identify Companies/Clients for which the address property 'Account number' (address.accNumber) is either unassigned or not filled in at all:

• The left Field restriction evaluates the text property 'Account number' as a check value using Aggregate with the Type 'Length' to check whether the length of an existing character string deviates from the default value 9 (!=).

• The right Field restriction is required to cover the case that there is no information at all for the 'Account number'.

►IMPORTANT◄ The right Field restriction formally checks whether the 'Account number' contains an empty string (''). This condition is actually implemented in the database in such a way that it checks whether there is an empty string or 'No value' ($null).

• The Aggregate with the Type 'Length' only returns the value 0 in the special case that the address property 'Account number' actually contains an empty string ('') and not 'No value' ($null).

String processing – Upper (UPPER): Grouping users according to the first letter in the username

A CSV search should return the number of Users who share the same initial letter for the 'username' (username).

No distinction should be made between upper and lower case letters (e.g. { admin,AEINSTEIN}→ A).

Configuration:

String processing – Lower (LOWER): Upper/lower case non-specific comparison

A search should list all Users for which there is a match between the address property 'Country' (address.countryCode) and the first two characters of the internal identifier for the 'Locale' (locale).

Configuration:

►IMPORTANT◄ An Aggregate with the Type 'Lower' cannot generally be used to process the internal name of enumeration values (here: Country, Locale). This only works here in exceptional cases, as the relevant properties for the user account (locale) or the address (countryCode) are treated as text properties in the database, in contrast to the general standard. In general, properties that refer to Enumerations contain the 'ordinal' value of the Long type in the database. A Projection on such a property – e.g. the address property 'Salutation' (address.salutation) for a user that refers to the dynamic enumeration Salutation – is then not suitable as an input value for a calculation function for text processing in an Aggregate. When executing the query, an error message usually appears, which refers to the conflict of types.

String processing – Trim (TRIM): Search for 'untrimmed' texts

A search is to determine Addresses for which 'untrimmed' text – i.e. a character string with at least one bordered space – was entered as the 'Name' (name1).

Configuration:

Examples of aggregation functions

Typical example: Aggregation of key figures in a search with grouping

A tuple search determines three 'static key figures' for the use of Guest users accounts by different Companies/Clients (column: COMPANY):

• Number of Guest users per company (column: GUEST_ACCOUNTS).

• Sum of logins executed by Guest users per company (column: GUEST_LOGINS).

• Average login count executed per account per company (column: LOGIN_AVG).

Runtime example:

   <core:TupleSearchResult maxResults="100" count="3">
      <columns>
         <name>COMPANY</name>
         <name>GUEST_ACCOUNTS</name>
         <name>GUEST_LOGINS</name>
         <name>LOGIN_AVG</name>
      </columns>
      <result>
         <row>
            <item xsi:type="xsd:long">101</item>
            <item xsi:type="xsd:long">2</item>
            <item xsi:type="xsd:int">0</item>
            <item xsi:type="xsd:double">0.0</item>
         </row>
         <row>
            <item xsi:type="xsd:long">1902</item>
            <item xsi:type="xsd:long">12</item>
            <item xsi:type="xsd:int">179</item>
            <item xsi:type="xsd:double">14.916666666666666</item>
         </row>
         <row>
            <item xsi:type="xsd:long">2202</item>
            <item xsi:type="xsd:long">2</item>
            <item xsi:type="xsd:int">9</item>
            <item xsi:type="xsd:double">4.5</item>
         </row>
      </result>

Configuration:

The data structure for Guest users provides the following characteristics for our statistics:

• The 'Company' (companyId) property is a required field for the guest user account, which must be filled with a reference to the 'ID' (id) of exactly one company account (see Companies/Clients).

• The 'Number of logins' (loginCount) property provides the number of logins carried out with a guest user account as an integer.

Unusual example: Determining key figures for company accounts

A tuple search should determine three 'statistical key figures' for the addresses of Companies/Clients maintained in the system:

• Count of all Companies/Clients (column: COMPANIES).

• Count of all Companies/Clients in whose address (address) a Country is selected in the 'Country' (countryCode) property (column: WITH_COUNTRY).

• Number of different Country values in the 'Country' (countryCode) property in the addresses of companies (column: COUNTRIES).

Runtime example:

   <core:TupleSearchResult maxResults="100" count="1">
      <columns>
         <name>COMPANIES</name>
         <name>WITH_COUNTRY</name>
         <name>COUNTRIES</name>
      </columns>
      <result>
         <row>
            <item xsi:type="xsd:long">40</item>
            <item xsi:type="xsd:long">28</item>
            <item xsi:type="xsd:long">10</item>
         </row>
      </result>
   </core:TupleSearchResult>

Configuration:

Three Projections of the Aggregate type are required for the output columns of the tuple search:

Variant:

Based on the existing configuration, an additional output column (HAS_PARENT) is added which specifies the number of Companies/Clients whose list field 'parent companies' (parentCompanies) contains at least one entry.

   <core:TupleSearchResult maxResults="100" count="1">
      <columns>
         <name>COMPANIES</name>
         <name>WITH_COUNTRY</name>
         <name>COUNTRIES</name>
         <name>HAS_PARENT</name>
      </columns>
      <result>
         <row>
            <item xsi:type="xsd:long">57</item>
            <item xsi:type="xsd:long">45</item>
            <item xsi:type="xsd:long">10</item>
            <item xsi:type="xsd:long">46</item>
         </row>
      </result>
   </core:TupleSearchResult>

The following configuration for the HAS_PARENT column prevents the multiplication of the result rows before aggregation and ensures that the search returns correct data overall:

CAUTION If the database system used does not support the use of the Subsearch projection within an aggregation function, an error message is displayed. This is the case for MSSQL, for example (see 'Alternative configuration' below).

Configuration:

Alternative Configuration:

Since not every database system accepts the use of a Subsearch projection within an aggregation function, the following configuration is intended to show how the desired results can be achieved without a Subsearch projection in a given application.

In the previous approach, the Subsearch projection was used to avoid multiplying input data ('cross product' {companies} x {parent companies}) for the aggregation function.

The following alternative approach deliberately accepts the multiplication of the input data and compensates for its effect by adjusting the Type and Projection for the aggregation function.