2019年更新:自从我写下这个答案以来的10年中,已经发现了更多可能产生更好结果的解决方案。此外,此后的SQL Server版本(尤其是SQL 2012)引入了新的T-SQL功能,可用于计算中位数。SQL Server版本还改进了其查询优化器,这可能会影响各种中位数解决方案的性能。网络,我最初的2009年帖子仍然可以,但是对于现代SQL Server应用程序可能会有更好的解决方案。看看2012年的这篇文章,这是一个很好的资源: https : //sqlperformance.com/2012/08/t-sql-queries/median
本文发现以下模式比所有其他替代方法快得多,至少在他们测试的简单模式上要快得多。该解决方案比最慢的(PERCENTILE_CONT
)解决方案快373倍(!!!)。请注意,此技巧需要两个单独的查询,这些查询可能并非在所有情况下都可行。它还需要SQL 2012或更高版本。
DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.EvenRows);
SELECT AVG(1.0 * val)
FROM (
SELECT val FROM dbo.EvenRows
ORDER BY val
OFFSET (@c - 1) / 2 ROWS
FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;
当然,仅因为2012年对一种架构进行的一项测试取得了不错的成绩,您的工作量可能会有所不同,尤其是在使用SQL Server 2014或更高版本时。如果性能对于中位数计算很重要,我强烈建议尝试并性能测试该文章中建议的几个选项,以确保找到最适合您的模式的选项。
我还要特别小心地使用在此问题PERCENTILE_CONT
的其他答案之一中推荐的(SQL Server 2012中的新增功能),因为上面链接的文章发现此内置功能比最快的解决方案慢373倍。此差异有可能在7年后得到改善,但是我个人不会在大桌子上使用此功能,直到我验证了其性能与其他解决方案的对比。
以下是2009年的原始帖子:
有很多方法可以做到这一点,而性能却大不相同。这是一个经过特别优化的解决方案,其中包括Median,ROW_NUMBER和performance。当涉及执行期间生成的实际I / O时,这是一个特别理想的解决方案-它看起来比其他解决方案更昂贵,但实际上要快得多。
该页面还包含其他解决方案和性能测试详细信息的讨论。请注意,如果有多行中位数列的值相同,则使用唯一列作为歧义消除器。
与所有数据库性能方案一样,始终尝试使用真实硬件上的真实数据测试解决方案–您永远都不知道何时更改SQL Server优化器或环境的特殊性会使正常快速的解决方案变慢。
SELECT
CustomerId,
AVG(TotalDue)
FROM
(
SELECT
CustomerId,
TotalDue,
-- SalesOrderId in the ORDER BY is a disambiguator to break ties
ROW_NUMBER() OVER (
PARTITION BY CustomerId
ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
ROW_NUMBER() OVER (
PARTITION BY CustomerId
ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
FROM Sales.SalesOrderHeader SOH
) x
WHERE
RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句