Aggregation functions (a.k.a. window functions)

205
0
04-16-2020 07:27 AM
Status: Open
Labels (1)
AngeloTaylor2
New Contributor III

There are a handful of what could be considered missing aggregate, accumulation and/or window functions in the ArcGIS Pro FIELD CALCULATOR that would demonstrate a common and modern degree of calculation power (which I think a large % of non-gis business professionals would expect and most gis professionals would love to have given exposure to modern tools (e.g. SQL, Tableau etc.)).  These types of aggregate functions have been in these old & new software for over 10 years now.  These missing functions represent a huge speed/productivity/ease-of-use boost analysts new and old and thus lower barriers to adoption in business settings:

Aggregate Window Functions

  • SUM (), MAX (), MIN (), AVG (). COUNT ()

Cumulative Window Functions (accumulate a sorted or unsorted table's record values row-by-row across an entire table, or across other field(s) representing categories)

  •  Sum()

Analytic/Ranking Window Functions

  • RANK (), DENSE_RANK (), ROW_NUMBER (), NTILE (), PERCENTILE()

Value Window Functions

  • LAG (), LEAD (), FIRST_VALUE (), LAST_VALUE ()

In implementation, these functions could additionally be added to a geoprocessing toolbox.

Randomly selected reference material:

https://www.sqlshack.com/use-window-functions-sql-server/ 

https://www.youtube.com/watch?v=H6OTMoXjNiM