mysql - SQL:值高于值总体的百分比

Question

我希望计算高于每组值总体百分比的值。

假设我有:

CREATE TABLE project
(
    id int,
    event int, 
    val int
);

INSERT INTO project(id,event,val) 
VALUES
    (1, 11, 43), 
    (1, 12, 19), 
    (1, 13, 19),
    (1, 14, 53),
    (1, 15, 45),
    (1, 16, 35),
    (2, 21, 22),
    (2, 22, 30),
    (2, 23, 25),
    (2, 24, 28);

我现在想计算每个 id 的 val 值是多少，例如高于该 id 值的 5% 或 30%。

例如，对于 id=1，我们有以下值:43, 19, 19, 53, 45, 35。因此列联表如下所示:

19 35 43 45 53 
 2  1  1  1  1 

并且 val=20(高于 19)将被选择为高于行的 5%(实际上是 6 行中的 2 行)。

id 2 的列联表为:

22 25 28 30
 1  1  1  1

我的预期结果是:

id  val_5p_coverage  val_50p_coverage
1   20               36
2   23               26

val_5p_coverage 是值 val 需要至少高于 id 中 val 的 5%。

val_50p_coverage 是值 val 需要至少高于 id 中 val 的 50%。

如何使用 SQL 计算此值？

Answer 1

我设法在HiveQL(针对 Hadoop)中做到了这一点，如下所示:

create table prep as
    select *,
            CUME_DIST() OVER(PARTITION BY id ORDER BY val ASC) as proportion_val_equal_or_lower
    from project

SELECT id,
       MIN(IF(proportion_val_equal_or_lower>=0.05, val, NULL)) AS val_5p_coverage,
       MIN(IF(proportion_val_equal_or_lower>=0.50, val, NULL)) AS val_50p_coverage
FROM prep
GROUP BY id

虽然这本身不是 MySQL 也不是 SQL，但在 MySQL 或 SQL 中可能会有所帮助。