PostgreSQL平均值计算与数据过滤技巧

小伙伴们对文章编程感兴趣吗？是否正在学习相关知识点？如果是，那么本文《PostgreSQL精准计算平均值，高效过滤数据技巧》，就很适合你，本篇文章讲解的知识点主要包括。在之后的文章中也会多多分享相关知识点，希望对大家的知识积累有所帮助！

本教程详细讲解如何在PostgreSQL中计算平均值时，高效地排除特定范围的数据。文章通过分析一个常见的错误示例，解释了为何不应在客户端代码中进行初步过滤后再次尝试用SQL查询一个不存在的“临时表”，并提供了使用SQL的`WHERE`子句直接在数据库层面进行数据过滤和聚合的正确且高效的方法。

在数据分析和报表生成中，计算平均值是一项基本操作。然而，很多时候我们需要排除数据集中某些异常值或不符合特定条件的数据点，以确保平均值的准确性和代表性。本文将以PostgreSQL为例，详细阐述如何高效且正确地实现这一目标。

数据准备

首先，我们假设有一个名为 measurements 的表，用于存储各项测量数据。其结构如下：

CREATE TABLE measurements (
  id SERIAL PRIMARY KEY,
  measurement INTEGER NOT NULL
);

该表包含一个自增ID和 measurement 列，用于存储整数类型的测量值。

问题描述

我们的目标是计算 measurements 表中 measurement 列的平均值。但需要注意的是，我们希望排除那些值小于0或大于1000的测量数据，只对在 [0, 1000] 范围内的有效数据进行平均值计算。

错误方法分析

在实际开发中，开发者有时会尝试一种看似合理但实际上存在问题的处理方式。以下是一个常见的错误示例，它尝试在客户端（例如Deno环境下的JavaScript）中分两步完成：

import postgres from "https://deno.land/x/postgresjs/mod.js";

const sql = postgres({}); // 假定 sql 实例已正确初始化

const averageMeasurement = async() => {
    // 第一步：尝试从数据库中获取符合条件的数据
    const excMeasurements = await sql`SELECT * FROM measurements WHERE measurement <= 1000 AND measurement > 0`;

    // 第二步：尝试对第一步获取到的结果再次执行 SQL 聚合
    // 错误点：excMeasurements 是一个 JavaScript 变量，不是数据库中的表或视图
    const rows =  await sql`SELECT AVG(measurement) AS average FROM excMeasurements`;
    return rows[0].average;
}

export { averageMeasurement };

错误原因分析：

上述代码中，excMeasurements 是一个在Deno（JavaScript）环境中接收到的数据数组或对象，它代表了从数据库查询到的结果集。然而，它仅仅是客户端内存中的一个变量。当第二条SQL语句 SELECT AVG(measurement) AS average FROM excMeasurements 被执行时，数据库服务器会尝试在自身的数据库架构中查找名为 excMeasurements 的关系（即表或视图）。由于数据库中并不存在这样一个关系，它会报告一个错误，例如“relation "excMeasurements" does not exist”（关系“excMeasurements”不存在），从而导致客户端接收到“Internal Server Error”或类似的数据库操作失败信息。

这种方法不仅错误，而且效率低下，因为它首先将所有符合初步过滤条件的数据从数据库传输到客户端，然后再尝试对这些数据在数据库中进行聚合，这在逻辑上是矛盾的。

正确且高效的解决方案

解决这个问题的关键在于，将数据过滤和聚合操作全部委托给数据库服务器来完成。PostgreSQL提供了强大的SQL语句，允许我们通过 WHERE 子句在聚合函数执行之前对数据进行筛选。

核心SQL语句：

SELECT AVG(measurement) AS average
FROM measurements
WHERE measurement >= 0 AND measurement <= 1000;

工作原理：

1. FROM measurements：指定从 measurements 表中查询数据。
2. WHERE measurement >= 0 AND measurement <= 1000：这是过滤条件。数据库在计算平均值之前，会首先筛选出 measurement 列的值在0到1000（包括0和1000）之间的所有行。
3. SELECT AVG(measurement) AS average：对经过 WHERE 子句过滤后的数据行，计算 measurement 列的平均值，并将结果命名为 average。

这种方法将过滤和聚合逻辑完全封装在一条SQL查询中，由数据库服务器高效地执行，避免了不必要的数据传输和客户端逻辑的混淆。

在Deno/Postgres.js中集成

将上述正确的SQL查询集成到Deno（或其他Node.js环境）的Postgres.js客户端代码中非常直接：

import postgres from "https://deno.land/x/postgresjs/mod.js";

const sql = postgres({}); // 确保 sql 实例已正确初始化，例如：postgres('postgres://user:password@host:port/database');

const averageMeasurement = async() => {
    const rows = await sql`
        SELECT AVG(measurement) AS average
        FROM measurements
        WHERE measurement >= 0 AND measurement <= 1000;
    `;
    // 检查是否有结果，以防表为空或没有符合条件的数据
    if (rows.length > 0 && rows[0].average !== null) {
        return rows[0].average;
    } else {
        // 如果没有符合条件的数据，AVG() 会返回 NULL，可以根据业务需求返回 0 或抛出错误
        return null; // 或者 0，或者抛出 new Error("No valid measurements found for average calculation.");
    }
}

export { averageMeasurement };

注意事项与最佳实践

1. 数据库端处理的优势： 始终优先在数据库服务器端完成数据过滤和聚合操作。这可以最大限度地减少网络传输的数据量，并充分利用数据库管理系统（DBMS）的查询优化器和索引，从而显著提高查询效率和响应速度。
2. SQL的语义清晰： WHERE 子句是SQL标准中用于条件过滤的明确机制，其逻辑清晰、易于理解和维护。
3. 处理空结果集： 当 WHERE 子句过滤后没有数据行，或者所有符合条件的行的 measurement 值都为 NULL 时，AVG() 函数将返回 NULL。在客户端代码中，需要妥善处理这种情况，例如返回 null、0，或者抛出特定的异常，以符合业务逻辑。
4. 其他过滤场景： WHERE 子句的功能远不止数值范围过滤。它还可以用于各种复杂的条件，例如：
   • measurement IS NOT NULL：排除空值。
   • measurement IN (10, 20, 30)：只包含特定值。
   • measurement LIKE 'prefix%'：基于模式匹配过滤字符串（如果 measurement 是字符串类型）。
   • 结合 AND、OR、NOT 构建更复杂的逻辑。
5. 避免混合逻辑： 避免将数据库查询逻辑与客户端语言的逻辑混淆。数据库查询应尽可能地独立和完整，客户端代码主要负责构建查询、发送请求和处理结果。

总结

在PostgreSQL中计算平均值并排除特定范围的数据，最正确和高效的方法是直接在 SELECT AVG() 查询中使用 WHERE 子句进行条件过滤。这种方法利用了数据库的强大能力，确保了数据处理的效率和准确性，同时也使得代码逻辑更加清晰和易于维护。理解客户端代码与数据库查询之间的边界，是编写高性能、健壮应用程序的关键。

本篇关于《PostgreSQL平均值计算与数据过滤技巧》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！