sql - 如何使用 PostgreSQL 9.2 计算游戏中每个级别的百分位数

Question

我有一张游戏日志表。像这样:

Level Shuffle_Count
  1        3
  2        1
  2        2
  2        1
  3        0
  3        4

这意味着每当用户玩一个关卡时，都会向表中添加一行。这些行具有显示用户播放的级别的级别数据和显示该级别期间 shuffle 发生的次数的 shuffle_count 数据。

我想通过计算每个级别的 shuffle_count 的中位数来知道每个级别发生了多少次 shuffle。在下面的代码中，我可以分别找到第 2 级的中位数。首先，我创建了一个临时表，它对 shuffle_counts 进行排序，并将它们分成 4 个带有 ntile 的偶数组。然后我在名为 quartile 的新列中选择值为 3 的 min shuffle_count。

with ranked_test as (
    SELECT shuffle_count, ntile(4) OVER (ORDER BY shuffle_count) AS quartile FROM ch.public.game_log WHERE level = 2
)
SELECT min(shuffle_count) FROM ranked_test
WHERE quartile = 3
GROUP BY quartile;

这是在选择 min shuffle_count 之前创建的表，其中四分位数 = 3(大约是中位数):

Shuffle_Count quartile
     0           1
     0           1
     2           2
     3           2
     4           3
     8           3
     12          4
     19          4

到现在为止还挺好。但问题是我有 1000 多个级别，我无法为每个级别手动执行此操作。我需要从 1 到 1000 的每个级别的 shuffle_count 的中值。我知道这可以用 PostgreSQL 9.4 中的一行来完成，但不幸的是我现在没有那个选项。

我无法通过简单的 Group By 实现这一点。我想我需要更复杂的查询，包括 FOR 或其他东西。

你有什么想法吗，伙计们？提前致谢。

Answer 1

我认为这应该适用于您的用例:

with ranked_test as (
    select 
        level,
        shuffle_count, 
        ntile(4) over(partition by level order by shuffle_count) quartile 
    from ch.public.game_log
)
select level, quartile , min(shuffle_count) 
from ranked_test
where quartile = 3
group by level, quartile;

这基本上是您的工作查询的扩展版本:

在 CTE 中，我们移除了 level 上的过滤器在子查询中，并将其添加到 partition by取而代之的是窗函数

在外部查询中，我们将级别添加到 select和 group by条款