PostgreSQL计算平均值时排除指定范围外值的问题

背景信息 #

表结构：

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

原实现代码（计算所有数值的平均值）：

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

const averageMeasurement = async() => {
    const rows =  await sql`SELECT AVG(measurement) AS average FROM measurements`;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

需求：计算平均值时排除measurement大于1000或小于0的数值，但以下尝试代码触发Internal Server Error：

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

const averageMeasurement = async() => {
    const excMeasurements = await sql`SELECT * FROM measurements WHERE measurement <= 1000 AND measurement > 0`
    const rows =  await sql`SELECT AVG(measurement) AS average FROM excMeasurements`;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

错误原因 #

你试图把客户端变量excMeasurements当作数据库表名传入第二个SQL查询，但PostgreSQL无法识别这个变量——它只认识数据库中实际存在的表、视图或临时表，客户端的查询结果集不能直接作为表名使用。

解决方案 #

方案1：单SQL查询直接过滤（推荐，性能最优） #

在AVG聚合函数的查询里直接添加过滤条件，数据库层面一次完成过滤和计算，效率最高：

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

const averageMeasurement = async() => {
    const rows = await sql`
        SELECT AVG(measurement) AS average 
        FROM measurements 
        WHERE measurement BETWEEN 1 AND 1000
    `;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

这里用BETWEEN 1 AND 1000等价于measurement > 0 AND measurement <= 1000，写法更简洁。

方案2：使用子查询/CTE（适合需要复用过滤逻辑的场景） #

如果需要先定义过滤后的数据集再计算，可以用子查询或者CTE（公共表表达式）在SQL内部完成：

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

const averageMeasurement = async() => {
    const rows = await sql`
        WITH filtered_measurements AS (
            SELECT measurement 
            FROM measurements 
            WHERE measurement > 0 AND measurement <= 1000
        )
        SELECT AVG(measurement) AS average FROM filtered_measurements
    `;
    return rows[0].average;
}
const sql = postgres({});

export{averageMeasurement}

方案3：客户端层面计算（不推荐，大量数据时性能差） #

如果一定要在客户端处理过滤后的结果，需要先拿到所有符合条件的measurement值，再在JS里计算平均值：

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

const averageMeasurement = async() => {
    const excMeasurements = await sql`
        SELECT measurement 
        FROM measurements 
        WHERE measurement > 0 AND measurement <= 1000
    `;
    // 处理空结果的情况，避免除以0
    if (excMeasurements.length === 0) return null;
    const sum = excMeasurements.reduce((acc, row) => acc + row.measurement, 0);
    return sum / excMeasurements.length;
}
const sql = postgres({});

export{averageMeasurement}

这种方式不适合数据量较大的场景，因为需要把所有数据从数据库拉到客户端再计算，占用更多网络和内存资源。

内容的提问来源于stack exchange，提问作者tggtsed