Databases Reference
In-Depth Information
A word on using the Distinct qualifier
In some of our previous expressions, we have used the
Distinct
qualifier in our
Count
aggregation function. The
Distinct
qualifier is used in this case to avoid
duplicate counts. However, the use of this qualifier can make the calculation perform
poorly as it causes the operation to be single-threaded.
In some cases, it is advisable to avoid using the
Count
function and the
Distinct
qualifier by creating a counter field in the script (a field with the value of
1
) and
then using a more direct aggregation such as
Sum(RouteCounter)
in the final
chart's expression.
Getting the Average Load Factor per
Route per Airline
HighCloud Airlines have a new requirement in which the
Aggr
function will prove
useful. They want to know the average load factor percentage per airline, but over
each route. In this case, a direct aggregation function like the
Avg
function will
not give the result we need because of the additional "dimension" required in the
calculation. To illustrate, take the following chart:
In this chart, we can see the different routes each carrier serves along with the
corresponding load factor. We should now take the individual load factor per route
and perform an average operation over the list of values for each airline to obtain the
value we are looking for.