Writing a user-defined aggregate function in IRIS - example: Median

User-defined aggregate functions have been supported in IRIS since 2021.1.0. I'd wished upon a star for these years ago before finding a secret hacky way to override MAX and MIN in a custom datatype, but didn't get a chance to actually try one out until today. I thought it was an interesting experience/example - the question of how to get a Median in IRIS SQL came up once before - so I'm sharing it here without too much further comment.

One caveat: UDAFs don't have the nice object/SQL parity that other types of functions do, so you actually need to run SQL to define the aggregate function (helpfully wrapped in a classmethod in the below example). Compiling the class alone isn't enough.

/// Class implementing a Median aggregate function for IRIS SQLClass DC.Demo.Median
{

/// Returns a new global ref in IRISTEMP to use to store intermediate resultsClassMethod Initialize() As%String [ PublicList = ref, SqlProc ]
{
	New ref
	Set ref = $Name(^IRIS.Temp.UDAF.Median($Increment(^IRIS.Temp.UDAF.Median)))
	Set @ref = 0Quit ref
}

/// Updates temp global for a single recordClassMethod Iterate(ref As%String, value As%Numeric) As%String [ SqlProc ]
{
	If (value '= "") {
		Do$Increment(@ref)
		Do$Increment(@ref@(+value))
	}
	Quit ref
}

/// Finds the actual median (possibly an average of the two middle values)ClassMethod Finalize(ref As%String) As%Numeric [ SqlProc ]
{
	Set median = ""Set total = @ref
	Set position1 = (total+1)\2Set position2 = (total+2)\2Set val1 = ""Set val2 = ""Set reached = 0Set key = ""For {
		Set key = $Order(@ref@(key),1,refCount)
		Quit:key=""set reached = reached + refCount
		if (reached >= position1) && (val1 = "") {
			Set val1 = key
		}
		if (reached >= position2) && (val2 = "") {
			Set val2 = key
		}
		If (val1 '= "") && (val2 '= "") {
			Set median = (val1+val2)/2Quit
		}
	}
	Kill @ref
	Quit median
}

/// To actually define the UDAF from an SQL perspective, call this classmethod.ClassMethod Define()
{
	// Drop the function in case something has changed
	&sql(DROPAGGREGATE DC_Demo.Median)
	&sql(CREATEAGGREGATE DC_Demo.Median(arg NUMERIC) RETURNS NUMERIC
	   INITIALIZE WITH DC_Demo.Median_Initialize
	   ITERATE WITH DC_Demo.Median_Iterate
	   FINALIZE WITH DC_Demo.Median_Finalize)
	$$$ThrowSQLIfError(SQLCODE,%msg)
}

}

Hopefully this helps someone!