在 postgresql 中枚举 [英] Enumerate in postgresql

问题描述

有没有办法在 postgresql 中枚举查询行?

Is there any way to enumerate rows of query in postgresql?

我有这样的桌子:

Table players
id player_id game_id points

我想要这样的查询:

SELECT  game_id, <row_number in points group>, <array of ids> 
    FROM players WHERE game_id = %s 
    GROUP BY game_id, points; 

推荐答案

我不确定您要什么.点组中的行号"是一个简单的窗口函数 应用程序，但我不知道ID 数组"是什么意思.

I'm not sure what you're asking for. The "row number in points group" is a straight forward window function application but I don't know what "array of ids" means.

指定日期如下:

 id | player_id | game_id | points 
----+-----------+---------+--------
  1 |         1 |       1 |      0
  2 |         1 |       2 |      1
  3 |         1 |       3 |      5
  4 |         2 |       1 |      1
  5 |         2 |       2 |      0
  6 |         2 |       3 |      0
  7 |         3 |       1 |      2
  8 |         3 |       2 |      3
  9 |         3 |       3 |      1

您可以获得每场比赛的排名:

You can get the per-game rankings with this:

select game_id, player_id, points,
       rank() over (partition by game_id order by points desc)
from players

这会给你这样的输出:

 game_id | player_id | points | rank 
---------+-----------+--------+------
       1 |         3 |      2 |    1
       1 |         2 |      1 |    2
       1 |         1 |      0 |    3
       2 |         3 |      3 |    1
       2 |         1 |      1 |    2
       2 |         2 |      0 |    3
       3 |         1 |      5 |    1
       3 |         3 |      1 |    2
       3 |         2 |      0 |    3

这篇关于在 postgresql 中枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！