PostgreSQL计算阈值查询

Question

我有一个带有表t1的PostgreSQL数据库，我想计算一个阈值。 例如，汽车1使用的燃料比所有汽车的75％都多，汽车2使用的燃料比所有汽车的50％都多，... 在数学上，我知道自己想要做什么，但不知道如何构建查询。

id | name | value | threshold
________________________

1  | car1 |  30   |  ...%
2  | car2 |  15   |  ..%
3  | car3 |   7   |
4  | car4 |   5   |

这里是一个 SQL Fiddle： http://sqlfiddle.com/#!15/1e914/1

UPDATE t1
SET threshold = 
    select count(value)
     from t1

其中 (value > [每行的值]) 并且后跟 *100/总数()

非常抱歉我的表述很差，我有些迷茫。我还尝试了一些聚合函数。

- fabvys

---

请看 100 * PERCENT_RANK() OVER (ORDER BY value) 或者 100 * CUME_DIST() OVER (ORDER BY value)。 - dnoeth

@dnoeth 这两种方法都不可行，因为解决方案是相对于最节油的汽车而言的，而不是针对整个人口进行计算的。 - Patrick

@Patrick：如果这些函数不起作用，那么你的答案也是不正确的。两者都只是你的RANK/COUNT的微小变化 :-) - dnoeth

@dnoeth 不是真的。我正在使用 (rank() - 1) / count()，它正好给出了这个问题中的结果。- 1 可能很小，但这就是为什么你不能使用内置函数的原因。 - Patrick

@Patrick：好的，根据预期结果，两者都不正确，但这只是基于四行的描述 :-) 这就是为什么我写他应该看看这些函数是否符合他的需求。PERCENT_RANK基于(RANK-1)/(COUNT-1)，即具有小于当前值的值的行数，而CUME_DIST则基于小于或等于。 - dnoeth

最后结果是不是和百分位一样呢？ntile(100) OVER(ORDER BY value) - fabvys

Answer 1

你可以使用窗口函数来优雅地解决这个问题：窗口函数。

UPDATE t1
SET threshold = sub.thr
FROM (
  SELECT id, 100. * (rank() OVER (ORDER BY value) - 1) / count(*) OVER () AS thr
  FROM t1) sub
WHERE t1.id = sub.id;

rank()函数返回一个有序集合中的排名（从1开始），在这种情况下是按列value排序，然后将其除以集合中的总行数。请注意，count(*) OVER ()计算分区中的总行数，但不像常规的count(*)那样聚合行。

- Patrick

---

感谢大家的帮助。在上面的sqlfiddle链接中，您可以看到rank()，percent_rank()和cume_dist()之间的区别。我想我正在寻找percent_rank。我改变了顺序，现在我可以说33%的汽车比car2使用更多的燃料。（顺便说一下，我的真实表格不是关于汽车的）。还有一件事要补充，窗口函数不能与UPDATE一起使用。 - fabvys

你可以将窗口函数包装在子查询中，然后它应该可以工作。请参见更新的答案。请注意，使用 percent_rank() 给出的结果与您在问题中陈述的不同。在那里，百分比是相对于最节油的汽车，而该函数针对所有行进行计算。(rank() - 1) / count(*) 正好给出了您要求的结果。 - Patrick

没错，(rank()...)是正确的函数，它适用于我的问题。再次感谢。 - fabvys

Answer 2

WITH    q AS
        (
        SELECT  *,
                (RANK() OVER (ORDER BY value) - 1) * 100. / COUNT(*) OVER () nt
        FROM mytable
        )
UPDATE  mytable
SET     threshold = nt
FROM    q
WHERE   mytable.id = q.id

- Quassnoi

Answer 3

你想计算使用燃料的百分比吗？

UPDATE t1 
SET threshold = 
    (select value * 100 / t2.sumValue
    from (select sum(value) sumValue from t1) t2
    )

或者，如果您想计算特定的阈值，请展示您期望的结果？

- Pham X. Bach