时间戳

首页 > 解决方案 > 如何在 SQL Percentile Window 函数上添加条件？

mysql - 如何在 SQL Percentile Window 函数上添加条件？

问题描述

我想做一个特殊的查询来评估团队成员。每个成员都会有一个分数，分数超过该团队分数的 80% 的人将获得奖金。但是，我想添加一个条件，以便仅根据这些分数 > 0 计算第 80 个百分位分数。

例如，如果 A 队有

[0, 0, 0.6, 0.6, 0.8, 0.8]

然后百分位数将使用

[0.6, 0.6, 0.8, 0.8]

结果将是 0.8。否则，如果团队只有 score = 0，则 team_80th_score 将为 0。

该表如下所示，其中 team_80th_score* 是期望的结果。

team| member | score | team_80th_score*
----+-----------+------+--------
A   | Alex   |  0    |     0.8 
A   | Abby   |  0    |     0.8   
A   | Hunt   |  0.6  |     0.8  
A   | Tyler  |  0.6  |     0.8 
A   | Jack   |  0.8  |     0.8 
A   | Mile   |  0.8  |     0.8 
B   | John   |  0    |     0 
B   | Amy    |  0    |     0 
B   | Alice  |  0    |     0

我使用 Hive SQL，并了解在此基础窗口函数上构建将是可行的方法

select team, member, score, 
percentile_approx(score, 0.8) over (partition by team) as team_80th_score
from table;

但我不知道如何包含仅考虑分数 > 0 的条件（对于团队 A 的情况），如果 sum(score) group by team 为 0，则 0 以 team_80th_score 结尾（对于团队 B 的情况） .

在这种情况下，你会建议我做什么？

标签： mysqlsqlhivewindow-functionspartition

解决方案

嗯。. . 一种方法是在partition by. 请注意，这会在以下情况下返回无意义的值score = 0：

select team, member, score, 
       percentile_approx(score, 0.8) over (partition by team, (case when score > 0 then 1 else 0 end) as team_80th_score
from table;

要解决这个问题，请使用外部case表达式：

select team, member, score, 
       (case when score > 0
             then percentile_approx(score, 0.8) over (partition by team, (case when score > 0 then 1 else 0 end))
        end) as team_80th_score
from table;

推荐阅读