sql - 配置单元查询 : Selecting column over a partition based on a median of a different column

Question

我无法完成查询建模，因此需要帮助。

我的数据是:

id   name   school   height
1    A      S1       10
2    B      S1       12
3    C      S1       14
4    D      S2       15
5    E      S2       16
6    F      S2       17

我想选择每个学校的姓名和中位数高度的姓名。

预期输出:

id   name  school  myval
1    A    S1    B
2    B    S1    B
3    C    S1    B
4    D    S2    E
5    E    S2    E
6    F    S2    E

在这里，B 的高度是 S1 学校的中位数，E 是 S2 的中位数。

我知道我们可以使用百分位数获得中位数。但我无法弄清楚如何选择每个分区的值。

Answer 1

下面的查询将起作用:-

select 
  temp1.id,
  temp1.name,
  temp1.school,
  temp2.name 
from 
  (select 
     id,
     name,
     school,
     height 
  from 
     TABLE_NAME
  ) temp1
  left Join        
   (select 
      school,
      name 
    from 
      (select 
        id,
        name,
        school,
        height,
        SUM(height) OVER 
           (PARTITION BY school)/COUNT(height) OVER 
               (PARTITION BY school) as avg 
      from 
        TABLE_NAME) AVERG 
   where height=avg ) temp2 on temp1.school=temp2.school ;