Aggregate functions
These are the aggregate functions currently supported by the UNICOM Intelligence Data Model.
|
Aggregate function
|
Description
|
|
AVG(expr)
|
Returns the average of the values defined in the expression. This function can be used with numeric data only.
|
|
BASE(expr)
|
Returns the total number of cases included in the expression. Generally, the base includes every case for which the value is not NULL.
|
|
COUNT(expr)
|
Returns a count of the cases selected by the expression.
|
|
SUM(expr)
|
When used with numeric data, returns the sum of the values. When used with categorical data, it returns the union of the categorical values.
|
|
MIN(expr)
|
Returns the lowest value defined in the expression.
|
|
MAX(expr)
|
Returns the highest value defined in the expression.
|
|
STDEV(expr)
|
Returns the standard deviation of the values defined in the expression. This function can be used with numeric data only. The standard deviation is a measure of dispersion around the mean. In a normal distribution, 68% of cases fall within one standard deviation of the mean and 95% of cases fall within 2 standard deviations. For example, if the mean age is 45 with a standard deviation of 10, then 95% of the cases would be between 25 and 65 in a normal distribution.
|
Up-leving one level
In the following top-level expression the up-lev syntax has been used to up-lev the data in the Person-level Gender variable to the top level:
Sum(Person.(Gender = {male}))
This expression returns the number of males in the household.
The next expression is at the Person level and includes the up-lev syntax to promote the data in the Trip-level DaysAway variable to the Person level:
Gender = {Female} And Age > 16 And Sum(Trip.(DaysAway)) > 20
This expression selects women who took overseas trips that lasted a total of more than 20 days.
The next expression is at the Vehicle level and includes the up-lev syntax to promote the data in the Rating grid to the Vehicle level:
Sum(Rating.(Column * {Very_good}))
This expression selects vehicles for which the respondent selected the Very good category in response to at least one iteration of the Vehicle-level Rating grid.
Up-leving two levels
You can use the up-lev syntax multiple times to up-lev by more than one level. For example, the following expression is at the top (HDATA) level and the data in the Trip-level DaysAway variable has been up-leved twice to the top level.
Sum(Person.(Sum(Trip.(DaysAway > 20))))
This expression selects households in which at least one person took at least one overseas trip that lasted more than 20 days. If you want to select households in which at least one person took overseas trips that altogether lasted more than 20 days, you would need to move the comparison operator outside the innermost parentheses:
Sum(Person.(Sum(Trip.(DaysAway)) > 20))
Working with parallel levels
All of the examples so far use levels that have a direct parent-child relationship. However, some data sets contain levels that are not related to each other. For example, the diagram that shows the levels structure of the Household sample, also shows that the Person and Vehicle levels are parallel to each other (on different branches of the tree). This means that the data in the two levels is not directly related to each other. However, sometimes you might want to include data from parallel levels in an expression. For example, you might want to select men who live in households that have a motorbike. To do this, use the data in the Vehicle level in your Person-level expression.
When you include data from a parallel level in an expression, you must up-lev the data to the first common ancestor level and then down-lev the data to the level of the expression. In the Household sample, the only level that is an ancestor of both the Person and Vehicle levels is the top (HDATA) level. The following expression up-levs the Vehicle-level data to the top level and down-levs it to the Person level:
^.Sum(Vehicle.(VehicleType = {Motorbike})) > 0 And Gender = {Male}
This expression selects men and boys who live in a household that has one or more motorbike.
See also