时间戳

首页 > 解决方案 > 在 postgres 中将结果集从宽表重塑为长表并改进查询

sql - 在 postgres 中将结果集从宽表重塑为长表并改进查询

问题描述

我编写了以下查询来获取表中所有列的缺失值数量。但这会在一个宽形表中返回结果集：

1）我需要以长格式重新调整结果集。
2）是否可以改进查询以编写更简单的查询？

查询：

-- Variable to save the row count from the table
WITH total_rows AS 
    (
    SELECT COUNT(*) AS rows
    FROM table_name
    )

-- Query to join subqueries (wide table format)
SELECT
    *
FROM

-- Subquery1
    (SELECT
        (SELECT rows FROM total_rows) - COUNT(colum1) AS missing_colum1 -- counts all values except nulls
    FROM table_name
    ) AS missing_values_colum1,

-- Subquery2    
    (SELECT
        (SELECT rows FROM total_rows) - COUNT(colum2) AS missing_colum2
    FROM table_name
    ) AS missing_values_colum2,

-- Subquery3   
    (SELECT
        (SELECT rows FROM total_rows) - COUNT(colum3) AS missing_colum3
    FROM table_name
    ) AS missing_values_colum3,

-- Subquery4    
    (SELECT
        (SELECT rows FROM total_rows) - COUNT(colum4) AS missing_colum4
    FROM table_name
    ) AS missing_values_colum4
;

任何提示都会有所帮助。先感谢您。

标签： sqlpostgresql

解决方案

我不完全清楚你想要实现什么。

但对于初学者来说，您现有的查询可以简化为：

select count(*) as total_rows, 
       count(*) - count(column1) as missing_column1,
       count(*) - count(column2) as missing_column2,
       count(*) - count(column3) as missing_column3,
       count(*) - count(column3) as missing_column4
from table_name

如果您担心：count(*)只会评估一次，而不是五次。

如果您希望每列的数字作为行（“长格式”？）而不是列（“宽格式”），您可以从上面的结果中取消透视：

with totals as (
  select count(*) as total_rows, 
         count(*) - count(column1) as missing_column1,
         count(*) - count(column2) as missing_column2,
         count(*) - count(column3) as missing_column3,
         count(*) - count(column3) as missing_column4
  from table_name
)
select t.total_rows, m.col_name, m.num_missing
from totals t
  cross join lateral (
    values ('column1', missing_column1),
           ('column2', missing_column2),
           ('column3', missing_column3),
           ('column4', missing_column4)
  ) as m(col_name, missing);

推荐阅读