如何计算oracle sql中表的所有列中零的数量

Question

假设您有一个名为 db_table1 的数据库表，其中包含数百列，其中大多数包含大量 0。

我试图计算每列的零数并将其除以列的长度，以便我可以看到每列中零的比率。

下面的代码给出了每列中空值的比率。但我无法将其修改为计数 0 而不是空值

任何帮助将不胜感激

SELECT column_name, (num_nulls/ (SELECT COUNT(*) FROM db_table)*100)
FROM all_tab_columns 
WHERE table_name='table’

示例数据：

col_1	col_2	col_3
0.000	0.000	0.000
0.000	0.000	14.857
3.548	0.000	0.000
0.000	0.000	0.000
0.000	0.000	0.000
0.000	11.586	0.000

预期输出：

All_Columns	Ratio_of_Zeros
col_1	65.5
col_2	73.5
col_3	48.6

简而言之，我需要每列中的零总数除以总行数，这给出了每列中零的百分比或比率，这样我就可以像这样对待零null 并消除该列（如果超过）百分之 60 的零可以说...

Answer 1

您可以使用：

WITH AA AS (
select 
col_1,col_2,col_3
from table_name
)

SELECT column_name, COUNT(column_name) zeros FROM
(
SELECT * FROM (

SELECT * FROM AA
UNPIVOT(
    zeros  
    FOR column_name 
    IN ( 
col_1,col_2,col_3
    )
)
) WHERE zeros = 0
) GROUP BY column_name

Answer 2

您可以使用：

SELECT 'SELECT ''' || table_name || ''' AS table_name, ' ||
               '''' || column_name || ''' AS column_name, ' ||
               '100 - SUM(LENGTH(REPLACE(TO_CHAR("'|| column_name || '", ''fm999999999999999990.000''), ''0''))-1)' ||
               ' / SUM(LENGTH(TO_CHAR("'|| column_name || '", ''fm999999999999999990.000''))-1) * 100 AS ratio_of_zeroes ' ||
       'FROM "' || table_name || '" UNION ALL'
       column_name
FROM   user_tab_columns
WHERE  data_type = 'NUMBER';

生成 SQL 语句并从最后一行中删除 UNION ALL 并执行它。

如果您有表：

CREATE TABLE table_name (col_1, col_2, col_3) AS
SELECT 0.000,  0.000,  0.000 FROM DUAL UNION ALL
SELECT 0.000,  0.000, 14.857 FROM DUAL UNION ALL
SELECT 3.548,  0.000,  0.000 FROM DUAL UNION ALL
SELECT 0.000,  0.000,  0.000 FROM DUAL UNION ALL
SELECT 0.000,  0.000,  0.000 FROM DUAL UNION ALL
SELECT 0.000, 11.586,  0.000 FROM DUAL;

那么生成的语句（删除最后的 UNION ALL）为：

SELECT 'TABLE_NAME' AS table_name, 'COL_1' AS column_name, 100 - SUM(LENGTH(REPLACE(TO_CHAR("COL_1", 'fm999999999999999990.000'), '0'))-1) / SUM(LENGTH(TO_CHAR("COL_1", 'fm999999999999999990.000'))-1) * 100 AS ratio_of_zeroes FROM "TABLE_NAME" UNION ALL
SELECT 'TABLE_NAME' AS table_name, 'COL_2' AS column_name, 100 - SUM(LENGTH(REPLACE(TO_CHAR("COL_2", 'fm999999999999999990.000'), '0'))-1) / SUM(LENGTH(TO_CHAR("COL_2", 'fm999999999999999990.000'))-1) * 100 AS ratio_of_zeroes FROM "TABLE_NAME" UNION ALL
SELECT 'TABLE_NAME' AS table_name, 'COL_3' AS column_name, 100 - SUM(LENGTH(REPLACE(TO_CHAR("COL_3", 'fm999999999999999990.000'), '0'))-1) / SUM(LENGTH(TO_CHAR("COL_3", 'fm999999999999999990.000'))-1) * 100 AS ratio_of_zeroes FROM "TABLE_NAME";

执行的输出为：

<表类=“s-表”>
<标题>

TABLE_NAME
COLUMN_NAME
RATIO_OF_ZEROES

<正文>

TABLE_NAME
COL_1
83.33333333333333333333333333333333333333

TABLE_NAME
COL_2
80

TABLE_NAME
COL_3
80

db<>fiddle 此处