sql - 如何在单个选择查询中获取平均值、中位数、模式和范围？

Question

我正在尝试获取表中一组值的平均值、中位数、众数和范围。我能够得到平均值，但中位数、范围和众数我得到了错误的值。

下面是我为上述概念尝试过的代码。

Select 
    CDS.[Commodity_SourceSeriesID_LongDesc] AS 'Description',
    TD.TimeDimension_Year AS 'Year',
    AVG(DV.DataValues_AttributeValue) AS 'Average/Mean',
    MAX(dv.DataValues_AttributeValue) AS 'Maximum value for the Year',
    MIN(dv.DataValues_AttributeValue) AS 'Minimum value for the Year',
    ((MAX(dv.DataValues_AttributeValue) + MIN(dv.DataValues_AttributeValue)) / 2) AS 'Median',
    --,(SELECT TOP 1 with ties DataValues_AttributeValue
    --FROM   [CoSD].[DataValues] 
    --WHERE  DataValues_AttributeValue IS Not NULL AND DataValues_ERSCommodity_ID = 157 and DataValues_DataRowLifecyclePhaseID = 1
    --GROUP  BY DataValues_AttributeValue
    --ORDER  BY COUNT(*) DESC) AS Mode
    (MAX(dv.DataValues_AttributeValue) - MIN(dv.DataValues_AttributeValue))  AS 'Range'
FROM 
    [CoSD].[DataValues] DV 
INNER JOIN 
    [CoSD].[CommodityDataSeries] CDS ON CDS.Commodity_ID = DV.DataValues_Commodity_ID
INNER JOIN 
    [CoSD].[TimeDimension_LU] TD ON TD.TimeDimension_ID = DV.DataValues_TimeDimension_ID
WHERE 
    DataValues_Commodity_ID = 157  
    AND DataValues_DataRowLifecyclePhaseID IN (1, 4)
GROUP BY 
    DV.DataValues_TimeDimension_ID,
    CDS.Commodity_SourceSeriesID_LongDesc,
    TD.TimeDimension_Year

有办法实现吗？

谢谢

Answer 1

在 SQL 2012 或更高版本中，使用 percentile_cont 函数计算中位数通常更容易。看起来您的问题的其余部分已经得到解决，但我认为您也想了解这个选项。

https://msdn.microsoft.com/en-us/library/hh231473.aspx