SQL Server 2008 中的中位数和第 95 个百分位？ - NHS 报告要求

Question

我试图找出 SQL Server 2008 中的 95% 和中位数内置函数，但我不知道为什么 MS 不支持它们，真的很烦人...我们的工作场所报告非常复杂，需要一个直接的函数或可能是一个 dll 文件，我可以将其与 SQL Server 组装以将其用作正常功能。

任何人都可以提供建议吗？提前致谢。

目标 阿里

Answer 1

有 NTILE 函数，对于 MEDIAN 读取 这个。

Answer 2

您可以在 Visual Studio 中创建 DLL，通过创建在 SQL Server 中使用的自定义聚合函数来执行此操作。为此，创建一个新的 Visual Studio 项目并将目标框架设置为 .NET 3.5（这是针对 SQL 2008，在 SQL 2012 中可能有所不同）。然后创建一个类文件并放入以下代码或 C# 等效代码。

请注意，此算法获取最接近指定百分位数的值。如果您想使用不同的百分位算法，则可以为此修改代码。

此过程允许用户输入任何百分位进行查询。查询如下所示： SELECT dbo.Percentile(Value, 95) FROM Table

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

<Serializable>
<SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, _
  IsInvariantToOrder:=True, MaxByteSize:=-1, IsNullIfEmpty:=True)>
Public Class Percentile
  Implements IBinarySerialize
  Private _items As List(Of Decimal)
  Private _percentile As Integer

  Public Sub Init()
    _items = New List(Of Decimal)()
  End Sub

  Public Sub Accumulate(value As SqlDecimal, percentile As SqlInt32)
    _percentile = percentile
    If Not value.IsNull Then
      _items.Add(value.Value)
    End If
  End Sub

  Public Sub Merge(other As Percentile)
    _percentile = other._percentile
    If other._items IsNot Nothing Then
      _items.AddRange(other._items)
    End If
  End Sub

  Public Function Terminate() As SqlDecimal
    If _items.Count <> 0 Then
      Dim result As Decimal
      _items = _items.OrderBy(Function(i) i).ToList()

      Dim index = Convert.ToInt32(Math.Round((_percentile / 100D) * _items.Count, 0))
      If (index <> 0) Then
        index -= 1
      End If
      result = _items(index)

      Return New SqlDecimal(result)
    Else
      Return New SqlDecimal()
    End If
  End Function

  Public Sub Read(r As BinaryReader) Implements IBinarySerialize.Read
    'deserialize it from a string
    Dim list = r.ReadString()

    If Not (String.IsNullOrEmpty(list)) Then
      Dim index = list.IndexOf("|"c)

      If index <> -1 Then
        _items = New List(Of Decimal)
        _percentile = Convert.ToInt32(list.Substring(0, index))
        list = list.Substring(index + 1)

        For Each value In list.Split(","c)
          Dim number As Decimal
          If Decimal.TryParse(value, number) Then
            _items.Add(number)
          End If
        Next
      End If
    End If
  End Sub

  Public Sub Write(w As BinaryWriter) Implements IBinarySerialize.Write
    'serialize the list to a string
    Dim list As String = ""

    If (_items IsNot Nothing AndAlso _items.Count > 0) Then
      list = Convert.ToString(_percentile) + "|"
      For Each item In _items
        If Not list.EndsWith("|") Then
          list += ","
        End If
        list += item.ToString("#0.0##")
      Next
    End If
    w.Write(list)
  End Sub
End Class

然后编译它并将 DLL 和 PDB 文件复制到 SQL Server 计算机，并在 SQL Server 中运行以下命令：

CREATE ASSEMBLY CustomAggregate FROM '{path to your DLL}'
WITH PERMISSION_SET=SAFE;
GO

CREATE AGGREGATE Percentile(@value decimal(9, 3), @percentile int)
RETURNS decimal(9, 3) 
EXTERNAL NAME [CustomAggregate].[{namespace of your DLL}.Percentile];
GO

Answer 3

第 95 个百分位数的公式是
MAX(行数)*95/100

WITH kali1(k1, k2, k3, k4, k5, k6, k7) AS 
(
    SELECT MinsInAE,
           HoursInAE,
           DaysInAE AS DaysInAE,
           Month_Name,
           YearName,
           InternalNo,
           ROW_NUMBER() OVER(PARTITION BY Month_Name ORDER BY MinsInAE ASC) AS 
           'Row Number'
    FROM   AE.FactAandE
           INNER JOIN AE.PMI
                ON  AE.FactAandE.FK_PaitentMasterIndex = AE.PMI.PK_Patient
           INNER JOIN dbo.TimeDimension
                ON  dbo.TimeDimension.DateId = AE.FactAandE.FK_date
    WHERE  YearName = 'Calendar 2010'
) 
,
kali2(k21, k22, k23)
AS 
(
    SELECT MAX(k7) * 95 / 100,
           k4,
           MAX(k7)
    FROM   kali1
    GROUP BY
           k4
)
SELECT kali2.k21   AS percentilerow,
       kali2.k22   AS Month_Name,
       (kali1.k1)  AS percentilevalue
FROM   kali2
       INNER JOIN kali1
            ON  kali1.k4 = kali2.k22
WHERE  kali1.k7 = kali2.k21

Answer 4

我也为 NHS 工作，这是我编写的 SQL，用于找出中位数、最大值和第 95 个百分位
查看此内容并向我发送电子邮件[电子邮件受保护]

WITH    kali1 ( k1, k2, k3, k4, k5, k6, k7 )
          AS ( SELECT   MinsInAE ,
                        HoursInAE ,
                        DaysInAE AS DaysInAE ,
                        Month_Name ,
                        YearName ,
                        InternalNo ,
                        ROW_NUMBER() OVER ( PARTITION BY Month_Name ORDER BY MinsInAE ASC ) AS 'Row Number'
               FROM     AE.FactAandE
                        INNER JOIN AE.PMI ON AE.FactAandE.FK_PaitentMasterIndex = AE.PMI.PK_Patient
                        INNER JOIN dbo.TimeDimension ON dbo.TimeDimension.DateId = AE.FactAandE.FK_date
               WHERE    YearName = 'Calendar 2010'
             ),
        kali2 ( k21, k22, k23 )
          AS ( SELECT   MAX(k7) * 95 / 100 ,
                        k4 ,
                        MAX(k7)
               FROM     kali1
               GROUP BY k4
             ),
        kali3 ( k31, k32 )
          AS ( SELECT   AVG(1.0E * MinsInAE) AS Median ,
                        Month_Name
               FROM     ( SELECT    MinsInAE ,
                                    Month_Name ,
                                    2
                                    * ROW_NUMBER() OVER ( PARTITION BY Month_Name ORDER BY MinsInAE )
                                    - COUNT(*) OVER ( PARTITION BY Month_Name ) AS y
                          FROM      AE.FactAandE
                                    INNER JOIN AE.PMI ON AE.FactAandE.FK_PaitentMasterIndex = AE.PMI.PK_Patient
                                    INNER JOIN dbo.TimeDimension ON dbo.TimeDimension.DateId = AE.FactAandE.FK_date
                          WHERE     YearName = 'Calendar 2010'
                        ) AS d
               WHERE    y BETWEEN 0 AND 2
               GROUP BY Month_Name
             ),
        kali4 ( k41, k42, k43, k44, k46, k47 )
          AS ( SELECT   MinsInAE ,
                        HoursInAE ,
                        DaysInAE AS DaysInAE ,
                        Month_Name ,
                        YearName ,
                        InternalNo
               FROM     AE.FactAandE
                        INNER JOIN AE.PMI ON AE.FactAandE.FK_PaitentMasterIndex = AE.PMI.PK_Patient
                        INNER JOIN dbo.TimeDimension ON dbo.TimeDimension.DateId = AE.FactAandE.FK_date
               WHERE    YearName = 'Calendar 2010'
             ),
        kali5 ( k51, k52, k53 )
          AS ( SELECT   kali2.k22 AS Month_Name ,
                        ( kali1.k1 ) AS percentilevalue ,
                        kali2.k21 AS percentilerow
               FROM     kali2
                        INNER JOIN kali1 ON kali1.k4 = kali2.k22
               WHERE    kali1.k7 = kali2.k21
             )
    SELECT  kali3.k31 AS Median ,
            kali3.k32 AS Month_Name ,
            MAX(kali4.k41) AS Max_Mins_In_AE ,
            SUM(kali4.k41) AS Mins_IN_AE ,
            kali5.k51 AS Month_Name ,
            kali5.k52 AS percentile
    FROM    kali3
            LEFT JOIN kali4 ON kali3.k32 = kali4.k44
            LEFT JOIN kali5 ON kali5.k51 = kali3.k32
    GROUP BY kali3.k31 ,
            kali3.k32 ,
            kali5.k51 ,
            kali5.k52