Sql server moving average query

Pracuję z SQL Server 2008 R2, próbując obliczyć średnią ruchomą. Dla każdego rekordu w moim widoku chciałbym zebrać wartości 250 poprzednich rekordów, a następnie obliczyć średnią dla tego wyboru. Moje kolumny widoku są następujące: TransactionID jest unikalny. Dla każdego TransactionID. Chciałbym obliczyć średnią dla wartości kolumny powyżej 250 rekordów. Tak więc dla TransactionID 300, zebrać wszystkie wartości z poprzednich 250 wierszy (widok jest posortowany malejąco według TransactionID), a następnie w kolumnie MovAvg zapisać wynik średniej z tych wartości. Szukam zebrać dane w zakresie rekordów. zapytał Oct 28 14 o 20: 58 To pytanie Evergreen Joe Celko. Ignoruję, która platforma DBMS jest używana. Ale w każdym razie Joe był w stanie odpowiedzieć ponad 10 lat temu standardowym SQL. Cytat z Joe Celko SQL Puzzles and Answers: Ostatnia próba aktualizacji sugeruje, że możemy użyć predykatu do skonstruowania zapytania, które dałoby nam średnią ruchomą: Czy dodatkowa kolumna lub metoda zapytania są lepsze? Zapytanie jest technicznie lepsze, ponieważ podejście UPDATE będzie zdenormalizuj bazę danych. Jeśli jednak zapisane dane historyczne nie ulegną zmianie, a obliczanie średniej ruchomej jest kosztowne, można rozważyć zastosowanie podejścia opartego na kolumnach. Zapytanie dotyczące łamigłówek SQL: w każdym razie jednolite. Wystarczy rzucić do odpowiedniego kubełka wagi w zależności od odległości od aktualnego punktu czasowego. Na przykład limit wagi 1 dla punktów danych w ciągu 24 godzin od aktualnego punktu danych00 dla punktów danych w ciągu 48 godzin. W takim przypadku liczy się to, ile kolejnych punktów danych (np. 6:12 i 23:48) jest od siebie odległych. Przypadek użycia, o jakim myślę, byłby próbą wygładzenia histogramu wszędzie tam, gdzie punkty danych nie są wystarczająco gęste ndash msciwoj 27 maja 15 w 22:22 Nie jestem pewien, czy twój oczekiwany wynik (wynik) pokazuje klasyczną prostą ruchomą (rolling) średnią przez 3 dni. Ponieważ, na przykład, pierwsza potrójna liczba z definicji daje: ale oczekujesz 4,360 i jej mylące. Mimo to sugeruję następujące rozwiązanie, które wykorzystuje funkcję okienkową AVG. Takie podejście jest znacznie bardziej efektywne (jasne i mniej wymagające zasobów) niż SELF-JOIN wprowadzone w innych odpowiedziach (i jestem zaskoczony, że nikt nie dał lepsze rozwiązanie). Widzisz, że AVG jest opakowany w case, gdy rownum gt p. days następnie wymusza NULL w pierwszych wierszach, gdzie 3-dniowa średnia krocząca nie ma znaczenia. odpowiedziano 23 lutego 16 o 13:12 Możemy zastosować metodę brudnych lewych złączeń Joe Celkosa (cytowany powyżej przez Diego Scaravaggi), aby odpowiedzieć na pytanie, gdy zostało zadane. Generuje żądane dane wyjściowe: odpowiedź 16 stycznia 16 o 0:33 Twoja wymiana zgłoszeń na 2017 rok, IncPrzedyskutowaliśmy jak napisać średnie kroczące w Postgresie. Według popularnego popytu pokazywali ci, jak zrobić to samo w MySQL i SQL Server. Zajmij się tym, jak opisywać głośne wykresy: 7-dniowa poprzednia średnia linia: Wielka idea Nasz pierwszy wykres powyżej jest dość głośny i trudno jest uzyskać przydatne informacje. Możemy go wygładzić, wykreślając 7-dniową średnią na podstawie danych podstawowych. Można to zrobić za pomocą funkcji okna, łączenia się lub skorelowanych podkwerend - dobrze pokrywają pierwsze dwa. Zacznijmy od poprzedniej średniej, co oznacza, że ​​średni punkt 7 dnia miesiąca jest średnią z pierwszych siedmiu dni. Wizualnie przesuwa to kolce na wykresie w prawo, ponieważ duży skok jest uśredniany w ciągu następnych siedmiu dni. Najpierw utwórz tabelę zliczeń pośrednich Chcemy obliczyć średnią z łącznej liczby rejestracji dla każdego dnia. Zakładając, że mamy typową tabelę użytkowników z wierszem dla każdego nowego użytkownika i znacznikiem czasu, możemy utworzyć naszą agregację naszej tablicy rejestracyjnej, tak jak to jest: W Postgres i SQL Server możesz użyć tego jako CTE. W MySQL można zapisać go jako tabelę tymczasową. Postgres Rolling Average Na szczęście Postgres ma funkcje okna, które są najprostszym sposobem obliczenia średniej bieżącej. To zapytanie zakłada, że ​​daty nie zawierają luk. Zapytanie jest uśredniane w ciągu ostatnich siedmiu wierszy, a nie w ciągu ostatnich siedmiu dat. Jeśli w Twoich danych występują luki, wypełnij je generatorami lub dołącz do tabeli z wierszami o gęstej dacie. MySQL Rolling Average MySQL nie ma funkcji okienkowych, ale możemy wykonać podobne obliczenia za pomocą self-joinów. Dla każdego wiersza w tabeli zliczania dołączamy do każdego wiersza w ciągu ostatnich siedmiu dni i średniej. To zapytanie automatycznie obsługuje luki w dacie, ponieważ szukamy wierszy w zakresie dat, a nie poprzednich N wierszy. SQL Server Rolling Average SQL Server ma funkcje okienkowe, więc obliczanie średniej kroczki można wykonać w stylu Postgres lub stylu MySQL. Dla uproszczenia używaliśmy wersji MySQL z własnym połączeniem. Jest to koncepcyjnie takie samo, jak w MySQL. Jedyne tłumaczenia to funkcja dateadd i jawnie nazwana grupa według kolumn. Inne średnie Skupiliśmy się na 7-dniowej średniej kroczącej w tym poście. Gdybyśmy chcieli przyjrzeć się 7-dniowej średniej, to tak proste, jak sortowanie dat w innym kierunku. Jeśli chcemy spojrzeć na średnią, środkową, używamy: Postgres: wiersze między 3 poprzedzającymi i 3 następujące po MySql: między signups. date - 3 i signups. date 3 w MySQL SQL Server: między dateadd (dzień, -3, zapisy. date) i dateadd (dzień, 3, signups. date) AVG (Transact-SQL) ALL Stosuje funkcję agregującą do wszystkich wartości. ALL jest wartością domyślną. DISTINCT Określa, że ​​system AVG powinien być uruchamiany tylko w przypadku każdej unikalnej instancji wartości, niezależnie od tego, ile razy wartość ta wystąpi. wyrażenie Jest wyrażeniem dokładnej numerycznej lub przybliżonej numerycznej kategorii danych, z wyjątkiem typu danych bitowych. Zagregowane funkcje i podkwerendy są niedozwolone. OVER (partitionbyclause orderbyclause) partitionbyclause dzieli zestaw wyników utworzony przez klauzulę FROM na partycje, do których funkcja jest stosowana. Jeśli nie zostanie określony, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako pojedynczą grupę. orderbyclause określa kolejność logiczną, w której wykonywana jest operacja. orderbyclause jest wymagany. Aby uzyskać więcej informacji, zobacz OVER klauzula (języka Transact-SQL). Typ zwracany jest określany przez typ oszacowanego wyniku wyrażenia. kategoria dziesiętna (p, s) Jeśli typ danych wyrażenia jest aliasem typu danych, typem zwracanym jest również typ danych aliasu. Jednak, jeśli promowany jest podstawowy typ danych aliasu, na przykład od tinyint do int. zwracana wartość jest typu promowanego, a nie typu danych aliasu. AVG () oblicza średnią zestawu wartości, dzieląc sumę tych wartości przez liczbę wartości niepustych. Jeśli suma przekroczy maksymalną wartość dla typu danych wartości zwracanej, zostanie zwrócony błąd. AVG jest funkcją deterministyczną, jeśli jest używana bez klauzul OVER i ORDER BY. Jest niedeterministyczny, gdy zostanie określony za pomocą klauzul OVER i ORDER BY. Aby uzyskać więcej informacji, zobacz Funkcje deterministyczne i niedeterministyczne. A. Używanie funkcji SUM i AVG do obliczeń Poniższy przykład oblicza średnie godziny wakacji i sumę godzin zwolnień lekarskich, z których korzystali wiceprezes firmy Adventure Works Cycles. Każda z tych funkcji agregujących generuje pojedynczą wartość podsumowania dla wszystkich pobranych wierszy. W przykładzie użyto bazy danych AdventureWorks2017.