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

本文介绍了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