为什么索引视图不能有MAX（）聚合？ [英] Why can't indexed views have a MAX() aggregate?

问题描述

我一直在尝试一些索引视图并给我留下了深刻的印象，但我几乎总是需要最大或最小，并且无法理解为什么它不适用于这些，有人可以解释为什么吗？

I have been trying out a few index views and am impressed but I nearly always need a max or a min as well and can not understand why it doesn't work with these, can anyone explain why?

我知道他们不被允许，我只是无法理解为什么！计数等是允许的，为什么不MIN / MAX，我正在寻找解释......

I KNOW they are not allowed, I just can't understand why!!! Count etc. is allowed why not MIN/MAX, I'm looking for explanation...

推荐答案

这些聚合是不允许的因为它们不能仅根据更改的值重新计算。

These aggregates are not allowed because they cannot be recomputed solely based on the changed values.

某些聚合，例如 COUNT_BIG（）或 SUM（），只需查看更改的数据即可重新计算。这些在索引视图中是允许的，因为如果基础值发生更改，则可以直接计算该更改的影响。

Some aggregates, like COUNT_BIG() or SUM(), can be recomputed just by looking at the data that changed. These are allowed within an indexed view because, if an underlying value changes, the impact of that change can be directly calculated.

其他聚合，例如 MIN（）和 MAX（），只能通过查看正在更改的数据来重新计算。如果删除当前为max或min的值，则必须在整个表中搜索并找到新的max或min。

Other aggregates, like MIN() and MAX(), cannot be recomputed just by looking at the data that is being changed. If you delete the value that is currently the max or min, then the new max or min has to be searched for and found in the entire table.

同样的原则适用于其他聚合，例如 AVG（）或标准变体聚合。 SQL无法仅根据更改的值重新计算它们，但需要重新扫描整个表以获取新值。

The same principle applies to other aggregates, like AVG() or the standard variation aggregates. SQL cannot recompute them just from the values changed, but needs to re-scan the entire table to get the new value.

这篇关于为什么索引视图不能有MAX（）聚合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！