从初始查询的结果中执行多个计数查询的总和

问题描述：

我想编写一个查询来计算数据库中的每个对象。我知道你可以查询统计信息，但是我想知道如何编写这种一般类型的查询，以防我需要再次执行。从初始查询的结果中执行多个计数查询的总和

我有这个疑问，这在DB给每个表名的结果：

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema='public' 
AND table_type='BASE TABLE'

结果是这个样子：

table_name 
---------- 
queue_classic_jobs 
activities 
attachments 
comments 
csv_files

我想要做的每一个count(*)每个结果行的表格：

table_name  |  row_count 
---------------------------------- 
queue_classic_jobs    6 
activities      2 
attachments      4 
comments       8 
csv_files      10

然后求和row_count列。我的查询应该是什么样子？

答

使用plpgsql EXECUTE命令。

您有两种选择。首先是与临时表的匿名代码块保存结果：

CREATE TEMP TABLE IF NOT EXISTS results(table_name text, row_count bigint); 
TRUNCATE results; 
DO $$ 
DECLARE 
    tname text; 
BEGIN 
    FOR tname IN 
     SELECT table_name 
     FROM information_schema.tables 
     WHERE table_schema='public' 
     AND table_type='BASE TABLE' 
    LOOP 
     EXECUTE format($fmt$ 
      INSERT INTO results 
      SELECT '%s', count(*) from %s 
      $fmt$, tname, tname); 
    END LOOP; 
END $$; 

SELECT * FROM results 
UNION ALL 
SELECT 'TOTAL', sum(row_count) FROM results;

第二个选项是一个PLPGSQL功能：

CREATE OR REPLACE FUNCTION show_row_count() 
RETURNS TABLE (table_name text, row_count bigint) 
LANGUAGE plpgsql 
AS $$ 
DECLARE 
    tname text; 
BEGIN 
    FOR tname IN 
     SELECT i.table_name 
     FROM information_schema.tables i 
     WHERE table_schema='public' 
     AND table_type='BASE TABLE' 
    LOOP 
     RETURN QUERY EXECUTE format($fmt$ 
      SELECT '%s'::text, count(*) from %s 
      $fmt$, tname, tname); 
    END LOOP; 
END $$; 

WITH row_counts AS (SELECT * FROM show_row_count()) 
SELECT * FROM row_counts 
UNION ALL 
SELECT 'TOTAL'::text, sum(row_count) FROM row_counts;

了解更多：Executing Dynamic Commands

答

如果你想与个人ROW_COUNTS表做

SELECT nspname AS schemaname,relname,reltuples 
FROM pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE 
    nspname NOT IN ('pg_catalog', 'information_schema') AND 
    relkind='r' 
ORDER BY reltuples DESC

所有行的总数中的所有表都

With infotables as 
(
SELECT nspname AS schemaname,relname,reltuples 
FROM pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE 
    nspname NOT IN ('pg_catalog', 'information_schema') AND 
    relkind='r' 
ORDER BY reltuples DESC 
) 
select sum(reltuples) from infotables`

参考：How do you find the row count for all your tables in Postgres

谢谢回答，但我对实际的行数没有真正的兴趣。我想了解如何编写更通用的查询版本。我已经找到了你提供的链接 – AndrewLngdn

答

你可以使用分析函数来获得每行的总数。

  SELECT 
       nspname AS schemaname, 
       relname AS TABLE_NAME, 
       reltuples AS ROW_COUNT, 
       SUM (reltuples) OVER() AS total_rows_count 
      FROM 
       pg_class C 
      LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace) 
      WHERE 
       nspname = 'ptab' 
      AND relkind = 'r' 
      ORDER BY 
       reltuples DESC

从初始查询的结果中执行多个计数查询的总和

相关推荐