help: workaround for WHERE clause cannot contain window functions

[NickCrews]

I want to find outliers within each group:

import ibis
from ibis import _

t = ibis.examples.penguins.fetch()
is_outlier = _.body_mass_g > _.body_mass_g.quantile(0.95).over(group_by=["sex", "species"])
t[is_outlier]

but this causes ProgrammingError: (duckdb.BinderException) Binder Error: WHERE clause cannot contain window functions!
This programming error comes from how this results in ambiguous SQL. But that is ambiguous because SQL is "flat", so the engine doesn't know which comes first, the window or the filter. But in Ibis, where we have a treelike structure, we should know that the window happens first (as the inner Expr), then the filter (as the outer Expr). Am I understanding this correctly?

I can get this working in multiple steps with

t2 = t.group_by(["sex", "species"]).mutate(
    is_outlier=_.body_mass_g > _.body_mass_g.quantile(0.95)
)
t2[_.is_outlier]

but this just feels clunky, I don't want to have to create a second relation with the temp column floating around.

1. Any slick code golf solutions that are cleaner than my workaround?
2. Do we want/can we make ibis do what I want here?

[ianmcook]

FWIW, dbplyr allows window functions in filter() and automatically converts them to SQL queries with the window functions in the SELECT list inside a subquery. For example:

library(dplyr)
con <- DBI::dbConnect(duckdb::duckdb())
t <- copy_to(con, palmerpenguins::penguins)

t |> 
  filter(body_mass_g > mean(body_mass_g, na.rm = TRUE), .by = c(sex, species)) |>
  show_query()

## <SQL>
## SELECT
##   species,
##   island,
##   bill_length_mm,
##   bill_depth_mm,
##   flipper_length_mm,
##   body_mass_g,
##   sex,
##   "year"
## FROM (
##   SELECT *, AVG(body_mass_g) OVER (PARTITION BY sex, species) AS q02
##   FROM "palmerpenguins::penguins"
## ) q01
## WHERE (body_mass_g > q02)

[cpcloud]

Many DBs are adding support for QUALIFY which is effectively a way to allow window functions in WHERE clauses without having to literally allow them in WHERE. Perhaps we can leverage that where possible.

[cpcloud]

I think we'll have a solution for this soon™️

sqlglot supports transforming QUALIFY into WHERE (this is only important for some backends), but we need a way in ibis to represent a predicate that should be compiled into a QUALIFY clause. We may be able to do this with an internal-only ops.Value, or by partitioning predicates into a new qualify field on ops.Selection.

From there, sqlglot will compile either to QUALIFY if the backend supports it, or the equivalent projection + WHERE similar to what dplyr is doing.

[NickCrews]

Wow, that sounds like no small feat! Looking forward to it, let me know how I can help!