How to get the same value of AVG() on both aggregated & non-aggregated dataset

user_7224071 0
Viewed 52

We have a large data table that was being used to return total average price, however this and other queries were taking so long to process that we have aggragated the results by country and day.

This is an example of the original table:

Country   |  code | Price | Date 
--------------------------------------
IND       |  XXZ  | 7.435 | 2021-01-01
IND       |  XSZ  | 7.445 | 2021-01-01
IND       |  XAZ  | 6.435 | 2021-01-01
USA       |  XYN  | 2.524 | 2021-01-02
USA       |  AYD  | 9.524 | 2021-01-02
USA       |  XYD  | 2.534 | 2021-01-02
AUS       |  YYB  | 1.819 | 2021-01-03
AUS       |  YSB  | 4.319 | 2021-01-03
--------------------------------------
                 AVG(Price) = 5.254375

SELECT AVG(Price) from table

Results in 5.254375

To optimise all queries against this recordset, we aggregate the table using this expression:

SELECT country,sum(price),Avg(price),date 
FROM table 
GROUP BY country,date

Country |  sum(Price)| AVG(Price) | Date 
----------------------------------------------
IND     |  21.315    | 7.105      | 2021-01-01
USA     |  14.582    | 4.86066667 | 2021-01-02
AUS     |  1.638     | 3.069      | 2021-01-03
-----------------------------------------------
                   Avg = 5.011555557

When I take the Avg(Avg(Price)) = 5.011555557, this value doesn't match with the AVG(Price) = 5.254375 on non-aggregated data.

However now in the report, we still want to be able to show the correct average figure, but we cannot use the original table because thee query takes too long to process.

How can we calculate the overall average price, only using this aggregated record set?

presto amazon-athena math sql
2 Answers

This is not really so much to do with SQL, but simple math.

You need to use the original count to rebuild to correct TOTAL/COUNT expression.

We can calculate that from the total, it is highly subject to rounding errors, but it gets close, ideally, if this is super important to you, then you should record the original count.

SELECT country, sum(price), Avg(price), Count(1), date 
FROM table GROUP BY by country, date
Country sum(Price) AVG(Price) Count Date
IND 21.315 7.105 3 2021-01-01
USA 14.582 4.86066667 3 2021-01-02
AUS 1.638 3.069 2 2021-01-03

Or we can re-construct the count:

SELECT Country, totalPrice, avePrice, totalPrice/avePrice, Date
FROM AggregatedValues

_results the same table as above.

But we can't just use AVG, because that uses the number of rows in the result, instead we should evaluate the average ourselves:

SELECT SUM(sumPrice) / SUM(sumPrice/avePrice) FROM AggregatedValues

Or is you had stored the count:

SELECT SUM(sumPrice) / SUM(Count) FROM AggregatedValues

Check this out in sql fiddle: http://sqlfiddle.com/#!18/818872/7

I see your date column in that dataset, so we can still rollup the aggregated values using group by over the date:

SELECT date, SUM(sumPrice) / SUM(sumPrice/avePrice) 
FROM AggregatedValues
GROUP BY date;
date Ave
2021-01-01T00:00:00Z 5.254376

http://sqlfiddle.com/#!18/818872/9

Now ultimately, the average is not the figure of 5.254375 you were expecting, this is due to loss of precision when you store the aggregated value, if you had stored the original count though, we get as close as we can get:

Updated fiddle, my original example lost precision!

But that also highlighted how this type of reverse average can never be as accurate as using the original set!

http://sqlfiddle.com/#!18/27e7c/1

SELECT date, SUM(sumPrice) / SUM([count]) 
FROM AggregatedValues
GROUP BY date;
date Ave
2021-01-01T00:00:00Z 5.254375
user_16902170

You have 2 options

A: Use another query that only gets average and doesn't return lines

B: Get sum of all prices per country and count of records per country. Then you can get avg by dividing total of all price sums with all record count

user_76896070
Related