Query Builder, Over() funtion?

Frappe Framework
, , , ,
1

Hello,

What is the propper way to use Over() function from SQL using frappe.qb?

example:

(count(i.amount) * 100.0 / sum(count(i.amount)) over())
2

You can find examples of usages for OVER in PyPika’s official docs.

from pypika.analytics import Median, Avg, StdDev
from frappe.query_builder import DocType

customer_dimension = DocType('Customer Dimension')

median_income = Median(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('MEDIAN')
avg_income = Avg(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('AVG')
stddev_income = StdDev(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('STDDEV')

query = frappe.qb.from_(customer_dimension) \
    .select(median_income, avg_income, stddev_income) \
    .where(customer_dimension.customer_state.isin(['DC', 'WI'])) \
    .orderby(customer_dimension.customer_state)
1 Like
3

Can you help me with the solution for the above example, please?

I already read the entire pypika documentation before posting my question here.

I have tried to use over as the example you provided but with Sum and Count functions while providing the column I want to work on to the over() function, but both tries error out with the following message:

AttributeError: 'Sum' object has no attribute 'over'

even when placing it after my calculation it errors saying:

AttributeError: 'ArithmeticExpression' object has no attribute 'over'

thanks for your help.

4

The example I’ve shared worked flawlessly. I’ve shared the library version in case you’re running on a dated version which has a bug:

In [2]: from pypika.analytics import Median, Avg, StdDev
   ...: from frappe.query_builder import DocType
   ...: 
   ...: customer_dimension = DocType('Customer Dimension')
   ...: median_income = Median(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('MEDIAN')
   ...: avg_income = Avg(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('AVG')
   ...: stddev_income = StdDev(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('STDDEV')

In [3]: frappe.qb.from_(customer_dimension) \
   ...:     .select(median_income, avg_income, stddev_income) \
   ...:     .where(customer_dimension.customer_state.isin(['DC', 'WI'])) \
   ...:     .orderby(customer_dimension.customer_state)
Out[3]: SELECT MEDIAN(`annual_income`) OVER(PARTITION BY `customer_state`) `MEDIAN`,AVG(`annual_income`) OVER(PARTITION BY `customer_state`) `AVG`,STDDEV(`annual_income`) OVER(PARTITION BY `customer_state`) `STDDEV` FROM `tabCustomer Dimension` WHERE `customer_state` IN ('DC','WI') ORDER BY `customer_state`

In [4]: pypika.__version__
Out[4]: '0.48.9'

Regarding the example you’ve shared, there are at least a couple of glaring problems:

  • The code is syntactically incorrect. There is a dangling over call.
  • Are you using the sum, count functions from pypika.analytics as I specified earlier? It doesn’t appear to be, the case is a giveaway - You ought to import Count & Sum from said module and use them. Not Python builtins, they aren’t the same as the ones defined in PyPika or frappe.query_builder modules.
(count(i.amount) * 100.0 / sum(count(i.amount)) over())
6

Thank you so much, this fixed the issue, I have imported the Sum and Count funtions directly, and now every thing works fine :heart:

1 Like