我有一个表示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的排名（数字或%），根据给定时间段内每个用户的行数进行排名。为了简单起见，让我们说从永远。
示例如下：
| 对象类型|对象标识|动作|操作ts|用户id| user_id |
| --|--|--|--|--| ------------ |
| 企业级|五十六|更新了|2023-07-21 12：51：43.904511 +00：00|三十四| 34 |
| 企业级|五十六|更新了|2023-07-21 12：52：46.472828 +00：00|三十四| 34 |
| 企业级|五十六|删除|2023-07-21 13：00：26.499430 +00：00|三十四| 34 |
| 企业级|五十六|更新了|2023-07-21 12：51：43.904511 +00：00|三十四| 34 |
| 企业级|五十六|更新了|2023-07-21 12：52：46.472828 +00：00|三十四| 34 |
| 企业级|五十六|删除|2023-07-21 13：00：26.499430 +00：00|三十四| 34 |
| 企业级|五十六|更新了|2023-07-21 12：51：43.904511 +00：00|三十四| 34 |
| 企业级|五十六|更新了|2023-07-21 12：52：46.472828 +00：00|三十四| 34 |
| 企业级|五十六|删除|2023-07-21 13：00：26.499430 +00：00|五十九| 59 |
| 企业级|五十六|更新了|2023-07-21 12：51：43.904511 +00：00|五十八| 58 |
| 企业级|五十六|更新了|2023-07-21 12：52：46.472828 +00：00|五十九| 59 |
| 企业级|五十六|删除|2023-07-21 13：00：26.499430 +00：00|五十八| 58 |
在这里，user_id = 34将排在第一位，因为该用户具有最多的条目。
我已经设法编写了以下查询：
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 = 58的预期输出为：
user_id = 58, rank = 2型
2条答案
按热度按时间edqdpe6u1#
你写的查询很接近。要获得特定用户的排名，只需在外部SQL查询中添加WHERE子句，以筛选所需的user_id。
以下是如何修改查询：
字符串
在这个查询中，最里面的子查询按user_id对crud_logs表进行分组，并计算每个用户的条目数。中间的子查询按用户数量降序排列这些用户。最后，外部查询过滤这些结果以返回具有
user_id = 58的用户的排名。xxe27gdn2#
基本查询
可以在同一查询级别中将窗口函数
rank()Package 在聚合count(*)周围：字符串
微妙问题一：是否忽略
null值？您在
user_id和ON DELETE SET NULL上有一个FK约束-这表明可以删除用户并保留其日志条目，同时将user_id重置为null。“NULL”用户将像任何其他用户一样进行排名，表示已删除用户的集合。我猜你会想把它排除在外。于是：型
微妙问题二：固有时间周期
这些时间段（为了简单起见，我们忽略了它们）对于
timestamptz类型来说可能很棘手。通常情况下，您需要给定时区中的天/周/月的统计信息。通过正确地考虑时区因素，避免鬼鬼祟祟的角落案例错误。例如2023年7月在澳大利亚：
型
fiddle
请参阅：
如果表很大，
(operation_ts)上的索引应该会有所帮助。