T Sql Moving Average Funktion

Einleitung Mit der Veröffentlichung von SQL Server 2016 Service Pack 1 ist die In-Memory ColumnStore-Technologie jetzt auch in den Standard-, Web - und sogar Express - und LocalDB-Editionen verfügbar. Neben dem Vorteil von nur 1 Codebasis zu pflegen, wird diese Änderung in der Politik auch eine klare Plattenspeicherplatz sparen aufgrund seiner hohen Daten-Deduplizierung und Kompression Ratios und nicht zuletzt seine auch eine ernsthafte Ad-hoc-Abfrage Leistung Booster Der Hauptunterschied zwischen den SQL-Varianten ist, wie viel CPU-Leistung und Speicher für Aufgaben wie den (Re-) Aufbau des Clustered ColumnStore Index zugewiesen werden. Zum Beispiel: Mit der Standard Edition wird ein einziger Core (max. 100 Prozessorzeit des sqlservr Prozesses) verwendet und das Abfragen eines CCI geschieht mit maximal 2 CPUs (MAXDOP2), abgesehen von der Nutzung aller verfügbaren CPUs in Enterprise Edition. Erstellen eines Cluster-ColumnStore-Index (CCI) mit SQL Server 2016 Standard Edition: Erstellen eines CCI mit allen 4 verfügbaren Cores mit SQL Server 2016 Enterprise Edition: Die Basiszeitpunkte für das Laden von 7.2 GB 60 Million Zeilen aus einer einzelnen TPCH-lineItem-Datei zeigen nicht viel Ein Unterschied zwischen den Aromen, wenn Bulk Einfügen der Daten direkt in eine Heap-Tabelle oder eine Tabelle mit einem CCI wird der Unterschied deutlich werden, wenn wir die Zeit zum Bau eines CCI auf einer Heap-Tabelle oder den Wiederaufbau eines CCI zu vergleichen: Zusammenfassend, die absolute Schnellste Weg, um Daten in einer Tabelle mit einem Clustered ColumnStore Index zu haben, ist: Laden in Heap erstellen die CCI anschließend mit SQL 2016 Ent. Ed. Direktes Laden in CCI Für Tabellen, in denen bereits ein Clustered ColumnStore Index erstellt wurde, stellen Sie sicher, dass Sie direkt in komprimierte Zeilengruppen streamen, um den Durchsatz zu maximieren. Um dies zu tun, sollte die Batch-Größe des Batches gleich oder größer als 100K Zeilen (genau 102400) sein. Kleinere Batches werden zuerst in komprimierte Deltastabellen geschrieben, bevor Tupel in die endgültigen komprimierten Row Group Segmente verschoben wird, was bedeutet, dass SQL Server die Daten zweimal berühren muss: Es gibt verschiedene Optionen zum Laden von Daten und wir gehen über die am häufigsten verwendeten Wie der Befehl Bulk Insert, BCP und SSIS. Lets sehen, was benötigt wird, um beste Leistung zu bekommen und wie zu überwachen 1) T-SQL-Bulk Insert Lassen Sie uns mit dem Befehl BULK INSERT starten: Prüfen der Anzahl der Zeilen, die bereits in die CCI geladen wurden, auch wenn Wird die Tabelle Sperren-Option verwendet, fragen Sie eine neue dmv mit dem Namen sys. dmdbcolumnstorerowgroupphysicalstats: Diese DMV wird auch zeigen, die möglichen Resource Group Staaten detaillierter beim Laden. Beim Laden von Daten gibt es vier mögliche Gruppengruppen. Wenn Sie sehen, dass der Zustand INVISBILE wie in der Abbildung unten bedeutet, dass Daten in eine RowGroup komprimiert werden. 1: OPEN160160160160160160160 (RowGroup akzeptiert neue Datensätze) 2: CLOSED160160160 (RowGroup wird gefüllt, aber noch nicht durch den Tupel-Mover-Prozess komprimiert) 3: COMPRESSED160 (RowGroup ist im Prozess, aus Daten im Delta - RowGroup wird gefüllt und komprimiert). 4 TOMBSTONE160 (RowGroup ist bereit, Müll gesammelt und entfernt werden) Durch die Festlegung der Batch-Größe mit einem Wert von 102400 oder höher erreichen Sie maximale Leistung und Daten werden gestreamt und direkt in seine endgültige RG komprimiert wird dieses Verhalten zeigt sich als COMPRESSED. Sie können auch ein DMV überprüfen, das mit SQL2014 eingeführt wurde, um den RowGroup-Status zu überprüfen, der die sys. columnstorerowgroups DMV ist: Testergebnis Masseneinfügung von Daten in eine Tabelle mit CCI über den Befehl Bulk Insert kann durch Hinzufügen der Batchsize102400 und TABLOCK-Optionen. Dies führt zu einer Verbesserung des Durchsatzes um 8. 2) BCP. exe Das BCP-Dienstprogramm wird noch ziemlich stark in vielen Produktionsumgebungen verwendet, damit es sich lohnt, es schnell zu überprüfen: standardmäßig sperrt das BCP 1000 Zeilen an der Zeit zu SQL Server. Die Zeit, die benötigt wird, um 7.2GB Daten über BCP zu laden: 530 Sekunden. Or160 113K rowssec Der RowGroup-Zustand zeigt NVISIBLE an, was bedeutet, dass bei den Standardeinstellungen der Delta Store verwendet wird. Um sicherzustellen, dass der BCP-Befehl die Daten direkt in die komprimierten RGs fließt, müssen Sie die Option batchsize b mit einem Wert von mindestens 102400 hinzufügen. Ich lief verschiedene Tests mit größeren Batchgrößen: bis zu 1048576, aber die 102400 gab mir am besten Ergebnis. BCP DB. dbo. LINEITEMCCI in F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock Der RowGroup-Zustand zeigt nun COMPRESSED an, was bedeutet, dass wir den Delta Store und die Datenströme in die komprimierten RGs umgehen: Ergebnis: das BCP Abgeschlossen in 457 Sekunden oder 133K Zeilen pro Sekunde oder Beim Testen bemerkte ich, dass die Standardeinstellungen von SSIS 2016 Speicherpuffergrößen verwenden, die möglicherweise auch die Batchgröße auf weniger als 100K Zeilen beschränken können. Im Beispiel unten sehen Sie, dass die Daten in den Deltaspeichern gelandet sind: Die RG-Zustände sind geschlossen und die deltastorehobtid-Felder werden gefüllt, was bedeutet, dass die Delta-Speicher genutzt werden. Dies war der Augenblick zu erreichen und zu überprüfen, mit meinen Kollegen, die glücklicherweise haben, um dies zu bemerken und eine Lösung ist bereits vorhanden (siehe: Data Flow Puffer Auto Sizing Fähigkeit Vorteile Datenbelastung in CCI). Um die CCI-Streaming-Fähigkeiten voll auszuschöpfen, müssen Sie die Einstellungen für den Standard-Speicher BufferSize amp MaxRows erhöhen: Ändern Sie diese in 10x größere Werte: 8211 DefaultMaxBufferRows von 10000 in 1024000 und das wichtigste: 8211 DefaultBufferSize von 10485760 in 104857600. Hinweis: Die neue AutoAdjustBufferSize-Einstellung sollte auf True gesetzt sein, wenn Sie sehr breite Datenzeilen laden. Ändern Sie auch die Werte für den Zieladapter: 8211 Zeilen pro Batch: 160 von keinem in 102400 8211 Maximale Einfügungs-Commitgröße: von 2147483647 in 102400 Die mit SQL Server 2016 SP1 eingeführte Feature-Parität eröffnet eine völlig neue Palette von Möglichkeiten, von Hoffnung zu profitieren Die oben beschriebenen Komplettlösungen helfen Ihnen, die Bulk Insert-, BCP - und SSIS-Leistung zu maximieren, wenn Sie Daten in einen Clustered-ColumnStore-Index laden. Dies ist der schnellste Weg, um Daten aus einer Flatfile in eine Tabelle in SQL Server 2016 zu laden Post auf diesem Thema vor vielen Jahren, die Einführung von In-Memory-optimierte Tabellen und aktualisierbar Columnstore Tabellenindizes. Auch die Liste der zu transportierenden Datentransportfahrzeuge wächst: Neben BCP, dem T-SQL-Bulk-Insert-Befehl, dem SSIS als ETL-Tool und PowerShell gibt es einige neue, wie PolyBase, External R Script oder ADF. In diesem Beitrag werde ich mit der Überprüfung beginnen, wie viel schneller die neue langlebige amp nicht dauerhaft In-Memory-Tabellen sind die Festlegung der Baseline Für diese Tests Im mit einem Azure DS4V2 Standard VM mit 8 cores28 GB RAM und 2 Festplatten mit Host-Caching RW aktiviert. (Beide Luns bieten 275 MBsec RW-Durchsatz, obwohl die GUI eine Grenze von 60MBsec angibt). Ich habe eine einzelne 60 Million row7.2 Gigabyte TPCH lineitem flache Datei als Daten zu laden. Als Baseline für den Vergleich verwenden wir die Zeit, die es braucht, um die Datei in eine Heap-Tabelle zu laden: Dieser reguläre Bulk Insert Befehl vervollständigt innerhalb von 7 Minuten mit einem Durchschnitt von 143K rowssec. Aktivieren der Testdatenbank für speicheroptimierte Tabellen Die in SQL20142016 enthaltenen In-Memory-Tabellen sind für sehr schnelle OLTP mit vielen kleinen Transaktionen und hohen Parallelität ausgelegt, was eine ganz andere Art von Arbeitsauslastung als Masseneinfügung ist Aus Kuriositäten gibt es einen Versuch Es gibt 2 Arten von In-Memory-Tabellen: langlebig und nicht haltbar Tabellen. Die dauerhafte werden persist Daten auf der Festplatte, die nicht haltbar diejenigen nicht. Um diese Option zu aktivieren, müssen wir eine Haushaltung durchführen und ein schnelles Datenträgervolumen für das Hosting dieser Dateien zuweisen. Ändern Sie zuerst die Datenbank, um die Option Enthält MEMORYOPTIMIZEDDATA zu aktivieren, gefolgt von dem Hinzufügen eines Dateipfads und einer Dateigruppe, die die speicheroptimierten Tabellen enthält: Die dritte Sache zu tun ist, einen separaten Speicherpool der SQL Server-Instanz hinzuzufügen, damit sie alle beibehalten kann Die Daten, die wir in In-Memory-Tabellen von seinem Default-Speicherpool laden: Binden einer Datenbank an einen Speicherpool Die folgenden Schritte, um einen separaten Speicherpool zu definieren und eine Datenbank an sie zu binden, sind nachfolgend aufgeführt: Zusätzliche Speicher-Pools werden über den Server verwaltet SQL Resource Governor. Der vierte und letzte Schritt besteht darin, die Testdatenbank mit dem Befehl sys. spxtpbinddbresourcepool an den neuen Speicherpool zu binden.160 Damit die Bindung wirksam wird, müssen wir die Datenbank offline nehmen und wieder online holen. Sobald sie gebunden sind, können wir dynamisch die Speichermenge ändern, die ihrem Pool über den Befehl ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80) zugewiesen ist. Bulk Insert in Durable In-Memory-Tabelle Nun sind alle mit der Option In-Memory aktiviert, können wir eine in-Memory-Tabelle erstellen. Jede speicheroptimierte Tabelle muss mindestens einen Index (entweder einen Range - oder einen Hash-Index) aufweisen, die vollständig (wieder) im Speicher zusammengesetzt sind und niemals auf der Festplatte gespeichert sind. Eine dauerhafte Tabelle muss einen deklarierten Primärschlüssel haben, der dann durch den erforderlichen Index unterstützt werden kann. Um einen Primärschlüssel zu unterstützen, fügte ich der Tabelle eine zusätzliche Rownumber-ROWID1-Spalte hinzu: Das Angeben einer Batchgröße von 1 (bis zu 5) Million Zeilen für das Bulk-Insert-Kommando hilft, Daten auf der Festplatte zu halten, solange das Bulk-Insert aktiv ist (anstatt zu speichern Alles am Ende) damit minimiert Speicherdruck auf den Speicher-Pool PookHK wir geschaffen. Die Datenbelastung in die dauerhafte In-Memory-Tabelle schließt in 5 Minuten 28 Sekunden oder 183K Rowssec ab. Das ist eine okay Zeit aber nicht so viel schneller als unsere Grundlinie. Betrachtet man die sys. dmoswaitstats zeigt, dass die no.1 waitstat IMPPROVIOWAIT ist, die auftritt, wenn SQL Server wartet auf eine Bulk-Last IO zu beenden. Betrachten des Leistungsindikators Bulk-Kopie Rowssec und Disk Write Bytessec zeigt das Spülen auf 275-MBsec-Plattenspitzen an, sobald eine Charge eingegangen ist (die grünen Spikes). Das ist das Maximum von dem, was die Platte liefern kann, aber nicht alles erklärt. Angesichts des geringen Gewinns werden wir diese für zukünftige Untersuchungen parken. Überwachen des Speicher-Pools Über die sys. dmresourcegovernorresourcepools dmv können wir überprüfen, ob unsere In-Memory-Tabelle den neu erstellten PoolHK-Speicher nutzt Pool: Der Ausgang zeigt an, dass die 7.2GB (einige extra für die Rowid) unkomprimiert in den Speicher geladen wurden PoolHk pool: Wenn Sie versuchen, mehr Daten zu laden, als Sie dem Pool zur Verfügung stehen, erhalten Sie eine korrekte Nachricht wie diese: Die Anweisung wurde beendet. Msg 701, Ebene 17, Status 103, Zeile 5 In dem Ressourcenpool 8216PookHK ist nicht genügend Arbeitsspeicher vorhanden, um diese Abfrage auszuführen. Um eine Ebene tiefer bei der Speicherplatzzuweisung auf einer Tabelle pro In-Speichertabelle auszusehen, können Sie die folgende Abfrage ausführen (aus dem SQL Server In-Memory OLTP-Interne für SQL Server 2016-Dokument): Die Daten, die wir gerade geladen haben, werden als Varheap-Struktur mit einem Hash-Index: Bisher so gut Jetzt können wir weitergehen und überprüfen, wie Staging in einer nicht dauerhaften Tabelle führt Bulk Insert in nicht-dauerhafte In-Memory-Tabelle Für IMND-Tabellen brauchen wir nicht einen Primärschlüssel, so dass wir nur Hinzufügen und Nicht-Cluster-Hash-Index und setzen DURABILITY SCHEMAONLY. Der Bulk-Einsatz Das Laden von Daten in die nicht dauerhafte Tabelle ist innerhalb von 3 Minuten mit einem Durchsatz von 335 K rowssec abgeschlossen (vs. 7 Minuten). Dies ist 2,3 mal schneller als das Einfügen in eine Heap-Tabelle. Herkömmlicherweise ist SSIS der schnellste Weg, um eine Datei schnell in SQL Server zu laden, da SSIS alle Daten verarbeiten wird, die vorverarbeitet werden, damit die SQL Server-Engine kann Verbringen ihre CPU-Ticks auf die Daten auf der Festplatte. Wird dies immer noch der Fall sein, wenn das Einfügen der Daten in eine nicht dauerhafte Tabelle Unter einer Zusammenfassung der Tests, die ich mit SSIS für diesen Post lief: die SSIS Fastparse-Option und160 die DefaultBufferMaxRows und DefaultBufferSize-Einstellungen sind die wichtigsten Performance Booster. Auch der Native OLE DB (SQLOLEDB.1) Provider führt etwas besser als der SQL Native Client (SQLNCLI11.1). Wenn Sie SSIS und SQL Server nebeneinander ausführen, wird die Erhöhung der Netzwerkpaketgröße nicht benötigt.160160 Net Ergebnis: ein grundlegendes SSIS-Paket, das eine flache Dateiquelle liest und die Daten direkt über ein OLE DB-Ziel in die Non-Durable-Tabelle schreibt Ähnelt dem Befehl Bulk Insert in eine IMND-Tabelle: Die 60 Millionen Zeilen werden in 2 Minuten 59 Sekunden oder 335 KB rowssec geladen, identisch mit dem Befehl Bulk insert. SSIS mit Balanced Data Distributor Aber wait8230160 die In-Memory-Tabellen sind entworfen, um Lock-Amp-Latch frei, so dass dies bedeutet, dass wir Daten auch über mehrere Streams laden können Das ist leicht zu erreichen mit SSIS der Balanced Data Distributor bringt genau das (die BDD Wird im Common-Abschnitt der SSIS-Toolbox aufgelistet) Hinzufügen der BDD-Komponente und Einfügen der Daten in die gleiche Non-Durable-Tabelle mit 3 Streams bietet den besten Durchsatz: Wir sind jetzt bis zu 526000 Rowssec Betrachten dieser sehr flachen Linie mit nur 160 der CPU-Zeit, die von SQLServer verwendet wird, scheint es, wir schlagen einige Engpass: Ich schnell versucht, kreativ zu sein durch die Nutzung der Modulo-Funktion und fügte 2 weitere Datenströme im Paket (jeder Verarbeitung 13 der Daten) 160, aber es ist nicht besser Viel (1 min52sec) so ein großes Thema für eine Zukunft zu untersuchen post160160 Die In-Memory Nicht-Durable Tabelle Option bringt einige ernsthafte Leistungsverbesserungen für das Staging von Daten laden Daten 1,5x schneller mit einem regulären Masseneinsatz und bis zu 3,6x mal schneller Mit SSIS. Diese Option, vor allem OLTP beschleunigen, kann auch einen großen Unterschied machen, um Ihr Batch-Fenster schnell schrumpfen (Fortsetzung) Ich muss eine rollende Summe über einen Zeitraum berechnen. Veranschaulichen Sie die AdventureWorks-Beispieldatenbank. Würde die folgende hypothetische Syntax genau das tun, was ich brauche: Leider kann die RANGE-Fensterrahmen-Ausdehnung derzeit nicht zulassen, ein Intervall in SQL Server. Ich weiß, ich kann eine Lösung mit einer Unterabfrage und eine reguläre (non-window) Aggregat schreiben: Angesichts der folgenden Index: Der Ausführungsplan ist: Während nicht schrecklich ineffizient, scheint es, wie es sollte möglich sein, diese Abfrage mit nur Fenster Aggregat auszudrücken Und analytische Funktionen, die in SQL Server 2012, 2014 oder 2016 (bisher) unterstützt werden. Zur Klarheit suche ich nach einer Lösung, die einen einzigen Durchlauf über die Daten durchführt. In T-SQL bedeutet dies, dass die OVER-Klausel die Arbeit erledigen wird, und der Ausführungsplan wird Fenster-Spools und Window Aggregate. Alle Sprachelemente, die die OVER-Klausel verwenden, sind Fairplay. Eine SQLCLR-Lösung ist akzeptabel, vorausgesetzt, es werden korrekte Ergebnisse erzielt. Für T-SQL-Lösungen gilt: Je weniger Hashes, Sorts und Window SpoolsAggregates im Ausführungsplan, desto besser. Fühlen Sie sich frei, Indizes hinzuzufügen, aber separate Strukturen sind nicht erlaubt (so dass keine vorberechneten Tabellen synchron mit Triggern gehalten werden, zum Beispiel). Referenztabellen sind erlaubt (Tabellen von Zahlen, Daten usw.) Idealerweise werden Lösungen genau die gleichen Ergebnisse in der gleichen Reihenfolge wie die Unterabfrage-Version oben produzieren, aber irgendetwas stimmt auch korrekt ist auch akzeptabel. Leistung ist immer eine Überlegung, so sollten Lösungen zumindest vernünftigerweise effizient sein. Dedizierter Chatroom: Ich habe einen öffentlichen Chatraum für Diskussionen zu dieser Frage und seinen Antworten erstellt. Jeder Benutzer mit mindestens 20 Rufpunkten kann direkt teilnehmen. Bitte ping mich in einem Kommentar unten, wenn Sie weniger als 20 Rep haben und möchte teilnehmen. Viele Grüße, Paul Ich habe ein paar verschiedene Ansätze, eine in T-SQL und eine in CLR. Der T-SQL-Ansatz kann folgendermaßen zusammengefasst werden: Nehmen Sie das Produktproduktprodukt in die Produktdatenbanken ein Merge in die beobachteten Verkaufsdaten Aggregieren Sie die Daten auf die Produktdateebene Berechnen Sie Rollsummen in den letzten 45 Tagen auf der Grundlage dieser Aggregatdaten (die irgendwelche enthalten Fehlende Tage ausgefüllt) Filtern Sie diese Ergebnisse nur zu den Produktdatepaarungen, die einen oder mehrere Verträge mit SET STATISTICS IO ON hatten. Dieser Ansatz berichtet Tabelle TransactionHistory. Scanzahl 1, logisch liest 484. was den einzelnen Durchgang über die Tabelle bestätigt. Als Referenz verwendet die ursprüngliche Schleifensuchabfrage Tabelle TransactionHistory. Scanzahl 113444, logisch liest 438366. Wie von SET STATISTICS TIME ON berichtet. Die CPU-Zeit ist 514ms. Dies ist für die ursprüngliche Abfrage günstiger als 2231 ms. Die CLR-Zusammenfassung kann wie folgt zusammengefasst werden: Lesen der Daten in den Speicher, sortiert nach Produkt und Datum Während der Verarbeitung jeder Transaktion, fügen Sie eine laufende Summe der Kosten hinzu. Wenn eine Transaktion ein anderes Produkt als die vorherige Transaktion ist, setzen Sie die laufende Summe auf 0 zurück. Pflegen Sie einen Zeiger auf die erste Transaktion, die das gleiche (Produkt, Datum) wie die aktuelle Transaktion hat. Wenn die letzte Transaktion mit dem (Produkt, Datum) angetroffen wird, berechnen Sie den Rollsumme für diese Transaktion und wenden Sie sie auf alle Transaktionen mit demselben an (Produkt, Datum). Rückgabe aller Ergebnisse an den Benutzer Verwenden von SET STATISTICS IO ON. Diese Vorgehensweise berichtet, dass keine logische IO aufgetreten ist Wow, eine perfekte Lösung (Eigentlich scheint es, dass SET STATISTICS IO nicht berichtet, IO in CLR entstanden. Aber aus dem Code ist es leicht zu sehen, dass genau ein Scan der Tabelle gemacht wird Und ruft die Daten in der Reihenfolge durch den Index Paul vorgeschlagen. Die CPU-Zeit ist jetzt 187ms. So berichtet von SET STATISTICS TIME ON. So ist dies eine ziemlich Verbesserung gegenüber dem T-SQL-Ansatz. Zwar ist die gesamte verstrichene Zeit der beiden Ansätze Sehr ähnlich bei etwa einer halben Sekunde. Allerdings hat die CLR-basierte Ansatz muss 113K Zeilen an die Konsole (vs. nur 52K für die T-SQL-Ansatz, dass Gruppen nach productdate), so dass deshalb Ive konzentrierte sich auf CPU-Zeit statt Ein weiterer großer Vorteil dieser Ansatz ist, dass es genau die gleichen Ergebnisse wie die ursprüngliche Loopseek-Ansatz, einschließlich einer Zeile für jede Transaktion auch in Fällen, in denen ein Produkt mehrmals verkauft wird am selben Tag (AdventureWorks, ich speziell verglichen Zeile Ergebnisse und bestätigten, dass sie sich mit der ursprünglichen Pauls-Abfrage verbinden.) Ein Nachteil dieses Ansatzes, zumindest in seiner gegenwärtigen Form, ist, dass er alle Daten im Speicher liest. Jedoch benötigt der Algorithmus, der nur streng entworfen wurde, den aktuellen Fensterrahmen zu einem beliebigen Zeitpunkt im Speicher und könnte aktualisiert werden, um für Datensätze zu arbeiten, die Speicher überschreiten. Paul hat diesen Punkt in seiner Antwort illustriert, indem er eine Implementierung dieses Algorithmus erzeugt, der nur das Schiebefenster speichert. Dies kommt auf Kosten der Erteilung höherer Berechtigungen für die CLR-Baugruppe, wäre aber auf jeden Fall wert, diese Lösung bis zu beliebig großen Datensätzen zu skalieren. T-SQL - ein Scan, gruppiert nach Datum Der Ausführungsplan Aus dem Ausführungsplan sehen wir, dass der von Paul vorgeschlagene Originalindex ausreicht, um einen einzelnen geordneten Scan von Production. TransactionHistory durchzuführen. Verwenden einer Zusammenführungsverknüpfung, um die Transaktionshistorie mit jeder möglichen Produktdatenkombination zu kombinieren. Es gibt einige wesentliche Annahmen, die in diesem Ansatz gebacken werden. Ich vermute, es wird bis zu Paul zu entscheiden, ob sie akzeptabel sind :) Ich benutze die Production. Product Tabelle. Diese Tabelle ist frei verfügbar auf AdventureWorks2012 und die Beziehung wird durch einen Fremdschlüssel aus Production. TransactionHistory erzwungen. So dass ich dies als faires Spiel interpretiert. Diese Vorgehensweise basiert auf der Tatsache, dass Transaktionen keine Zeitkomponente auf AdventureWorks2012 haben, wenn sie das getan haben, die Erzeugung der vollständigen Menge von Produktdate-Kombinationen wäre nicht mehr möglich, ohne zuerst einen Pass über die Transaktionshistorie. Ich produziere ein Rowset, das nur eine Zeile pro Produktdatum-Paar enthält. Ich denke, dass dies wohl stimmt und in vielen Fällen ein wünschenswerteres Ergebnis der Rückkehr ist. Für jedes productdate habe ich eine Spalte NumOrders hinzugefügt, um anzugeben, wie viele Verkäufe aufgetreten sind. Sehen Sie im folgenden Screenshot einen Vergleich der Ergebnisse der ursprünglichen Abfrage gegenüber der vorgeschlagenen Abfrage in Fällen, in denen ein Produkt mehrmals am selben Tag verkauft wurde (zB 319 2007-09-05 00: 00: 00.000) CLR - ein Scan , Full ungrouped result set Die Hauptfunktion body Es gibt nicht eine Tonne, um hier zu sehen Der Hauptteil der Funktion deklariert die Eingänge (die der entsprechenden SQL-Funktion entsprechen müssen), richtet eine SQL-Verbindung ein und öffnet den SQLReader. Ive getrennt die Hauptlogik, so dass es einfacher zu konzentrieren: Die folgende Logik könnte inline geschrieben werden, aber es ist ein wenig leichter zu lesen, wenn sie in ihre eigenen Methoden aufgeteilt werden. Binden alles zusammen in SQL Alles bis zu diesem Punkt wurde in C, so können sehen, die eigentliche SQL beteiligt. (Alternativ können Sie dieses Deployment-Skript verwenden, um die Assembly direkt aus den Bits meiner Assembly zu erstellen, anstatt sie selbst zu kompilieren.) Der CLR-Ansatz bietet viel mehr Flexibilität, den Algorithmus zu optimieren, und er könnte von einem Experten sogar noch weiter optimiert werden In C. Allerdings gibt es auch Nachteile der CLR-Strategie. Ein paar Dinge im Auge zu behalten: Diese CLR-Ansatz hält eine Kopie des Datensatzes im Speicher. Es ist möglich, einen Streaming-Ansatz verwenden, aber ich begegnete Anfang Schwierigkeiten und festgestellt, dass es ein hervorragendes Connect Problem beschwert, dass Änderungen in SQL 2008 machen es schwieriger, diese Art von Ansatz zu verwenden. Es ist immer noch möglich (wie Paul zeigt), sondern erfordert eine höhere Ebene der Berechtigungen, indem Sie die Datenbank als TRUSTWORTHY und die Gewährung EXTERNALACCESS an die CLR-Montage. So gibt es einige Mühe und potenzielle Sicherheit Implikation, aber die Auszahlung ist ein Streaming-Ansatz, der besser skalieren kann, um viel größere Datenmengen als die auf AdventureWorks. CLR kann für einige DBAs weniger zugänglich sein, so dass eine solche Funktion mehr von einer Black Box, die nicht so transparent ist, nicht so leicht modifiziert, nicht so leicht implementiert, und vielleicht nicht so leicht zu debuggen. Dies ist ein ziemlich großer Nachteil im Vergleich zu einem T-SQL-Ansatz. Bonus: T-SQL 2 - die praktische Ansatz-ID tatsächlich verwenden Nach dem Versuch, über das Problem kreativ für eine Weile denken, dachte ich auch post die ziemlich einfache, praktische Art und Weise, die ich wahrscheinlich wählen würde, um dieses Problem anzugehen, wenn es kam in Meine tägliche Arbeit. Es macht Gebrauch von SQL 2012-Fenster-Funktionalität, aber nicht in Art der bahnbrechenden Art und Weise, dass die Frage erhofft wurde: Dies ergibt tatsächlich eine ziemlich einfache Gesamtabfrage-Plan, auch wenn man auf die beiden beiden relevanten Abfragepläne zusammen: Ein paar Gründe Ich mag diesen Ansatz: Es liefert die vollständige Ergebnismenge angefordert in der Problem-Anweisung (im Gegensatz zu den meisten anderen T-SQL-Lösungen, die eine gruppierte Version der Ergebnisse zurückgibt). Es ist leicht zu erklären, zu verstehen und zu debuggen Ich werde nicht wiederkommen ein Jahr später und frage mich, wie die Heck Ich kann eine kleine Änderung ohne ruinieren die Korrektheit oder Leistung Es läuft in etwa 900ms auf dem bereitgestellten Datensatz, anstatt die 2700ms von Die ursprüngliche Loop-seek Wenn die Daten viel dichter waren (mehr Transaktionen pro Tag), wächst die Berechnungskomplexität nicht quadratisch mit der Anzahl der Transaktionen im Schiebefenster (wie es für die ursprüngliche Abfrage) Ich denke, das adressiert einen Teil von Pauls Sorge über wollte mehrere Scans vermeiden Es ergibt sich im Wesentlichen keine Tempdb IO in den letzten Updates von SQL 2012 aufgrund neuer tempdb faul schreiben Funktionalität Für sehr große Datensätze ist es trivial, die Arbeit in separate Chargen für jedes Produkt, wenn Speicherdruck aufgeteilt wurden Um ein Anliegen zu werden Ein paar potenzielle Einschränkungen: Während es technisch scannen Production. TransactionHistory nur einmal, seine nicht wirklich ein One-Scan-Ansatz, weil die Temp-Tabelle von ähnlicher Größe und müssen zusätzliche Logik IO auf dieser Tabelle als gut durchzuführen. Allerdings sehe ich dies nicht als zu unterschiedlich von einem Arbeitstisch, dass wir mehr manuelle Kontrolle haben, da wir seine genaue Struktur definiert haben Abhängig von Ihrer Umgebung könnte die Verwendung von tempdb als positiv angesehen werden (zB auf einem separaten Satz von SSD-Laufwerke) oder eine negative (hohe Parallelität auf dem Server, viele tempdb-Konkurrenz bereits) beantwortet September 8 15 am 15:41 Dies ist eine lange Antwort, so dass ich beschlossen, eine Zusammenfassung hier hinzufügen. Zuerst präsentiere ich eine Lösung, die genau das gleiche Ergebnis in der gleichen Reihenfolge wie in der Frage erzeugt. Es scannt die Haupttabelle 3 mal: um eine Liste der ProductIDs mit dem Datumsbereich für jedes Produkt zu erhalten, um die Kosten für jeden Tag zusammenzufassen (weil es mehrere Transaktionen mit demselben Datum gibt), um das Ergebnis mit den ursprünglichen Zeilen zu verbinden. Als nächstes vergleiche ich zwei Ansätze, die die Aufgabe zu vereinfachen und zu vermeiden, einen letzten Scan der Haupttabelle. Ihr Ergebnis ist eine tägliche Zusammenfassung, d. h. wenn mehrere Transaktionen auf einem Produkt das gleiche Datum haben, werden sie in eine Zeile gerollt. Mein Ansatz vom vorherigen Schritt scannt die Tabelle zweimal. Annäherung durch Geoff Patterson scannt die Tabelle einmal, weil er externes Wissen über die Strecke der Daten und der Liste der Produkte verwendet. Endlich stelle ich eine Single-Pass-Lösung, die wieder eine tägliche Zusammenfassung, aber es doesnt erfordern externe Kenntnisse über die Bandbreite der Daten oder die Liste der ProductIDs. Ich verwende AdventureWorks2014-Datenbank und SQL Server Express 2014. Änderungen an der ursprünglichen Datenbank: Geänderter Typ von Production. TransactionHistory. TransactionDate von datetime auf Datum. Die Zeitkomponente war ohnehin Null. Added calendar table dbo. Calendar Added Index to Production. TransactionHistory MSDN-Artikel über OVER-Klausel hat einen Link zu einem hervorragenden Blog-Post über Fenster-Funktionen von Itzik Ben-Gan. In diesem Beitrag erklärt er, wie OVER funktioniert, der Unterschied zwischen ROWS und RANGE Optionen und erwähnt dieses Problem der Berechnung einer rollenden Summe über einen Zeitraum. Er erwähnt, dass die aktuelle Version von SQL Server nicht implementieren RANGE in vollem Umfang und implementiert keine zeitlichen Intervall-Datentypen. Seine Erklärung für den Unterschied zwischen ROWS und RANGE gab mir eine Idee. Daten ohne Lücken und Duplikate Wenn die TransactionHistory-Tabelle Daten ohne Lücken und ohne Duplikate enthält, würde die folgende Abfrage korrekte Ergebnisse liefern: In der Tat würde ein Fenster mit 45 Zeilen genau 45 Tage umfassen. Termine mit Lücken ohne Duplikate Leider haben unsere Daten Termine. Um dieses Problem zu lösen, können wir eine Kalendertabelle verwenden, um einen Satz von Daten ohne Lücken zu erzeugen, und dann LEFT JOIN Originaldaten zu diesem Set zu verwenden und dieselbe Abfrage mit ROWS BETWEEN 45 PRECEDING und CURRENT ROW zu verwenden. Dies würde nur dann zu korrekten Ergebnissen führen, wenn sich Daten nicht wiederholen (innerhalb derselben Produkt-ID). Termine mit Lücken mit Duplikaten Leider haben unsere Daten sowohl Lücken in Daten und Termine können innerhalb der gleichen ProductID wiederholen. Zur Lösung dieses Problems können wir GROUP Originaldaten von ProductID, TransactionDate, um eine Reihe von Daten ohne Duplikate zu generieren. Dann verwenden Sie Kalender-Tabelle, um eine Reihe von Daten ohne Lücken zu generieren. Dann können wir die Abfrage mit ROWS BETWEEN 45 PRECEDING und CURRENT ROW verwenden, um rollende SUM zu berechnen. Dies würde zu korrekten Ergebnissen führen. Siehe Kommentare in der folgenden Abfrage. Ich bestätigte, dass diese Abfrage dieselben Ergebnisse wie der Ansatz aus der Frage erzeugt, die Unterabfrage verwendet. Erste Abfrage verwendet Unterabfrage, Sekunde - dieser Ansatz. Sie können sehen, dass Dauer und Anzahl der Lesungen ist viel weniger in diesem Ansatz. Die Mehrheit der geschätzten Kosten in diesem Ansatz ist die endgültige ORDER BY. siehe unten. Subquery-Ansatz hat einen einfachen Plan mit verschachtelten Schleifen und O (nn) Komplexität. Plan für diesen Ansatz scannt TransactionHistory mehrere Male, aber es gibt keine Schleifen. Wie Sie sehen können mehr als 70 der geschätzten Kosten ist die Sort für die endgültige ORDER BY. Top Ergebnis - Unterabfrage. Unten - ÜBER. Vermeiden von Extra-Scans Der letzte Index-Scan, Merge Join und Sort in dem Plan oben wird durch die endgültige INNER JOIN mit der ursprünglichen Tabelle verursacht, um das endgültige Ergebnis genau das gleiche wie ein langsamer Ansatz mit Unterabfrage zu machen. Die Anzahl der zurückgegebenen Zeilen ist dieselbe wie in der TransactionHistory-Tabelle. Es gibt Zeilen in TransactionHistory, wenn mehrere Transaktionen am selben Tag für dasselbe Produkt auftraten. Wenn es OK ist, nur tägliche Zusammenfassung im Ergebnis anzuzeigen, dann kann diese letzte JOIN entfernt werden und die Abfrage wird ein bisschen einfacher und ein bisschen schneller. Der letzte Index-Scan, der Zusammenführungs-Join und die Sortierung aus dem vorherigen Plan werden durch Filter ersetzt, der die von Kalender hinzugefügten Zeilen entfernt. TransactionHistory wird noch zweimal gescannt. Ein zusätzlicher Scan ist erforderlich, um den Bereich der Daten für jedes Produkt zu erhalten. Ich war interessiert zu sehen, wie es mit einem anderen Ansatz, wo wir externe Kenntnisse über die globale Reihe von Daten in TransactionHistory. Plus zusätzliche Tabelle Produkt, das alle ProductIDs hat, um diesen zusätzlichen Scan zu vermeiden. Ich entfernte Berechnung der Anzahl von Transaktionen pro Tag von dieser Abfrage, um Vergleich gültig zu machen. Es kann in beiden Abfragen hinzugefügt werden, aber Id wie es einfach zu vergleichen. Ich musste auch andere Daten verwenden, weil ich 2014-Version der Datenbank verwenden. Beide Abfragen liefern das gleiche Ergebnis in derselben Reihenfolge zurück. Hier sind Zeit und IO-Statistiken. Die Zwei-Scan-Variante ist ein bisschen schneller und hat weniger Lesevorgänge, da die One-Scan-Variante den Arbeitstisch viel nutzen muss. Außerdem erzeugt eine Scan-Variante mehr Zeilen als nötig, wie Sie in den Plänen sehen können. Es erzeugt Daten für jede ProductID, die sich in der Produkttabelle befindet, auch wenn eine ProductID keine Transaktionen hat. Es gibt 504 Zeilen in der Produkttabelle, aber nur 441 Produkte haben Transaktionen in TransactionHistory. Außerdem erzeugt es den gleichen Zeitraum für jedes Produkt, was mehr als erforderlich ist. Wenn die Transaktionshistorie eine längere Gesamtgeschichte aufweist, wobei jedes einzelne Produkt eine relativ kurze Historie aufweist, wäre die Anzahl der zusätzlichen nicht benötigten Zeilen noch höher. Auf der anderen Seite ist es möglich, die Zwei-Scan-Variante ein bisschen weiter zu optimieren, indem sie einen weiteren, engeren Index auf nur (ProductID, TransactionDate) erzeugt. Dieser Index würde verwendet werden, um StartEnd-Daten für jedes Produkt (CTEProducts) zu berechnen, und es würde weniger Seiten haben, als den Index zu decken und dadurch weniger Lesevorgänge verursachen. So können wir wählen, haben entweder einen extra expliziten einfachen Scan oder haben einen impliziten Arbeitstisch. BTW, wenn es in Ordnung ist, ein Ergebnis mit nur täglichen Zusammenfassungen haben, dann ist es besser, einen Index, der nicht ReferenceOrderID enthält. Es würde weniger Seiten weniger IO verwenden. Single Pass-Lösung mit CROSS APPLY Es wird eine wirklich lange Antwort, aber hier ist eine weitere Variante, die nur tägliche Zusammenfassung wieder zurückgibt, aber es nur einen Scan der Daten und es doesnt erfordern externe Kenntnisse über Bereich der Daten oder Liste der ProductIDs. Es tut nicht Zwischensortierungen als auch. Die Gesamtleistung ist ähnlich wie bei früheren Varianten, scheint aber ein bisschen schlechter zu sein. Die wichtigste Idee ist, eine Tabelle von Zahlen zu verwenden, um Zeilen zu erzeugen, die die Lücken in Daten füllen würden. Verwenden Sie für jedes vorhandene Datum LEAD, um die Größe der Lücke in Tagen zu berechnen, und verwenden Sie dann CROSS APPLY, um die erforderliche Anzahl von Zeilen in die Ergebnismenge hinzuzufügen. Zuerst versuchte ich es mit einer permanenten Tabelle der Zahlen. Der Plan zeigte eine große Anzahl von Lesungen in dieser Tabelle, obwohl die tatsächliche Dauer war ziemlich viel die gleiche, als wenn ich Zahlen auf der Fliege mit CTE generiert. Dieser Plan ist länger, da die Abfrage zwei Fensterfunktionen (LEAD und SUM) verwendet. Eine alternative SQLCLR-Lösung, die schneller ausgeführt wird und weniger Speicher benötigt: Das erfordert das EXTERNALACCESS-Berechtigungsset, da es eine Loopback-Verbindung zu dem Zielserver und der Datenbank anstelle der (langsamen) Kontextverbindung verwendet. Dies ist, wie die Funktion aufrufen: Erzeugt genau die gleichen Ergebnisse, in der gleichen Reihenfolge, wie die Frage. Profiler logische Lesevorgänge: 481 Der Hauptvorteil dieser Implementierung ist, dass sie schneller als die Kontextverbindung ist und weniger Speicher benötigt. Es hält nur zwei Dinge im Speicher zu einem beliebigen Zeitpunkt: Jede doppelte Zeilen (gleiche Produkt-und Transaktionsdatum). Dies ist erforderlich, weil entweder das Produkt oder das Datum ändert, wissen wir nicht, was die endgültige laufende Summe sein wird. In den Beispieldaten gibt es eine Kombination aus Produkt und Datum mit 64 Zeilen. Eine gleitende 45-Tage-Palette von Kosten und Transaktionsdaten nur für das aktuelle Produkt. Dies ist erforderlich, um die einfache laufende Summe für Zeilen einzustellen, die das 45-Tage-Schiebefenster verlassen. Diese minimale Zwischenspeicherung sollte sicherstellen, dass diese Methode sicherlich besser schneidet, als zu versuchen, den gesamten Eingangssatz im CLR-Speicher zu halten. Wenn Sie auf der 64-Bit-Enterprise-, Developer - oder Evaluation-Edition von SQL Server 2014 sind, können Sie In-Memory OLTP verwenden. Die Lösung wird nicht ein einziger Scan sein und wird kaum nutzen alle Fenster-Funktionen, aber es könnte etwas Wert auf diese Frage und der Algorithmus verwendet könnte möglicherweise als Inspiration für andere Lösungen verwendet werden. Zuerst müssen Sie In-Memory OLTP in der AdventureWorks-Datenbank aktivieren. Der Parameter für die Prozedur ist eine In-Memory-Tabellenvariable, die als Typ definiert werden muss. ID ist in dieser Tabelle nicht eindeutig, sie ist für jede Kombination von ProductID und TransactionDate eindeutig. Es gibt einige Kommentare in der Prozedur, die Ihnen sagen, was es tut, aber insgesamt ist es die Berechnung der laufenden Summe in einer Schleife und für jede Iteration es einen Lookup für die laufende Summe, wie es vor 45 Tagen (oder mehr) war. Der laufende Gesamtbetrag abzüglich der laufenden Summe, wie es vor 45 Tagen war, ist die rollende 45 Tage Summe, die wir suchen. Rufen Sie das Verfahren wie folgt auf. Testen Sie dies auf meinem Computer Client Statistics meldet eine Gesamtausführungszeit von etwa 750 Millisekunden. Für Vergleiche dauert die Unterabfrage-Version 3,5 Sekunden. Dieser Algorithmus könnte auch von regulären T-SQL verwendet werden. Berechnen Sie die laufende Summe unter Verwendung von Bereichszeilen und speichern Sie das Ergebnis in einer temporären Tabelle. Dann können Sie diese Tabelle mit einer Selbstverknüpfung zu der laufenden Summe abfragen, wie sie vor 45 Tagen war, und die Rollsumme berechnen. Allerdings ist die Implementierung der Bereich im Vergleich zu Zeilen ist ziemlich langsam aufgrund der Tatsache, dass muss Duplikate der Reihenfolge nach Klausel anders zu behandeln, so dass ich nicht bekommen, alle, die gute Leistung mit diesem Ansatz. Eine Problemumgehung könnte darin bestehen, eine andere Fensterfunktion wie lastvalue () über eine berechnete laufende Summe unter Verwendung von Zeilen zu verwenden, um einen Bereich, der die Summe simuliert, zu simulieren. Eine andere Möglichkeit ist die Verwendung von max () over (). Beide hatten einige Probleme. Finden Sie den entsprechenden Index zu verwenden, um zu vermeiden und Spulen mit der max () over () Version zu vermeiden. Ich gab auf, diese Dinge zu optimieren, aber wenn Sie sich für den Code interessiert sind, habe ich so weit bitte lassen Sie mich wissen. Antwort # 2 am: September 15, 2010, um 12:38 Uhr Nun, das war lustig :) Meine Lösung ist ein bisschen langsamer als GeoffPattersons aber ein Teil davon ist die Tatsache, dass Im binden an die ursprüngliche Tabelle, um eine von Geoffs Annahmen (dh eine Zeile pro eliminieren Produktdatenpaar). Ich ging mit der Annahme, das war eine vereinfachte Version einer endgültigen Abfrage und kann zusätzliche Informationen aus der ursprünglichen Tabelle. Hinweis: Im Kreditausleihe Geoffs Kalendertabelle und in der Tat am Ende mit einer sehr ähnlichen Lösung: Hier ist die Abfrage selbst: Grundsätzlich entschied ich, dass der einfachste Weg, um damit umzugehen war die Option für die ROWS-Klausel verwenden. Aber das erfordert, dass ich nur eine Zeile pro ProductID haben. TransactionDate Kombination und nicht nur das, aber ich musste eine Zeile pro ProduktID und mögliches Datum haben. Ich habe, dass die Kombination der Produkte, Kalender und TransactionHistory Tabellen in einem CTE. Dann musste ich einen weiteren CTE erstellen, um die rollenden Informationen zu erzeugen. Ich hatte dies zu tun, denn wenn ich es beigefügt die ursprüngliche Tabelle direkt habe ich Zeile Beseitigung, die meine Ergebnisse warf. Danach war es einfach, meinen zweiten CTE wieder an den ursprünglichen Tisch zu bringen. Ich fügte die TBE-Spalte (zu beseitigen), um loszuwerden, die leeren Zeilen in den CTEs erstellt. Auch habe ich ein CROSS APPLY in der ersten CTE, um Grenzen für meine Kalender-Tabelle zu generieren. Ich habe dann den empfohlenen Index hinzugefügt: Und bekam den endgültigen Ausführungsplan: EDIT: Am Ende fügte ich einen Index auf der Kalendertabelle, die Leistung durch eine angemessene Marge beschleunigt. Ich habe ein paar alternative Lösungen, die nicht verwenden Indizes oder Referenztabellen. Vielleicht könnten sie in Situationen nützlich sein, in denen Sie keinen Zugriff auf zusätzliche Tabellen haben und keine Indizes erstellen können. Es scheint, dass es möglich ist, korrekte Ergebnisse zu erhalten, wenn Gruppierung von TransactionDate mit nur einem einzigen Durchlauf der Daten und nur eine einzelne Fensterfunktion. Allerdings konnte ich nicht herausfinden, eine Möglichkeit, es zu tun, mit nur einem Fenster-Funktion, wenn Sie nicht gruppieren können durch TransactionDate. Um einen Bezugsrahmen zur Verfügung zu stellen, hat die ursprüngliche Lösung, die in der Frage geschrieben wurde, eine CPU-Zeit von 2808 ms ohne den Deckungsindex und 1950 ms mit dem Deckungsindex. Ich teste mit der AdventureWorks2014-Datenbank und SQL Server Express 2014. Beginnt mit einer Lösung für, wenn wir durch TransactionDate gruppieren können. Eine laufende Summe über die letzten X Tage kann auch folgendermaßen ausgedrückt werden: Laufende Summe für eine Zeile, die Summe aller vorherigen Zeilen ausführt - laufende Summe aller vorherigen Zeilen, für die das Datum außerhalb des Datumsfensters liegt. In SQL, eine Möglichkeit, dies auszudrücken ist, indem Sie zwei Kopien Ihrer Daten und für die zweite Kopie, multipliziert die Kosten mit -1 und Hinzufügen von X1 Tage an die Spalte Datum. Das Berechnen einer laufenden Summe über alle Daten wird die obige Formel implementieren. Ill zeigen dies für einige Beispieldaten. Unten ist ein Beispieldatum für eine einzelne ProductID. Ich vertrete Daten als Zahlen, um die Berechnungen einfacher zu machen. Startdaten: Fügen Sie eine zweite Kopie der Daten hinzu. Die zweite Kopie hat 46 Tage bis zum Datum und die Kosten multipliziert mit -1: Nehmen Sie die laufende Summe sortiert nach Datum aufsteigend und CopiedRow absteigend: Filtern Sie die kopierten Zeilen, um das gewünschte Ergebnis zu erhalten: Die folgenden SQL ist eine Möglichkeit, um die Über dem Algorithmus: Auf meiner Maschine dauerte dies 702 ms CPU-Zeit mit dem Deckungsindex und 734 ms CPU-Zeit ohne Index. Der Abfrageplan finden Sie hier: brentozarpastetheplanidSJdCsGVSl Ein Nachteil dieser Lösung ist, dass es scheint eine unvermeidbare Art bei der Bestellung durch die neue TransactionDate Spalte zu sein scheint. Ich denke nicht, dass diese Art durch das Hinzufügen von Indizes gelöst werden kann, weil wir zwei Kopien der Daten kombinieren müssen, bevor Sie die Bestellung. Ich konnte eine Art am Ende der Abfrage, indem Sie in einer anderen Spalte zu ORDER BY loszuwerden. Wenn ich von FilterFlag bestellt habe, habe ich festgestellt, dass SQL Server diese Spalte von der Sortierung optimieren und eine explizite Sortierung durchführen würde. Lösungen, wann wir eine Ergebnismenge mit doppelten TransactionDate-Werten für die gleiche ProductId zurückgeben müssen, waren viel komplizierter. Ich würde das Problem zusammenfassen, da es gleichzeitig erforderlich ist, durch die gleiche Spalte zu teilen und zu ordnen. Die Syntax, die Paul zur Verfügung gestellt, dass Problem, so ist es nicht überraschend, dass seine so schwer zu äußern, mit den aktuellen Fenster-Funktionen in SQL Server (wenn es nicht schwer zu sagen, es gäbe keine Notwendigkeit, die Syntax zu erweitern). Wenn ich die obige Abfrage ohne Gruppierung verwende, dann bekomme ich unterschiedliche Werte für die rollende Summe, wenn es mehrere Zeilen mit dem gleichen ProductId und TransactionDate gibt. Eine Möglichkeit, dies zu beheben, besteht darin, dieselbe laufende Summenberechnung wie oben zu tun, aber auch die letzte Zeile in der Partition zu markieren. Dies kann mit LEAD geschehen (vorausgesetzt, ProductID ist nie NULL) ohne zusätzliche Sortierung. Für den letzten laufenden Summenwert verwende ich MAX als Fensterfunktion, um den Wert in der letzten Zeile der Partition auf alle Zeilen in der Partition anzuwenden. Auf meiner Maschine dauerte das 2464ms CPU-Zeit ohne den Deckungsindex. Wie vorher scheint es eine unvermeidliche Art zu geben. Der Abfrageplan finden Sie hier: brentozarpastetheplanidHyWxhGVBl Ich denke, dass es Raum für Verbesserungen in der oben genannten Abfrage. Es gibt sicherlich andere Möglichkeiten, um Windows-Funktionen verwenden, um das gewünschte Ergebnis zu erhalten.