Equivalent of sum(case when-then-else-end) SQL statements in Ibis?

[lostmygithubaccount]

I'm essentially trying to replicate this but in Ibis: https://github.com/dbt-labs/dbt-demo-data/blob/main/models/marts/orders.sql#L34-L51

these sum(case when-then-else-end) as statements are frequently used in dbt demos and can be combined with Jinja for programmatic control flow. for instance, something like:

{% for product in products %}
    sum(case when products.product_name = {{ product }} then product_price else 0 end) as subtotal_{{ product }},
{% endfor %}
    sum(product_price) as subtotal

I've achieved this in dataframe APIs with pivot methods, joining the pivoted table with another computing the overall subtotal. I feel like there has to be something easier in Ibis between the cases and filtering, but I'm struggling to figure it out. is this something trivial?

my code, minus embarrassing attempts to get this to work, is here: https://github.com/dbt-labs/dbt-demo-data/blob/cody/ibis/ibis.ipynb

[jcmkk3]

Most aggregates in ibis have a where parameter that you can use for conditional aggregation. You can also just sum a boolean comparison if you are trying to get a conditional count. Try something like this:

products.aggregate(
    products["product_price"].sum(where=product["is_drink_item"] == 1).name("subtotal_drink_price")
)

products.aggregate(
    (product["is_drink_item"] == 1).sum().name("drink_items_count")
)

There might be an even easier way, but that is how you'd translate it most directly.

[datamike]

I'm quite new to both Ibis and dbt (SQL, now that I do know ...).

I'm quite hopeful that Ibis and the more fluent Pythonic way will continue advancing, because I would so much rather read (and write) this:

products.aggregate(
    products["product_price"].sum(where=product["is_drink_item"] == 1).name("subtotal_drink_price")
)

than the dbt expression equivalent.

Also, I haven't seen much material on writing and executing data model tests with Ibis, Pandas, etc. (one of the biggest pros of the dbt ecosystem). Yet Python seems to offer a smorgasbord of ways to write unit tests.

Thanks for listening ;-)