在Server中查找中位数
在Server中查找中位数
提问于 2017-11-16 23:39:45
我希望从Management中的unitRate视图中获得dbo.ReplaceCost_DirectCost_Details的中值。我已经得到了Min，Max和it.But的avg不知道中位数。我尝试了下面的代码，但没有得到中间的.Thanks在advacen的帮助。
select
JobName as JobName
,Client as Client
,AssetClass as AssetClass
,AssetType as AssetType
,AssetSubType as AssetSubType
,Component as Component
,ComponentType as ComponentType
,ComponentSubType as ComponentSubType
,UnitRate AS UnitRate
,Max(UnitRate) over (partition by JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType) as [MaxFinalUnitRate]
,Min(UnitRate) over (partition by JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType) as [MinFinalUnitRate]
,AVG(UnitRate) over (partition by JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType) as [MeanFinalUnitRate]
,AVG (UnitRate) over (partition by JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType)as Median
from
(
Select top (10)
JobName as JobName
,Client as Client
,AssetClass as AssetClass
,AssetType as AssetType
,AssetSubType as AssetSubType
,Component as Component
,ComponentType as ComponentType
,ComponentSubType as ComponentSubType
,UnitRate AS UnitRate
,ROW_NUMBER () over (partition by JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType order by UnitRate) as [RowNum]
,COUNT(*) OVER (PARTITION BY JobName,Client,AssetClass,AssetType,AssetSubType,Component,ComponentType,ComponentSubType ) AS RowCnt
from [dbo].[ReplaceCost_DirectCost_Details] rdd
where client = 'APV_Ballina_Shire_Council_Old' and UnitRate is not Null and UnitRate <> 0
) x
WHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2) Stack Overflow用户
发布于 2017-11-17 03:00:39
(很抱歉使用了第二个答案，但如果将其添加到前面的答案中，则会丢失)
我真的不确定您的查询的预期输出是什么。但是我注意到，您使用的是TOP(10)，为此，必须有的顺序，否则前10行的结果是不确定的。
虽然下面的内容可能会产生比您需要的更多的行，但它可能会帮助您找到解决方案。
WITH Basis as (
SELECT
JobName
, Client
, AssetClass
, AssetType
, AssetSubType
, Component
, ComponentType
, ComponentSubType
, UnitRate
, ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType
ORDER BY UnitRate)
AS [rownum]
FROM [dbo].[ReplaceCost_DirectCost_Details] rdd
WHERE client = 'APV_Ballina_Shire_Council_Old'
AND UnitRate IS NOT NULL
AND UnitRate <> 0
)
, Top10s as (
SELECT
JobName
, Client
, AssetClass
, AssetType
, AssetSubType
, Component
, ComponentType
, ComponentSubType
, UnitRate
, rownum
, COUNT(*) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType)
AS rowcnt
FROM Basis
WHERE rownum <= 10
)
, Medians as (
SELECT
JobName
, Client
, AssetClass
, AssetType
, AssetSubType
, Component
, ComponentType
, ComponentSubType
, AVG(UnitRate) AS Median
FROM Top10s
WHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY
JobName
, Client
, AssetClass
, AssetType
, AssetSubType
, Component
, ComponentType
, ComponentSubType
, AVG(UnitRate)
)
SELECT
JobName
, Client
, AssetClass
, AssetType
, AssetSubType
, Component
, ComponentType
, ComponentSubType
, UnitRate
, rownum
, rowcnt
, MAX(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [maxfinalunitrate]
, MIN(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [minfinalunitrate]
, AVG(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [meanfinalunitrate]
, Medians.Median
FROM Top10s t
JOIN Medians m ON t.JobName = m.JobName
AND t.Client = m.Client
AND t.AssetClass = m.AssetClass
AND t.AssetType = m.AssetType
AND t.AssetSubType = m.AssetSubType
AND t.Component = m.Component
AND t.ComponentType = m.ComponentType
AND t.ComponentSubType = m.ComponentSubType
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47340987
复制相关文章
相似问题
腾讯云开发者
