Postgresql 动态统计某一列的某一值出现的次数实例

select to_char(log. date , 'yyyy-MM-dd HH24' ) as hour ,

   log.exten, sum ( case log.grade when '1' then 1 else 0 end ) as "1" ,

   sum ( case log.grade when '2' then 1 else 0 end ) as "2" ,

   sum ( case log.grade when '3' then 1 else 0 end ) as "3" ,

   sum ( case log.grade when '4' then 1 else 0 end ) as "4" ,

   sum ( case log.grade when '5' then 1 else 0 end ) as "5" ,

   log.direction from iface_satisfaction_investigation as log

where log. date >= '2017-08-03 0' and log. date < '2017-08-04 0'

group by hour ,log.exten,log.direction order by hour ,log.exten,log.direction asc

/*计算表的空间大小*/

select oid,table_schema as "模式" ,

     table_name as "表名" ,

     row_estimate:: bigint as "表中的行数(估计值)" ,

     pg_size_pretty(total_bytes) as "总大小" ,

   pg_size_pretty(table_bytes) as "表大小" ,

     pg_size_pretty(index_bytes) as "索引大小" ,

   pg_size_pretty(toast_bytes) as "toast表总大小"

from (

     select *, total_bytes-index_bytes- coalesce (toast_bytes,0) as table_bytes

         from (

             select

                 c.oid,

                 nspname as table_schema,

                 relname as table_name,

                 c.reltuples as row_estimate,

                 pg_total_relation_size(c.oid) as total_bytes,

                 pg_indexes_size(c.oid) as index_bytes,

                 pg_total_relation_size(reltoastrelid) as toast_bytes

             from pg_class c

             left join pg_namespace n on n.oid = c.relnamespace

             where relkind = 'r'

  ) t1

) t2 order by 2,3;

/*统计用户表信息*/

select

     schemaname as "模式" ,

     relname as "表名" ,

     seq_scan as "顺序扫描的 次数 " ,

     seq_tup_read as "顺序扫描获取活动行的数量" ,

     idx_scan as "索引扫描次数" ,

     idx_tup_fetch as "索引扫描获取活动行的数量" ,

     n_tup_ins as "累计插入的行数" ,

     n_tup_upd as "累计更新的行数(包含HOT 更新的行)" ,

     n_tup_del as "累计删除的行数" ,

     n_live_tup as "当前活动行估计数量" ,

     n_dead_tup as "当前死亡行的估计数量" ,

     n_mod_since_analyze as "最后一次分析后被修改的行估计数量" ,

     last_vacuum as "上次被手动清理的时间(不统计VACUUM FULL)" ,

     last_autovacuum as "上次自动清理的时间" ,

     last_analyze as "上次手动分析的时间" ,

     last_autoanalyze as "上次自动清理分析的时间" ,

     vacuum_count as "手动清理的次数" ,

     autovacuum_count as "自动清理的次数" ,

     analyze_count as "手动分析的次数" ,

     autoanalyze_count as "自动分析的次数" ,

     pg_size_pretty(pg_table_size(relid)) as "表大小(不包含索引)"

from pg_stat_user_tables

order by 1;

/*统计用户表IO信息*/

select

     schemaname as "模式" ,

     relname as "表名" ,

     heap_blks_read as "读取的磁盘块数量" ,

     heap_blks_hit as "缓冲区命中数量" ,

     idx_blks_read as "表上所有索引读取的磁盘块数" ,

     idx_blks_hit as "表上的所有索引缓冲区命中数量" ,

     toast_blks_read as "TOAST表(如果有)读取的磁盘块数" ,

     toast_blks_hit as "TOAST表(如果有)缓冲区命中数量" ,

     tidx_blks_read as "TOAST表索引(如果有)读取的磁盘块数" ,

     tidx_blks_hit as "TOAST表索引(如果有)缓冲区命中数量"

from pg_statio_user_tables

order by 1;

/*统计用户索引信息*/

select

     indexrelid,

     schemaname as "模式" ,

     relname as "索引所在的表名称" ,

     indexrelname as "索引名称" ,

     idx_scan as "索引扫描次数" ,

     idx_tup_read as "索引扫描返回的索引项数量" ,

     idx_tup_fetch as "简单索引扫描获取的活动行数量" ,

     pg_size_pretty(pg_relation_size(indexrelid)) as "索引大小"

from pg_stat_user_indexes

order by 1,2;

/*追踪函数,需要打开track_functions参数(默认关闭)*/

select * from pg_stat_user_functions;