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

Question

我有这张桌子。

列名	数据类型
ID	uuid
起源	uuid
类型	varchar(31)
日期	时间戳

示例记录：

ID	起源	类型	日期
1	1111	一个	2021-11-09 10:01:31.001
2	1111	一个	2021-11-08 03:02:22.020
3	1111	乙	2021-10-01 11:03:13.003
4	2222	一个	2021-11-07 13:04:54.040
5	3333	乙	2021-11-09 20:05:45.005
6	3333	乙	2021-11-08 21:06:36.060
7	3333	乙	2021-11-08 00:07:27.700

我想做一个 SQL 查询，生成一个报告，显示过去 7 天（列）origin中每个type或两者都有的 s 的百分比。date有几条相同的记录origin。

期望的示例结果：

PercentageOfOriginThatHasTypeAInLast7Days	PercentageOfOriginThatHasTypeBInLast7Days	PercentageOfOriginThatHasBothTypeInLast7Days
66.66	33.33	0

我怎么能那样做？

我正在使用 PostgreSQL 11.x

标签： sqlpostgresql

Answer 1

您可以使用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