Robert Kalka

Robert Kalka Przedsiębiorca,
pasjonat.

Temat: StDev dla wielu kolumn

Witam.

Na SQL znam się tyle ile muszę. Z czymś takim jeszcze się nie spotkałem, więc proszę o radę.

W bazie danych którą przejąłem, mam 40 kolumn na dane. W zależności od kontekstu, w konkretnym rekordzie dane mogą być tylko w jednej kolumnie, w dwóch, trzech, ... do czterdziestu (kolumny nie zawierające danych mają wartość null). Co prawda każdy rekord ma pole z liczbą wypełnionych kolumn, ale nie bardzo wiem jak i czy można to wykorzystać w zapytaniu. Generalnie chodzi o obliczenie odchylenia standardowego z czegoś takiego:

ileD D1 D2 D3 D4 D5
2 3.4 6.2 null null null
1 4.1 null null null null
3 4.5 4.4 3.8 null null
2 5.2 null null null null

Dla jednej kolumny było by: Select StDev(D1)from...
Jak to będzie dla "ustrojstwa" opisanego wyżej?

Pozdrawiam.

Temat: StDev dla wielu kolumn

A jaki to silnik bazy danych? Jeśli taki, który wspiera procedury i funkcje, to można napisać samemu funkcję obliczającą odchylenie standardowe za pomocą dynamicznego SQLa. Najpierw trzeba ustalić, czy nazwy kolumn mają postać "wspólny prefix numer " (np. Kolumna1, Kolumna2, ...KolumnaN). Wtedy pętli można tworzyć kolejne nazwy kolumn (doklejać wartość licznika), generować SQL, który zsumuje kwadraty różnic od średnich i podzieli przez N (zakładam, że to SD to po prostu statystyka opisowa, z populacji, a nie estymator z próby? Wtedy przez n-1). I dalej przeiterować kursorem po wszystkich rekordach. Dla większej liczby danych może to być wolne.

Inna opcja to napisanie procedury SQL lub programu w jakimś języku (np. w R, C# itd), który przepisze te dane do bardziej strawnego dla SQL formatu:


ID ID_obiektu ZmiennaKolumnowa Wartość
1 1 1 45
2 1 2 34
3 2 1 27
4 2 2 37
5 2 3 21
6 3 1 23


I potem st.dev(Wartość) grupowana po ID_obiektuTen post został edytowany przez Autora dnia 12.09.16 o godzinie 00:25
Robert Kalka

Robert Kalka Przedsiębiorca,
pasjonat.

Temat: StDev dla wielu kolumn

Witam.

SQL 2008 R2 Express.

Kolumny są numerowane od D1 do D40.

Problemem jest ilość danych, od kilku do kilkunastu tysięcy rekordów.
Procedura pisana w VB nie wyrabia (najpierw trzeba policzyć średnią, potem jeszcze raz od początku sumowanie (średnia-dana)^2).
Próbowałem tworzyć widok, przepisywać dane mniej więcej jak w Twoim przykładzie, ale to też zbyt wolno działa.
Na pisaniu funkcji i procedur w SQL nie znam się kompletnie - stąd moje pytanie.
Paweł Broda

Paweł Broda Software Engineer

Temat: StDev dla wielu kolumn

słowo-wytrych to UNPIVOT. Potem odfiltrowujesz nulle i gotowe
Robert Kalka

Robert Kalka Przedsiębiorca,
pasjonat.

Temat: StDev dla wielu kolumn

Dzięki.

Fajne ustrojstwo ten UNPIVOT, zwłaszcza, że zwraca dane bez nuli.

Następna dyskusja:

3-dniowa konferencja w obsz...




Wyślij zaproszenie do