RANK 函数不适用于 posgresql 中的 CTE 表？

Question

我正在尝试根据两列的评论和分区（strig_full_nm and exam_year_nb）对CTE表（A）输出进行排名。

以下是我的代码：

\`WITH a AS
(
select rr.risk_full_nm ,e.exam_year_nb,COUNT(DISTINCT e.exam_dmn_xtrnl_id) as "reviews"
from examwrksp.dsh_exam_base e
left join star.firm f ON f.firm_id = e.crd_nb --firm
left join examwrksp.dsh_mlstn_event_dates d ON d.exam_id  = e.exam_id --exam milestones
left join examwrksp.dsh_scope_risk_cntnt rr on rr.exam_id = e.exam_id
where e.exam_year_nb \>= '2018' and e.exam_ctgry_cd = 'CYCLE' and e.exam_st_ds in ('Open','Closed') and e.dstrt_nm != 'MAP Group' and e.exam_type_ds not like 'Funding%'
and e.exam_dmn_prmry_fl = 'Y' and f.main_ofc = 'Y' and d.crd_nb is null
and rr.scope_actv_st is not null and rr.scope_actv_st = 'ACTIVE' and rr.unit_type_label_tx not in ('Discovery Review','Risk Identification Review')
GROUP BY rr.risk_full_nm ,e.exam_year_nb
)
select a.risk_full_nm,a.exam_year_nb,a.reviews,
RANK () OVER(PARTITION BY a.risk_full_nm,a.exam_year_nb ORDER BY a.reviews desc)
from a
order by reviews desc\`

我希望我的等级列会排名我的记录，但我得到了每行中的“ 1”。我想念什么？

Answer 1

我分析了您的疑问。您的查询中有一些错误的逻辑代码。我向您解释：

1. 每次计算每个分组字段的窗口聚合功能。但是，分组字段从上获得（按语句进行分区。在查询中，您对两个字段进行了分组，a.risk_full_nm和a.exam_year_nb分组这些字段后，所有结果的行都不同，因为您必须重复（同一）。我使用示例查询来解释

：

with test_data as materialized 
(
    select 1 as id, 'user1' as username, 'admin' as type_of, '2022-01-14'::date as login_date 
    union all 
    select 2 as id, 'user2' as username, 'user' as type_of, '2022-01-06'::date as login_date
    union all 
    select 3 as id, 'user1' as username, 'admin' as type_of, '2022-01-29'::date as login_date
    union all 
    select 4 as id, 'user3' as username, 'user' as type_of, '2022-02-11'::date as login_date
    union all 
    select 5 as id, 'user2' as username, 'user' as type_of, '2022-01-16'::date as login_date
    union all 
    select 6 as id, 'user2' as username, 'user' as type_of, '2022-01-17'::date as login_date
    union all 
    select 7 as id, 'user2' as username, 'user' as type_of, '2022-01-18'::date as login_date
)
select 
    id, 
    username, 
    type_of,  
    count(*) over (partition by id, username, type_of) as login_count
from 
    test_data;

-- Result of this query 
id  username    type_of     login_count
-----------------------------------------
1   user1       admin       1
2   user2       user        1
3   user1       admin       1
4   user3       user        1
5   user2       user        1
6   user2       user        1
7   user2       user        1

在此查询中，我想获得每个用户的登录名。是不同的。
当我们从“摸索”字段列表中删除“ ID”字段时，我们返回了正确计算的正确数据并计数（）。例如：

select 
    id, 
    username, 
    type_of,  
    count(*) over (partition by username, type_of) as login_count
from 
    test_data

-- Result this query: 

id  username    type_of     login_count
----------------------------------------
3   user1       admin       2
1   user1       admin       2
7   user2       user        4
2   user2       user        4
5   user2       user        4
6   user2       user        4
4   user3       user        1