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

Question

编辑: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

这给了我一个奇怪的错误，输出包括一堆 NULL...见下表:

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

Answer 1

所需输出中的一行代表单个 SalesRep – 因此，您必须按 SalesRep 仅 分组。然后 case 表达式将作为参数进入 inside avg 调用，如下所示:

select 
    salesrep,
    <b>avg(</b>case when ntile = 1 then <s>avg(</s>numsales<s>)</s> end<b>)</b>,
    <b>avg(</b>case when ntile = 2 then <s>avg(</s>numsales<s>)</s> end<b>)</b>,
    ...
    ...
    <b>avg(</b>case when ntile = 10 then <s>avg(</s>numsales<s>)</s> end<b>)</b>

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

group by salesrep<s>, ntile</s>
;

或者，重写上面的内容而不使用那些花哨的格式:

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
;