立即开始为您的 PostgreSQL 加速。
作者：Anber Arif
作为一款拥有各种 内置数据类型、运算符和 函数 的开源关系型数据库，PostgreSQL 是数据分析的强大工具。在本指南中，我们将介绍如何在 PostgreSQL 中实现常见的数据分析用例，并展示如何随着数据的增长进行扩展 📈。
从基础查询到高级聚合和分组策略，我们将探索 PostgreSQL 如何成为处理数据的强大平台。然后，我们将深入探讨 Timescale 的 Hyperfunctions，展示一种更轻松、更有效的方式来处理 PostgreSQL 中的复杂分析任务，特别是对于时间序列和统计分析。
数据分析是利用信息做出明智决策的艺术。这是一个包含多个关键步骤的过程。
识别：根据特定问题或需求识别相关数据进行分析。
收集：从数据库、传感器或软件等各种来源收集数据。
清洗：通过去除错误、重复项和不一致性来优化数据。
分析：应用统计或计算技术来寻找模式或关系。
解释：将分析后的数据转化为可操作的洞察，用于决策制定。
现在，让我们分解一些数据分析真正发挥作用的基本用例。
优化流程和运营效率：数据分析在商业或制造等各个领域发挥着至关重要的作用。通过仔细分析数据，企业可以简化运营，识别瓶颈，并优化流程以实现最高效率。
识别模式：数据分析最强大的方面之一是它能够揭示模式。这些模式可以是随时间的趋势、季节性波动、指示潜在欺诈的异常情况，或客户行为倾向。通过识别这些模式，企业可以预测变化，做出主动决策，并检测可能需要关注的异常情况。
产品开发：数据分析不仅仅是优化现有流程，它也是创新的催化剂。公司利用数据洞察来开发更符合市场需求或客户偏好的新产品。
提升客户体验：了解客户是企业成功的关键。数据分析使组织能够深入了解客户偏好、行为和满意度。
在数据分析中，不同的方法有助于得出各种洞察。以下是不同数据分析类型概述。
描述性分析涉及对数据进行汇总和整理，以呈现清晰简洁的概述。它侧重于了解数据的基本特征，包括集中趋势、离散度和可视化。它的目标是描述数据传达的信息，而不是进行推断或预测。例如，百分位数、四分位数、平均数、中位数和众数是用于揭示数据集内模式、趋势或异常情况的常见描述性统计量。
了解百分位数近似工作原理以及为什么它比平均值更适合数据分析。
诊断性分析旨在揭示不同变量或数据集之间的关系，以识别因果关系。它深入挖掘数据，以了解为何出现某些模式或结果。这种类型的分析通常涉及相关性、回归分析或时间序列分析等技术。例如，在时间序列分析中，诊断性分析探索一个变量的变化如何随时间影响另一个变量，建立相关性或因果关系。您可以在我们的深入分析中了解更多信息：时间序列分析示例和应用。
预测性分析侧重于根据历史数据模式预测未来的趋势、结果或行为。它使用统计算法、机器学习模型或其他技术来预测未来可能发生的事情。回归分析和 时间序列预测 模型属于预测性分析。通过分析历史数据，预测性分析有助于预测未来的场景或趋势。
规范性分析超越预测，提出可能的行动或策略建议。它利用预测性分析的洞察，建议最佳行动方案以优化结果。优化算法、仿真模型和 A/B 测试等技术是规范性分析的一部分。例如，在 A/B 测试中，会比较不同的版本或策略，以确定哪种效果更好，并指导决策制定。
这篇关于数据分析类型的文章简明扼要地解释了数据分析类型和方法。
现在我们已经介绍了数据分析的基础知识，让我们看看如何利用它们在 PostgreSQL 中。在本节中，我们将深入探讨 PostgreSQL 中数据分析的以下关键方面。
探索和描述数据：这个过程涉及了解 PostgreSQL 数据库中存储的数据结构和数据。它包括检查数据布局、列类型和存储的总体信息的方法。通过了解数据分布和特征，这种探索为深入分析和明智决策奠定了基础。
聚合和分组数据：更深入地进行数据操作，这一方面涉及根据特定条件压缩和整理 PostgreSQL 数据。聚合技术提供汇总的洞察，揭示数据集中的模式或趋势。分组数据能够进行分类，揭示关系，并促进对数据集的更深入理解。
探索性命令，例如带有**JOIN**的**SELECT**，对于理解数据结构和关系至关重要。JOIN 允许根据公共列组合表，揭示数据集之间的连接。此命令通过显示来自多个表的相互关联的信息来展示数据的形状，有助于理解全面分析至关重要的关系。
现在，让我们看看在 PostgreSQL 中探索和描述数据的基本命令。
SELECT *
FROM table_name;
此命令从指定的表中获取所有列（**\***），提供对表内容的全面视图。
SELECT column1, column2
FROM table_name
WHERE condition;
此命令根据指定的条件从表中选择特定列。例如，WHERE column1 = 'value'检索 column1 与特定值匹配的行。
SELECT t1.column_name1, t2.column_name2
FROM table1 t1
JOIN table2 t2 ON t1.shared_column = t2.shared_column;
在此示例中，SELECT 语句从 table1 和 table2 中检索指定的列。JOIN 子句根据共享列链接表，在结果输出中显示来自两个表的相关信息。此简单命令展示了如何合并和显示来自多个表的数据，从而了解数据的结构和关系。
PostgreSQL 提供了一套广泛的聚合函数。这些函数包含基本的聚合操作，例如 **AVG()**、**COUNT()**、**SUM()**、**MAX()**、**MIN()**、**ARRAY_AGG()** 和 STRING_AGG()。PostgreSQL 允许在聚合过程中连接字符串或创建数组。这种多样化的函数数组为用户提供了用于操作和汇总数据的通用工具。
在 PostgreSQL 中，函数主要对单个行进行操作，执行特定操作或转换。相反，聚合函数对列中的多行执行计算，汇总或聚合值以生成单个结果。
PostgreSQL 支持各种类别的内置聚合
通用聚合：涵盖标准函数，如 AVG()、SUM() 和 COUNT()，用于基本的数学和计数操作。
统计聚合：包括统计函数，如 STDDEV()、VARIANCE() 和 CORR()，用于分析目的。
有序集聚合：提供函数，如 PERCENTILE_CONT() 和 PERCENTILE_DISC()，用于计算百分位数值和相关操作。
假设集聚合：提供假设集函数，用于分析各种场景而不改变数据集。
分组操作：启用操作，如 GROUPING SETS、CUBE 和 ROLLUP，以在一个查询中执行多个分组。
此外，它允许用户创建自定义聚合函数，根据特定的分析需求定制功能。此功能使用户能够灵活地扩展 PostgreSQL 的聚合功能，以精确地满足其独特的分析需求。
以下是如何在 PostgreSQL 中计算百分位数的示例
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median_sales
FROM
sales_data;
此查询使用 percentile_cont() 聚合函数来计算数据集中销售额的中位数。在此代码中
percentile_cont(0.5) 是用于计算百分位数的函数。在这种情况下，0.5 代表第 50 个百分位数，对应于中位数。
WITHIN GROUP (ORDER BY sales_amount) 指定列 sales_amount 来对数据集进行排序。这确保根据 sales_amount 列中的排序值准确计算中位数。
AS median_sales 将标签 median_sales 分配给计算得出的结果值。
分组数据涉及根据指定标准（例如州、部门或任何分类信息）将数据组织成不同的类别。此过程将共享共同特征的多行折叠成一个合并行，代表指定组。例如，按地区对销售数据进行分组将单个销售条目折叠成每个地区的汇总信息。
您可以使用时间序列分箱将数据分组到间隔或箱中，从而允许在这些指定期间内进行分析。为了计算每个箱的百分位数，您可以使用 time_bucket 函数进行分箱，并在每个箱内使用 percentile_cont() 进行百分位数计算。让我们考虑一个包含列 timestamp 和 sensor_value 的表 sensor_data。
SELECT
time_bucket('1 hour', timestamp) AS hourly_bin,
percentile_cont(0.25) WITHIN GROUP (ORDER BY sensor_value) AS p25,
percentile_cont(0.50) WITHIN GROUP (ORDER BY sensor_value) AS p50,
percentile_cont(0.75) WITHIN GROUP (ORDER BY sensor_value) AS p75
FROM sensor_data
GROUP BY hourly_bin
ORDER BY hourly_bin;
此查询使用 time_bucket 函数根据 timestamp 列创建每小时的箱。在每个每小时的箱内，它使用 percentile_cont 函数计算 sensor_value 的第 25、50（中位数）和第 75 个百分位数。
数据分区涉及在定义的分区或子集中执行聚合，而不会折叠行。此技术允许根据某些标准（例如时间间隔或类别）在不同的分区中进行计算（例如平均值或总和），而不会丢失数据集中的粒度。
考虑一个名为 sales_data 的表，其中包含列 time_stamp、category 和 sales_value。
SELECT
time_stamp,
category,
sales_value,
AVG(sales_value) OVER (PARTITION BY category ORDER BY time_stamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_avg_per_category
FROM sales_data;
此查询计算 sales_data 表中按不同细分类别分类的销售数据的滚动平均值。PARTITION BY 子句根据 category 列对数据进行细分，为每个唯一类别创建单独的分区。ORDER BY time_stamp 根据 time_stamp 列按升序对每个分区中的数据进行排序。然后，AVG() 函数与 OVER 子句结合使用，在包含当前行和每个类别分区中前三行的窗口上计算 sales_value 的平均值。
PostgreSQL 中的某些计算表现出不同的速度，有些比其他计算更快。但是，随着数据集的增长，所有分析的效率往往都会下降。这种减速是由于要处理的数据量增加造成的，从而影响了查询执行时间和整体分析性能。
使用 Timescale，您可以使用 hyperfunctions 来优化和加速特定类型的数据分析，这极大地提高了 PostgreSQL 中数据操作的效率。Timescale hyperfunctions 是针对时间序列查询、聚合和分析任务专门定制的一组优化函数、过程和数据类型。它们与 Timescale 的云服务和 PostgreSQL 的 TimescaleDB 扩展深度整合。虽然某些 hyperfunctions 是默认包含的，但其他一些是 Timescale Toolkit 的一部分，其中包含直接在数据库中执行的高性能 Rust 函数。
使用 Timescale hyperfunctions，您可以在处理大量数据的同时，在 PostgreSQL 中保持实时分析。究竟有多广泛，您可能想知道？在我们迄今为止最大的试用工作中，我们成功地管理了一个 350 TB+ 云数据库，一个 Timescale 实例处理每天100 亿条新记录的摄取。
同一个数据库为我们所有客户的实时仪表板提供支持，以推动我们的 Insights 工具，我们正在将最有趣 的统计信息汇总到一个 UDDSketch 中，它是我们 hyperfunctions 集合的一部分。如您所见，没有理由不能扩展 PostgreSQL 以进行复杂的数据分析。
但是，为了实现像 Insights 工具这样的壮举，我们必须使用我们盒子中的许多工具来扩展 PostgreSQL。以下是一些您可以探索以完成自己的数据分析任务的工具
数据压缩：Timescale 中的数据压缩 是一个关键功能，它优化了存储和查询性能。它显着减少了存储数据的数量，从而带来了令人印象深刻的存储节省，通常超过 90%。此压缩功能旨在提高某些查询的效率，从而加快其执行和响应时间。通过利用先进的压缩技术，Timescale 将存储空间占用降到最低，而不会影响数据完整性，从而确保简化的数据管理和高效的查询处理。
时间序列优化：Timescale 针对各种用例的时间序列分析进行了精细调整和优化。它在处理时间序列数据方面表现出非凡的性能，与普通 PostgreSQL 相比，时间序列查询速度提高了 1000 倍。这种优化涵盖了各种时间序列分析场景，证明了 Timescale 能够对以时间为导向的数据集提供快速的查询执行和有见地的分析。
查询时间缩短：Timescale 的效率延伸到将经常运行的查询的执行时间缩短到几毫秒。这种功能显着提高了查询性能，通过 预先聚合数据并自动且增量地更新后台数据 来实现对常用查询的闪电般快速的响应。通过利用其优化的架构和专门的功能，例如 自动分区，Timescale 极大地减少了查询处理时间，使用户能够快速检索关键信息。
地理空间和矢量数据工具包：Timescale 集成了专门的工具，如 PostGIS 扩展，为 PostgreSQL 提供强大的地理空间功能。此扩展支持空间索引，支持各种几何数据类型，并促进高效的空间操作和分析。此外，该平台利用 PostgreSQL 的演变，成为一个熟练的 矢量数据库，有效地管理基于矢量的数据类型并优化存储机制。
Hyperfunctions 是 Timescale 的通用工具箱，使分析师和开发人员能够利用这些数据库系统的功能。
时间向量是一种结构化的方式，可以有效地组织和处理以时间为导向的信息。在核心，时间向量封装了旨在简化数据聚合和操作过程的聚合访问器和变异器。聚合访问器有助于从时间序列数据集中检索汇总信息。它们便于提取重要的聚合数据点，例如最大值、最小值或平均值，这些数据点对于全面的时间序列分析至关重要。
另一方面，变异器使用户能够修改时间数据集中的特定数据点，从而允许有针对性地进行更改或调整。
Timescale 中的滚动聚合是指时间序列数据分析中使用的强大分析技术。这些聚合允许在滑动或滚动的时间窗口上计算汇总统计数据，例如平均值、标准差、百分位数或其他统计度量。滚动聚合不会一次性对整个数据集计算聚合值，而是将数据分解成较小的连续时间间隔，在每个间隔内计算聚合值。
此方法提供了几个优点。它能够检查特定时间段的数据趋势和模式，从而了解统计度量随时间的变化方式。通过使用滚动聚合，分析师可以捕捉动态变化，检测异常情况，并了解较短时间段内数据的可变性。
请考虑以下示例
SELECT
bucket,
average(rolling(stats_agg) OVER fifteen_min),
stddev(rolling(stats_agg) OVER fifteen_min, 'pop'),
kurtosis(rolling(stats_agg) OVER fifteen_min, 'pop')
FROM (SELECT
time_bucket('1 min'::interval, ts) AS bucket,
stats_agg(val)
FROM measurements
GROUP BY 1) AS stats
WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING);
此查询使用 time_bucket() 函数将时间序列数据组织成一分钟间隔，创建标为 bucket 的不同时间桶。stats_agg() 函数然后聚合每个时间桶内的值，收集统计见解。然后，查询使用名为 fifteen_min 的 WINDOW 函数建立当前行之前 15 分钟的滚动窗口，有效地计算滚动统计数据。average() 函数计算滚动平均值，stddev() 计算滚动标准差，而 kurtosis() 在此定义的滚动窗口内计算峰度。
与 Timescale 提供的简化滚动聚合方法相比，在标准 PostgreSQL 中执行类似的时间序列分区聚合可能会更加复杂和繁琐。PostgreSQL 确实支持窗口函数，这对于时间序列分析至关重要，但与 Timescale 相比，滑动或滚动窗口聚合的实现涉及更手动和复杂的流程。
在PostgreSQL中，该过程通常涉及使用 PARTITION BY 等窗口函数构建显式分区或数据段。这些分区将数据集划分为指定的时间间隔，但需要更详细地定义和管理窗口规范。对这些分区进行数据聚合需要精确的排序和分区才能准确计算值，这往往导致更复杂的 SQL 查询和更高的查询复杂度。
此外，在 PostgreSQL 中，实现滑动窗口聚合可能涉及更复杂的语法或手动处理时间间隔，这使得它不像 Timescale 提供的专用函数那样直观，并且可能更容易出错。PostgreSQL 中的这种额外复杂性可能需要对 SQL 窗口函数有更深入的理解，以及更复杂的查询结构，这可能会增加出错的可能性，并需要更广泛的测试以确保结果准确。
百分位数在统计分析中起着至关重要的作用，可以提供有关数据分布的见解。在 Timescale 中，超函数提供了计算百分位数的有效方法，使用户能够更全面地了解数据分布。与平均值等传统方法相比，百分位数将数据划分为间隔，并确定特定百分比的观测值落在其下的值。
以下是一个使用 Timescale 中的超函数演示百分位数计算的示例
SELECT
approx_percentile(0.25, percentile_agg(response_time)) as p25,
approx_percentile(0.75, percentile_agg(response_time)) as p75
FROM responses;
在此 SQL 查询中，我们使用 approx_percentile 函数与 Timescale 中的 percentile_agg 超函数结合，计算 responses 数据集中 response_time 列的近似百分位数。approx_percentile 函数允许我们指定所需的百分位数（在本例中为 25% 和 75%），并将它们应用于使用 percentile_agg 聚合的 response_time 数据。当比较 Timescale 的超函数和 PostgreSQL 的标准百分位数函数之间的百分位数计算时，对比主要在于效率和处理大规模时间序列数据的处理方式。Timescale 的超函数有效地实现了近似百分位数计算，这特别适用于更大的数据集。
相比之下，尽管准确，但 PostgreSQL 的标准百分位数函数（例如 percentile_cont 和 percentile_disc）可能因其计算复杂度而在大容量时间序列数据方面面临性能挑战。
时间分组通过将数据组织成离散时间段来简化对不同时间间隔的百分位数的评估。它有助于在这些定义的时段内计算百分位数，从而可以轻松地检索特定时间范围或时间间隔内的百分位数值。
SELECT * FROM responses
WHERE ts >= now()-'1 hour'::interval
AND response_time > (
SELECT approx_percentile(0.95, percentile_agg)
FROM responses_1h_agg
WHERE bucket = time_bucket('30 minutes'::interval, now()-'30 minutes'::interval)
);
此示例根据 response_time 超过近似第 95 个百分位数的值的实例从 responses 表中检索记录。外部查询将数据检索限制在最后一个小时内。同时，利用时间分组，内部子查询有效地计算了该小时内特定 30 分钟间隔的第 95 个百分位数的值。这突出了时间分组如何轻松地跨不同时间段计算百分位数值，从而便于在时间序列数据集的特定时间间隔内进行高效和精确的分析。
数据库中的回归分析是一种统计方法，用于识别和量化变量之间的关系。在 Timescale 中，回归分析通过其超函数（特别是 stats_agg 函数）得到促进。以下是一个演示 stats_agg 用于双变量回归的应用的示例
SELECT
average_y(stats_agg(val2, val1)), -- equivalent to average(stats_agg(val2))
stddev_x(stats_agg(val2, val1)), -- equivalent to stddev(stats_agg(val1))
slope(stats_agg(val2, val1)) -- the slope of the least squares fit line of the values in val2 & val1
FROM measurements_multival;
此查询使用 stats_agg 超函数对 measurements_multival 数据集中 val2 和 val1 两个变量进行回归分析。它在一个函数调用中计算几个回归指标，包括 val2 的平均值（等效于应用于 val2 的平均函数）、val1 的标准差（等效于应用于 val1 的 stddev）、以及将 val2 对 val2 进行绘图时的最小二乘拟合线的斜率。这种简化的方法简化了回归分析，提供了一种简洁的方法来在一个查询中推导出多个回归指标。
将这种方法与 PostgreSQL 进行对比，PostgreSQL 中的传统回归通常涉及使用专门的回归函数（如 regr_slope、regr_intercept 等），分别应用于每个变量或变量对。Timescale 的 stats_agg 通过允许在一个函数调用中计算多个回归指标来简化了这一过程，从而提供了一种更简化和更全面的回归分析方法。这种功能减少了查询复杂度，并提高了与 PostgreSQL 的单个回归函数相比回归计算的效率。
在处理大量时间序列数据或分析工作负载时，本机 PostgreSQL 可能会在性能和可扩展性方面遇到挑战。随着数据集的增长或在进行复杂的时间序列分析时，PostgreSQL 传统架构的局限性可能会阻碍高效的数据处理。
Timescale 扩展了 PostgreSQL 的功能，特别是在提高时间序列和分析应用程序的性能和可扩展性方面。创建免费的 Timescale 帐户以获取对优化 PostgreSQL 用于时间序列数据的强大平台的访问权限，从而实现更快、更高效的数据分析。