datesbereichschnitt in SQL

Ich habe eine Tabelle, in der jede Zeile ein Start- und Enddatum hat. Diese können beliebig kurz oder lang sein.

Ich möchte die Summe der Schnittpunkte aller Zeilen mit zwei Start- und Enddatumsdaten abfragen.

Wie können Sie das in MySQL tun?

Oder müssen Sie die Zeilen auswählen, die die Anfangs- und Endzeiten der Abfrage schneiden, dann die tatsächliche Überlappung jeder Zeile berechnen und sie clientseitig aufsummieren?


Um ein Beispiel zu geben, verwenden Sie Millisekunden, um es klarer zu machen:

Einige Zeilen:

ROW START STOP 1 1010 1240 2 950 1040 3 1120 1121 

Und wir möchten wissen, wie viel time diese Zeilen zwischen 1030 und 1100 hatten.

Lässt die Überlappung jeder Zeile berechnen:

 ROW INTERSECTION 1 70 2 10 3 0 

Also ist die Summe in diesem Beispiel 80.

Solutions Collecting From Web of "datesbereichschnitt in SQL"

Wenn dein Beispiel in der ersten Zeile 70 hätte sagen sollen

Annahme von @range_start und @range_end als Bedingungsparameter:

 SELECT SUM( LEAST(@range_end, stop) - GREATEST(@range_start, start) ) FROM Table WHERE @range_start < stop AND @range_end > start 

Unter Verwendung der größten / kleinsten und datesfunktionen sollten Sie in der Lage sein zu bekommen, was Sie direkt am datestyp arbeiten müssen.

Ich fürchte, du hast kein Glück.

Da Sie nicht wissen, wie viele Zeilen Sie "kumulativ schneiden", benötigen Sie entweder eine rekursive Lösung oder einen Aggregationsoperator.

Der Aggregationsoperator, den Sie benötigen, ist keine Option, weil SQL nicht den datatyp hat, an dem er arbeiten soll (dieser Typ ist ein Intervalltyp, wie in "timedaten und das relationale model" beschrieben).

Die rekursive Lösung mag möglich sein, aber es ist wahrscheinlich schwierig zu schreiben, anderen Programmierern schwer zu lesen, und es ist auch fraglich, ob der Optimierer diese Abfrage in die optimale datazugriffsstrategie umwandeln kann.

Oder ich habe deine Frage missverstanden.

Es ist eine ziemlich interessante Lösung, wenn Sie die maximale time kennen, die Sie jemals haben werden. Erstellen Sie eine Tabelle mit allen darin enthaltenen Zahlen von einer bis zu Ihrer maximalen time.

 millisecond ----------- 1 2 3 ... 1240 

Nennen Sie es time_dimension (diese Technik wird häufig in der Dimensionsmodellierung im Data Warehousing verwendet.)

Dann das:

 SELECT COUNT(*) FROM your_data INNER JOIN time_dimension ON time_dimension.millisecond BETWEEN your_data.start AND your_data.stop WHERE time_dimension.millisecond BETWEEN 1030 AND 1100 

… gibt Ihnen die Gesamtzahl der Millisekunden Laufzeit zwischen 1030 und 1100.

Ob Sie diese Technik verwenden können, hängt natürlich davon ab, ob Sie die maximale Anzahl an Millisekunden, die jemals in Ihren data enthalten sein wird, sicher vorhersagen können.

Dies wird oft in Data Warehousing verwendet, wie ich schon sagte; es passt gut zu einigen Arten von Problemen – zum Beispiel habe ich es für Versicherungssysteme verwendet, wo eine Gesamtzahl von Tagen zwischen zwei data benötigt wurde, und wo der gesamte datesbereich der data leicht zu schätzen war (von der frühestes Kundendatum der Geburt zu einem date ein paar Jahre in die Zukunft, über das Enddatum der Policen hinaus, die verkauft wurden.)

Vielleicht nicht für Sie arbeiten, aber ich dachte, es war es wert, als eine interessante Technik zu teilen!

Nachdem Sie das Beispiel hinzugefügt haben, ist es klar, dass ich Ihre Frage tatsächlich missverstanden habe.

Sie sind nicht "kumulative Zeilen".

Die Schritte, die Sie zu einer Lösung bringen werden, sind:

überschneiden Sie den Start- und Endpunkt jeder Zeile mit den angegebenen Start- und Endpunkten. Dies sollte möglich sein mit CASE-Ausdrücke oder etwas dieser Art, etwas im Stil von:

SELECT (CASE Startdatum <givenstartdate: givenstartdate, CASE startdate> = givenstartdate: startdate) als retainedstartdate (ebenfalls für enddate) als retainendendate FROM … Nullen und solche Sachen nach Bedarf sortieren.

Verwenden Sie mit dem retainedstartdate und retainedenddate eine datesfunktion, um die Länge des beibehaltenen Intervalls (das ist die Überlappung Ihrer Zeile mit dem angegebenen timeabschnitt) zu berechnen.

SELECT die SUM () von denen.