sql - 如何在聚合和非聚合数据集上获得相同的 AVG() 值

Question

我们有一个用于返回总平均价格的大型数据表，但是这个查询和其他查询的处理时间太长，以至于我们按国家和日期汇总了结果。

这是原始表格的示例:

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

结果在 5.254375

为了优化针对此记录集的所有查询，我们使用此表达式聚合表:

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.

但是现在在报表中，我们仍然希望能够显示正确的平均数，但是我们不能使用原始表，因为您的查询处理时间太长。

我们如何仅使用这个聚合记录集来计算总体平均价格？

Answer 1

这与 SQL 关系不大，而是简单的数学。

您需要使用原始计数 重建以更正TOTAL/COUNT表达。

我们可以从总数中计算出，它很容易出现舍入误差，但它很接近，理想情况下，如果这对您来说非常重要，那么您应该记录原始计数。

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

<表类="s-表"><头>国家总和(价格)AVG(价格)计数<日>日期 <正文>IND21.3157.10532021-01-01美国14.5824.8606666732021-01-02澳大利亚1.6383.06922021-01-03

或者我们可以重新构建计数:

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

_results 与上面相同的表。

但我们不能只使用 AVG，因为它使用结果中的行数，相反我们应该自己评估平均值:

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

或者您是否存储了计数:

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

在 sql fiddle 中查看:http://sqlfiddle.com/#!18/818872/7

我在那个数据集中看到了你的日期列，所以我们仍然可以使用 group by 在日期上汇总聚合值:

SELECT date, SUM(sumPrice) / SUM(sumPrice/avePrice) 
FROM AggregatedValues
GROUP BY date;

<表类="s-表"><头><日>日期 大道<正文>2021-01-01T00:00:00Z5.254376

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

现在最终，平均值不是5.254375的数字。如您所料，这是由于存储聚合值时精度损失所致，如果您存储了原始计数，我们将尽可能接近:

更新了 fiddle ，我原来的例子失去了精度!

但这也强调了这种类型的反向平均永远无法像使用原始集那样准确!

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

SELECT date, SUM(sumPrice) / SUM([count]) 
FROM AggregatedValues
GROUP BY date;

<表类="s-表"><头><日>日期 大道<正文>2021-01-01T00:00:00Z5.254375