I'm trying to create an aggregate UDF, for example something like sum or median.
The documentation and examples at https://docs.snowflake.net/manuals/sql-reference/udf-sql.html and https://docs.snowflake.net/manuals/sql-reference/sql/create-function.html don't explain how to do so.
Can someone please explain how and/or provide a MWE?
you want to use a javascript user defined table function of which that have a sum example here
The main gotcha's you want to look out for, is inside the UDF code the SQL parameters names are uppercase, you can see this in the examples, but if you missed noting it, it can lead to lots of head banging. Also Javascript has no int types so, all value have to go in/out via a double, but an int32 can safely be stored in a double, so that is not a major concern.. and if you need more precision, you might want to make you function output not the final "sum" but return multiple values one of which is the an aggregate key, and then sum in SQL space.