More efficient way to get aggregate results in different groupings?

I have a table like:

id    category    subcategory     value0    value1    value2
1     1           1               100       324       940
1     1           2               222       404       1000
1     2           3               333       304       293
1     2           3               490       490       400
1     3           2               140       400       499

I want to calculate AVG(value0) based on only id, category and AVG(value1), AVG(value2) based on id, category, subcategory.

Right now, I'm calculating this by:

SELECT DISTINCT
    id
  , category
  , sub-category
  , AVG(value0) OVER (PARTITION BY id, category)
  , AVG(value1) OVER (PARTITION BY id, category, subcategory)
  , AVG(value2) OVER (PARTITION BY id, category, subcategory)
FROM my_table

I'm using the above code in a longer query but it's resulting in this error, so I was hoping to better optimize it by not using window functions: Resources exceeded during query execution. The query could not be executed in the allotted memory. Peak usage: 133% of limit. Top memory consumer: OVER() clauses: 100%.

I was thinking something like:

SELECT 
    id
  , category
  , subcategory
  , AVG(value1)
  , AVG(value2)
FROM my_table
GROUP BY 1, 2, 3

But I'm unsure of how to tie in my AVG(value0) results.

CodePudding user response：

You can LEFT JOIN the results of the two separate aggregation queries, using the two columns id and category:

SELECT * FROM

(SELECT 
  id,
  category,
  subcategory,
  AVG(value1) AS avgvalue1,
  AVG(value2) AS avgvalue2
FROM your_table
GROUP BY 1, 2, 3)

LEFT JOIN

(SELECT 
  id,
  category,
  AVG(value0) AS avgvalue0
FROM your_table
GROUP BY 1, 2)

USING (id, category)