我正在使用SQL Server2012。我想问的是,我是否应该继续研究创建SP(或UDF,但可能涉及#Temp表,我在想SP)的能力,以便拥有一个SP。确定可重用对象的中位数?
我希望这不是一个通用的问题,并且可以解决,但是我花了一些时间研究确定中值的能力。一些可能的障碍包括需要传递查询的字符串表示形式,该字符串表示形式将返回我希望对其进行中值处理的数据。
有人尝试过吗?
这是我用来生成一些快速统计信息的存储过程。
只需通过源,测量和/或过滤器即可。
CREATE PROCEDURE [dbo].[prc-Dynamic-Stats](@Table varchar(150),@Fld varchar(50), @Filter varchar(500))
-- Syntax: Exec [dbo].[prc-Dynamic-Stats] '[Chinrus-Series].[dbo].[DS_Treasury_Rates]','TR_Y10','Year(TR_Date)>2001'
As
Begin
Set NoCount On;
Declare @SQL varchar(max) =
'
;with cteBase as (
Select RowNr=Row_Number() over (Order By ['+@Fld+'])
,Measure = ['+@Fld+']
From '+@Table+'
Where '+case when @Filter='' then '1=1' else @Filter end+'
)
Select 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 (Select Measure From cteBase where RowNr=(Select Cnt=count(*) from cteBase)/2) B on 1=1
Join (Select Top 1 Measure,Hits=count(*) From cteBase Group By Measure Order by 2 desc ) C on 1=1
'
Exec(@SQL)
End
退货
RecordCount DistinctCount SumTotal Minimum Maximum Mean Median Mode StdDev
3615 391 12311.81 0.00 5.44 3.4057 3.57 4.38 1.06400795277565
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句