QEP 59: Aggregate functions for QGIS expressions

[nyalldawson]

QGIS Enhancement 59 (was 40): Aggregate functions for QGIS expressions

Date 2016/04/29 Author Nyall Dawson (@nyalldawson) Contact nyall dot dawson at gmail dot com Version QGIS 2.16 Funding Kanton of Zug, Switzerland

Summary

This proposal covers implementing aggregate functions in the QGIS expression engine, allowing calculation of various statistical and summary values from the fields within a layer.

Aggregates will be implemented in two forms:

1. Aggregate functions which operate on the values from the current layer, including the ability to calculate grouped and relational aggregates.
2. A summary aggregate function which operates on all the values from a different layer.

   Form 1: Current layer aggregates

The first set of aggregate functions will operate within the current layer only. They will support both calculation of the aggregate for ALL features within the layer, as well as for matching grouped features, and features from one of the layer's child relations.

The aggregates will be implemented through the addition of multiple aggregate functions, each representing a single aggregate calculation. For instance, “sum”, “count”, “mean”, etc.

1A Calculation of statistics

Numeric aggregates will include all statistics currently calculated by QgsStatisticalSummary (ie, those which are available in the “statistical summary dock”), including:

• Count (count(...))
• Sum (sum(...))
• Mean (mean(...))
• Median (median(...))
• Standard Deviation (stdev(...))
• Max (max(...))
• Min (min(...))
• Range (max - min) (range(...))
• Minority (least common value present) (minority(..))
• Majority (most common value present) (majority(...))
• Variety (count of distinct values) (variety(...))
• First (Q1) and Third (Q3) Quartile and Inter Quartile Range (Q3-Q1) (q1(...), q3(...), and iqr(...))

New classes for calculating string aggregates (QgsStringStatisticalSummary) and date/time aggregates (QgsDateTimeStatisticalSummary) will be created. These will be designed in a reusable manner so that the calculations can be reused by other parts of QGIS code, for instance allowing string and date/time statistics in the statistical summary dock.

QgsStringStatisticalSummary will calculate:

• count (number of non-null strings)
• count distinct strings
• count missing values (null strings)
• concatentation (ie, joining values with a delimiter, much like the Postgres expression array_to_string(array_agg(field), ',')

QgsDateTimeStatisticalSummary will calculate:

• min date/time
• max date/time
• range between min and max date/times

Both QgsStringStatisticalSummary and QgsDateTimeStatisticalSummary will have 100% unit test coverage.

1B Aggregate function syntax

Each of the aggregate functions will accept the same parameters:

• A sub-expression for the raw values used for calculating the aggregate. This sub-expression could be either a simple field reference or a calculation in itself
• An optional “group by” sub-expression. If specified, the aggregate will be calculated using only features with the same group by value as the current feature.
• An optional "filter" sub-expression. If specified, the aggregate will be calculated using only features which pass the filter criteria.

  1C Aggregate function examples

• sum("passengers"): returns the sum of the “passengers” field from all features in the layer
• sum("passengers" / 7): returns the sum of the calculation “passengers” / 7 for all features in the layer
• sum("passengers", "town" ) ( or sum("passengers", group_by := "town" ) using named parameters) : returns the sum of the “passengers” field from all features in the layer with the same “town” value as the current feature.
• sum("passengers", left("town",1 )) ( or sum("passengers", group_by := left("town",1 )) : returns the sum of the “passengers” field from all features in the layer with the same first character in the “town” field as the current feature.
• sum("passengers",,"state"='vic' or "class" > 3) ( or sum("passengers", filter := "state"='vic' or "class" > 3) : returns the sum of the “passengers” field from all features in the layer where "state"='vic' or "class">3.

  1D Caching of values

Aggregate calculation will be cached in short-term storage (within the passed QgsExpressionContext), so for instance the aggregate will only need to be calculated once for each unique group-by value for a single map render if used within data defined symbology, rather than once for every feature being rendered.

1E Relational aggregates

The aggregate functions will be accompanied by a specific aggregate_relation function, to simplify calculation of aggregates for related features. When using aggregate_relation the aggregate will be calculated using only the related child features from the matching layer relation.

Examples:

• aggregate_relation( 'my_relation', 'mean', "field_from_related_table" ) ( or aggregate_relation( relation:='my_relation', calculation := 'mean', expression := "field_from_related_table" ): returns the mean value of the “field_from_related_table” field for all matching child features using the 'my_relation' relation from the layer.
• aggregate_relation( 'my_relation', 'sum', "field_from_related_table" ) ( or aggregate_relation( relation:='my_relation', calculation := 'sum', expression := "field_from_related_table" ): returns the sum of the “field_from_related_table” field for all matching child features using the 'my_relation' relation from the layer.

Note: when using aggregate_relation the calculation sub-expression will ONLY be allowed to reference fields from the child layer.

Form 2: Summary aggregate function

The second aggregate function operates on all the values from a different layer, returning a single summary value. This will be implemented by the addition of a new aggregate function. The syntax of the aggregate function will be:

aggregate('layer name or id', 'aggregate_type', expression to aggregate, optional filter expression)

• The first parameter will be either the name or ID of a layer within the project from which to calculate the aggregate value.
• The 'aggregate_type' parameter will be a string representing the aggregate calculation to perform, eg 'sum', 'mean', 'count', etc.
• Both 'expression to aggregate' and the 'optional filter expression' are sub-expressions which reference the fields from the specified layer. The first represents the raw values to use for the aggregate calculation, the second an optional filter such that only features from the layer where the filter is true will be included in the aggregate calculation. Filters will take advantage of expression compilation to help speed up the calculation on supported providers. Note that both the expression to aggregate and the filter expression will ONLY be able to reference fields from the foreign table.

  2B Examples

• aggregate('rail_stations','sum',"passengers") ( or aggregate( layer:='rail_stations',calculation:='sum',expression:="passengers")): would sum up all values from the “passengers” field in the 'rail_stations' layer
• aggregate('rail_stations','sum', "passengers"/7) ( or aggregate(layer:='rail_stations',calculation:='sum', expression:="passengers"/7)`) : would sum up a daily average of “passengers” by dividing the “passengers” field by 7 before summing the values
• aggregate('rail_stations','sum',"passengers","class">3) ( or aggregate(layer:='rail_stations',calculation:='sum',expression:="passengers",filter:="class">3)): would sum up all values from the “passengers” field from features where the “class” attribute is greater than 3 only

  2C Caching

Aggregate calculation will be cached in short-term storage (within the QgsExpressionContext), so for instance the aggregate will only need to be calculated once for each map render if used within data defined symbology rather than once for every feature being rendered. The calculation will be designed to utilised expression compilation where available, so that if possible the calculation of the aggregate will be delegated to the provider.

Relation to virtual layers

While it is acknowledged that there is also a need for calculation of aggregates within virtual layers, expressions are prevalent throughout all areas of QGIS (data defined symbology, labeling, field calculator, reporting using atlas based compositions, etc) and there is a defined need for calculation of aggregates within expressions.

Accordingly this QEP covers only the inclusion of aggregates within the expression engine and modifications to the virtual layer feature will be out of scope.

Backwards Compatibility

N/A

Votes

(required)

[giohappy]

Great work @nyalldawson ! That's what I suggested a couple of years ago, but it was too early ;)

[mhugo]

Hi, good to see some proposition to handle this need.

I am not sure to understand why the SQL syntax is not considered. Is it really too complex ? If I am correct, the two "forms" are needed because we have no way to select with join in expressions ? But then it introduces two distinct forms that seems to me less expressive than what is possible with SQL. So then we would have different dialects in QGIS to do very similar things ...

Just a thought, what about an evolution of the expression parser to tend to SQL. Something backward compatible with the current expression, but with an optional SELECT FROM, GROUP BY and with a new type of functions (aggregates) ?

modifications to the virtual layer feature will be out of scope.

Do you see modifications to the virtual layer feature that would be needed to handle aggregates ?

Actually, we could have a function sql("") that would call the virtual layer engine to compute aggregates in an expression ...

[nyalldawson]

@mhugo

Re SQL:

I've given long deliberation to this, but in the end reached the conclusion that it's a mistake to try to make the expression engine follow SQL syntax. Here's why:

• the expression engine is designed around returning a SINGLE value, not a row or table of values, whereas SQL is designed around returning rows/tables of values.
• this means we would need all kinds of weird variations from standard SQL syntax to do what the expression engine currently does. Eg, how would field calculator work? The options would be:
  1. Go with full SQL: SELECT some_col FROM source_table where id=row.id. That's definitely not user friendly, and a huge step backward from just "some_col"
  2. Or go with some custom SQLish statement: SELECT some_col FROM source_table: Not obvious to either inexperienced SQL users OR experienced SQL users, who would expect this to return all records from source_table, not a single value.
  3. Some horrible mashup: SELECT some_col : No benefits at all.
• for similar reasons, I don't think trying to force the SQL GROUP BY syntax into expressions makes sense:
  1. You could try: sum("passengers") group by city - that's OK for simple cases, but breaks for things like sub-expression based aggregates.
  2. Eg trying to sum the "passengers" field + 200 - you'd either need sum("passengers" + 200 group by city) (nothing like real SQL) or sum("passengers" + 200) group by city (kind of semi-real SQL, but wouldn't work in any other database engine). The latter option also won't work for complex cases like sum("passengers") group by city / sum("population") group by state - it forces you back to non standard SQL, and the only way around this would be having to do subselects or use CTE WITH clauses... none of this is practical for beginner/intermediate SQL users. A function-based approach however DOES make this calculation achievable for beginner (or intermediate?) users.

That's why I fell back to the function-based approach. The entire expression engine has been designed around use of nested functions. And honestly, I think this approach makes sense for the use case of expressions, which is returning a single value result from an expression calculation. Trying to twist the expression engine too closely around SQL syntax is just going to result in a mess.

That's why I believe we've got 2 totally different use cases here:

1. SQL based virtual tables (out of scope for this work)
2. Use of aggregate calculation functions within expressions (what this QEP covers)

Hope that helps clarify!

[nyalldawson]

Updated, with the following changes based on @wonder-sk's feedback:

• the relational support has been removed from the generic aggregate functions and moved to it's own aggregate_relation function. This was done to provide cleaner distinctions between the relational/non-relational use cases. See section 1E.
• A filter parameter has been added to the aggregates, to support calculating aggregates based on a filtered subset of the current layer. See section 1B/1C.

[nyalldawson]

Any further feedback on this? I'd like to get this work in for 2.16, and freeze is closing in. I've got an informal "looks good" from @wonder-sk (via hangouts), but am I good to go on this?

[NathanW2]

+1 from me. I don't see anything bad with this and agree reinventing SQL syntax isn't for here. These can still be used in the virtual layers so good win I think.

[nyalldawson]

Implemented in https://github.com/qgis/QGIS/commit/307aabd66ac089cba9f2556028d0b81b328cc345

[DelazJ]

feature implemented so maybe, should we close this QEP

[jmelomunicipia]

I've been trying to use this feature but I'm not being able to make it work. You describe the feature as aggregate_relation but in QGIS 2.16 or 2.18 the feature is listed as relation_aggregate. I'm using this syntax: relation_aggregate(relation := 'tubo_et_face_et_inicial', aggregate := 'sum', expression := "compr_linear") to try to sum up all the values in the "compr_linear field of the child table, to a virtual field in the parent table. Is this syntax correct, or am I doing something wrong?

[geodatup]

good feature, but I am not be able to make it work on virtual layer. This work nicely on standard field.

[nicolasmcfadden]

Hello, I'm very sorry if this isn't the right place to post this but I think that my question could be a complimentary enhancement for the QEP so here I go... I've been using an aggregate function with a concatenate attribute (because I need access to the layer attribute that the normal concatenate function doesn't have) to build test sheets with an Atlas. However using the concatenate parameter of the doesn't allow use of a group_by function and the list i generate appears in a random order.

I had posted a ticket on gis stackexchange explaining my problem: https://gis.stackexchange.com/questions/268432/grouping-and-sorting-a-list-of-equipment-using-an-aggregate-function-in-the-prin

I haven't gotten an answer but thought that this could be an enhancement proposal to the aggregate function? I'm very sorry if this isn't the right place for posting this but I do love your work on QGIS3.

[nyalldawson]

@nicolasmcfadden

That enchantment is likely to come for 3.2

[nicolasmcfadden]

Ok great to know! Thx for all your work!

[nicolasmcfadden]

Hello guys, any news on this?

[andreasneumann]

I think we should really close this QEP. Any additional discussions and enhancements should be treated separately. The work of the original QEP has been implemented quite a while ago.

[nicolasmcfadden]

I definitely see your point but all other similar functions have a group by option, isn't this important for completing this QEP?

[nicolasmcfadden]

Hello everyone, So is this QEP closed or might a group_by functionality be added? Thx for your work

[mauriciomarquezgoa]

Hello, I'm just trying to make a group_by to this functionality and I can not find a way to do it. It would be interesting if they could add the possibility of doing the order by even. Thanks for the excellent work, greetings!

[DelazJ]

@mauriciomarquezgoa the order by is already implemented and available in nightly build if you want to test (https://github.com/qgis/QGIS/pull/9945). And I think group_by is already available, no? Anyway, I think discussion on features should be handled as feature request report and this QEP closed. @NathanW2 ?

[M-Rick]

@mauriciomarquezgoa I think group_by is already available, no?

Hi Well it doesn't seem to be. I have tried to use it and I get an error with it.

aggregate(
layer:='Flats',
aggregate:='sum',
group_by:="street",
expression:=IF("building_flats" IS NULL,0,to_real("building_flats"))
)

Erreurs de l'Analyseur: Invalid QgsExpressionFunction::Parameter name 'group_by' for aggregate

[M-Rick]

Any news for this?

[Fertuesta25]

Is it possible to implement filtering in the relation_aggregate function?