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?
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