You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
It appears there is no way to do this in PRQL right now (at least not cleanly):
SELECT
location,
day,
LAST_VALUE(temperature) IGNORE NULLS OVER (
PARTITION BY location
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS temperature,
LAST_VALUE(pressure) IGNORE NULLS OVER (
PARTITION BY location
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS pressure
FROM weather_data
Some attempts:
from weather_data
group {location}(
sort day
window rows:..0(
filter pressure != null
derive {pres = last pressure})
window rows:..0(
filter temperature != null
derive {temp = last temperature}))
Semantically, you would expect a filter in window (filter ... | derive ...) to filter the records in that window used for the later derivation, but instead the filter is applied to the entire table, which is not the right thing.
It doesn't appear possible to move the filter inside the derive clause (like derive {temp = temperature | x -> filter x !=null | last}, because filter is typed as a function relation -> relation. I'm guessing this probably won't end up being added to PRQL (absent a huge amount of work), because while DuckDB supports filter clauses in window functions, most don't.
Another attempt:
let last_non_null = col <array>-><scalar> s"LAST({col}) IGNORE NULLS"from weather_data
group {location}(
sort day
window range:..0(
derive {
pres = last_non_null pressure,
temp = last_non_null temperature
}))
This fails (no matter how you type hint) because prqlc fails to recognize last_non_null as a window function. In any case, even if this worked, you now need to change the let ... statement depending on what SQL dialect you are compiling to.
The only way I have gotten this to work is to do the windowing (partitioning + sorting + rows-between + aggregation) entirely in the target SQL dialect using a big s-string, which is not very clean. The limitations of s-strings make it impossible to fully parameterize the windowing process (e.g. there is no way to make s-string valued function that expands tuples inside the s-string, in case you want your SQL-valued function to partition by a variable number of columns).
The text was updated successfully, but these errors were encountered:
What's up?
It appears there is no way to do this in PRQL right now (at least not cleanly):
Some attempts:
Semantically, you would expect a filter in
window (filter ... | derive ...)
to filter the records in that window used for the later derivation, but instead the filter is applied to the entire table, which is not the right thing.It doesn't appear possible to move the
filter
inside the derive clause (likederive {temp = temperature | x -> filter x !=null | last}
, becausefilter
is typed as a functionrelation -> relation
. I'm guessing this probably won't end up being added to PRQL (absent a huge amount of work), because while DuckDB supports filter clauses in window functions, most don't.Another attempt:
This fails (no matter how you type hint) because prqlc fails to recognize last_non_null as a window function. In any case, even if this worked, you now need to change the
let ...
statement depending on what SQL dialect you are compiling to.The only way I have gotten this to work is to do the windowing (partitioning + sorting + rows-between + aggregation) entirely in the target SQL dialect using a big s-string, which is not very clean. The limitations of s-strings make it impossible to fully parameterize the windowing process (e.g. there is no way to make s-string valued function that expands tuples inside the s-string, in case you want your SQL-valued function to partition by a variable number of columns).
The text was updated successfully, but these errors were encountered: