如何使用逆分布函数模拟MEDIAN（）聚合函数

一些数据库足够强大，可以实现MEDIAN()聚合函数。 请记住， MEDIAN()与MEAN()或AVG() （平均）略有不同（并且通常更有用）。

虽然平均值是按SUM(exp) / COUNT(exp) ，但是MEDIAN()告诉您样本中所有值的50％高于MEDIAN()而集合中的其他50％则小于MEDIAN() MEDIAN() 。

因此，换句话说，如果您执行以下查询：

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 3   FROM DUAL
)
SELECT
  avg(value),
  median(value)
FROM
  t;

…那么平均值和中位数是相同的：

avg   median
2     2

但是，如果您这样严重扭曲数据：

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 100 FROM DUAL
)
SELECT
  avg(value),
  median(value)
FROM
  t;

然后，您的平均值也将出现偏差，而中位数仍将指示样本中大多数值的位置

avg      median
34.333   2

上面的示例在统计上当然是微不足道的，但是如果您拥有更多数据，则可以轻松地看到这种影响是戏剧性的且相关的：

图像许可证CC-BY-SA 3.0。 由Cmglee上传到Wikipedia

偏斜效应在统计中非常重要，为了对任何事物做出有趣的主张，使用百分位通常比使用平均值更有用。 以一个国家的平均收入与中位数收入为例。 尽管美国（以及许多其他国家/地区） 的平均收入一直在稳定增长， 但在过去十年中 ， 中位数收入却有所下降 。 这是由于财富越来越严重地向超级富翁倾斜。

这个博客不是关于政治的，而是关于Java和SQL的，因此让我们回到计算实际情况。

在SQL中使用分位数

正如我们之前所看到的， MEDIAN()将样本分为两个大小相等的组，并在这两个组之间“取值”。 此特定值也称为第50个百分位数，因为样本中所有值的50％都小于MEDIAN() 。 因此，我们可以建立：

• MIN(exp) ：0百分位数
• MEDIAN(exp) ：第50个百分点
• MAX(exp) ：100％

以上所有都是百分位数的特殊情况，尽管所有SQL数据库（和SQL标准）都支持MIN()和MAX() ，但SQL标准不支持MEDIAN() ，而以下jOOQ数据库仅支持MEDIAN() ：

• BR
• 数据库
• 甲骨文
• Sybase SQL Anywhere

在SQL标准中，还有另一种计算MEDIAN() ，以及通常计算任何百分位数的方法， 因为PostgreSQL 9.4在PostgreSQL中也使用…

有序集合聚合函数

有趣的是，除了窗口函数外 ，您还可以为某些聚合函数指定ORDER BY子句，这些聚合函数根据有序集聚合数据。

SQL标准percentile_cont函数就是这样的一个函数，该函数将百分位数作为参数，然后接受一个附加的WITHIN GROUP子句，该子句将ORDER BY子句作为参数。 这些特定的有序集函数也称为逆分布函数 ，因为我们要查找样本中所有值的分布中特定百分位数的位置（ 如果您不对数学感到恐惧，请查看Wikipedia文章 ）

因此，在PostgreSQL 9.4+中，可以像下面这样模拟MEDIAN()函数：

WITH t(value) AS (
  SELECT 1   UNION ALL
  SELECT 2   UNION ALL
  SELECT 100
)
SELECT
  avg(value),
  percentile_cont(0.5) WITHIN GROUP (ORDER BY value)
FROM
  t;

这个有趣的语法是标准化的，并且可能由Oracle的LISTAGG（）所知，该语法允许将值聚合为串联的字符串：

WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 100 FROM DUAL
)
SELECT
  listagg(value, ', ') WITHIN GROUP (ORDER BY value)
FROM
  t;

该查询简单地产生：

listagg
---------
1, 2, 100

附带说明一下： LISTAGG()当然是完全没用的，因为它返回VARCHAR2 ，在Oracle中它再次具有最大长度4000。 无用…

开箱即用的仿真

与往常一样，jOOQ将开箱即用地模拟这些事情。 您既可以使用DSL.median()函数，也可以使用即将发布的jOOQ 3.6和新的DSL.percentileCont()函数来产生相同的值：

DSL.using(configuration)
   .select(
       median(T.VALUE),
       percentileCont(0.5).withinGroupOrderBy(T.VALUE)
   )
   .from(T)
   .fetch();

翻译自: https://www.javacodegeeks.com/2015/01/how-to-emulate-the-median-aggregate-function-using-inverse-distribution-functions.html