主页 / dba / 问题 / 106253
Accepted
DaveB
Asked: 2015-07-08 14:07:11 +0800 CST

Postgres - 窗口函数排名和计数

  • 772

我有一些表格可以跟踪运动员在赛道上的时间：

竞赛、竞赛用户和竞赛用户会话

我正在构建一个查询，该查询为每个提供的“competition_user”提取当前排名和竞争对手总数，我可以得到排名，但我的计数（totalUsers）并没有计算比赛中的所有竞争对手，它似乎只是向上计数给提供的用户（例如，给出与排名相同的值）

SELECT compUserId, rank, totalUsers 
    FROM (
        SELECT cu.competition_user_id as compUserId, cu.user_id as userId,  
    count(*) OVER w as totalUsers, rank() OVER w as rank 
        FROM competition_users cu 
        LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
        LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
        WHERE cu.left_competition = false 
        AND cu.competition_id in (:compIds)
        WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) 
    ) as sub 
WHERE compUserId in (:compUserIds)

我的理解是，默认框架是整个窗口，而这似乎是从框架开始计数到当前行？

postgresql window-functions

1 个回答

  1. Best Answer

    您的问题似乎是您WINDOW为w您的COUNT(*)和您的rank().

    当您使用WINDOW包含ORDER BY子句的 a ，然后应用某些聚合（例如SUMor COUNT）时，它会在 ordering 中连续应用聚合，这就是为什么您的COUNT和rank()是相同的。

    如果您修改您的查询有多个窗口

    SELECT compUserId, rank, totalUsers 
FROM (
    SELECT cu.competition_user_id as compUserId, cu.user_id as userId,  
    count(*) OVER (PARTITION BY cu.competition_id) as totalUsers, 
    rank() OVER (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) as rank 
    FROM competition_users cu 
    LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
    LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
    WHERE cu.left_competition = false 
    AND cu.competition_id in (:compIds)
) as sub 
WHERE compUserId in (:compUserIds);

    这样您就只将 应用PARTITION BY到您的COUNT(*)窗口，并为您的 提供PARTITION BY和ORDER BY子句rank()，我相信您会得到您想要的结果。

    参考这个SQL FIDDLE作为参考，其中我有一个通用id字段，一个com_num表示比赛 id 的com_time字段，以及一个表示竞争对手时间的字段。

    • 15

相关问题