sql - 查找前 n 个平均值

Question

我有一个看起来像这样的表:

UID DATE GROUP VALUE

数据类型有:

 VARCHAR DATE VARCHAR NUMERIC

例子:

abc1000 2012-09-01 1205 1000.0000
abc1000 2012-09-01 1210 1010.0000
abc1000 2012-09-02 1205 1100.0000
abc1000 2012-09-02 1210 1020.0000
def1010 2012-09-01 1205 2000.0000

我需要为每个唯一的“UID+GROUP”找到前 N(15) 个值 (VALUE) 并对它们进行平均，因此输出如下所示:

abc1000 1205 1050.0000
abc1000 1210 1015.0000
def1010 1205 2000.0000

Answer 1

select uid, [group], avg(value)
from
(
 select *,
        row_number() over (partition by uid, [group] order by value desc) rn 
        from yourtable
) v
where rn<=15
group by uid, [group]