Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/PRQL/prql/issues/2723 below:

Window functions, revisited · Issue #2723 · PRQL/prql · GitHub

Working on the type checking lately, I've realized that window functions don't quite fit into the current type system.

If we ignore that we coerce scalars into tuples of scalars (select x into select {x}), the type of select would be:

let select = columns<{scalar..}> rel<relation> -> <relation> ...

... which says that it takes a tuple of any scalars and a relation to produce another relation.

But if you have:

select {x, sum x, lag 1 x}

... the type of x and sum x would be scalar, but type of lag 1 x would be [scalar] (array of scalars).

Also, what's the type of x? I just said that it is scalar, but how can it then be passed into sum and lag, which both expect an array of scalars?

What I propose to do:

• change window function to work similar to select and derive:

  select {x}
  window {lag 1 x}

• ban using aggregation or window functions in all functions except aggregate and window,

This would mean that:

• in select, column references resolve to scalars and the resulting tuple contains scalars,
• in window, column references resolve to arrays of scalars and the resulting tuple contains arrays of scalars
• in window, you cannot do x + y, because x is an array and y is an array,
• in aggregate, column references resolve to arrays of scalars and the resulting tuple contains scalars,
• in aggregate, you cannot do sum (x + y), because x is an array and y is an array, but you could do (sum x) + (sum y), because the sums are scalars.

Essentially, select and derive would operate row-wise, window would operate column-wise and aggregate would do something in-between.

Examples:

  from tracks
- derive {normalized_price = price / average price}
+ window {avg_price = average price}
+ derive {normalized_price = price / avg_price

  from tracks
- derive {rnk = rank}
+ window {rnk = rank}

This would also resolve the unexpected "windowing by default" behavior.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4