PG数据库如何统计数据填充率

实战说明

在数据库中，数据填充率是指表中非空值的比例。本次将介绍如何使用 SQL 查询来统计数据填充率。

首先，我们需要了解一些基本的表结构信息。在这个例子中，我们关注的是名为 “cases” 的 schema 下的 “case_base” 表。这个表包含了多个列，每个列都有一个对应的命名空间、数据域、表名、列名、列名备注、字段类型、允许为空等信息。

为了计算数据填充率，我们需要先统计表中的总行数和非空行数。这可以通过以下 SQL 查询实现：

实现代码如下

SELECT
  n.nspname AS 命名空间，
  '病案首页' AS 数据域，
  c.relname AS 表名，
  a.attname AS 列名，
  col_description(a.attrelid, a.attnum) AS 列名备注，
  format_type(a.atttypid, a.atttypmod) AS 字段类型，
  (CASE WHEN a.attnotnull = true THEN '否' ELSE '是' END) AS 允许为空，
  (SELECT count(*) FROM cases.case_base) AS 行数，
  ceil((SELECT count(*) FROM cases.case_base) *
    (1 - coalesce(round(cast(s.null_frac as numeric), 3), 0))) AS 非空，
  1 - coalesce(round(cast(s.null_frac as numeric), 3), 0) AS 填充率
FROM
  pg_class c
JOIN
  pg_namespace n ON n.oid = c.relnamespace
JOIN
  pg_attribute a ON a.attrelid = c.oid
LEFT JOIN
  pg_stats s ON s.schemaname = n.nspname
    AND s.tablename = c.relname
    AND s.attname = a.attname
WHERE schemaname = 'cases' AND tablename = 'case_base' AND
  c.relkind = 'r' AND
  n.nspname NOT LIKE 'pg_%' AND
  n.nspname NOT LIKE 'information_schema%' AND
  a.attnum > 0
ORDER BY
  n.nspname,
  c.relname,
  a.attnum DESC;

最终实现效果如下：

在这个查询中，我们使用了 count(*) 函数来计算总行数，然后使用 coalesce 和 round 函数来计算非空行数。最后，我们将非空行数除以总行数，得到数据填充率。

通过这个 SQL 查询，我们可以快速地了解到 “cases” schema 下的 “case_base” 表的数据填充率。这对于分析和优化数据库性能非常有帮助。

相关表简要说明

一、在上述 SQL 查询中，涉及到了几张表：

1）pg_class：这个表存储了数据库中所有表的元数据信息。在这个查询中，我们通过连接 pg_class 表来获取表名（c.relname）和命名空间（n.nspname）。

2）pg_namespace：这个表存储了数据库中所有命名空间的元数据信息。在这个查询中，我们通过连接 pg_namespace 表来获取命名空间名称（n.nspname）。

3）pg_attribute：这个表存储了数据库中所有表的属性（列）的元数据信息。在这个查询中，我们通过连接 pg_attribute 表来获取列名（a.attname）、列名备注（col_description(a.attrelid,a.attnum)）、字段类型（format_type(a.atttypid,a.atttypmod)）、是否允许为空（a.attnotnull）等信息。

4）pg_stats：这个表存储了数据库中所有表的统计信息。在这个查询中，我们通过连接 pg_stats 表来获取非空行数（s.null_frac）和填充率（1 - coalesce(round(cast(s.null_frac as numeric),3), 0)）。