PostgreSQL中仅显示结果大于0的统计列问题排查

原查询语句 #

SELECT 
    (count (*) FILTER(Where "FailCode" BETWEEN 200 AND 202))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col1",
    (count (*) FILTER(Where "FailCode" BETWEEN 301 AND 356))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col2"
FROM myTable

当前查询结果 #

需求 #

仅显示结果大于0的统计列：当Col2的值大于0时显示该列，否则只显示Col1。

尝试的错误查询 #

Select *
from(
    Select
(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 202))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col1",
(count (*) FILTER(Where "FailCode" BETWEEN 301 AND 356))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col2" )
    from myTable) as t
where t.col1 > 0

错误信息 #

ERROR:  subquery in FROM must have an alias
LINE 2: from(
            ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
SQL state: 42601
Character: 14

期望结果 #

解决方案 #

1. 修正语法错误（但无法隐藏列） #

你尝试的查询存在语法错误：子查询中多了一个多余的闭合括号)。修正后的查询如下，但它仅过滤行，无法动态隐藏列：

SELECT *
FROM (
    SELECT
        (count (*) FILTER(Where "FailCode" BETWEEN 200 AND 202))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col1",
        (count (*) FILTER(Where "FailCode" BETWEEN 301 AND 356))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col2"
    FROM myTable
) AS t
WHERE t.Col2 > 0;

2. 动态SQL实现列的动态显示 #

由于PostgreSQL的静态SQL无法动态调整返回的列数，需要使用动态SQL结合PL/pgSQL函数来实现需求：

CREATE OR REPLACE FUNCTION get_filtered_stats()
RETURNS SETOF record AS $$
DECLARE
    col2_val numeric;
    sql_query text;
BEGIN
    -- 先计算Col2的结果值
    SELECT 
        (count (*) FILTER(Where "FailCode" BETWEEN 301 AND 356))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100
    INTO col2_val
    FROM myTable;

    -- 根据Col2的值构建查询语句
    IF col2_val > 0 THEN
        sql_query := '
            SELECT 
                (count (*) FILTER(Where "FailCode" BETWEEN 200 AND 202))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col1",
                (count (*) FILTER(Where "FailCode" BETWEEN 301 AND 356))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col2"
            FROM myTable';
    ELSE
        sql_query := '
            SELECT 
                (count (*) FILTER(Where "FailCode" BETWEEN 200 AND 202))/SUM(count (*) FILTER(Where "FailCode" BETWEEN 200 AND 3301)) OVER()*100 as "Col1"
            FROM myTable';
    END IF;

    -- 执行动态查询并返回结果
    RETURN QUERY EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

调用函数

• 当Col2存在时（值>0）：

SELECT * FROM get_filtered_stats() AS t("Col1" numeric, "Col2" numeric);

• 当仅返回Col1时：

SELECT * FROM get_filtered_stats() AS t("Col1" numeric);

3. 应用层处理（更简便） #

如果你的业务允许，也可以在应用代码中先执行原查询获取所有列，再判断Col2的值是否大于0，最后决定在界面上显示哪些列。这种方式无需修改SQL，实现成本更低。

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