介绍 select to_char (log.date, & # 39; yyyy-MM-dd HH24& # 39;), as 小时,
log.exten,才能,总和(case log.grade when & # 39; 1 & # 39;, then 1, else 0,结束),as “1”;
和才能(case log.grade when & # 39; 2 & # 39;, then 1, else 0,结束),as “2“,
和才能(case log.grade when & # 39; 3 & # 39;, then 1, else 0,结束),as “3“,
和才能(case log.grade when & # 39; 4 & # 39;, then 1, else 0,结束),as “4“,
和才能(case log.grade when & # 39; 5 & # 39;, then 1, else 0,结束),as “5”;
log.direction 才能得到iface_satisfaction_investigation as log
where log.date 祝辞=,& # 39;2017 - 08 - 03,就是# 39;,以及log.date & lt;, & # 39; 2017 - 08 - 04 -,就是,,
group by 小时,log.exten log.direction order by 小时,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 “吐司表总大小,,,
得到;
select *, total_bytes-index_bytes-coalesce (toast_bytes, 0), as table_bytes
得到;
选择
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
得到pg_class c
left join pg_namespace n 提醒n.oid =c.relnamespace
where relkind =, & # 39; " # 39;
,)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 “累计更新的行数(包含热;更新的行)“,
n_tup_del as “累计删除的行数“,
n_live_tup as “当前活动行估计数量,,
n_dead_tup as “当前死亡行的估计数量,,
n_mod_since_analyze as “最后一次分析后被修改的行估计数量,,
last_vacuum as “上次被手动清理的时间(不统计VACUUM 完整的)“,
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 “表大小(不包含索引)“;
得到pg_stat_user_tables
order by 1;/*统计用户表IO信息*/选择
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_blks_hit as “吐司表(如果有)缓冲区命中数量,,
tidx_blks_read as “吐司表索引(如果有)读取的磁盘块数“,
tidx_blks_hit as “吐司表索引(如果有)缓冲区命中数量,
得到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 “索引大小“
得到pg_stat_user_indexes
order by 1、2;/*追踪函数,需要打开track_functions参数(默认关闭)*/null
这期内容当中小编将会给大家带来有关利用Postgresql怎么动态统计某一列的值,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
实例解析:
to_char:用于查询时间格式化,to_char(日志。目前为止,& # 39;yyyy-MM-dd HH24& # 39;),大致的结果是:2017-08-03 13
sum():毫无疑问是用来计算总和的。
<代码>(情况下日志。年级当& # 39;1 & # 39;那么其他1 0结束)是计算什么呢?
<强>他的意思就是:
计算年级这个列的值为1的时候有多少行,后面的总和(……)就类推了。
其他的也没有什么好讲的了
<强>补充:PostgreSQL常用的统计信息
我就废话不多说了,大家还是直接看代码吧~
