Histogram Bin Dimension

I need a dimension for rrder value buckets (or bins) to use in a histogram chart. I used the postgres width_bucket function like so:

orderValueBucket: {
      sql: `width_bucket(order_value, 0, 100, 10) * 10`,
      type: `number`
    }

But I’d like the min and max to be the minimum and maximums of the order_value instead of hardcoded 0 and 100.

I got this far with window functions in SQL

SELECT 
    count(*),
    width_bucket(order_value, 0, 100, 10) AS bucket,
    MAX(MAX(order_value)) OVER() as max_value,
    Min(Min(order_value)) OVER() as min_value
FROM shopify_orders  
GROUP BY bucket 
ORDER BY bucket;

But I can’t figure out how to actually use the max_value and min_value variables in the width_bucket function.

Any help would be much appreciated.

Hi @kevincmclaughlin :wave:

Perhaps you could try adding new measures for MIN(order_value) and MAX(order_value):

 minOrderValue: {
    sql: `order_value`,
    type: `min`
  },
 maxOrderValue: {
    sql: `order_value`,
    type: `max`
  },
 orderValueBucket: {
    sql: `width_bucket(order_value, ${CUBE.minOrderValue}, ${CUBE.maxOrderValue}, 10) * 10`,
    type: `number`
  },

Thanks @hassankhan. Unfortunately, that won’t work because it results in the following query error

aggregate functions are not allowed in GROUP BY

because the query ends up like this:

width_bucket(order_value, min(order_value), max(order_value), 10) * 10

and you can’t put aggregate functions (min & max) in a groupby

Hi @kevincmclaughlin :wave:

It looks like maybe orderValueBucket is a measure rather than a dimension - could you try moving its definition to dimensions and seeing if the problem persists?

Hi @hassankhan,

The orderValueBucket is a dimension actually. It just didn’t look like it in my shortcode. Any other thoughts?

Kevin

This works as raw SQL but I can’t figure out how to do it in cubejs.

WITH min_max AS (
    SELECT 
        min(order_value) AS min_val,
        max(order_value) AS max_val
    FROM orders
) 
SELECT 
    count(*),
    width_bucket(order_value, min_val, max_val, 10) AS bucket
FROM orders, min_max  
GROUP BY bucket 
ORDER BY bucket;