On a tangent, i wonder what this op would look like in SQL? Probably would need support for filtering in a window function, which I'm not sure is standardized?
But also props to Wes McKinney for giving us a dataframe library during a time when we had none. Java still doesn’t have a decent dataframe library so we mustn’t take these things for granted.
The Pandas API is no longer the way things should be done today nor should it be in new tutorials. Pandas was the jquery of its time —- great but no longer the state of the art. But I have much gratitude for it being around when it was needed.
I disagree that Pandas is no longer state of the art: its interface is optimized for a different use case compared to Polars.
I'm not a big fan of the pandas API but it's a super useful tool nonetheless.
select id, max(views) from <tbl>
where sales > avg(sales) over (partition by id) group by 1
df |> group_by(id) |>
filter(sales > mean(sales)) |>
summarize(max(views))
df |> filter(.by=id, sales>mean(sales)) |> summarize(.by=id,max(views))
df |> summarize(.by=id, max(views[sales>mean(sales)]))https://news.ycombinator.com/item?id=30067406
And here in the docs:
https://dplyr.tidyverse.org/reference/dplyr_by.html
And maybe also:
https://www.tidyverse.org/blog/2023/02/dplyr-1-1-0-per-opera...
I guess part of it is that there’s some ‘non-locality’ in the pipeline where the grouping could be relatively distant from the operation acting on the grouped data. Similarly, you get to worry about eg grouping data that is already grouped.
I quite like the prql solution which is to have a ‘structured grouping’ where you have to delimit the pipeline that operates on grouped data, but maybe it can still lead to bad edits for complex queries.
No need to filter within the window function if you use subquery or CTE, which is supported everywhere.
-- "find the maximum value of 'views',
-- where 'sales' is greater than its mean, per 'id'".
select max(views), id -- "find the maximum value of 'views',
from example_table as et
where exists
(
SELECT *
FROM
(
SELECT id, avg(sales) as mean_sales
FROM example_table
GROUP by id
) as f --
where et.sales > f.mean_sales -- where 'sales' is greater than its mean
and et.id = f.id
)
group by id; -- per 'id'".If you mean filtering the rows in the window, you can do 'sum(case when condition then value else null end) over (window)'; if you mean selecting rows based on the value of a window function you use 'qualify' where supported or a trivial subquery or CTE and 'where' (which qualify is just shorthand for)
According to wikipedia, windowing was standardized back in 2003.