Support for `First`/`Last` aggregations in Snowflake backend

[radeusgd]

Support for `First`/`Last` aggregations in Snowflake backend

[radeusgd]

Log of my attempts:

1. The way we did this for Postgres, e.g.

SELECT FIRST_VALUE(X) IGNORE NULLS OVER (ORDER BY Y ASC NULLS FIRST) AS "FOO" FROM TEST123 GROUP BY Z

fails with

[TEST123.X] is not a valid group by expression

The thing is - FIRST_VALUE in Snowflake is only a Window function, it is not an aggregate function. So it cannot be used as an aggregate directly. It will work if I add X and Y to GROUP BY but that changes query semantics in a way we don't want here.

2. Technically, the Window function for each group will yield the same result in that group, so I tried the ANY_VALUE trick:

SELECT ANY_VALUE(FIRST_VALUE(X) IGNORE NULLS OVER (ORDER BY Y ASC NULLS FIRST)) AS "FOO" FROM TEST123 GROUP BY Z

but that also fails with

Window function [FIRST_VALUE(TEST123.X) OVER (ORDER BY TEST123.Y ASC NULLS FIRST)] may not appear inside an aggregate function.