Writing a function (UDF) in SQL to auto populate Group By Numbers

user_10891424 0
Viewed 101

In Snowflake, you can write a UDF to automate many repetitive tasks. One of which I'd like to write a function to is to automatically populate Group By 1,2,3,4,5... columns. Frankly, I am curious why no modern databases do it, cause after all, it should be relatively easy to figure out which column in the result is a dimension and which is an aggregation.

The UDF can be written in Javascript or some other kind of languages.

Any thoughts or suggestions on how I can proceed with this idea?

snowflake-cloud-data-platform user-defined-functions sql python javascript
1 Answers

Such functionality is supported by dbt macros. Using a macro from a package:

select
  field_1,
  field_2,
  field_3,
  field_4,
  field_5,
  count(*)
from my_table
{{ dbt_utils.group_by(5) }}

It requires to know how many non-aggregated column exist in advance. More at: group_by

I am curious why no modern databases do it, cause after all, it should be relatively easy to figure out which column in the result is a dimension and which is an aggregation.

The closest equivalent is GROUP BY ALL from DuckDB: Friendlier SQL with DuckDB

user_50708790
Related