sql 求峰度 偏度 中位数

[liunana000]

create table test(
value int 
)

insert into test select 3
union all
select 4
union all
select 5
union all
select 6

求出峰度 偏度 和中位数 谢谢大神！

[Neo_whl]

请解释下几个名词:峰度 偏度 和中位数，忘记这些东东了

[Neo_whl]

首先中位数 select avg(value) as 中位数 from test

[Mr_Nice]

USE AdventureWorksDW;
Go
– 偏度
SELECT Skew = SUM(((Age*1.0-m.mean)/m.[StDev])*((Age*1.0-m.mean)/m.[StDev])*((Age*1.0-m.mean)/m.[StDev]))
              * MIN(m.corrfact1)
  FROM vTargetMail v CROSS JOIN
       (SELECT mean = AVG(Age*1.0), [StDev] = STDEV(Age),
               corrfact1 = COUNT(*)*1.0 / (COUNT(*)-1) / (COUNT(*)-2)
          FROM vTargetMail v) AS m;
--峰度
SELECT Kurt = SUM(SQUARE(SQUARE(((Age*1.0-m.mean)/m.[StDev])))) * MIN(m.corrfact2) - MIN(m.subfact)
  FROM vTargetMail v CROSS JOIN
       (SELECT mean = AVG(Age*1.0), [StDev] = STDEV(Age),
               corrfact2 = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
               subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
          FROM vTargetMail v) AS m;


--中位数
Select ((
		Select Top 1 value
		From   (
				Select	Top 50 Percent value
				From	test
				Where	valueIs NOT NULL
				Order By value
				) As A
		Order By value DESC) + 
		(
		Select Top 1 value
		From   (
				Select	Top 50 Percent value
				From	@Temp
				Where	valueIs NOT NULL
				Order By valueDESC
				) As A
		Order By valueAsc)) / 2

[Mr_Nice]

USE AdventureWorksDW;
Go
– 偏度
SELECT Skew = SUM(((Age*1.0-m.mean)/m.[StDev])*((Age*1.0-m.mean)/m.[StDev])*((Age*1.0-m.mean)/m.[StDev]))
              * MIN(m.corrfact1)
  FROM vTargetMail v CROSS JOIN
       (SELECT mean = AVG(Age*1.0), [StDev] = STDEV(Age),
               corrfact1 = COUNT(*)*1.0 / (COUNT(*)-1) / (COUNT(*)-2)
          FROM vTargetMail v) AS m;
--峰度
SELECT Kurt = SUM(SQUARE(SQUARE(((Age*1.0-m.mean)/m.[StDev])))) * MIN(m.corrfact2) - MIN(m.subfact)
  FROM vTargetMail v CROSS JOIN
       (SELECT mean = AVG(Age*1.0), [StDev] = STDEV(Age),
               corrfact2 = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
               subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
          FROM vTargetMail v) AS m;
 
 
--中位数
Select ((
        Select Top 1 value
        From   (
                Select    Top 50 Percent value
                From    test
                Where    valueIs NOT NULL
                Order By value
                ) As A
        Order By value DESC) + 
        (
        Select Top 1 value
        From   (
                Select    Top 50 Percent value
                From    @Temp
                Where    valueIs NOT NULL
                Order By valueDESC
                ) As A
        Order By value Asc)) / 2