使用窗口函数查询

Question

create table hr
(
    candidate_id INTEGER PRIMARY KEY, -- candidate unique identifier
    candidate_gname VARCHAR(30) NOT NULL, -- candidate's given name
    candidate_fname VARCHAR(30) NOT NULL, -- candidate's family name
    position VARCHAR(30) NOT NULL, -- job position the candidate applied for
    hired BOOLEAN NOT NULL, -- final hiring decision (yes/no)
    zipcode CHAR(5) NOT NULL, -- hiring office zipcode
    population CHAR(2) NOT NULL -- candidate's membership to the
    -- "general" population (GP) or to the
    -- "protected" population (PP)
);

EX1） “受保护”人口的成员资格是否会影响被雇用的随机候选人的机会？编写一个SQL查询，以计算相对于两个人群的正面和负雇用决策的相对频率（“ PP”和“ GP”）。

以下是我使用窗口函数编写的查询， 但是FREQ应该总结到人口最多1个WRT，但考虑到整个，我的疑问总结了1个。 我做什么变化？ 预期结果：

FREQ	雇用	人口
0.49	true	GP
0.51	错误的	GP
0.52	true	PP
0.48	false	PP

我写过的

Select round((avg(freq1)/5000),2) as freq,hired,population
From (Select population,hired,count(hired) 
        over (partition by population, hired) as freq1
        From hr
    ) as hr1
Group by population,hired
Order by population, hired desc;

查询：查询的结果：

freq | hired | population 
------+-------+------------
 0.15 | t     | GP
 0.45 | f     | GP
 0.06 | t     | PP
 0.34 | f     | PP
(4 rows)

Answer 1

您需要了解人口（误导人口）人口，而不是除以5000

drop table if exists t;
create table t
(population varchar(2),hired varchar(5));

insert into t values
('pp','true'),('pp','true'),('pp','false'),('pp','true'),('pp','false'),
('gp','true'),('gp','true'),('gp','false'),('gp','true'),('gp','false');

Select round((avg(freq1))/popcount,2) as freq,hired,population
From (Select population,hired,
      count(hired) over (partition by population, hired) as freq1,
      count(hired) over (partition by population) as popcount
        From t hr
    ) as hr1
Group by population,hired
Order by population, hired desc;

'0.60', 'true', 'gp'
'0.40', 'false', 'gp'
'0.60', 'true', 'pp'
'0.40', 'false', 'pp'