根据MSDN, Median在Transact-SQL中不能作为聚合函数使用。但是,我想知道是否可以创建此功能(使用create Aggregate函数、用户定义函数或其他方法)。
最好的方法(如果可能的话)是什么——允许在聚合查询中计算中值(假设是数值数据类型)?
根据MSDN, Median在Transact-SQL中不能作为聚合函数使用。但是,我想知道是否可以创建此功能(使用create Aggregate函数、用户定义函数或其他方法)。
最好的方法(如果可能的话)是什么——允许在聚合查询中计算中值(假设是数值数据类型)?
当前回答
在SQL Server 2012中,您应该使用PERCENTILE_CONT:
SELECT SalesOrderID, OrderQty,
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY OrderQty)
OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
参见:http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/
其他回答
对于大规模数据集,您可以尝试以下GIST:
https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2
它通过聚合您在集合中找到的不同值(例如年龄或出生年份等)来工作,并使用SQL窗口函数来定位您在查询中指定的任何百分比位置。
在UDF中,写:
Select Top 1 medianSortColumn from Table T
Where (Select Count(*) from Table
Where MedianSortColumn <
(Select Count(*) From Table) / 2)
Order By medianSortColumn
简单、快速、准确
SELECT x.Amount
FROM (SELECT amount,
Count(1) OVER (partition BY 'A') AS TotalRows,
Row_number() OVER (ORDER BY Amount ASC) AS AmountOrder
FROM facttransaction ft) x
WHERE x.AmountOrder = Round(x.TotalRows / 2.0, 0)
中找到
这是查找属性中值的最简单方法。
Select round(S.salary,4) median from employee S
where (select count(salary) from station
where salary < S.salary ) = (select count(salary) from station
where salary > S.salary)
在我的解决方案表中是一个只有分数列的学生表,我正在计算分数的中位数,这个解决方案是基于SQL server 2019的
with total_c as ( --Total_c CTE counts total number of rows in a table
select count(*) as n from student
),
even as ( --Even CTE extract two middle rows if the number of rows are even
select marks from student
order by marks
offset (select n from total_c)/2 -1 rows
fetch next 2 rows only
),
odd as ( --Odd CTE extract middle row if the number of rows are odd
select marks from student
order by marks
offset (select n + 1 from total_c)/2 -1 rows
fetch next 1 rows only
)
--Case statement helps to select odd or even CTE based on number of rows
select
case when n%2 = 0 then (select avg(cast(marks as float)) from even)
else (select marks from odd)
end as med_marks
from total_c