T-SQL의 Theil-Sen 추정기 함수 것을 찾았 지만 SQL로 다시 코딩 할

사람은 있습니까 Theil-센 회귀 T-SQL로 작성 기능을?

Perl로 작성된 것을 찾았 지만 SQL로 다시 코딩 할 수 없습니다.



답변

내가 말했을 때 거짓말을했다. 나는 SQL로 그것을 코딩 할 수 없다. 나는 너무 게으르다. 다음은 사용법 예제가있는 코드입니다.

강령은 기반으로 Theisen은의 사용, 펄 라이브러리 QuickMedian을 . 데이터를 프로 시저에 쉽게 전달할 수있는 새로운 테이블 유형을 정의 해 봅시다.

CREATE TYPE dbo.TheilSenInputDataTableType AS TABLE
(
    ID INT IDENTITY(1,1),
    x REAL,
    y REAL
)

TheilSen.pm에있는 내부 루프를 올바르게 해석하기 위해 솔루션에서 CROSS APPLY 문을 사용하므로 ID 열을 참고하십시오.

my ($x1,$x2,$y1,$y2);
foreach my $i(0 .. $n-2){
    $y1 = $y->[$i];
    $x1 = $x->[$i];
    foreach my $j($i+1 .. $n-1){
        $y2 = $y->[$j];
        $x2 = $x->[$j];

실제 유형 값의 배열을 저장하려면 새로운 데이터 유형이 필요합니다.

CREATE TYPE [dbo].[RealArray] AS TABLE(
    [val] [real] NULL
)

주어진 배열에 대한 중앙값을 반환 하는 f_QuickMedian 함수 는 다음과 같습니다 . 이것에 대한 크레딧은 Itzik Ben-Gan 에게갑니다 .

CREATE FUNCTION [dbo].[f_QuickMedian](@RealArray RealArray READONLY)
RETURNS REAL
AS
BEGIN
    DECLARE @Median REAL;
    DECLARE @QMedian REAL;

    SELECT @Median = AVG(1.0 * val)
    FROM
    (
        SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c
        FROM @RealArray AS o
        CROSS JOIN (SELECT c = COUNT(*) FROM @RealArray) AS c
    ) AS x
    WHERE rn IN ((c + 1)/2, (c + 2)/2);

    SELECT TOP 1 @QMedian = val FROM @RealArray
    ORDER BY ABS(val - @Median) ASC, val DESC

    RETURN @QMedian
END

그리고 p_TheilSen Estimator :

CREATE PROCEDURE [dbo].[p_TheilSen](
      @TheilSenInput TheilSenInputDataTableType READONLY
    , @m Real OUTPUT
    , @c Real OUTPUT
)
AS
BEGIN
    DECLARE
        @m_arr RealArray
      , @c_arr RealArray;

    INSERT INTO @m_arr
        SELECT m
        FROM
        (
            SELECT
                t1.x as x1
                , t1.y as y1
                , t2o.x as x2
                , t2o.y as y2
                , t2o.y-t1.y as [y2 - y1]
                , t2o.x-t1.x as [x2 - x1]
                , CASE WHEN (t2o.x <> t1.x) THEN  CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x) ELSE NULL END AS [($y2-$y1)/($x2-$x1)]
                , CASE WHEN t1.y = t2o.y THEN 0
                  ELSE
                    CASE WHEN t1.x = t2o.x THEN NULL
                        ELSE
                        -- push @M, ($y2-$y1)/($x2-$x1);
                        CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x)
                    END
                  END as m
            FROM @TheilSenInput t1
            CROSS APPLY
                    (
                    SELECT  t2.x, t2.y
                    FROM    @TheilSenInput t2
                    WHERE   t2.ID > t1.ID
                     ) t2o
        ) t
        WHERE m IS NOT NULL

    SELECT @m = dbo.f_QuickMedian(@m_arr)

    INSERT INTO @c_arr
        SELECT y - (@m * x)
            FROM @TheilSenInput

    SELECT @c = dbo.f_QuickMedian(@c_arr)

END

예:

DECLARE
      @in TheilSenInputDataTableType
    , @m Real
    , @c Real

INSERT INTO @in(x,y) VALUES (10.79,118.99)
INSERT INTO @in(x,y) VALUES (10.8,120.76)
INSERT INTO @in(x,y) VALUES (10.86,122.71)
INSERT INTO @in(x,y) VALUES (10.93,125.48)
INSERT INTO @in(x,y) VALUES (10.99,127.31)
INSERT INTO @in(x,y) VALUES (10.96,130.06)
INSERT INTO @in(x,y) VALUES (10.98,132.41)
INSERT INTO @in(x,y) VALUES (11.03,135.89)
INSERT INTO @in(x,y) VALUES (11.08,139.02)
INSERT INTO @in(x,y) VALUES (11.1,140.25)
INSERT INTO @in(x,y) VALUES (11.19,145.61)
INSERT INTO @in(x,y) VALUES (11.25,153.45)
INSERT INTO @in(x,y) VALUES (11.4,158.03)
INSERT INTO @in(x,y) VALUES (11.61,162.72)
INSERT INTO @in(x,y) VALUES (11.69,167.67)
INSERT INTO @in(x,y) VALUES (11.91,172.86)
INSERT INTO @in(x,y) VALUES (12.07,177.52)
INSERT INTO @in(x,y) VALUES (12.32,182.09)


EXEC p_TheilSen @in, @m = @m OUTPUT, @c = @c OUTPUT

SELECT @m
SELECT @c

보고:

m = 52.7079
c = -448.4853

비교를 위해서, perl 버전은 동일한 데이터 세트에 대해 다음 값을 리턴합니다.

m = 52.7078651685394
c = -448.484943820225

TheilSen 추정기를 사용하여 파일 시스템에 대한 DaysToFill 지표를 계산합니다. 즐겨!


답변

이것은 다음 질문 / 답변 (여기서는 DBA.SE)과 유사하게 SQLCLR에서 무언가를 수행하는 데 적합 할 것입니다.

가장 긴 공통 부분 문자열 문제에 대한 SQL Server 구현이 있습니까?

나중에 시간이 지나면 이것이 얼마나 실현 가능한지 알게 될 것입니다.


답변

또한 T-SQL, Oracle 및 서버 (일반적으로 SQL로 작성하기에는 너무 복잡함)도 확인했습니다.

그러나 이것에 관심 있을 수도 있습니다 (Python의 과학 / 통계 패키지). 이 알고리즘은 Python과 Python에서도 구현됩니다. 파이썬은 Perl과 달리 인간이 이해할 수있는 최소한의 언어입니다.

당신의 질문은 나에게 흥미를 느끼고 나는 파고 들었다. 이 알고리즘을 포함하는 C 및 C ++ 라이브러리가 있으며 몇 개의 R 패키지로도 제공됩니다. 그리고 @srutzky의 게시물도 흥미로워 보입니다.

흥미로운 질문 BTW에 +1-포럼에 오신 것을 환영합니다 🙂


답변