将percentile_cont与"group by"一起使用T-SQL中的陈述 [英] Use percentile_cont with a "group by" statment in T-SQL

问题描述

我想使用percentile_cont函数在T-SQL中获取中间值.但是，我还需要获取平均值.我想做以下事情:

I'd like to use the percentile_cont function to get median values in T-SQL. However, I also need to get mean values as well. I'd like to do something like the following:

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结果进行分组...

Can this be accomplished? I know I can use the OVER clause to group the percentile_cont results...

但是然后我被困在两个查询中，不是吗?

but then I'm stuck using two queries, am I not?

推荐答案

只需弄清楚...就必须删除group by，并给两个聚合函数一个over语句.

Just figured it out... gotta drop the group by and give both aggregation functions a over statement.

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

这篇关于将percentile_cont与"group by"一起使用T-SQL中的陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！