Postgres的使用 - 1

SELECT
	CASE WHEN O.id IS NULL THEN false ELSE true END as used,
	D.status,
	count(*) AS sc 
FROM partition P
	join disk D on P.disk_id=D.id
	left join osd O on o.partition_id=P.id
GROUP BY used, D.status;

WITH timerange AS (
	SELECT
		timestamp with time zone '2018-07-27 14:00:00+08:00' AS begin_time,
		timestamp with time zone '2018-08-30 10:00:00+08:00' AS end_time
)
SELECT V.id, V.name,
	CAST(avg(VS.total_iops) AS bigint) AS total_iops,
	CAST(avg(VS.total_bandwidth_kbyte) AS bigint) AS total_bandwidth_kbyte,
	max(VS.created_at) AS "created_at"
FROM volume V join volume_stat VS on V.id=VS.volume_id
WHERE VS.created_at >= (SELECT begin_time FROM timerange) AND VS.created_at < (SELECT end_time FROM timerange)
GROUP BY V.id, V.name
ORDER BY total_iops DESC, total_bandwidth_kbyte DESC;