Redshift查询需求：如何在SELECT中动态统计指定表行数

问题背景 #

当前table1结构：

col1
-------------
schema.table1
schema.table2
schema.table3

期望查询结果：

col1           col2
------------------------------------------------------------
schema.table1  (select count(*) from schema.table1) 的结果
schema.table2  (select count(*) from schema.table2) 的结果
schema.table3  (select count(*) from schema.table3) 的结果

尝试以下语句无法运行，自定义函数也因不能使用FROM关键字而失败：

select col1, (select count(*) from col1) 
from table1

解决方案 #

方案1：利用Redshift系统视图（高效推荐） #

Redshift的svv_table_info系统视图存储了各表的元数据信息，包括近似行数，无需全表扫描即可快速获取数据：

select 
    t.col1,
    s.rows as col2
from table1 t
left join svv_table_info s
on split_part(t.col1, '.', 1) = s.schema
and split_part(t.col1, '.', 2) = s.table;

说明

• split_part(t.col1, '.', 1) 拆分出col1中的schema名称
• split_part(t.col1, '.', 2) 拆分出col1中的表名称
• svv_table_info.rows 为表的近似行数，若需精确值，可先对目标表执行ANALYZE更新统计信息

方案2：动态SQL（适用于精确行数需求） #

如果必须获取精确行数，可通过动态SQL生成并执行每个表的count(*)查询：

步骤1：生成查询语句

select 
    'select ''' || col1 || ''' as col1, count(*) as col2 from ' || col1 || ' union all'
from table1;

步骤2：拼接并执行完整SQL

将上述输出的语句拼接，去掉最后一行的union all，执行拼接后的SQL：

select 'schema.table1' as col1, count(*) as col2 from schema.table1 union all
select 'schema.table2' as col1, count(*) as col2 from schema.table2 union all
select 'schema.table3' as col1, count(*) as col2 from schema.table3;

步骤3：存储过程自动化执行

可通过存储过程自动完成拼接与执行：

create or replace procedure get_table_counts()
language plpgsql
as $$
declare
    v_sql text;
begin
    select string_agg('select ''' || col1 || ''' as col1, count(*) as col2 from ' || col1, ' union all ')
    into v_sql
    from table1;
    
    execute v_sql;
end;
$$;

-- 调用存储过程
call get_table_counts();

说明

• 动态SQL会对每个表执行全表扫描，数据量大时性能损耗明显，仅在必要时使用
• 执行用户需拥有所有目标表的查询权限

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