在PostgreSQL中生成百分比报告的复杂SQL查询

Question

我有这个表。

数据类型时间戳

列名
id	uuid
原点	uuid
类型	varchar(31(
日期

这里有一种方法：

select count(*) filter (where xt = 'A' and xt = mt) * 100.0 / count(*) typeA
, count(*) filter (where xt = 'B' and xt = mt) * 100.0 / count(*) typeB
, count(*) filter (where xt <> mt) * 100.0 / count(*) BothTypes
from (
select origin, max(type) xt , min(type) mt
from data
where date >  now() - interval '7 days'
group by origin
) t 

db<gt；小提琴这里

您可以使用cte:

with cte as (
select r.origin, r.type, count(*) c from records r where r.date >= now() - interval '7 day' group by r.origin, r.type
),
cte1 as (
select c.origin, count(distinct c.type) c1 from cte c group by c.origin
)
select sum(case when c.type = 'A' then 1 end)/cast((select count(*) from cte) as float), 
sum(case when c.type = 'B' then 1 end)/cast((select count(*) from cte) as float),
(select count(*) from cte1 c1 where c1.c1 = (select count(distinct c3.type) from records c3))/((select count(*) from cte) as float)
from cte c