sql-server - 在 T-SQL 中将percentile_cont 与 "group by"语句结合使用

Question

我想使用 percentile_cont 函数获取 T-SQL 中的中值。但是，我还需要获取平均值。我想做如下的事情:

SELECT  CustomerID ,
    AVG(Expenditure) AS MeanSpend , percentile_cont
    ( .5) WITHIN GROUP(ORDER BY Expenditure) OVER( ) AS MedianSpend
FROM    Customers
GROUP BY CustomerID

这能实现吗？我知道我可以使用 OVER 子句对 percentile_cont 结果进行分组...

但是我却陷入了使用两个查询的困境，不是吗？

Answer 1

刚刚弄清楚...必须删除 group by 并给两个聚合函数一个 over 语句。

SELECT CustomerID,
    AVG(Expenditure) OVER(PARTITION BY CustomerID) AS MeanSpend,
    percentile_cont(.5) WITHIN GROUP(ORDER BY Expenditure) OVER(PARTITION BY CustomerID) AS MedianSpend
FROM Customers