How insert AVG in BETWEEN clause?

Question

For example lets take Northwind. I want to use CASE clause to create categories by comparing units_in_stock with its AVG value and place this value in multiple BETWEEN clauses. That is what I have got:

    SELECT product_name, unit_price, units_in_stock,
        CASE
            WHEN units_in_stock > (SELECT AVG(units_in_stock) + 10 FROM products) THEN 'many'
            WHEN units_in_stock BETWEEN (SELECT AVG(units_in_stock) - 10 FROM products) AND (SELECT AVG(units_in_stock) + 10 FROM products) THEN 'average'
            ELSE 'low'
        END AS amount
    FROM products
    ORDER BY units_in_stock;

According to Analyze tool in pgAdmin AVG(units_in_stock) was calculated three times. Is there a way to reduce amount of calculations?

Answer 1

You can use window functions insead of a subquery. Also, there is no need to use BETWEEN in the second WHEN condition; values that are greater than the average + 10 are handled by the first branch, and never reach the second branch.

I would phrase this as:

SELECT product_name, unit_price, units_in_stock,
    CASE
        WHEN units_in_stock >  AVG(units_in_stock) OVER() + 10 THEN 'many'
        WHEN units_in_stock >= AVG(units_in_stock) OVER() - 10 THEN 'average'
        ELSE 'low'
    END AS amount
FROM products
ORDER BY units_in_stock;

I would expect the database to optimize the query so that the window average is only computed once. If that's not the case, an alternative would be to compute the average in a subquery first:

SELECT product_name, unit_price, units_in_stock,
    CASE
        WHEN units_in_stock >  avg_units_in_stock + 10 THEN 'many'
        WHEN units_in_stock >= avg_units_in_stock - 10 THEN 'average'
        ELSE 'low'
    END AS amount
FROM (SELECT p.*, AVG(units_in_stock) OVER() avg_units_in_stock FROM products p) p
ORDER BY units_in_stock;