如何基于crud_logs表查询指定user_id的活动行数排名

现有crud_logs表用于记录用户API端点活动，表结构定义如下：

create table crud_logs (
    id           bigint generated always as identity
        constraint pk_crud_logs
            primary key,
    object_type  varchar(255)                           not null,
    object_id    bigint                                 not null,
    action       crudtypes                              not null,
    operation_ts timestamp with time zone default now() not null,
    user_id      bigint
        constraint fk_crud_logs_user_id_users
            references users
            on delete set null
);

在用户统计API开发中，需基于全量历史数据，按用户的活动记录行数对用户进行排名，仅获取单个指定user_id的排名（支持数值或百分比形式）。示例数据中user_id=34因记录数最多排名第一，现有查询可返回所有用户的排名，但需要调整为只输出指定用户的结果，例如查询user_id=58时预期输出：

user_id = 58, rank = 2

现有查询代码：

select user_id, rank() over (order by cnt desc )
from (select user_id, count(*) cnt from crud_logs group by user_id) sq

解决方案 #

1. 获取数值排名 #

在现有排名逻辑基础上，通过外层筛选指定user_id即可，以下是两种实现方式：

方式一：嵌套子查询

select user_id, user_rank
from (
    select 
        user_id, 
        rank() over (order by cnt desc) as user_rank
    from (
        select user_id, count(*) cnt 
        from crud_logs 
        group by user_id
    ) sq
) ranked
where user_id = 58; -- 替换为目标user_id

方式二：CTE（更清晰易读）

with user_activity as (
    select user_id, count(*) as cnt
    from crud_logs
    group by user_id
),
user_ranks as (
    select user_id, rank() over (order by cnt desc) as user_rank
    from user_activity
)
select user_id, user_rank
from user_ranks
where user_id = 58; -- 替换为目标user_id

如果需要和示例输出格式完全一致，可使用字符串拼接：

select concat('user_id = ', user_id, ', rank = ', user_rank) as result
from (
    select 
        user_id, 
        rank() over (order by cnt desc) as user_rank
    from (
        select user_id, count(*) cnt 
        from crud_logs 
        group by user_id
    ) sq
) ranked
where user_id = 58;

2. 获取百分比排名 #

若需要以百分比形式返回排名（表示该用户超过的用户比例），可使用percent_rank()函数，计算后乘以100并保留小数：

select 
    user_id, 
    round(percent_rank() over (order by cnt desc) * 100, 2) as rank_percent
from (
    select user_id, count(*) cnt 
    from crud_logs 
    group by user_id
) sq
where user_id = 58;

注：percent_rank()返回0到1之间的数值，例如排名第2且总共有10个用户时，计算结果为(2-1)/(10-1)*100≈11.11%，表示该用户超过了11.11%的用户。

内容的提问来源于stack exchange，提问作者Aleksei Khatkevich