CREATE TABLE x (i serial primary key,value integer not null);

我想计算MEDIAN的价值(而不是AVG).中位数是将集合分成两个包含相同数量元素的子集的值.如果元素数量是偶数,则中位数是最低分数中最大值的平均值,也是最大分数的最小值. (详见维基百科)

这是我如何设法计算MEDIAN,但我想有一个更好的方法：

SELECT AVG(values_around_median) AS median
  FROM (
    SELECT
       DISTINCT(CASE WHEN FIRST_VALUE(above) OVER w2 THEN MIN(value) OVER w3 ELSE MAX(value) OVER w2 END)
        AS values_around_median
      FROM (
        SELECT LAST_VALUE(value) OVER w AS value,SUM(COUNT(*)) OVER w > (SELECT count(*)/2 FROM x) AS above
          FROM x
          GROUP BY value
          WINDOW w AS (ORDER BY value)
          ORDER BY value
        ) AS find_if_values_are_above_or_below_median
      WINDOW w2 AS (PARTITION BY above ORDER BY value DESC),w3 AS (PARTITION BY above ORDER BY value ASC)
    ) AS find_values_around_median

有任何想法吗？