3 ms·
Without having checked, maybe something like: select id, max(views) from <tbl> where sales > avg(sales) over (partition by id) group by 1 In dplyr, there
by dan-robertson 2y ago
Without having checked, maybe something like:
select id, max(views) from <tbl>
where sales > avg(sales) over (partition by id) group by 1
In dplyr, there is an ‘old style’ method which works on an intermediate ‘grouped data frame’ and a new style which doesn’t. In the old style:
df |> group_by(id) |>
filter(sales > mean(sales)) |>
summarize(max(views))
In the new style, either:
df |> filter(.by=id, sales>mean(sales)) |> summarize(.by=id,max(views))
Or:
df |> summarize(.by=id, max(views[sales>mean(sales)]))
- countrymile 2y agoI understand that is the new style, but I'm not aware of the rationale of the switch to this method. The old style seems must clearer and easier to read.
- dan-robertson 2y agoTrying to find a good reference for you. There’s this from the author of dplyr: https://news.ycombinator.com/item?id=30067406 https://news.ycombinator.com/item?id=30067406 And here in the docs: https://dplyr.tidyverse.org/reference/dplyr_by.html https://dplyr.tidyverse.org/reference/dplyr_by.html And maybe also: https://www.tidyverse.org/blog/2023/02/dplyr-1-1-0-per-operation-grouping/ 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.