假設您有一個名為 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% 的零可以說。 ..
uj5u.com熱心網友回復：
您可以使用：
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
uj5u.com熱心網友回復：
您可以使用：
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";
執行的輸出是：
TABLE_NAME COLUMN_NAME RATIO_OF_ZEROES TABLE_NAME COL_1 83.33333333333333333333333333333333333333 TABLE_NAME COL_2 80 TABLE_NAME COL_3 80
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/449526.html
上一篇：SQL查詢到所需的輸出