SQL Server Reporting Services 中的平均值、中位数、众数

问题描述

是否可以计算一列数据的均值、中位数、众数、标准差等?

Is it possible to calculate an mean, median, mode, standard deviation, etc. of a column of data?

一般来说，是否可以在 SQL Server Reporting Services 中进行此类数学计算?

In general, is it possible to do these sorts of math calculations in SQL Server Reporting Services?

如果是这样，怎么做?

推荐答案

扩展@Homer 的答案，下面的代码可用于获取中值和众数.我需要整数，但接受小数或双数会很快改变.

Expanding on @Homer's answer, the code below can be used to get both the Median and the Mode. I needed Integers but it would be a quick change to accept Decimal or Double.

Dim values As New System.Collections.Generic.List(Of Integer)
Dim valueCounts As New System.Collections.Generic.Dictionary(Of Integer, Integer)

Function AddValue(newValue As Integer) As Integer
    values.Add(newValue)
    AddValue = newValue
    If Not valueCounts.ContainsKey(newValue) Then
        valueCounts.item(newValue) = 1
    Else
        valueCounts.item(newValue) += 1
    End If
End Function

Function GetMedian() As Double
    Dim count As Integer = values.Count
    If count = 0 Then
        Return 0
    Else
        values.Sort()
        If count Mod 2 = 1 Then
            Return values(CInt((count / 2) - 0.5))
        Else
            Dim index1 As Integer = count \ 2
            Dim index2 As Integer = index1 - 1

            Dim value1, value2 As Integer
            value1 = values(index1)
            value2 = values(index2)

            Return (value1 + value2) / 2
        End If
    End If
End Function

Function GetMode() As String
    Dim max As Integer = 0
    For Each v As Integer In valueCounts.Values
        If v > max Then
            max = v
        End If
    Next v

    Dim maxCount As Integer = 0
    Dim retValue As String = ""
    For Each vcKvp As System.Collections.Generic.KeyValuePair(Of Integer, Integer) In valueCounts
        If vcKvp.Value = max Then
            maxCount += 1
            If Not String.IsNullOrEmpty(retValue) Then
                retValue &= ", "
            End If
            retValue &= vcKvp.Key
        End If
    Next vcKvp

    If maxCount = valueCounts.Count Then
        Return "N/A"
    End If

    Return retValue
End Function

这篇关于SQL Server Reporting Services 中的平均值、中位数、众数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！