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!