python - 在 SQLAlchemy 中使用 func rank 对表中的行进行排名

Question

我有一个这样定义的表:

   Column    |  Type   | Modifiers | Storage | Stats target | Description
-------------+---------+-----------+---------+--------------+-------------
 id          | uuid    | not null  | plain   |              |
 user_id     | uuid    |           | plain   |              |
 area_id     | integer |           | plain   |              |
 vote_amount | integer |           | plain   |              |

我希望在查询该数据库时能够生成排名“列”。此排名列将按 vote_amount 列排序。我试图创建一个查询来执行此操作，它看起来像这样:

subq_rank = db.session.query(user_stories).add_columns(db.func.rank.over(partition_by=user_stories.user_id, order_by=user_stories.vote_amount).label('rank')).subquery('slr')
data = db.session.query(user_stories).select_entity_from(subq_rank).filter(user_stories.area_id == id).group_by(-subq_rank.c.rank).limit(50).all()

希望我的尝试能让您了解我要实现的目标。

谢谢。

Answer 1

好吧，如果您需要在每个查询中更好地使用这些列，我会在数据库中进行。我将创建一个包含列排名的 View ，并在查询中调用此 View 以直接显示代码中的数据:

CREATE VIEW [ranking_user_stories] AS
SELECT TOP 50 * FROM 
(SELECT *, rank() over (partition by user_stories.user_id order by user_stories.vote_amount ASC) AS ranking
FROM user_stories
WHERE user_stories.area_id = id) uS
ORDER BY vote_amount ASC

这与您的代码的逻辑相同，但在 SQL 中，如果您使用的是 MySQL，只需将 TOP 50 更改为 LIMIT 50(并放在查询末尾)。我认为按排名放置最后一组没有意义，但如果您需要:

CREATE VIEW [ranking_user_stories] AS
    SELECT TOP 50 MAX(id) AS id, user_id, area_id, MAX(vote_amount) AS vote_amount, ranking FROM 
    (SELECT *, rank() over (partition by user_stories.user_id order by user_stories.vote_amount ASC) AS ranking
    FROM user_stories
    WHERE user_stories.area_id = id) uS
    ORDER BY MAX(vote_amount) ASC
    GROUP BY user_id, area_id, ranking