用于计算子组中的排名和中位数的 SQL 排名查询

我想计算Median http://en.wikipedia.org/wiki/Median of y in 子组这个简单的xy_table:

  x | y --groups--> gid |   x | y --medians-->  gid |   x | y
-------             -------------               -------------
0.1 | 4             0.0 | 0.1 | 4               0.0 | 0.1 | 4
0.2 | 3             0.0 | 0.2 | 3                   |     |
0.7 | 5             1.0 | 0.7 | 5               1.0 | 0.7 | 5
1.5 | 1             2.0 | 1.5 | 1                   |     |
1.9 | 6             2.0 | 1.9 | 6                   |     |
2.1 | 5             2.0 | 2.1 | 5               2.0 | 2.1 | 5
2.7 | 1             3.0 | 2.7 | 1               3.0 | 2.7 | 1

在此示例中，每个x是唯一的，并且该表已按以下顺序排序x。 我现在想要GROUP BY round(x)并得到包含中位数的元组y在每个组中。

我已经可以用这个计算整个表的中位数排名查询:

SELECT a.x, a.y FROM xy_table a,xy_table b
WHERE a.y >= b.y
GROUP BY a.x, a.y
HAVING count(*) = (SELECT round((count(*)+1)/2) FROM xy_table)

Output: 0.1, 4.0

但我还没有成功编写一个查询来计算子组的中位数。

注意力：我没有median()可用聚合功能。也请不要提出特殊的解决方案PARTITION, RANK, or QUANTILE声明（如类似但过于特定于供应商的所以问题 https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server）。我需要纯 SQL（即与 SQLite 兼容，无需median()功能）

Edit:我实际上是在寻找Medoid http://en.wikipedia.org/wiki/Medoid而不是Median http://en.wikipedia.org/wiki/Median.

我建议用您的编程语言进行计算：

for each group:
  for each record_in_group:
    append y to array
  median of array

但如果你坚持使用 SQLite，你可以按以下顺序对每个组进行排序y然后像这样选择中间的记录http://sqlfiddle.com/#!5/d4c68/55/0 http://sqlfiddle.com/#!5/d4c68/55/0:

UPDATE：即使没有，只有更大的“中值”才重要。行，所以没有avg()需要：

select groups.gid,
  ids.y median
from (
  -- get middle row number in each group (bigger number if even nr. of rows)
  -- note the integer divisions and modulo operator
  select round(x) gid,
    count(*) / 2 + 1 mid_row_right
  from xy_table
  group by round(x)
) groups
join (
  -- for each record get equivalent of
  -- row_number() over(partition by gid order by y)
  select round(a.x) gid,
    a.x,
    a.y,
    count(*) rownr_by_y
  from xy_table a
  left join xy_table b
    on round(a.x) = round (b.x)
    and a.y >= b.y
  group by a.x
) ids on ids.gid = groups.gid
where ids.rownr_by_y = groups.mid_row_right