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 SQL
Class DC.Demo.Median
{

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

/// Updates temp global for a single record
ClassMethod 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)\2
	Set position2 = (total+2)\2
	Set val1 = ""
	Set val2 = ""
	Set reached = 0
	Set 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)/2
			Quit
		}
	}
	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(DROP AGGREGATE DC_Demo.Median)
	&sql(CREATE AGGREGATE 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!