如何在Redshift中查询一个Schema下所有表各列的最大值？

要批量处理300张表的各列最大值查询，手动编写每个表的MAX()语句效率太低，直接利用数据库的系统元数据表生成动态SQL，执行后就能一次性得到所有结果。

不同数据库的实现代码 #

MySQL #

SELECT CONCAT(
    'SELECT ''', TABLE_NAME, ''' AS `table`, ''', COLUMN_NAME, ''' AS `column`, MAX(`', COLUMN_NAME, '`) AS max_value FROM `', TABLE_NAME, '`',
    IF(@row < (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()), ' UNION ALL ', '')
) INTO @sql
FROM information_schema.COLUMNS
CROSS JOIN (SELECT @row := 0) r
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, COLUMN_NAME;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

DO $$
DECLARE
    sql_text TEXT;
BEGIN
    SELECT string_agg(
        format('SELECT ''%I'' AS "table", ''%I'' AS "column", MAX(%I) AS max_value FROM %I',
               table_name, column_name, column_name, table_name),
        ' UNION ALL '
    ) INTO sql_text
    FROM information_schema.columns
    WHERE table_schema = current_schema();

    EXECUTE sql_text;
END $$;

SQL Server #

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += CONCAT(
    'SELECT ''', t.name, ''' AS [table], ''', c.name, ''' AS [column], MAX(', QUOTENAME(c.name), ') AS max_value FROM ', QUOTENAME(t.name), ' UNION ALL '
)
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id;

SET @sql = LEFT(@sql, LEN(@sql) - 10); -- 移除末尾多余的UNION ALL

EXEC sp_executesql @sql;

执行后得到的结果示例 #

注意事项 #

• 执行账号需要拥有所有目标表的查询权限
• 针对数据量较大的表，全表扫描会占用较多系统资源，建议在业务低峰期运行
• 部分特殊数据类型（如TEXT、BLOB）无法直接使用MAX()函数，需根据实际类型做适配处理

内容的提问来源于stack exchange，提问作者Vsem Udachi