如何在PostgreSQL中维持两个不同schema查询结果的顺序

场景1：已知需要统计的表名 #

如果是固定的几个表，直接用UNION ALL合并统计结果后排序即可：

-- 统计table1
SELECT 'sch1' AS schema, 'table1' AS table_name, COUNT(*) AS count FROM sch1.table1
UNION ALL
SELECT 'sch2' AS schema, 'table1' AS table_name, COUNT(*) AS count FROM sch2.table1
-- 统计table2
UNION ALL
SELECT 'sch1' AS schema, 'table2' AS table_name, COUNT(*) AS count FROM sch1.table2
UNION ALL
SELECT 'sch2' AS schema, 'table2' AS table_name, COUNT(*) AS count FROM sch2.table2
-- 按表名分组，每个表下先sch1后sch2排序
ORDER BY table_name, schema;

场景2：自动遍历所有同名表 #

如果要统计两个Schema中所有同名的表，需要借助系统视图动态生成统计语句，以下是主流数据库的实现：

PostgreSQL版本

WITH shared_tables AS (
    -- 获取两个Schema共有的表名
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'sch1'
    INTERSECT
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'sch2'
),
table_counts AS (
    -- 统计sch1的表行数
    SELECT 
        'sch1' AS schema,
        table_name,
        (EXECUTE format('SELECT COUNT(*) FROM sch1.%I', table_name)) AS count
    FROM shared_tables
    UNION ALL
    -- 统计sch2的表行数
    SELECT 
        'sch2' AS schema,
        table_name,
        (EXECUTE format('SELECT COUNT(*) FROM sch2.%I', table_name)) AS count
    FROM shared_tables
)
SELECT * FROM table_counts
ORDER BY table_name, schema;

MySQL版本

-- 生成动态统计语句
SET @sql = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ''sch1'' AS `schema`, ''', table_name, ''' AS table_name, COUNT(*) AS count FROM sch1.', table_name, ' UNION ALL ',
        'SELECT ''sch2'' AS `schema`, ''', table_name, ''' AS table_name, COUNT(*) AS count FROM sch2.', table_name
    )
    SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.tables t
WHERE t.table_schema = 'sch1'
AND EXISTS (
    SELECT 1 FROM information_schema.tables 
    WHERE table_schema = 'sch2' AND table_name = t.table_name
);

-- 添加排序并执行
SET @sql = CONCAT(@sql, ' ORDER BY table_name, `schema`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结合LAG()计算行数差值 #

拿到上述统计结果后，直接用窗口函数就能快速计算同表两个Schema的行数差：

WITH table_counts AS (
    -- 替换成上面的统计语句
    SELECT 'sch1' AS schema, 'table1' AS table_name, 1000 AS count
    UNION ALL SELECT 'sch2' AS schema, 'table1' AS table_name, 500 AS count
    UNION ALL SELECT 'sch1' AS schema, 'table2' AS table_name, 3000 AS count
    UNION ALL SELECT 'sch2' AS schema, 'table2' AS table_name, 1000 AS count
)
SELECT 
    schema,
    table_name,
    count,
    -- 计算sch2与sch1的行数差（反向差值调换顺序即可）
    count - LAG(count) OVER (PARTITION BY table_name ORDER BY schema) AS row_diff
FROM table_counts
ORDER BY table_name, schema;

关于之前GROUP BY的问题 #

你之前用GROUP BY得到的结果不符合预期，大概率是错误地将两个Schema的同表数据合并统计，或者未指定正确的排序规则。正确思路是分别统计每个Schema的每个表，再合并结果后按「表名+Schema」排序，而非对合并数据做GROUP BY。

内容的提问来源于stack exchange，提问作者Jophin Joseph