如何查找表中的所有NUMERIC列并对它们执行SUM()操作

Question

我在Netezza、DB2和PostgreSQL数据库中有几个表，我需要对它们进行协调，我们得出的最好方法是在所有3个数据库的所有NUMERIC表列中执行SUM（）。

有人有一种快速简单的方法来找到所有NUMERIC、INTEGER或BIGINT的列，然后对所有这些列运行SUM（）吗？

对于比较结果，我也可以手动进行，或者如果有人有办法在通用表中捕获这些结果并自动检查SUM中的差异？

Answer 1

对于DB2，您可以使用此元数据，它将帮助您找出每列的数据类型

SELECT
               COLUMN_NAME || ' ' || REPLACE(REPLACE(DATA_TYPE,'DECIMAL','NUMERIC'),'CHARACTER','VARCHAR') || 
               CASE 
                              WHEN DATA_TYPE = 'TIMESTAMP' THEN '' 
                              ELSE
                                             ' (' ||
                                             CASE 
                                                            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(30))
                                                            WHEN NUMERIC_PRECISION IS NOT NULL THEN CAST(NUMERIC_PRECISION AS VARCHAR(30)) || 
                                                                           CASE 
                                                                                          WHEN NUMERIC_SCALE = 0 THEN ''
                                                                                          ELSE ',' || CAST(NUMERIC_SCALE AS VARCHAR(3)) 
                                                                           END
                                                            ELSE ''
                                             END || ')'
               END || ',' "SQLCOL",
               COLUMN_NAME,
               DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, ORDINAL_POSITION
FROM SYSIBM.COLUMNS
WHERE TABLE_NAME = 'insert your table name'
AND TABLE_SCHEMA = 'insert your table schema'
ORDER BY ORDINAL_POSITION

Answer 2

对于Netezza，我得到了以下查询：

SELECT 0 AS ATTNUM, 'SELECT' AS SQL
UNION
SELECT ATTNUM, 'SUM(' || ATTNAME || ') AS S_' || ATTNAME || ',' AS COLMN
FROM _V_RELATION_COLUMN RC
WHERE NAME = '<table-name>'
AND FORMAT_TYPE= 'NUMERIC'
UNION
SELECT 10000 AS ATTNUM, ' 0 AS FLAG FROM ' || '<table-name>'
ORDER BY ATTNUM

仍在研究如何在DB2和PostgreSQL之间实现这一点。