PostgreSQL 聚合 是从数据中获取有意义的见解的必要步骤 - 它将原始数据的行和行转换为有用的信息，这对决策至关重要。它接受多行数据并输出单行，使用户能够高效地分析和汇总数据。
PostgreSQL 支持许多内置的聚合函数；常见的聚合函数包括 SUM()、MAX()、MIN() 和 AVG()，用于基本的统计运算，而更复杂的函数包括 stddev 和 variance，这些函数对更复杂的统计分析很有用。
在本文中，我们将讨论一些最佳实践，以帮助您利用 PostgreSQL 聚合函数并充分利用它们来增强数据分析。
虽然聚合是函数的一个子集，但它们在工作方式上与标准函数根本不同。聚合接受一组相关行以输出单个结果，而标准函数为每行提供一个结果。简而言之，函数在行上工作，而聚合在列上工作。
让我们考虑一个例子来更好地理解这一点。假设我们有一个sales 表，它包含三列：product_id、quantity_sold 和price_per_unit，并且我们想要计算每种产品的总收入和每种产品的总销售量。
让我们先创建一个表
CREATE TABLE SALES (
product_id INTEGER,
quantity_sold INTEGER,
price_per_unit INTEGER
);
现在，让我们添加一些值以查看函数和聚合之间的区别
INSERT INTO SALES VALUES (0001, 10.00, 15.25);
INSERT INTO SALES VALUES (0002, 12.00, 22.50);
INSERT INTO SALES VALUES (0002, 10.00, 20.00);
INSERT INTO SALES VALUES (0001, 8.00, 15.00);
现在，我们可以创建一个函数来计算收入，方法是接收单价和销售量
CREATE FUNCTION calculate_revenue(quantity_sold INT, price_per_unit NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN quantity_sold * price_per_unit;
END;
$$ LANGUAGE plpgsql;现在，让我们使用此函数来计算每个产品的收入
SELECT product_id, quantity_sold, price_per_unit, calculate_revenue(quantity_sold, price_per_unit) AS total_revenue
FROM SALES;以下是结果
在此示例中，我们的calculate_revenue 函数返回每行的总收入。
现在，要查找每种产品的总销售量，我们可以使用内置的SUM() 聚合函数
SELECT product_id, SUM(quantity_sold) AS total_quantity_sold
FROM sales
GROUP BY product_id;如您所见，它作用于quantity_sold 列并计算每种产品的总销售量
换句话说，聚合将来自多行的输入合并成单个结果（在本例中按product_id 分组）。在幕后，这些 PostgreSQL 聚合逐行工作，这就引发了一个重要的问题：聚合如何知道先前行中存储的值？这就是状态转换函数发挥作用的地方。
聚合函数存储它已经看到过的行的状态，并且随着添加新行，内部状态也会更新。在我们的示例中，内部状态只是迄今为止所有已售产品的总和。
处理所有传入行并更新内部状态的函数称为状态转换函数。它接受两个参数，当前状态和传入行的值，并输出一个新状态。随着聚合函数扫描不同的行，状态转换函数会更新内部状态，使 PostgreSQL 能够快速遍历列。
但是，这并非全部。SUM()、MAX() 和 MIN() 等聚合函数具有一个非常简单的状态，只有一个值，但这并非总是如此。有些聚合可以具有复合状态。
例如，在 AVG() 聚合的情况下，您需要将计数和总和都存储为内部状态。但是，然后我们需要采取另一个步骤来获取结果，即将总和除以总计数。此计算由另一个称为最终函数的函数执行；它接受状态并执行获取最终结果所需的计算。
因此，每次有新行时都会调用状态转换函数，但最终函数仅在状态转换函数处理完一组行后才调用一次。虽然状态转换函数在计算上并不比最终函数更昂贵，但前者仍然是您考虑进入聚合的行数时最昂贵的部分。
当您有大量时间序列数据 持续被摄取时，您需要一些可以帮助提高性能的东西。好消息是 PostgreSQL 已经拥有优化聚合的机制。
由于状态转换函数在每行上运行，我们可以并行化它以提高性能。我们可以通过初始化状态转换函数的多个实例并将每个实例提供一个行子集作为输入来做到这一点。
一旦这些并行聚合运行完毕，我们最终会得到多个部分状态（每个并行聚合一个）。但是，由于我们需要聚合整套行，因此我们需要一个中间函数，在运行最终函数之前合并所有部分聚合。这就是我们需要另一个函数的地方，称为合并函数，我们可以迭代地对所有部分状态运行它以获取合并状态。然后，最后，我们可以运行最终函数以获取最终结果。
如果这不清楚，请再次考虑 AVG() 函数。通过并行化状态转换函数，我们可以计算行子集的总和和计数。然后，我们可以使用合并函数将所有子集的总和和计数加起来，然后再运行最终函数。
如果您想从数据分析中获得最大价值，优化这些聚合的设计至关重要。以下是一些允许有效聚合设计的实践
优化数据聚合的一种方法是使用两步聚合过程，它基本上模拟了 PostgreSQL 如何为聚合实现状态转换和最终函数。这种方法涉及返回内部状态的内部调用，这与我们上面讨论的转换函数完全相同，以及接受内部状态并返回结果的访问器调用，这与前面提到的最终函数完全相同。
这对于时间序列数据特别有用。通过使用访问器和聚合公开聚合的内部架构，我们可以更好地理解如何构建我们的调用。
物化视图 在 PostgreSQL 中是一种通过查询结果优化性能的强大方法。它们预先计算经常运行的查询并将结果存储在数据库中。因此，每次运行查询时，数据库都不需要执行它；相反，结果已经可以使用，这意味着您将快速收到查询的响应。这有助于减少重复计算，从而实现更高效的分析。
但是，您需要在每次更新数据时刷新物化视图，这可能非常消耗资源。
预聚合 指的是作为表持久化的物化查询结果。您可以创建一个单独的汇总表来存储聚合数据，并使用触发器来管理对聚合的更新，从而允许访问该表，而不是重复调用聚合函数。这可以节省许多重新计算并提高整体性能，尤其是在多次计算相同的聚合时。
虽然物化视图是一种加速常用查询并避免昂贵重新计算的强大方法，但仍然存在一个大问题 - 您需要手动刷新它们以使视图保持最新，尤其是在随着新数据的到来，它很快就会过时的情况下。
而且这不是一次性的事情；如果你想继续避免重新计算，你需要增量刷新视图。除此之外，物化视图没有内置的自动运行的刷新机制。这意味着物化视图将不会包含上次刷新后更新或添加的数据，如果你的数据是实时的，这将是一个特别麻烦的问题。
我们构建了 连续聚合 来克服物化视图的这些限制，并使实时分析成为可能。你可以将它们视为具有自动刷新策略的实时聚合的物化视图。每次刷新运行时，它只使用自上次刷新以来更改的数据（而不是整个数据集），使该过程更有效。一旦你获得了最新的结果，就可以使用这些物化视图来进行实时仪表盘和实时分析等用例。
连续聚合涉及使用 time_bucket() 函数，它允许你对不同时间间隔的数据进行分组。它与 PostgreSQL 的 date_bin() 函数非常相似，但在起始时间和桶大小方面更加灵活。
创建刷新策略非常简单；你只需要定义刷新间隔，这样你的连续聚合就可以定期自动更新。整个过程比物化视图效率高得多，可以最大程度地减少计算量，并实现实时分析。
了解最佳实践以充分利用 PostgreSQL 聚合对于改进数据分析和从数据中获得更有意义的信息至关重要。我们已经讨论了 PostgreSQL 聚合的工作原理以及最佳设计实践。
虽然内置的聚合非常适合最大程度地减少计算量，但它们也存在一个很大的限制 - 它们并不总是最新的，这可能是一个很大的问题，尤其是当你拥有时间序列数据时。如果你想提高时间序列聚合性能，立即试用 Timescale。你可以尝试使用连续聚合，看看它们如何提高你的分析能力。