计算一行中为空的属性的数量

Question

我想向表中添加一个新列来记录每个元组（行）值为空的属性的数量。如何使用SQL来获取数字？

例如，如果元组是这样的：

Name | Age | Sex
-----+-----+-----
Blice| 100 | null

我想将元组更新为：

Name | Age | Sex | nNULL
-----+-----+-----+--------
Blice| 100 | null|  1

另外，因为我正在编写PL/pgSQL函数，并且表名是从参数获取的，所以我事先不知道表的模式。这意味着我需要用输入表名更新表。有人知道怎么做吗？

Answer 1

这是 @winged Panther 根据请求发布的内容的审核版本。

该函数将具有给定名称的列添加到调用角色具有必要权限的任何现有表中：

CREATE OR REPLACE FUNCTION f_add_null_count(_tbl regclass, _newcol text)
  RETURNS void AS
$func$
BEGIN
   -- add new col
   EXECUTE format('ALTER TABLE %s ADD COLUMN %I smallint', _tbl, _newcol);

   -- update new col with dynamic count of nulls
   EXECUTE (
      SELECT format('UPDATE %s SET %I = (', _tbl, _newcol)  -- regclass used as text
          || string_agg(quote_ident(attname), ' IS NULL)::int + (')
          || ' IS NULL)::int'
      FROM   pg_catalog.pg_attribute
      WHERE  attnum > 0
      AND    NOT attisdropped
      AND    attrelid = _tbl  -- regclass used as OID
      AND    attname <> _newcol  -- no escaping here, it's the *text*!
      );
END
$func$  LANGUAGE plpgsql;

SQL小提琴演示。

< li >使用< code>%I或< code>quote_ident()转换为< code>regclass、< code>format()整理标识符。我在示例中使用了所有三种技术，每一种恰好都是使用它们的最佳选择。更多信息请点击此处: < ul > < li >作为PostgreSQL函数参数的表名

我用粗体格式化了相关的代码片段。

> < li>

我的查询基于< code > pg _ catalog . pg _ attribute ，但这是一个有利弊的可选决定。使我的查询更简单更快，因为我可以使用表的OID。相关:

• 如何检查给定模式中是否存在表
• 在PostgreSQL中选择具有特定列名的列

您必须将新添加的列从计数中排除，否则计数将减一。

使用数据类型 smallint 进行计数，因为表中不能超过 1600 列。

我不使用变量，而是直接执行< code>SELECT语句的结果。在plpgsql中，赋值是比较昂贵的。不过，没什么大不了的。也是品味和风格的问题。

我习惯在参数和变量前面加上下划线（_tbl），以排除变量和列名之间的歧义。

Answer 2

在Postgres中，您可以将其表示为：

select t.*,
       ((name is null)::int +
        (age is null)::int +
        (sex is null)::int
       ) as numnulls
from table t;

为了在未知表上实现这一点，您需要使用动态SQL并获取列列表（例如从information_schema.columns））。

Answer 3

可能不拼写列。将列取消透视为行并计数。

聚合函数的计数（

适用于具有任意数量的任何数据类型的列的任何表。

在内置JSON函数的Postgres 9.3中：

SELECT *, (SELECT count(*) - count(v)
           FROM json_each_text(row_to_json(t)) x(k,v)) AS ct_nulls
FROM   tbl t;

什么是x（k， v）？

json_each_text（）返回一组有两列的行。默认列名是key和value，可以在我链接的手册中看到。我提供了表和列别名，因此我们不必依赖默认名称。第二列名为v。

或者，在至少 8.3 以来的任何 Postgres 版本中，安装了额外的模块 hstore，甚至更短，更快：

SELECT *,  (SELECT count(*) - count(v) FROM svals(hstore(t)) v) AS ct_nulls
FROM   tbl t;

这个更简单的版本只返回一组单个值。我只提供了一个简单的别名v，它自动被视为表和列别名。

< li >在Postgres数据库中的多个模式上安装hstore的最佳方法是什么？

由于附加列在功能上是依赖的，我会考虑根本不在表中持久化它。不如像上面演示的那样实时计算它，或者创建一个具有多态输入类型的小函数：

CREATE OR REPLACE FUNCTION f_ct_nulls(_row anyelement)
  RETURNS int  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT (count(*) - count(v))::int FROM svals(hstore(_row)) v';

(< code >并行安全仅适用于Postgres 9.6或更高版本。)

然后:

SELECT *, f_ct_nulls(t) AS ct_nulls
FROM   tbl t;

您可以将其包装到视图中...

数据库

这也应该回答你的第二个问题：

…表名是从参数获取的，我事先不知道表的模式。这意味着我需要用输入表名更新表。