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

Question

我想计算这个简单的xy_table中y的中位数，并且要按照子组进行计算：

  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)

输出：0.1，4.0

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

注意：我没有可用的median() 聚合函数。请不要提出带有特殊PARTITION、RANK 或 QUANTILE 语句的解决方案（如在类似但过于特定于供应商的SO问题中找到的）。我需要纯SQL（即与SQLite兼容且没有median()函数）

编辑：实际上，我正在寻找Medoid而不是Median。

- Juve

---

你的例子中第二个表格和第三个表格之间有什么关系？第一个 gid 改为了 0.1，而且 x 和 y 值都不是 0.0 组的中位数。 - Aprillion

抱歉，我打错字了。第三个表应该显示gid(组ID)组的中位数。在这种情况下，我假设[4,3]的中位数是4（较大的值）。 - Juve

[4,3] 的中位数通常为 3.5，正如您在维基百科链接的第一段所建议的那样，您是否明确希望获得更大的值？ - Aprillion

是的，我要更大的那一个。查询应仅选择现有记录，而不引入新记录。对于将其称为“中位数”，我感到抱歉。 :) 我目前正在尝试使用您的查询，目前看起来很不错。我刚刚删除了 left_row/right_row 以及 avg 部分，因为我不需要它们。 - Juve

Answer 1

我建议在你的编程语言中进行计算：

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

但如果你使用的是SQLite，你可以按照y排序每个组，并像这样选择中间的记录：http://sqlfiddle.com/#!5/d4c68/55/0。更新：对于偶数行，只有更大的“中位数”值才是重要的，因此不需要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

- Aprillion

---

谢谢，您的回答为我的解决方案提供了基础。为什么不直接使用 SELECT round(x) gid, 1+(count(*))/2 mid_row 来获取“中间”行？至少对我来说有效。 - Juve

是的，它是从左侧中间点计算中剩余下来的 :) - Aprillion

Answer 2

好的，这需要依赖于一个临时表：

create temporary table tmp (x float, y float);

insert into tmp
  select * from xy_table order by round(x), y

但是你可以为你感兴趣的一系列数据创建这个。另一种方法是确保xy_table具有这种排序顺序，而不仅仅是按x排序。原因是SQLite缺乏行编号功能。

然后：

select tmp4.x as gid, t.* from (
  select tmp1.x, 
         round((tmp2.y + coalesce(tmp3.y, tmp2.y)) / 2) as y -- <- for larger of the two, change to: (case when tmp2.y > coalesce(tmp3.y, 0) then tmp2.y else tmp3.y end)
  from (
    select round(x) as x, min(rowid) + (count(*) / 2) as id1, 
           (case when count(*) % 2 = 0 then min(rowid) + (count(*) / 2) - 1 
                 else 0 end) as id2
    from (  
      select *, rowid from tmp
    ) t
    group by round(x)
  ) tmp1
  join tmp tmp2 on tmp1.id1 = tmp2.rowid
  left join tmp tmp3 on tmp1.id2 = tmp3.rowid
) tmp4
join xy_table t on tmp4.x = round(t.x) and tmp4.y = t.y

如果你想把中位数看作是两个中间值中较大的一个，但这并不符合@Aprillion已经指出的定义，那么你只需在查询的第三行取两个y值中较大的一个，而不是它们的平均值。

- muhmud