时间戳

首页 > 解决方案 > 如何使用 PostgreSQL 9.2 计算游戏中每个级别的百分位数

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

问题描述

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


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

这意味着每当用户玩关卡时，都会在表格中添加一行。这些行的级别数据显示用户播放了哪个级别，而 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 之前创建的表，其中 quartile = 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 或其他东西。

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

标签： sqlpostgresqlpostgresql-9.1postgresql-9.3postgresql-9.2

解决方案

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

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窗口函数中

  • 外部查询，我们将级别添加到selectandgroup by子句

推荐阅读