每行計算 NULL 值

我想在不列舉列名的情況下計算表中每行存在的空值數。例如：

WITH t as (VALUES
 (NULL ,'hi',2,NULL,'null'),
 ('' ,'hi',2,3,'test'),
 (NULL ,'hi',2,3,'null')
)
SELECT countnulls(t)
FROM t;

會導致：

numnulls
2
0
1

我能得到的最接近的是以下黑客row_to_json()：

select 
(CHAR_LENGTH(row_to_json(t)::text)
- CHAR_LENGTH(REPLACE(row_to_json(t)::text, 'null', '')))/4 from t;

這是……相當黑客（不是一個好的方式）。它可以工作，但是當它出現在實際數據或列名中時，它會將字元串“null”計為 NULL。所以在上述情況下是不正確的。

1.你知道列名

對於 Postgres 9.6或更高版本，請使用num_nulls()

SELECT id, num_nulls(a,b,c,d,e)
FROM   tbl;

對於任何數據類型的混合，準確返回您想要的結果。

手冊：

num_nulls(VARIADIC "any")…返回空參數的數量

對於 Postgres 9.5或更早版本，轉換為text[]，array_remove(arr, null)並使用剩餘的數組長度進行精確計數：

SELECT 5 - cardinality(array_remove(ARRAY[a::text,b::text,c::text,d::text,e::text], null))
FROM   tbl;

任何類型都可以轉換為text. text當然，對於列而言，演員表是多餘的。

array_remove()需要 Postgres 9.3 或更高版本。

cardinality()需要 Postgres 9.4 或更高版本。array_length(arr, 1)在舊版本中替換為。

2. 你不知道列名，但 Postgres 知道

在實際表（或其他註冊對象，如視圖或物化視圖）上建構時，我們可以從系統目錄中檢索列名，pg_attribute以使用動態 SQL 完全自動化。像：

CREATE OR REPLACE FUNCTION f_num_nulls(_tbl regclass)
 RETURNS SETOF int
 LANGUAGE plpgsql AS
$func$
BEGIN
  RETURN QUERY EXECUTE format(
     'SELECT num_nulls(%s) FROM %s'
   , (SELECT string_agg(quote_ident(attname), ', ')  -- column list
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped    -- no dropped (dead) columns
      AND    attnum > 0)         -- no system columns
   , _tbl
  );
END
$func$;

稱呼：

SELECT * FROM f_num_nulls('myschema.tbl');

按目前物理順序返回每行的計數。沒有別的，絕對通用。

有關的：

• 表名作為 PostgreSQL 函式參數
• psql: SELECT * … 除了一列

我們還可以使用多態函式傳遞每一行以返回單個計數。有關的：

• 重構 PL/pgSQL 函式以返回各種 SELECT 查詢的輸出

3.你什麼都不知道：匿名記錄

萬一即使 Postgres 也不知道列名（例如VALUES您的範例中的表達式），請轉換為文件類型（json, jsonb, xml, hstore）以獲取句柄，例如 ypercube （現在已刪除評論）和Evan展示的那樣。

但是匿名記錄沒有主鍵或每個定義的任何其他唯一屬性。在每個子查詢中計數LATERAL以防止錯誤聚合。展示**jsonb**：

SELECT *
FROM  (
  VALUES
     (NULL ,'hi',2,NULL,'null')
   , (NULL ,'hi',2,NULL,'null')       -- duplicate row !!!
   , ('' ,'hi',2,3,'test')
   , (NULL ,'hi',2,3,'null')
  ) t                                 -- column names unknown
, LATERAL (
  SELECT count(*) FILTER (WHERE j.value = jsonb 'null') AS num_nulls
  FROM   jsonb_each(to_jsonb(t)) j
  ) c;

或者**json**: 可能會快一點，因為轉換更便宜。

展示 3 種不同的方式：

SELECT *
FROM  (
  VALUES
     (NULL ,'hi',2,NULL,'null')
   , (NULL ,'hi',2,NULL,'null')
   , ('' ,'hi',2,3,'test')
   , (NULL ,'hi',2,3,'null')
  ) t   -- column names unknown
, to_json(t) j
, LATERAL (
  SELECT count(*) FILTER (WHERE j1.value::text = 'null') AS num_nulls1
  FROM   json_each(to_json(t)) j1
  ) c1
, LATERAL (
  SELECT count(*) FILTER (WHERE j->>k IS NULL) AS num_nulls2
  FROM   json_object_keys(j) k
  ) c2
, LATERAL (
  SELECT count(*) - count(j->>k ) AS num_nulls3
  FROM   json_object_keys(j) k
  ) c3;

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/205887