EXCEL STDEVA 함수구현 ( MS SQL )

EXCEL 의 STDEVA 를 MS SQL 에서 구현하기

 

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/stdev-transact-sql?view=sql-server-ver15 

STDEV(Transact-SQL) - SQL Server

MSSQL 내부 함수로 STDEV 라는걸 지원하지만 STDEVA 와는 다르다

 

그래서 다른 언어로 구현된 걸 찾아보았다.

 

http://www.lionheart.pe.kr/index.php?mid=board_uFoa63&category=850&m=0&document_srl=2047&order_type=desc 

학습자료 - 표준편차

C#으로 구현한 표준편차이다.

 

이걸 MS SQL로 바꾸면 다음과 같이 된다.

TempTable과 val은 추출 테이블명과 컬럼으로 변경하면 된다.

 

----------------------------------------------------------------------------------------------------------------

-- 입력된 데이터 수
DECLARE @n as int = 0

-- 평균분산표준편차
DECLARE @mean as decimal(18,14) = 0 , 
@variance as decimal(18,14) = 0, 
@stddev as decimal(18,14) = 0

-- 총 합계데이터 제곱합
DECLARE @total as decimal(18,14) = 0.0 , 
@totalxsq as decimal(18,14) = 0.0

-- 데이터 값
DECLARE @num1 as decimal(18,14) = 0

-- 표준편차 리턴
DECLARE @STDEVA as decimal(18,14)

-- 데이터 수 
SET @n = (SELECT COUNT(1) FROM TempTable)

DECLARE CUR CURSOR FOR

-- 결과 값
SELECT val FROM TempTable

OPEN CUR

FETCH NEXT FROM CUR INTO @num1

WHILE @@FETCH_STATUS = 0

BEGIN
SET @total = @total + @num1
SET @totalxsq = @totalxsq + POWER(@num1 , 2)
FETCH NEXT FROM  CUR INTO @num1

END

CLOSE CUR
DEALLOCATE CUR

SET @mean = @total / @n
SET @variance = (@totalxsq - (convert(decimal(18,14) ,POWER(convert(decimal(18,14), @total),2)) / @n)) / (@n - 1)

SET @STDEVA = convert(decimal(18,14) , SQRT(convert(decimal(18,14) , @variance)))

 

-- 4자릿수 반올림. 
SET @STDEVA = convert(decimal(18,4) , round(@STDEVA,4))

 

----------------------------------------------------------------------------------------------------------------

C#용 테스트 코드는 다음과 같다

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace Rextester
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //Your code goes here
            Console.WriteLine("Hello, world!");
            double[] numbers = new double[] { 5.075 ,5.075 ,5.074 ,5.076 ,5.076 ,5.076 ,5.076 ,5.076 ,5.076 ,5.074 ,5.074 ,5.075 ,5.076};
            표준편차2(numbers);
        }
        
        private static double 표준편차1(double[] numbers)
        {
            double mean = numbers.Average();
            double sdev = Math.Sqrt(numbers.Average(n => { double dif = n - mean; return dif * dif; }));

            return sdev;
        }

        private static double 표준편차2(double[] numbers)
        {
            int n = numbers.Length; // 입력된 데이터 수

            double mean, variance, stddev; //평균분산표준편차

            double total = 0.0d, totalxsq = 0.0d;  //총합계데이터제곱합

            for (int i = 0; i < n; i++)
            {
                total += numbers[i];

                totalxsq += (double)Math.Pow((double)numbers[i], 2);
            }

            mean = total / n;

            variance = (totalxsq - ((double)Math.Pow((double)total, 2) / n)) / (n - 1);

            stddev = (double)Math.Sqrt((double)variance);
                      
            
            Console.WriteLine(n.ToString());
            Console.WriteLine(total.ToString());
            Console.WriteLine(totalxsq.ToString());
            Console.WriteLine(mean.ToString());
            Console.WriteLine(variance.ToString());
            Console.WriteLine(stddev.ToString());
            
            return stddev;
        }        
    }
}

 

--------------------------------------------------------------------------------------------------------------------

1. DOUBLE 형은 MSSQL에 없고 , real 형이 이를 대체한다는 글들도 있었지만 실제로 decimal(18,14)가 가장 가까웠다

2. Pow는 Power , Sqrt는 동일한 내부 함수가 있어 그대로 사용했다