feat: Way to calculate cumulative product over window

[PtrBld]

Is your feature request related to a problem?

I am trying to calculate the cumulative product over a window and can not figure out how I would do this in ibis.
In pandas you would have this: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.cumprod.html

What is the motivation behind your request?

No response

Describe the solution you'd like

Similar to the cumsum functionality:
cumulative_col=.col.cumsum().over(ibis.window(order_by="date", group_by=["grouper"]))
I would expect something similar for products:
cumulative_col=.col.cumprod().over(ibis.window(order_by="date", group_by=["grouper"]))

What version of ibis are you running?

9.5.0

What backend(s) are you using, if any?

DuckDB

Code of Conduct

• I agree to follow this project's Code of Conduct

[gforsyth]

Thanks for raising this @PtrBld !

So it's a bit odd, but most SQL engines don't implement a product function which is why Ibis doesn't have one. DuckDB does have a product function, but generally we don't expose functionality unless it's useful in at least two of our backends. We probably could implement product using sum, exp and the natural log for a few of our backends, but that also seems like a potential footgun if a column has a 0 in it.

Anyway, for a temporary workaround (only for DuckDB) that is a bit awkward, but does work, you can do something like this:

[nav] In [1]: import ibis
         ...: ibis.options.interactive = True
         ...: t = ibis.memtable(
         ...:     {
         ...:         "id": [1, 2, 3, 4, 5, 6],
         ...:         "grouper": ["a", "a", "a", "b", "b", "c"],
         ...:         "values": [3, 2, 1, 2, 3, 2],
         ...:     }
         ...: )

[ins] In [2]: t
Out[2]: 
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━┩
│ int64 │ string  │ int64  │
├───────┼─────────┼────────┤
│     1 │ a       │      3 │
│     2 │ a       │      2 │
│     3 │ a       │      1 │
│     4 │ b       │      2 │
│     5 │ b       │      3 │
│     6 │ c       │      2 │
└───────┴─────────┴────────┘

[ins] In [3]: import ibis.expr.datatypes as dt

[ins] In [4]: @ibis.udf.agg.builtin
         ...: def product(x) -> dt.float64:
         ...:     ...
         ...: 

[ins] In [5]: t.mutate(cumprod=product(t.values).over(ibis.cumulative_window(order_by
         ...: ="id", group_by="grouper"))).order_by("id")
Out[5]: 
┏━━━━━━━┳━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
┃ id    ┃ grouper ┃ values ┃ cumprod ┃
┡━━━━━━━╇━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ int64 │ string  │ int64  │ float64 │
├───────┼─────────┼────────┼─────────┤
│     1 │ a       │      3 │     3.0 │
│     2 │ a       │      2 │     6.0 │
│     3 │ a       │      1 │     6.0 │
│     4 │ b       │      2 │     2.0 │
│     5 │ b       │      3 │     6.0 │
│     6 │ c       │      2 │     2.0 │
└───────┴─────────┴────────┴─────────┘

[PtrBld]

Thanks @gforsyth for your quick response! I already knew it was kind of a odd request, but I think there is value in maintaining similar API capabilities to pandas. But I probably agree that it is not really maintainable especially with the range of available backends.

For the moment I have solved the issue in a similar fashion to your suggestion. Works for now...