question: percent_rank vs cume_dist

[xmnlab]

It seems the Ibis percent_rank operation is in fact the cume_dist SQL operation as explained at [1].

So how could we implement percent_rank SQL operation?

Suggestion 1:

• maybe ibis percent_rank could have one optional argument like cume_dist (default: True)
• and if it is false maybe we can test that using [2], but it means that we should add scipy as a dependence.

any thoughts about this issue?

refs:
[1]

ibis/ibis/tests/all/test_window.py

Line 43 in ae71b3a

# these can't be equivalent, because pandas doesn't have a way to

[2] https://stackoverflow.com/questions/39823470/getting-postgresql-percent-rank-and-scipy-stats-percentileofscore-results-to-mat

extra ref: https://riptutorial.com/sql/example/27456/percent-rank-and-cume-dist

[xmnlab]

suggestion 2:

• propose a change on pandas github to handle these 2 cases: percent rank cume_dist

maybe related to this file: https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/algos_rank_helper.pxi.in

I really don't know if this topic was discussed before, maybe @jreback could give us some information/feedback

[xmnlab]

@jreback do you know someone who could help in this discussion?

[xmnlab]

I also opened an issue on pandas github: pandas-dev/pandas#28975

[scottcode]

I don’t know much about the specific feature in question, but I wanted to chime in with a thought about functionalities in various backends.

It seems like ibis is meant to provide a unifying abstraction over many backends. The different backends may have varying functionality, but is it really ibis’s scope to fill in capabilities that are missing in a particular backend? Some functionality might just not be supported for a particular backend, or if possible add it to the backend directly.

[xmnlab]

hey @scottcode related to your question ..

IMHO

Some functionality might just not be supported for a particular backend, or if possible add it to the backend directly.

In general I think that creates an operation just inside a particular backend directly would be dangerous because another person could add the same operation in a future with different name or any small unnecessary differences ... so it would be inconsistent ..

is it really ibis’s scope to fill in capabilities that are missing in a particular backend?
I think the users should have a way to use the functions they need using ibis expressions (as much as possible).

In my own experience, I have tried to understand how pandas implement that operation .. and if pandas has this operation I have tried to port that to Ibis as similar as possible.

related to the current issue ... it seems that percent_rank and cume_dist are both used by backends such as omniscidb, postgresql, mysql, mssql ...

[xmnlab]

maybe we can use a similar approach used for ntile (#2146). In this case, the tests can compare ibis cume_dist with (pure) pandas percent_rank and we can create an ibis pandas percent_rank operation and use it to check other backends percent_rank operation.

[cpcloud]

@xmnlab Can you clarify what might be actionable here? Should we rename percent_rank to cume_dist?

[xmnlab]

hi @cpcloud
I created this PR long time ago: #2224

so basically the percent_rank tests uses pandas df.rank(pct=True) .. but this works as SQL CumeDist.

So, the easiest way would be to rename the operation to CumeDist. and for PercentRank the test should be implemented manually as described in that old PR. also some backend should change the translation to percent_rank to cume_dist.

let me know if you want more information about that.

[cpcloud]

We're now correctly implementing percent_rank everywhere, and we have #3590 for adding cume_dist, closing this out.