Sql 12 miesięczna średnia ruchoma

Mam produkcji tabeli, która zawiera następującą strukturę: Mam danych dla każdego rep od 112017 do 812017. Co chcę być w stanie zrobić, to utworzyć 12 miesięcy średniej ruchomej począwszy od 112017 dla każdego rep, w następujący sposób: gdzie każdy wiersz reprezentuje 12-miesięczna średnia ruchoma dla wymienionego przedstawiciela w ustalonym czasie. Znalazłem kilka przykładów, które były niejasno bliskie i próbowałem ich bezskutecznie. Wydaje się, że dodanie grupy przez element rep jest głównym odstępstwem od innych przykładów. Dotyczy to tak dalece jak to było: zapytanie to wydaje się generować średnią sumę lub sumę, ponieważ nie ma grupowania w skorelowanym podzapytaniu. Kiedy próbuję grupować według, pojawia się błąd, że może wrócić tylko w jednym wierszu. pytanie 10 października 13 o 14: 47Rolling 12 miesięcy Średnia w języku DAX Obliczanie 12-miesięcznej średniej kroczącej w języku DAX wygląda na proste zadanie, ale kryje w sobie pewną złożoność. W tym artykule wyjaśniono, jak napisać najlepszą formułę, omijając typowe pułapki za pomocą funkcji analizy czasu. Zaczynamy od zwykłego modelu danych AdventureWorks z tabelą produktów, sprzedaży i kalendarza. Kalendarz został oznaczony jako tabela kalendarza (konieczna jest praca z dowolną funkcją analizy czasu) i zbudowaliśmy prostą hierarchię rok-miesiąc-data. Dzięki tej konfiguracji bardzo łatwo jest utworzyć pierwszą tabelę przestawną pokazującą sprzedaż w czasie: podczas analizy trendów, jeśli sprzedaż podlega sezonowości lub, bardziej ogólnie, jeśli chcesz usunąć efekt szczytów i spadków sprzedaży, powszechną techniką jest obliczanie wartości w danym okresie, zwykle 12 miesięcy i jej średnia. Średnia krocząca z okresu 12 miesięcy zapewnia sprawny wskaźnik trendu i jest bardzo przydatna na wykresach. Biorąc pod uwagę datę, możemy obliczyć 12-miesięczną średnią kroczącą z tą formułą, która nadal ma pewne problemy, które rozwiążemy później: zachowanie formuły jest proste: oblicza wartość Sales po utworzeniu filtru w kalendarzu, pokazuje dokładnie jeden pełny rok danych. Rdzeniem tej formuły jest DATESBETWEEN, która zwraca kompletny zbiór dat między dwiema granicami. Niższy z nich to: Czytanie go z najbardziej wewnętrznej strony: jeśli wyświetlamy dane przez miesiąc, powiedzmy, lipiec 2007, to bierzemy ostatnią widoczną datę za pomocą LASTDATE, która zwraca ostatni dzień w lipcu 2007. Następnie używamy NEXTDAY, aby zająć 1 miejsce. sierpnia 2007 r. i ostatecznie używamy SAMEPERIODLASTYEAR, aby przesunąć go z powrotem o jeden rok, otrzymując datę 1 sierpnia 2006 r. Górna granica jest po prostu OSTATECZNA, tj. do końca lipca 2007 r. Jeśli użyjemy tej formuły w tabeli przestawnej, wynik wygląda dobrze, ale mają problem z ostatnią datą: w rzeczywistości, jak widać na rysunku, wartość jest prawidłowo obliczana do 2008 r. Wtedy nie ma żadnej wartości w 2009 r. (co jest poprawne, nie mamy sprzedaży w 2009 r.), ale istnieje zaskakująca wartość w grudniu 2017 r., gdzie nasza formuła pokazuje sumę całkowitą zamiast wartości pustej, jak można się było spodziewać. W rzeczywistości, w grudniu, LASTDATE zwraca ostatni dzień roku, a NEXTDAY powinien zwrócić 1 stycznia 2017. Ale NEXTDAY to funkcja analizy czasu i oczekuje się, że zwróci zestawy istniejących dat. Ten fakt nie jest bardzo oczywisty i warto kilka słów więcej. Funkcje wywiadu czasowego nie wykonują matematyki na datach. Jeśli chcesz wziąć dzień po danej dacie, możesz po prostu dodać 1 do dowolnej kolumny daty, a wynik będzie następnego dnia. Zamiast tego funkcje inteligentnej analizy czasu przesuwają zestaw daty i wstecz w czasie. Tak więc NEXTDAY przyjmuje swój wkład (w naszym przypadku tabelę jednorzędową z 31 grudnia 2017 r.) I przesuwa ją dzień później. Problem polega na tym, że wynikiem powinien być 1 stycznia 2017 r., Ale ponieważ tabela kalendarza nie zawiera tej daty, wynikiem jest BLANK. W ten sposób nasze wyrażenie wylicza wartość Sprzedaży z pustą dolną granicą, co oznacza początek czasu, co daje w efekcie wielką sumę sprzedaży. Aby poprawić formułę, wystarczy zmienić kolejność oceny dolnej granicy: Jak widać, teraz NEXTDAY jest wywoływana po przesunięciu rocznego z powrotem. W ten sposób bierzemy 31 grudnia 2017 r., Przenosimy go do 31 grudnia 2009 r. I przyjmujemy następnego dnia, tj. 1 stycznia 2017 r .: istniejącą datę w tabeli kalendarza. Wynik jest teraz oczekiwany: w tym momencie wystarczy podzielić tę liczbę przez 12, aby uzyskać średnią kroczącą. Ale, jak łatwo sobie wyobrazić, nie zawsze możemy go podzielić przez 12. W rzeczywistości na początku okresu nie ma 12 miesięcy do zsumowania, ale mniejszej liczby. Musimy obliczyć liczbę miesięcy, dla których istnieje sprzedaż. Można to osiągnąć za pomocą filtrowania krzyżowego tabeli kalendarza z tabelą sprzedaży po zastosowaniu nowego 12-miesięcznego kontekstu. Definiujemy nową miarę, która oblicza liczbę istniejących miesięcy w okresie 12 miesięcy: Na następnej ilustracji widać, że miara Months12M oblicza poprawną wartość: Warto zauważyć, że formuła nie działa, jeśli wybierzesz okres dłużej niż 12 miesięcy, ponieważ parametr CalendarMonthName ma tylko 12 wartości. Jeśli potrzebujesz dłuższych okresów, musisz użyć kolumny RRRRMM, aby móc policzyć więcej niż 12. Ciekawą częścią tej formuły, która korzysta z filtrowania krzyżowego, jest fakt, że oblicza liczbę dostępnych miesięcy nawet podczas filtrowania za pomocą innych atrybuty. Jeśli na przykład wybierzesz kolor niebieski za pomocą krajarki, sprzedaż rozpocznie się w lipcu 2007 r. (Nie w 2005 r., Jak to się dzieje w przypadku wielu innych kolorów). Korzystając z filtra krzyżowego w obszarze Sprzedaż, formuła prawidłowo obliczyła, że ​​w lipcu 2007 r. Istnieje jeden miesiąc dostępnych sprzedaży dla produktu Blue: W tym momencie średnia krocząca jest tylko ODDYCHAJ: gdy używamy go w tabeli przestawnej, nadal mały problem: w rzeczywistości wartość jest obliczana również dla miesięcy, dla których nie ma sprzedaży (tj. przyszłe miesiące): Można to rozwiązać za pomocą instrukcji JEŻELI, aby zapobiec wyświetlaniu wartości formuły w przypadku braku sprzedaży. Nie mam nic przeciwko IF, ale dla uzależnionych od wyników wśród was, zawsze warto pamiętać, że IF może być zabójcą wydajności, ponieważ może wymusić uruchomienie silnika formuły DAX. W tym konkretnym przypadku różnica jest znikoma, ale , co do zasady, najlepszym sposobem na usunięcie wartości, gdy nie ma sprzedaży, jest korzystanie z czystych formuł silnika do przechowywania danych, takich jak ta: Porównywanie wykresu za pomocą Avg12M z innym, który pokazuje Sprzedaż, można łatwo ocenić, jak średnia krocząca przedstawia trendy w znacznie bardziej przejrzysty sposób: informuj mnie o nadchodzących artykułach (biuletyn). Usuń zaznaczenie, aby swobodnie pobrać plik. Teraz chcę wyświetlić wykres przedstawiający 12-miesięczną średnią kroczącą z ostatnich 12 miesięcy, tj. Na osi poziomej z ostatnich 12 miesięcy, a na osi pionowej - jako procent choroby. Stworzyłem zbiór danych o nazwie Last12Months, ale oczywiście jest to niepoprawne, np. w przypadku średniej ruchomej z lipca 2017 r. średnia ruchoma potrzebuje procentów chorobowych z sierpnia 2009 r. do lipca 2017 r. W przypadku korzystania z zestawu danych zawierającego ostatnie 12 miesięcy, odsetki za choroby sprzed lipca 2017 r. nie są dostępne do obliczenia kwoty średnia z lipca 2017 r. Czy mógłbyś podać więcej wyjaśnień do tego możesz podać przykładowy układ raportu, aby wyczyścić swoje wymagania, do ostatniego akapitu, wydaje się, że chcesz dać filtr do grupy kategorii na wykresie, aby pokazać ostatnie 12 ostatnich miesiąc, prawda, jeśli tak jest, myślę, że możesz skorzystać z funkcji Dzisiaj w usługach raportowania, aby uzyskać bieżący rok i miesiąc, a następnie użyć w tym roku odjąć 1, otrzymasz ostatnie 12 miesięcy. Jeśli źle Cię zrozumiem, daj nam znać. Dzięki, Challen Fu Pamiętaj, aby oznaczyć odpowiedzi jako odpowiedzi, jeśli pomagają i usuwają zaznaczenie, jeśli nie udzielą pomocy. Oznaczona jako odpowiedź przez HennieErgon piątek, 19 sierpnia 2017 11:14 AM Wtorek, 16 sierpnia 2017 5:17 AM IIf (miesiąc (dzisiaj ()) lt 10, (rok (dzisiaj ()) - 2) amp quotM0 więcej miesięcy (dzisiaj ()), (rok (dzisiaj ()) - 2) amp quot ampim Miesiąc (dzisiaj ())) IIf (miesiąc (dzisiaj ()) lt 10, rok (dzisiaj ()) amp quot0 amp amp (miesiąc (dziś ()) - 1), Year (Today ()) amp quot amp amp (Month (today ()) - 1)) Oznaczona jako odpowiedź przez Challen Fu Moderator sobota, 20 sierpnia 2017 12:50 Edytowane przez HennieErgon poniedziałek, sierpień 22, 2017 6:18 Poprawna kolejność formuł Piątek, 19 sierpnia 2017 11:12 Chcę mieć wykres przedstawiający średnią 12-miesięczną w ciągu ostatnich 12 miesięcy, tj. Na osi poziomej z ostatnich 12 miesięcy, oraz na oś pionowa procent choroby. Stworzyłem zbiór danych o nazwie Last12Months, ale oczywiście jest to niepoprawne, np. w przypadku średniej ruchomej z lipca 2017 r. średnia ruchoma potrzebuje procentów chorobowych z sierpnia 2009 r. do lipca 2017 r. W przypadku korzystania z zestawu danych zawierającego ostatnie 12 miesięcy, odsetki za choroby sprzed lipca 2017 r. nie są dostępne do obliczenia kwoty średnia z lipca 2017 r. Czy mógłbyś podać więcej wyjaśnień, możesz podać przykładowy układ raportu, aby usunąć swoje wymagania, do ostatniego akapitu, wydaje się, że chcesz podać filtr do grupy kategorii na wykresie, aby pokazać ostatnie 12 miesiąc, prawda, jeśli tak jest, myślę, że możesz skorzystać z funkcji Dzisiaj w usługach raportowania, aby uzyskać bieżący rok i miesiąc, a następnie użyć w tym roku odjąć 1, otrzymasz ostatnie 12 miesięcy. Jeśli źle Cię zrozumiem, daj nam znać. Dzięki, Challen Fu Pamiętaj, aby oznaczyć odpowiedzi jako odpowiedzi, jeśli pomagają i usuwają zaznaczenie, jeśli nie udzielą pomocy. Oznaczona jako odpowiedź przez HennieErgon piątek, 19 sierpnia 2017 11:14 wtorek, 16 sierpnia 2017 5:17 Dzięki za odpowiedź. Zmęczony filtrem w dniu kategorii, ale potem mam inne problemy. Załączam przykład. Jak widzisz, mój format daty wygląda jak 2009M09. Ale aby filtrować przez ostatnie 12 (lub 24) miesiące, muszę podzielić rok i miesiąc. HOWerver, jak widać na przykładzie, na przykład pominięto 2009M10, 2009M11 i 2009M12, ponieważ wydaje się, że 10, 11 i 12 są niższe niż 9 środa, 17 sierpnia 2017 9:46 AM Jak mi powiedziałeś, Zrobiłem filtr na YearMonth Kategoria: IIf (Miesiąc (dziś ()) lt 10, (Rok (Dzisiaj ()) - 2) amp quot0mot amp Miesiąc (dzisiaj ()), (Rok (Dzisiaj ()) - 2) amp quotamp amp Month (dzisiaj ())) IIf (miesiąc (dzisiaj ()) lt 10, rok (dzisiaj ()) amp quot0 amp amp (Month (today ()) - 1), Year (Today ()) amp quot amp wzmacniacz (Miesiąc (dzisiaj ()) - 1)) Oznaczony jako odpowiedź przez Challenga Fu Moderatora sobota, 20 sierpnia 2017 r. 12:50 Edytowane przez HennieErgon poniedziałek, 22 sierpnia 2017 r. 6:18 Poprawna kolejność piątek, 19 sierpnia, 2017 11:12 AMCalculating Wartości w Rolling Window w Transact SQL Dwain Camps Obliczanie wartości w ramach Rolling Window w SQL Za każdym razem, gdy trzeba połączyć wartości w kilku wierszach SQL, problem może być trudny, szczególnie jeśli chodzi o wydajność. Skoncentrujemy się na problemach z kroczącym dwunastomiesięcznym sumowaniem, ale nasze metody można zastosować do dowolnego okna czasowego (np. 3 miesiące) lub do średnich i innych agregacji również w tych oknach czasowych. Łączna suma za miesiąc to suma za ten miesiąc plus poprzednie miesiące w danym oknie czasowym lub NULL, jeśli nie posiadasz wartości dla wszystkich poprzednich miesięcy w oknie czasowym. W poprzednich wersjach SQL Servera trzeba było przejść przez kilka kółek, aby wymyślić metodę, która działa dobrze, ale SQL 2017 oferuje kilka nowych funkcji, które upraszczają. W obu przypadkach istnieje kilka poprawnych rozwiązań. Który jest najszybszy i najbardziej wydajny We8217ll próbuje odpowiedzieć na to pytanie w tym artykule. Będziemy pracować w SQL 2017. Jeśli chcesz śledzić dalej, możesz skorzystać z zasobu Sample Queries. sql, który znajdziesz w załączniku. Konfiguracja danych i oświadczenie o problemach biznesowych Często zdarza się, że w ciągu miesiąca znajdujesz się z wieloma transakcjami, ale w naszym przypadku we8217ll zakładamy, że ty8217ve zgrupował już transakcje na każdy miesiąc. We8217ll przypisze nasz klucz podstawowy do typu danych DATE i uwzględni pewne wartości, powyżej których chcemy zgromadzić sumy kroczące z dwunastu miesięcy. Daje to również nieco inny plan kwerend, więc będziemy się interesować, aby zobaczyć, jak wyniki jego działania są porównywalne z innymi proponowanymi do tej pory rozwiązaniami. Tyle, jeśli chodzi o tradycyjne rozwiązania i moje przeprosiny, jeśli zdarzyło mi się przeoczyć jednego z twoich ulubionych, ale możesz je zakodować i dodać do szelek testu wydajności, który zaprezentujemy później, aby zobaczyć, jak się rozwija. Rozwiązanie 5: Używanie dziwacznej aktualizacji Jeśli nigdy nie słyszałeś o Kwirkowej aktualizacji (QU) i jak można ją zastosować do problemów, takich jak działające sumy, zdecydowanie polecam przeczytanie tego wyjątkowego artykułu przez MVP SQL Modena. zatytułowany Rozwiązywanie problemów z biegiem sumarycznym i rangowym. Zanim przejdziemy dalej, powinniśmy zauważyć, że są tacy, którzy twierdzą, że metoda QU reprezentuje nieudokumentowane zachowanie SQL Server i dlatego nie należy ufać. Można powiedzieć, że składnia jest wyraźnie opisana przez wpis MS Books On Line dla instrukcji UPDATE dla wersji SQL 2005, 2008 i 2017. Rzeczywiście, cofa się dalej. Z powodzeniem użyłem go w SQL Server 2000, ale został on odziedziczony po Sybase i był w pierwszej wersji SQL Server kiedykolwiek wydany. Dla naisayers I8217ll powiedzieć, że zachowanie 8220undocumented8221 jest co najmniej jednolita we wszystkich wersjach i prawdopodobnie nie ma powodu, aby podejrzewać, że będzie przestarzałe lub zmiany w przyszłych wersjach MS SQL. Zastanów się przed ostrzeżeniem Jeśli kiedykolwiek rozważysz zastosowanie QU do rozwiązania jakiegokolwiek problemu, musisz zwrócić uwagę na wiele zasad, które mają zastosowanie (również zawarte w cytowanym artykule autorstwa Jeffa). Te główne, które I8217ve obsłużyły w tym zapytaniu, można podsumować w następujący sposób: Tabela musi mieć indeks klastrowy, który wskazuje porządek wierszy źródłowych według okresu, w którym ma być wykonywany. Tabela musi zawierać kolumnę, w której można umieścić zagregowaną sumę bieżącą. Podczas wykonywania aktualizacji należy zablokować tabelę za pomocą podpowiedzi do zapytania TABLOCKX, aby upewnić się, że nikt inny nie dostanie się do INSERT, DELETE lub UPDATE przed ich wykonaniem. Musisz uniemożliwić SQLowi próbę zrównoleglenia zapytania przy użyciu podpowiedzi OPTION (MAXDOP 1). Ponieważ średnia krocząca z dwunastu miesięcy to po prostu całkowita liczba całkowita w przebraniu, możemy dodać kolumnę do naszej tabeli i zastosować zapytanie QU, aby wykonać nasze obliczenia. Muszę wyznać, że to wygląda trochę niechlujnie, ze wszystkimi zmiennymi, których potrzebujesz DO DEKLARUJ. Zasadniczo to, co robimy, to śledzenie ostatnich dwunastu (opóźnionych) wartości, aby usunąć dwunastą (tam, gdzie jest przypisana kolumna Rolling12Months) z tego, co w przeciwnym razie jest całkowitą liczbą całkowitą, jak opisano w artykule Jeff8217s. Mamy duże nadzieje na jego szybkość, ponieważ wiadomo, że jest to najszybsza metoda rozwiązania problemu z bieżącymi sumami. Jeszcze raz powinieneś przekonać się, że wyniki są spójne z wcześniejszymi rozwiązaniami, i tak to rozwiązanie wciąż zachowuje się tak samo w SQL 2017. Jeśli do tej pory używasz mnie do tej pory, możesz również zadać sobie pytanie, 8220 co się stanie, jeśli muszę obliczyć wiele uruchomień dwanaście miesięcy sumarycznych na różnych partycjach 8221 Jest to stosunkowo proste w przypadku wszystkich innych prezentowanych rozwiązań, ale proponuje nieco wyzwanie przy użyciu QU. Odpowiedź na to można znaleźć w załączonym pliku zasobów: Quirky Update Partitioned. sql. Rozwiązania SQL 2017 Do tej pory wszystko, co zrobiliśmy, będzie działać w SQL 2008. Jedyne, co zrobiliśmy, to nie jest obsługiwane w SQL 2005, to inicjalizacje zmiennych, KTÓRZY DEKLARUJEMY w podejściu QU. Teraz niech zobaczą nowe funkcje SQL 2017, które można zastosować do tego problemu. Rozwiązanie 6: Korzystanie z ramki okiennej Nasze pierwsze rozwiązanie SQL 2017 (6) pokazuje, jak używać ramki okna, która zaczyna się 11 wierszy przed bieżącym wierszem, aż do bieżącego wiersza, aby uzyskać oczekiwane wyniki. Po raz kolejny zwrócone wyniki są takie same, ale plan zapytań jest zupełnie inny niż w przypadku poprzedniego rozwiązania SQL 2017, jednak nie jesteśmy szczególnie optymistyczni, ponieważ to podejście przyniesie rozsądną alternatywę ze względu na liczbę 8220look-backs8221 potrzebną do działania . Porównanie wydajności metod Prawdziwym testem sprawdzającym, jak wiele rozwiązań wykonuje, jest sprawdzenie rzeczywistych czasów wykonania na serwerze w stanie spoczynku przy użyciu wiązki testowej z wieloma wierszami. Nasza wiązka testowa jest wyświetlana razem z tym, jak zostały zmienione Rozwiązania 1 i 2 (zobacz komentarze w kodzie), aby: Wstawić wyniki do tabeli tymczasowej, aby uniknąć wpływu czasu, jaki upłynął na zwrócenie wierszy do wyników SQL Server Management Studio8217s krata. Usuń arytmetykę DATE, ponieważ podczas generowania wiązek testowych o wielu milionach trudno jest wygenerować tyle unikalnych miesięcy, więc kolumna tabeli Data została zmieniona na typ danych BIGINT. Dla pozostałych rozwiązań (2 8211 6), wykreśliliśmy procesor, a czas, który upłynął, wynika z 1 M w 4-milionowych rzędach. Interpretacja wyników i czasu procesorów wydają się być spójne dla różnych metod w odniesieniu do ich kolejności. Wszystkie wydają się skalować w sposób liniowy. Aktualizacja Quirky, zakładając, że można ją zrozumieć i wszystkie powiązane z nią reguły, wydaje się być najszybszym dostępnym rozwiązaniem w celu rozwiązania tego problemu, nawet biorąc pod uwagę nowe funkcje dostępne w SQL 2017. W SQL 2017 podejście do ramy okna jest z pewnością zadbane, Kompaktowy i elegancki, ale lekko podąża za rozwiązaniem Eksluzywnej aktualizacji w testowanych przez nas rzędach. Te wyniki testu wydają się być zgodne z wcześniejszym testem na uruchamianie zestawień w SQL 8220Denali8221 CTP3 przez Microsoft Certified Master Wayne Sheffield na swoim blogu. Jeśli utkniesz z wcześniejszą wersją SQL (2005 lub 2008) iz jakiegoś powodu nie możesz używać Aktualizacji Eksluzywnej (np. Jeśli nie ufasz temu nieudokumentowanemu zachowaniu), najszybsze dostępne rozwiązania to CROSS APPLY TOP lub za pomocą skorelowanego zapytania podrzędnego, ponieważ oba te elementy wydawały się być w ścisłym związku. Wygląda na to, że należy unikać 8220traditional8221 INNER JOIN. Prawdopodobnie pogorszy się tylko, jeśli zajdzie potrzeba arytmetyki dat w klauzuli ON JOIN8217. Podobnie, korzystanie z Tabeli Tally lub wielu LAG (SQL 2017) z pewnością nie było dobrym pomysłem. Nie badaliśmy rozwiązań opartych na CURSOR, ale możesz wrócić do artykułu z bieżącymi podsumowaniami, aby dowiedzieć się, jak mogą one działać w tym przypadku. I8217ve również widział niektóre rozwiązania, które wykorzystują rekursywne Common Table Expression (rCTE), ale ja na pewno nie postawiłbym na ich wydajność w porównaniu do rozwiązań QU lub ram okiennych. Istnieje wiele sposobów obliczania wartości w kroczącym oknie w SQL i jest wśród nich wyraźnych zwycięzców wydajności. Mamy nadzieję, że niniejszy przewodnik po dostępnych metodach był interesujący i użyteczny. Subskrybuj więcej artykułów Dwutygodniowe biuletyny pomagają wyostrzyć twoje umiejętności i wyprzedzić Cię, dzięki artykułom, książkom i opinii, aby być na bieżąco. Chcesz więcej Zapisz się do naszego dwutygodniowego newslettera Razem: 33 Średnia: 4.65 Dwain Camps był kierownikiem projektu od wielu lat. Ponieważ wydajność aplikacji może być decydującym czynnikiem sukcesu dla projektów, ewangelizował on potrzebę opracowania wydajnego kodu SQL. Poprzez mentoring i pisanie artykułów na temat SQL ma nadzieję wyszkolić przyszłego pokolenia inżynierów oprogramowania na właściwe i złe sposoby dostarczania kodu SQL. Ma również szczególne zainteresowanie opracowywaniem rozwiązań złożonych, wymagających dużej ilości danych problemów przy użyciu wysokowydajnego SQL, ponieważ deklaratywny charakter SQL pozwala na opracowanie algorytmicznie unikalnych rozwiązań, których języki proceduralne mogą nie być w stanie. Śledź Dwain na Twitterze Bardzo dobry Świetny artykuł Byłem zaskoczony, że LGD () zrobił tak źle. Domyślam się, że każde wywołanie jest wykonywane osobno, a nie wyważone i zoptymalizowane jak okno. Świetne wyjaśnienie Zgadzam się, jest to świetne wyjaśnienie różnych sposobów obliczania wartości w ruchomym oknie. Jeśli przetestujesz te przykłady na SQL 2017, musisz zmienić MyTable z RollingTotalsExample. Wielkie dzięki, Mr. Camps Tally, metoda Hi Dwain, zauważyłem, że twoja kwerenda tabeli Tally powodowała operację Table Spool i myślałaś, że możesz rozważyć utworzenie części Tally tabeli Dates w ten sposób: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Date THEN a. Value END), Rolling12MonthsCASE GDY ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 THEN NULL ELSE SUM (Value) END INTO ResultsSoln2 FROM RollingTotalsPrzykład CROSS APPLY (mdash Usuń wartości arytmetyczne DATE (Date), (Date1), (Date2), (Date3), (Date4), (Date5), (Date6), (Date7), (Date8), (Date9), (Date10), (Date11)) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingData lt MAX (Data) ORDER BY GroupingDate (Przepraszam, jeśli formatowanie jest złe ndash brak podglądu) Ta zmiana nadal nie sprawi, że będzie pretendentem, ale robi ogromny postęp w tym zapytaniu. Dziękuję za komentarze. Dziękuję Joe i Nic. Irsquom cieszy się, że artykuł jest interesujący. Joe: Ja też byłem trochę zaskoczony wynikami LGD i zastanawiam się, jaki byłby punkt progu rentowności. Być może 3 miesiące mogą nie być tak złe, ale nadal trudno uwierzyć, że może być szybszy niż QU. Tally Tables MM: Z jakiegoś powodu, mam osobiste preferencje dla tabel Tally in-line, ale twoje wyniki są interesujące, jeśli rozważasz tylko w innych przypadkach. Pomoc z Moving Annual Total Mój pierwszy post. Muszę obliczyć łączną liczbę roczną ruchomą dla powyższej wartości z poprzednich 12 miesięcy, z tym miesiącem to 12 miesięcy. Następnie muszę uzyskać łączną roczną ruchomość za 12 miesięcy przed tą datą. Pomysł polega na porównaniu MAT na ten miesiąc z odpowiednim miesiącem ubiegłego roku i na każdy poprzedni miesiąc. Moja próba dała mi to: Z cte jako (SELECT rNum ROWNUMBER () Over (kolejność według daty) Data Date Rolling12Months CASE WHEN ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (Value) OVER (ORDER BY Data ROWS BETWEEN 11 PRECEDING I BIEŻĄCA WIERSZ) KONIEC Z RollingTotalsExample) Wybierz From cte, (Wybierz mRNum max (rNum) z cte) deMax Gdzie rNum między mRNum ndash 23 a mRNum Z możliwością zmiany instrukcji Were, aby odzwierciedlić, czy chcę w tym roku, czy też Poprzedni rok. Moje prawdziwe dane mają datę, jak w Integer 201709, co moim zdaniem ułatwi mi życie, ponieważ mogę odjąć 100, aby uzyskać rok poprzedni. Doskonały artykuł i każda pomoc będzie doceniona. To jest moje rozwiązanie robocze (z pewnym hałasem) mdash Łączna liczba 12 miesięcy z użyciem SQL 2017 i ramka okna JEŚLI OBIEKT (lsquotempdb..PreviousYearrsquo) NIE JEST PEŁNĄ STABILIZACJĄ PoprzedniYear Z cte as (WYBIERZ rNUM ROWNUMBER () Over (kolejność według daty Wartość Rolling12MonthsCASE GDY ROWNUMBER () OVER (data wysłania) gt 11 TO SUMA (Wartość) OVER (ORDER BY Data ROWS MIĘDZY 11 PRECEDING I BIEŻĄCA WIERSZ) END OF RollingTotalsExample) Wybierz pyRowNum ROWNUMBER () Over (order by mRNum ). . sStart mRNum ndash 24. eEnd mRNum ndash 12 do PreviousYear From cte, (Wybierz mRNum max (rNum) z cte) deMax Gdzie rNum między mRNum ndash 23 a mRNum ndash 12 mdash Toczenie 12 miesięcy sum z wykorzystaniem SQL 2017 i ramki okna IF OBJECTID (lsquotempdb..ThisYearrsquo) NIE JEST TABELĄ NULL DROP ThisYear Z cte as (SELECT rNum ROWNUMBER () Over (kolejność według daty) Date Date Wartość Rolling12Months CASE WHEN ROWNUMBER () OVER (data przesłania) gt 11 THEN SUMA (Value) OVER (ORDER BY Data ROWS MIĘDZY PRECYZYJNYM I BIEŻĄCYM RZECZYWISTOŚCIĄ) END OF RollingTotalsExample) Wybierz tyRowNum ROWNUMBER () Over (kolejność według mRNum). . sStart mRNum ndash 11. eEnd mRNum do ThisYear Od cte, (Wybierz mRNum max (rNum) z cte) deMax Gdzie rNum między mRNum ndash 11 i mRNum Wybierz z ThisYear ty Left Join PreviousYear py on ty. tyRowNum py. pyRowNum Mogą to działać Irsquom nie blisko comp z Dostęp do sql teraz, więc mogę go przetestować (może być kilka błędów typossyntax). WYBIERZ T. DateKey, AVG (T. ValueField) OVER (ODER BY T. DateKey ASC MIĘDZY 365 PRECEDING I A BIEŻĄCĄ WIERSZĄ) JAK YMAValueField OD DataTable AS T ORDER BY T. DateKey ASC W przypadku, gdy AVG jest jedną z zagregowanych funkcji nieobsługiwanych z zasięgiem BETWEEN (wiem, że SUM jest obsługiwane). WYBIERZ T. DateKey, SUM (T. ValueField) OVER (ODER BY T. DateKey ASC MIĘDZY 365 PRECEDING I A BIEŻĄCY RZĘD) PRZYPADEK, KTÓRE DATEDIFF (DZIEŃ, StartDate, T. DateKey) lt 365 TO DATEDIFF (DZIEŃ, StartDate, T. DateKey) ELSE 365 END AS YMAValueField FROM DataTable AS T ORDER BY T. DateKey ASC Subskrybuj więcej artykułów Dwutygodniowe biuletyny pomagają wyostrzyć twoje umiejętności i dotrzymać ci kroku dzięki artykułom, ebookom i opiniom, które będą Cię informować. Chcesz więcej Subskrybuj nasz dwutygodniowy biuletyn Odwiedź naszą bibliotekę artykułów, aby odkryć wzorce i praktyki, których potrzebujesz, aby przejść do bardziej elastycznych metod dostarczania baz danych. Dowiedz się, jak zautomatyzować proces budowania, testowania i wdrażania zmian w bazie danych, aby zmniejszyć ryzyko i przyspieszyć cykl dostarczania. Najwyżej oceniane ostatnie artykuły w programowaniu T-SQL Powiązane artykuły Również w SQL Wraz z pojawieniem się baz danych NoSQL, które wykorzystują aspekty SQL do odpytywania i obejmują pełną transakcyjność, istnieje niebezpieczeństwo hierarchicznego charakteru modeli dokumentów danych, powodując fundamentalne konflikt z teorią relacyjną Poprosiliśmy naszego eksperta ds. relacji, Hugh Bin-Haada, aby wytyczył trudny obszar dla teoretyków baz danych. Czytaj dalej Również w SQL Server Każdy programista bazy danych SQL Server musi znać Funkcje systemowe. Są to zarówno wzniosłe (takie jak liczba wierszy lub tożsamości), jak i niedorzeczność (IsNumeric ()) Robert Sheldon zapewnia przegląd najczęściej używanych z nich. hellip Czytaj dalej Również w programowaniu T-SQL Aby móc w pełni wykorzystać katalog systemowy, aby dowiedzieć się więcej o bazie danych, musisz znać funkcje metadanych. Oszczędzają mnóstwo czasu i wpisują się podczas odpytywania metadanych. Po zapoznaniu się z tymi funkcjami, katalog systemowy wydaje się nagle prosty w użyciu, jak pokazuje Robert Sheldon w tym artykule. hellip Czytaj dalej Również w T-SQL Programming Seria argumentów z deweloperami, którzy upierają się, że fuzzy wyszukiwania lub sprawdzanie pisowni Zrobić to w ramach aplikacji, a następnie relacyjnej bazie danych zainspirowanej Phil Factor, aby pokazać, jak to się robi. Gdy baza danych musi znaleźć odpowiedni materiał z wyszukiwanych haseł wprowadzonych przez użytkowników, baza danych musi nauczyć się oczekiwać i radzić sobie z oczekiwanym i nieoczekiwanym hellipem Czytaj więcej copy 2005 - 2017 Red Gate Software Ltd Co sądzisz o nowym Simple Talk Give nam swoją opinię