【PGSQL】240411工作总结 1

最近在进行数据库规模统计和历史数据缺失检查，在这里简单记录下一些常用的pgsql语句。
1、天级、分钟级，带日期字段的表，查缺失哪些时间的数据
（1）先按时间分组，查天级数据/分钟级数据一般每天是多少条。这里以五分钟级数据，每天288条为完整数据为例。

--按天分租，查询一般情况下每天数据的条数
select date_trunc('day',ts) as ts,count(value) as cnt 
from schemaname.my_table_name--表名
group by date_trunc('day',ts)

（2）用表中的最小到最大日期，生成递归序列。对该递归序列，用left join与原表联表，根据条件筛选缺失时间或缺失数据情况。
以下面的sql为例，原表：

ts	value
2024-01-01 00:00:05	63
2024-01-01 00:00:10	34
2024-01-01 00:00:15	…

筛选出的结果的示例：

start_date	tts	cnt
2024-01-01	null	null
2024-01-02	2024-01-02	98

--对分钟级数据的最小到最大日期，生成递归的start_date字段
WITH RECURSIVE date_series AS (
    SELECT date_trunc('day',MIN(ts)) AS start_date
    FROM schemaname.my_table_name --表名
    UNION ALL
    SELECT start_date + INTERVAL '1 day'
    FROM date_series
    WHERE start_date + INTERVAL '1 day' <= (SELECT date_trunc('day',MAX(ts)) FROM schemaname.my_table_name) --表名
),
a as (
select date_trunc('day',ts) as tts,count(value) as cnt 
from schemaname.my_table_name --表名
group by date_trunc('day',ts))

select start_date,tts,cnt from date_series
left join a
on date_series.start_date=a.tts
where tts is null --根据情况修改筛选条件：缺失时间或缺失数据，注意或筛选哪个放前面
or cnt=0 or cnt<288

24/04/12补充：真滴服了，有个老项目的日期字段是数字型（甚至一个表一个字段命名方式！每个表的字段名都不一样…），时间字段是202001010005这样，select的时候要进行一波转换：

SELECT date_trunc('day',to_date(cast(left(MIN(ds),8) as varchar(20)),'yyyymmdd')) AS start_date

2、求数据库的数据总量和数据库规模

--查看数据总量（行数）
SELECT SUM(reltuples) FROM pg_class WHERE relkind = 'r'`

--查看数据库规模（GB）
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size

3、查看数据库空间大小

---查看数据库空间大小
select pg_database.datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;
---查看数据表空间大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables order by pg_relation_size(relid) desc;
---查看schema库空间大小，不好含索引
select
	schemaname,
	pg_size_pretty(cast(sum(pg_relation_size(schemaname || '.' || tablename))as bigint))
from
	pg_tables t
inner join pg_namespace d on
	t.schemaname = d.nspname
group by schemaname