计算一行中为NULL的属性的数量 [英] Count the number of attributes that are NULL for a row

问题描述

我想向表中添加一个新列，以记录每个元组(行)的值为空的属性的数量.如何使用SQL来获取号码?

I want to add a new column to a table to record the number of attributes whose value are null for each tuple (row). How can I use SQL to get the number?

例如，如果一个元组是这样的:

for example, if a tuple is like this:

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

我要这样更新元组:

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

此外，因为我正在编写PL/pgSQL函数，并且表名是从参数获取的，所以我事先不知道表的架构.这意味着我需要使用输入表名来更新表.有人知道该怎么做吗?

Also, because I'm writing a PL/pgSQL function and the table name is obtained from argument, I don't know the schema of a table beforehand. That means I need to update the table with the input table name. Anyone know how to do this?

推荐答案

可能不带列说明.将列反向旋转到行并计数.

Possible without spelling out columns. Counter-pivot columns to rows and count.

集合函数 count(< expression>) 仅计算非空值，而

The aggregate function count(<expression>) only counts non-null values, while count(*) counts all rows. The shortest and fastest way to count NULL values for more than a few columns is count(*) - count(col) ...

适用于 any 表，其中 any 列数为 any 数据类型.

Works for any table with any number of columns of any data types.

在带有内置 JSON函数:

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 ，如

json_each_text() returns a set of rows with two columns. Default column names are key and value as can be seen in the manual where I linked. I provided table and column aliases so we don't have to rely on default names. The second column is named v.

或者，在至少8.3起的任何Postgres版本中，带有附加模块 hstore 已安装，甚至更短，速度也更快:

Or, in any Postgres version since at least 8.3 with the additional module hstore installed, even shorter and a bit faster:

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

此简单版本仅返回一组单个值.我只提供了一个简单的别名 v ，该别名会自动作为表和列别名.

This simpler version only returns a set of single values. I only provide a simple alias v, which is automatically taken to be table and column alias.

• 最佳在Postgres数据库中的多个架构上安装hstore的方法?

由于附加列是功能相关的，因此我会考虑不将其完全保留在表中.而不是像上面演示的那样即时计算它，或者使用多态输入类型，用于此目的:

Since the additional column is functionally dependent I would consider not to persist it in the table at all. Rather compute it on the fly like demonstrated above or create a tiny function with a polymorphic input type for the purpose:

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';

((仅适用于Postgres 9.6或更高版本的 PARALLEL SAFE .)

(PARALLEL SAFE only for Postgres 9.6 or later.)

然后:

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

您可以将其包装为 VIEW ...

You could wrap this into a VIEW ...

SQL小提琴 演示了所有内容.

SQL Fiddle demonstrating all.

这也应该回答您的第二个问题:

This should also answer your second question:

...表名称是从参数获取的，我事先不知道表的架构.这意味着我需要使用输入表名来更新表.

... the table name is obtained from argument, I don't know the schema of a table beforehand. That means I need to update the table with the input table name.

这篇关于计算一行中为NULL的属性的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！