如何在单个选择查询中获得均值、中值、模式和范围? [英] How to get mean ,median , mode and range in a single select query?

问题描述

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

I'm trying to get mean, median, mode and range for a set of values in a table. I was able to get the average but median, range and mode I'm getting a wrong one.

以下是我为上述概念尝试的代码.

Below is my code which I tried for the above concept.

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

有没有办法做到这一点?

Is there a way to achieve this?

谢谢

推荐答案

不确定这是否会有所帮助，但这里有一些 sql 可以让我在一组由

Not sure if this will help, but here is some sql which allows me generate some stats (..., mean, median, mode,..) within a group by

• cteBase 将是您的核心数据(非聚合或分组)
• cteMedian 将生成 cteBase 的中位数
• cteMode 会计算 cteBase 的模式

我只计算了一个度量，但我怀疑它可以很容易地扩展如果我有GrpByYear"，则必须将其扩展到您的复合字段中.

I am calculated only one measure, but I suspect it can easily be expanded Where I have "GrpByYear", this would have to be expanded into your compound fields.

;with cteBase as (
     Select RowNr=Row_Number() over (Partition By Year(TR_Date) Order By Year(TR_Date),TR_Y10)
           ,GrpByYear = Year(TR_Date)
           ,Measure = TR_Y10
     From [Chinrus-Series].[dbo].[DS_Treasury_Rates]
     Where Year(TR_Date)>2014
    )
    ,cteMedian as (Select A.GrpByYear,Measure From cteBase A Join (Select GrpByYear,RowNr=Max(RowNr)/2 from cteBase Group by GrpByYear) B on (A.GrpByYear=B.GrpByYear and A.RowNr=B.RowNr))
    ,cteMode   as (Select * from (Select RowNr=Row_Number() over (Partition By GrpByYear Order by Count(*) Desc),GrpByYear,Measure,Hits=count(*) From cteBase Group by GrpByYear,Measure) A Where RowNr=1)
    Select A.GrpByYear
          ,RecordCount   = Count(*)
          ,DistinctCount = Count(Distinct A.Measure)
          ,SumTotal      = Sum(A.Measure)
          ,Minimum       = Min(A.Measure)
          ,Maximum       = Max(A.Measure)
          ,Mean          = Avg(A.Measure)
          ,Median        = Max(B.Measure)
          ,Mode          = Max(C.Measure)
          ,StdDev        = STDEV(A.Measure)
     From cteBase A
     Join cteMedian B on A.GrpByYear=B.GrpByYear
     Join cteMode   C on A.GrpByYear=C.GrpByYear
     Group By A.GrpByYear
     Order By A.GrpByYear


Year    RecordCount DistinctCount   SumTotal    Minimum Maximum Mean    Median  Mode    StdDev
2016    110         43              204.82      1.63    2.25    1.862   1.84    1.83    0.128568690811108
2015    251         69              536.71      1.68    2.50    2.1382  2.16    2.20    0.1662836533952

这篇关于如何在单个选择查询中获得均值、中值、模式和范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！