sql - 如何在SQL中获取NTile的平均值？

编辑：postgresql
我有一些员工的销售数据。。。桌子看起来像这样：

    SalesRep    # of sales/day       NTILE(2)
    --------   --------------      ----------
    Johnny            1                  1
    Johnny            1                  1
    Johnny            4                  1
    Johnny            5                  2
    Johnny            5                  2
    Johnny            5                  2
    Sara              2                  1
    Sara              2                  1
    Sara              2                  1
    Sara              3                  2
    Sara              4                  2
    Sara              5                  2
    ...              ...               ...

我想找出每个销售代表在其业绩最差的50%和业绩最好的50%每天的平均销售额
例如，我希望输出表如下所示：

    SalesRep    #ofSales Bottom50%    #ofSales Top50%
    --------     --------------         ----------
    Johnny            2                  5
    Sara              2                  4
    ...             ...                ...

到目前为止我有：

select
    salesrep,
    case when ntile = 1 then avg(numsales) end,
    case when ntile = 2 then avg(numsales) end,
    ...
    ...
    case when ntile = 10 then avg(numsales) end

    from (

select
    salesrep,
    numsales,
    NTILE(10) over (PARTITION BY salesrep order by numsales asc) as ntile
from XXX
    ) as YYY

group by salesrep, ntile

这给了我一个奇怪的错误，输出包含了一堆空值。。。见下表：

    SalesRep     #ofSales Bottom50%    #ofSales Top50%
    --------     --------------         ----------
    Johnny            NULL                  5
    Sara               2                   NULL
    ...               ...                   ...

numsales end), avg(case when ntile = 2 then numsales end), ... ... avg(case when ntile = 10 then numsales end)from ( select salesrep, numsales, NTILE(10) over (PARTITION BY salesrep order by numsales asc) as ntile from XXX) as YYYgroup by salesrep;
或者，重写上面的内容而不使用任何花哨的格式：

select
    salesrep,
    avg(case when ntile = 1 then numsales end),
    avg(case when ntile = 2 then numsales end),
    ...
    ...
    avg(case when ntile = 10 then numsales end)

from (
    select
        salesrep,
        numsales,
        NTILE(10) over (PARTITION BY salesrep order by numsales asc) as ntile
    from XXX
) as YYY

group by salesrep
;