Wir erstellen eine Abfrage, um die Anzahl der Ereignisse pro Stunde und Tag zu zählen. An den meisten Tagen gibt es Stunden, die keine Aktivität haben und daher wird dort, wo die Abfrage ausgeführt wird, die Anzahl der Aktivitäten pro Stunde angezeigt, aber es gibt Lücken und die Abfrage schließt diese aus. Wir möchten immer noch die Stunden anzeigen, die keine Aktivität haben, und eine Null anzeigen, damit der Wert null angezeigt werden kann. Die Abfrage, die wir verwenden, sieht so aus ...
%Vor%Sie werden irgendwie eine Tabelle mit Tagen und Stunden benötigen, und dann müssen Sie einen äußeren Join zwischen dieser Tabelle und Ihrer Abfrage erstellen. So würde ich es machen. Beachten Sie, dass diese Lösung nur in SQL Server 2005 und 2008 funktioniert. Wenn Sie diese Plattformen nicht haben, müssen Sie tatsächlich eine Tabelle mit Zeiten in Ihrer Datenbank erstellen, aus der Sie sich anschließen können:
%Vor%Beachten Sie, dass die WITH-Anweisung am Anfang einen rekursiven allgemeinen Tabellenausdruck genannt wird und eine gute Methode zum Generieren von sequentiellen Tabellen mit relativ wenigen Elementen ist, wie Sie hier haben.
Zuerst habe ich eine Tabellenfunktion basierend auf der rekursiven allgemeinen Tabellenabfrage erstellt, die von beschrieben wird Dave Markle (Danke, dass du mir diesen Dave gezeigt hast!). Das ist extrem süß, weil ich die Funktion nur einmal ausführen muss und ich kann sie für die Analyse von Intervallen verwenden.
%Vor%Wenn Sie also aus dieser Funktion selbst auswählen, erhalten Sie eine Tabelle mit Zeitintervallen wie folgt:
fn_daterange ('12 / 14/2008 10:00:00 ', '12 / 14/2008 20:00:00', '01: 00: 00 ')
gibt zurück:
%Vor%Dann habe ich eine Beispieltabelle mit Ereignisdaten erstellt:
%Vor%Dann habe ich sie mit einem LEFT OUTER JOIN wie folgt verbunden:
%Vor%Holy Craps, das ist süß - ich kann das für alle Arten von Analysen bei der Arbeit verwenden! : -)
Danke Fred für die Frage und Dave für die Informationen über häufige Tabellenabfragen!
Ron
Wir hatten ein ähnliches Problem mit einer Performance-Monitoring-Software, aber da wir uns in einem DB2 / z-Mainframe-Shop befinden, haben wir es satt, SQL-Gymnastik zu machen, um solche Ergebnisse zu erzielen. SQL-Abfragen, die 'Funktionen' in jeder Zeile ausführen, die sie abrufen, sind notorisch nicht skalierbar, und die Datenbankadministratoren würden einen Feldtag über uns lachen, wenn wir versuchen würden, sie zu verwenden.
Stattdessen fanden wir es einfacher, das Datenbankschema so umzuformen, dass es eine Anzahl von Ereignissen in jeder Zeile enthält (anscheinend haben unsere DBAs nichts dagegen, mehr Speicherplatz zu verwenden, nur mehr CPU-Grunzen). In Ihrem Fall würde das eine Spalte namens tdm_quant hinzufügen, die Sie für jede einzufügende Zeile (d. H. Jedes Ereignis) auf 1 setzen würden.
Dann ändert sich das fünfte Feld Ihrer Anfrage von count(tdm_msg) nach sum(tdm_quant) , was dasselbe Ergebnis erzielt.
Zusätzlich können Sie einen speziellen Datensatz einfügen (einmal pro Stunde oder 24 davon zu Beginn eines jeden Tages, oder Sie können den gesamten Jahreswert am 1. Januar angeben, wenn Sie möchten), wo das Feld tdm_quant gesetzt ist bis Null. Da sie null sind, haben diese Datensätze keinen Effekt auf sum(tdm_quant) , aber Sie erhalten das von Ihnen gewünschte Verhalten. Eine Zeile wird für jede Stunde des Tages zurückgegeben, die als Total_ACTIVITIES null hat, wenn keine Ereignisse aufgetreten sind in dieser Stunde.
Der Rest Ihrer Abfrage muss nicht geändert werden.
Es klingt, als könnten Sie einen "linken äußeren Join" verwenden, indem Sie eine andere Tabelle verwenden, die die Zahlen 1 bis 24 enthält ...
Die grundlegende Antwort bezieht sich hier auf einen linken äußeren Join (LOJ) und eine explizite COUNT(column) , da NULL nicht gezählt wird, COUNT (*) jedoch alle Zeilen zählt. Der schwierige Teil erzeugt eine Tabelle, gegen die der LOJ ausgeführt werden kann. Die WITH-Klausel und die rekursive Lösung funktionieren in einer Reihe von DBMS (MS SQL Server anscheinend und fast sicher DB2 - wahrscheinlich auch andere).
Viele DBMS unterstützen temporäre Tabellen und gespeicherte Prozeduren; Die Kombination könnte verwendet werden, um eine Tabelle mit einem geeigneten Satz von Werten für das Feld Datum / Uhrzeit aufzufüllen, und dann den LOJ für diese Tabelle auszuführen (oder genauer gesagt FROM temp_table LEFT OUTER JOIN main_table ...). Nicht so sauber und ordentlich, aber funktioniert die meisten Orte.
Tags und Links sql gaps-and-islands