遍历列名以在PL/pgSQL函数中获取计数

Question

我在Postgres数据库中有一张表，我想确定其填充率（即我想了解数据缺失的频率）。我需要编写一个函数，对于所选列表中的每一列（几十列），统计具有非空值的列的数量和百分比。

问题是，我不知道如何以编程方式迭代列的列表，因为我不知道如何从其名称的字符串引用列。我已经了解了可以使用the EXECUTE command来运行动态编写的SQL，但我无法让它正常工作。这是我的当前函数：

CREATE OR REPLACE FUNCTION get_fill_rates() RETURNS TABLE (field_name text, fill_count integer, fill_percentage float) AS $$
DECLARE
    fields text[] := array['column_a', 'column_b', 'column_c'];
    total_rows integer;
BEGIN
    SELECT reltuples INTO total_rows FROM pg_class WHERE relname = 'my_table';

    FOR i IN array_lower(fields, 1) .. array_upper(fields, 1)
    LOOP
        field_name := fields[i];
        EXECUTE 'SELECT COUNT(*) FROM my_table WHERE $1 IS NOT NULL' INTO fill_count USING field_name;
        fill_percentage := fill_count::float / total_rows::float;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_fill_rates() ORDER BY fill_count DESC;

这个函数的原始代码会将每个字段都返回100%的填充率，但我知道这是错误的。我该如何修改这个函数？

- Hayden Schiff

Answer 1

我知道你已经解决了这个问题。但是让我建议您避免在动态查询中连接标识符，您可以使用带有标识符通配符的 format：

CREATE OR REPLACE FUNCTION get_fill_rates() RETURNS TABLE (field_name text, fill_count integer, fill_percentage float) AS $$
DECLARE
    fields text[] := array['column_a', 'column_b', 'column_c'];
    table_name name := 'my_table';
    total_rows integer;
BEGIN
    SELECT reltuples INTO total_rows FROM pg_class WHERE relname = table_name;

    FOREACH field_name IN ARRAY fields
    LOOP
        EXECUTE format('SELECT COUNT(*) FROM %I WHERE %I IS NOT NULL', table_name, field_name) INTO fill_count;
        fill_percentage := fill_count::float / total_rows::float;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

使用这种方式可以帮助您预防SQL注入攻击，并略微减少查询解析开销。更多信息请参见此处。

- Michel Milezzi

Answer 2

我在写问题之前想出了解决方案，但在提交之前--由于我已经做了写问题的工作，所以我会继续分享答案。问题在于我的EXECUTE语句，具体来说是那个USING field_name部分。我认为当我这样做时它被视为一个字符串字面量，这意味着查询正在评估"a string literal" IS NOT NULL，当然，这总是正确的。

我需要将列名注入到查询字符串中，而不是将其作为参数。因此，我将我的EXECUTE行更改为以下内容：

EXECUTE 'SELECT COUNT(*) FROM my_table WHERE ' || field_name || ' IS NOT NULL' INTO fill_count;

- Hayden Schiff

---

也许一个更好的想法是使用带有标识符通配符的格式。这将避免SQL注入。 - Michel Milezzi

Answer 3

除了代码中的一些问题（见下文），这可以通过在普通查询中对表进行单次扫描来大大提高速度和简化操作：

SELECT v.*
FROM  (
   SELECT count(column_a) AS ct_column_a
        , count(column_b) AS ct_column_b
        , count(column_c) AS ct_column_c
        , count(*)::numeric AS ct
   FROM   my_table
   ) sub
     , LATERAL (
      VALUES
         (text 'column_a', ct_column_a, round(ct_column_a / ct, 3))
       , (text 'column_b', ct_column_b, round(ct_column_b / ct, 3))
       , (text 'column_c', ct_column_c, round(ct_column_c / ct, 3))
   ) v(field_name, fill_count, fill_percentage);

关键的“技巧”在于 count() 只计算非空值，无需任何技巧。

我将百分比舍入到小数点后三位，这是可选的。为此，我转换为 numeric 。

使用 VALUES 表达式对结果进行展开以获取每个字段的一行记录。

如果需要多次使用或者要处理的列很多，可以动态生成并执行查询。但是，不要为每个列单独运行一个计数。只需动态构建上述查询即可。

CREATE OR REPLACE FUNCTION get_fill_rates(tbl regclass, fields text[])
  RETURNS TABLE (field_name text, fill_count bigint, fill_percentage numeric) AS
$func$
BEGIN
RETURN QUERY EXECUTE (
-- RAISE NOTICE '%', (  -- to debug if needed
   SELECT
     'SELECT v.*
      FROM  (
         SELECT count(*)::numeric AS ct
              , ' || string_agg(format('count(%I) AS %I', fld, 'ct_' || fld), ', ') || '
         FROM   ' || tbl || '
         ) sub
           , LATERAL (
            VALUES
               (text ' || string_agg(format('%L, %2$I, round(%2$I/ ct, 3))', fld, 'ct_' || fld), ', (') || '
         ) v(field_name, fill_count, fill_pct)
      ORDER  BY v.fill_count DESC'
   FROM  unnest(fields) fld
   );
END
$func$ LANGUAGE plpgsql;

呼叫：

SELECT * FROM get_fill_rates('my_table', '{column_a, column_b, column_c}');

作为您可以看到，现在这适用于任何给定的表和列列表。
并且所有标识符都会自动正确引用，使用format()或regclass类型的内置优点。

相关:

• 将表名作为PostgreSQL函数参数
• 如何在PostgreSQL中取消枢轴表格
• 查询交叉表视图
• 将一行转换为具有较少列的多行

---

你原始的查询可以像这样改进，但这只是给猪涂口红。 不要使用这种低效的方法。

CREATE OR REPLACE FUNCTION get_fill_rates()
  RETURNS TABLE (field_name text, fill_count bigint, fill_percentage float) AS
$$
DECLARE
   fields text[] := '{column_a, column_b, column_c}';  -- must be legal identifiers!
   total_rows float;  -- use float right away
BEGIN
   SELECT reltuples INTO total_rows FROM pg_class WHERE relname = 'my_table';

   FOREACH field_name IN ARRAY fields  -- use FOREACH
   LOOP
      EXECUTE 'SELECT COUNT(*) FROM big WHERE ' || field_name || ' IS NOT NULL'
      INTO fill_count;
      fill_percentage := fill_count / total_rows;  -- already type float
      RETURN NEXT;
   END LOOP;
END
$$ LANGUAGE plpgsql;

此外，pg_class.reltuples仅是一个估计值。既然您已经在计算了，那就使用实际计数。

相关内容：

• 在PL/pgSQL中迭代integer[]
• 在PostgreSQL中快速发现表的行数

- Erwin Brandstetter