5 ms·

The difference is a sanely and presciently designed expression API, which is a bit more verbose in some common cases, but is more predictable and much more expr

The difference is a sanely and presciently designed expression API, which is a bit more verbose in some common cases, but is more predictable and much more expressive in more complex situations like this. 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?

andy81 2y ago
Here's an example implementation in MSSQL - https://data.stackexchange.com/stackoverflow/query/edit/1873356 https://data.stackexchange.com/stackoverflow/query/edit/1873... No need to filter within the window function if you use subquery or CTE, which is supported everywhere.
- deleted 2y ago
  [deleted]
capitainenemo 2y ago
https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardization_history https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardiza... According to wikipedia, windowing was standardized back in 2003.
hobs 2y ago
-- "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'".
wenc 2y ago
Props to Ritchie Vink for designing polars. 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.
- tomrod 2y ago
  Well said.
- nerdponx 2y ago
  I think it's worth giving some credit to prior art here. Polars was heavily inspired by the "Spark SQL" API, and Pandas by R data frames. I disagree that Pandas is no longer state of the art: its interface is optimized for a different use case compared to Polars.
  - disgruntledphd2 2y ago
    I think a lot of the weirdness in pandas comes from firstly the original focus on time series, and secondly the attempt to make logical indexing work like it did in R. I'm not a big fan of the pandas API but it's a super useful tool nonetheless.
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 ago
  I 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 ago
    Trying 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.
bcoates 2y ago
If I understand what you mean, it's supported everywhere that has windowing at all afaik. 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)

andy81 2y ago

Here's an example implementation in MSSQL - https://data.stackexchange.com/stackoverflow/query/edit/1873356 https://data.stackexchange.com/stackoverflow/query/edit/1873... No need to filter within the window function if you use subquery or CTE, which is supported everywhere.

deleted 2y ago
[deleted]

capitainenemo 2y ago

https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardization_history https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardiza... According to wikipedia, windowing was standardized back in 2003.

hobs 2y ago

-- "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'".

wenc 2y ago

Props to Ritchie Vink for designing polars. 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.

tomrod 2y ago
Well said.
nerdponx 2y ago
I think it's worth giving some credit to prior art here. Polars was heavily inspired by the "Spark SQL" API, and Pandas by R data frames. I disagree that Pandas is no longer state of the art: its interface is optimized for a different use case compared to Polars.
- disgruntledphd2 2y ago
  I think a lot of the weirdness in pandas comes from firstly the original focus on time series, and secondly the attempt to make logical indexing work like it did in R. I'm not a big fan of the pandas API but it's a super useful tool nonetheless.

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 ago
I 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 ago
  Trying 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.

bcoates 2y ago

If I understand what you mean, it's supported everywhere that has windowing at all afaik. 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)