Berechnung der Anzahl der gleichzeitigen Ereignisse in SQL

1.) Die Abfrage hat nicht fangen alle überlappungen - dies wurde behoben, indem die anderen Antworten schon.

2.) Der Datentyp der Spalten starttime und endtime ist timestamp. So Ihr WHERE - Klausel ist leicht falsch, zu:

BETWEEN '2011-11-02' AND '2011-11-03'

Dazu gehören '2011-11-03 00:00'. Die Obere Grenze muss ausgeschlossen.

3.) Entfernt den gemischten Fall syntax ohne Anführungszeichen. Nicht gequotete Identifizierer sind gegossen, um den unteren Fall automatisch. Um es einfach auszudrücken: am Besten nicht verwenden Sie groß-und Kleinschreibung von Bezeichnern in allen in PostgreSQL.

4.) Verwandelt sich die Abfrage auf die explizite JOIN-das ist immer vorzuziehen. Tatsächlich, ich habe es ein LEFT [OUTER] JOIN, weil ich will, zu zählen, fordert, dass keine überschneidungen mit anderen Aufrufe, zu.

5.) Vereinfacht die syntax ein bisschen zum erreichen dieses base-Abfrage:

SELECT t1.sid, count(*) AS ct
FROM   calls_nov t1
LEFT   JOIN calls_nov t2 ON t1.starttime <= t2.endtime
                        AND t1.endtime >= t2.starttime
WHERE  t1.starttime >= '2011-11-02 0:0'::timestamp
AND    t1.starttime <  '2011-11-03 0:0'::timestamp
GROUP  BY 1
ORDER  BY 2 DESC;

Diese Abfrage ist extrem langsam für einen großen Tisch, weil jede Zeile ab '2011-11-02' verglichen werden, um jede Zeile die gesamte Tabelle, die führt zu (fast) O(n2) Kosten.

Schneller

Können wir drastisch reduzieren die Kosten durch der Vorauswahl der möglichen Kandidaten. Wählen Sie nur die Spalten und Zeilen Sie benötigen. Ich mache das mit zwei CTE.

1. Wählen Sie Anrufe, beginnend am Tag in Frage. -> CTE x
2. Berechnen Sie die neuesten am Ende der Anrufe. (Unterabfrage in CTE y)
3. Wählen Sie nur Anrufe, die überschneidungen mit der gesamten Palette von CTE x. -> CTE y
4. Die Letzte Abfrage ist viel schneller als das Abfragen der großen zugrunde liegenden Tabelle.

WITH x AS (
    SELECT sid, starttime, endtime
    FROM   calls_nov
    WHERE  starttime >= '2011-11-02 0:0'
    AND    starttime <  '2011-11-03 0:0'
    ), y AS (
    SELECT starttime, endtime
    FROM   calls_nov
    WHERE  endtime >= '2011-11-02 0:0'
    AND    starttime <= (SELECT max(endtime) As max_endtime FROM x)
    )
SELECT x.sid, count(*) AS count_overlaps
FROM   x
LEFT   JOIN y ON x.starttime <= y.endtime
             AND x.endtime >= y.starttime
GROUP  BY 1
ORDER  BY 2 DESC;

Schneller noch

Ich habe ein real life table von 350.000 Zeilen mit überlappenden Beginn /Ende-Zeitstempel gleicht. Ich verwendet, die für eine quick benchmark. PostgreSQL 8.4, knappe Ressourcen, weil es eine test-DB. Indizes auf start und end. (Index auf die Spalte ID ist hier irrelevant.) Getestet mit EXPLAIN ANALYZE, best of 5.

Gesamt-Laufzeit: 476994.774 ms

CTE Variante:

Gesamt-Laufzeit: 4199.788 ms-das ist > den Faktor 100.

Nach dem hinzufügen eines mehrspaltigen index der form:

CREATE INDEX start_end_index on calls_nov (starttime, endtime);

Gesamt-Laufzeit: 4159.367 ms

Ultimate Speed

Wenn das nicht genug ist, gibt es eine Möglichkeit es zu beschleunigen noch eine andere Größenordnung. Anstelle des CTEs oben, materialisieren die temp-Tabellen und - das ist der entscheidende Punkt - erstellen Sie eine index auf den zweiten. Könnte so Aussehen:

Ausführen als einer Transaktion:

CREATE TEMP TABLE x ON COMMIT DROP AS   
    SELECT sid, starttime, endtime
    FROM   calls_nov
    WHERE  starttime >= '2011-11-02 0:0'
    AND    starttime <  '2011-11-03 0:0';

CREATE TEMP TABLE y ON COMMIT DROP AS
    SELECT starttime, endtime
    FROM   calls_nov
    WHERE  endtime >= '2011-11-02 0:0'
    AND    starttime <= (SELECT max(endtime) FROM x);

CREATE INDEX y_idx ON y (starttime, endtime); -- this is where the magic happens

SELECT x.sid, count(*) AS ct
FROM   x
LEFT   JOIN y ON x.starttime <= y.endtime
             AND x.endtime >= y.starttime
GROUP  BY 1
ORDER  BY 2 DESC;

Lesen über temporäre Tabellen in der Anleitung.

Ultimative Lösung

• Erstellen Sie eine plpgsql-Funktion, welches die Magie.

• Diagnostizieren, die typische Größe des temp-Tabellen. Erstellen Sie eigenständige und Messen:

  SELECT pg_size_pretty(pg_total_relation_size('tmp_tbl'));

• Wenn Sie größer sind als Ihre Einstellung für temp_buffers dann vorübergehend legen Sie Sie hoch genug, in Ihrer Funktion zu halten, sowohl Ihre temporären Tabellen im RAM. Es ist eine große Beschleunigung, die, wenn Sie nicht haben, um die swap disc. (Kann, muss zuerst die Verwendung von temporären Tabellen in der Sitzung wirksam sind.)

CREATE OR REPLACE FUNCTION f_call_overlaps(date)
  RETURNS TABLE (sid varchar, ct integer) AS
$BODY$
DECLARE
    _from timestamp := $1::timestamp;
    _to   timestamp := ($1 +1)::timestamp;
BEGIN

SET temp_buffers = 64MB'; -- example value; more RAM for temp tables;

CREATE TEMP TABLE x ON COMMIT DROP AS   
    SELECT c.sid, starttime, endtime  -- avoid naming conflict with OUT param
    FROM   calls_nov c
    WHERE  starttime >= _from
    AND    starttime <  _to;

CREATE TEMP TABLE y ON COMMIT DROP AS
    SELECT starttime, endtime
    FROM   calls_nov
    WHERE  endtime >= _from
    AND    starttime <= (SELECT max(endtime) FROM x);

CREATE INDEX y_idx ON y (starttime, endtime);

RETURN QUERY
SELECT x.sid, count(*)::int -- AS ct
FROM   x
LEFT   JOIN y ON x.starttime <= y.endtime AND x.endtime >= y.starttime
GROUP  BY 1
ORDER  BY 2 DESC;

END;
$BODY$   LANGUAGE plpgsql;

Nennen:

SELECT * FROM f_call_overlaps('2011-11-02') -- just name your date

Gesamt-Laufzeit: 138.169 ms-das ist Faktor 3000

Was kann man tun, um ihn zu beschleunigen?

Allgemeine performance-Optimierung.

CLUSTER calls_nov USING starttime_index; -- this also vacuums the table fully

ANALYZE calls_nov;

Ich nehme an, Sie wollen wissen, die Menge der aktiven Anrufe zu einem bestimmten Zeitpunkt. Andere Antworten geben Sie, wie viele andere Anrufe wurden aktiv, während der aktuelle Anruf aktiv war. Für sehr lange fordert, dies können Ihnen sehr hohe zahlen. Es wurde mir angedeutet, dass die Anzahl der aktiven Anrufe, was Sie wollte, von einem Ihrer Kommentare zu den anderen Antworten (außerdem arbeite ich auch in der Telekommunikation). Leider habe ich nicht genug Ruf zu kommentieren, die Antwort noch, als ich mein Konto erstellt um diese Frage zu beantworten. Um die Anzahl der aktiven Anrufe, könntest du eine variable um eins erhöht, wenn ein Anruf gestartet wird, und verringert sich um eins, wenn es endete. Ich habe getestet, die auf eine MySQL-Datenbank mit über 50 Millionen Aufrufe. Sorry wegen der syntax Unterschiede zwischen MySQL und pgsql.

Fügte ich temporäre Tabellen für die Geschwindigkeit, aber mit nur 2m Zeilen und Indizes, die Sie möglicherweise nicht erforderlich. MySQL kann nicht auf die gleiche temporäre Tabelle doppelt, so hatte ich zwei.

CREATE TEMPORARY TABLE a
SELECT sid, StartTime, EndTime 
FROM calls_nov
WHERE StartTime between '2011-11-02' and '2011-11-03';

CREATE TEMPORARY TABLE b
SELECT *
FROM a;

SET @i := 0;

SELECT *, @i := @i + c.delta AS concurrent
FROM (
  SELECT StartTime AS time, 1 AS delta
  FROM a
  UNION ALL
  SELECT EndTime AS time, -1 AS delta
  FROM b
  ORDER BY time
) AS c
ORDER BY concurrent DESC
;

Den inneren SELECT gibt zwei Spalten. Die Spalte Zeit enthält jede Startzeit und jede EndTime aus der ursprünglichen Tabelle (zweimal die Menge der Zeilen), und die delta-Spalte ist +1 oder -1, je nachdem, welche Spalte wurde in 'die Zeit'. Dieses set ist bestellt durch die Zeit, die wir dann Durchlaufen, die in der äußeren SELECT.

Anstelle von", UM DURCH gleichzeitige DESC" wie, Sie hatten in Ihrer Anfrage, ich würde den Einsatz eines zusätzlichen äußeren AUSWÄHLEN, wo ich Sie bekommen könnte MAX, MIN etc. Werte und ich konnte auch GROUP BY Datum, Stunde usw. Dieser Teil der Abfrage (ORDER BY Auger DESC), eigentlich wollte ich das nicht testen. Ich habe meinen eigenen Vorschlag mit einer zusätzlichen äußeren Abfrage, wie UM VON sich nicht wie erwartet in MySQL bei der Bestellung durch eine variable gesetzt wurde, in der gleichen WÄHLEN. Er befiehlt, indem der Vorherige Wert der Variablen statt. Wenn Sie unbedingt müssen, um durch gleichzeitige Anrufe (und pgsql hat das gleiche problem), ich glaube, Sie könnten dies umgehen, indem Sie erneut mit Hilfe einer zusätzlichen äußeren AUSWÄHLEN und bestellen dort.

Die Abfrage, die ich lief war sehr schnell! Es durchsucht jede temporäre Tabelle, die einmal, und dann die Kombination der beiden einst (mit weniger Daten pro Zeile), und für meine eigene version mit einer zusätzlichen äußeren Abfrage durchsucht werden durch die Kombination noch einmal und dann Gruppen. Jeder Tisch wird nur einmal gescannt! Dies wird alles im RAM wenn Ihre hardware-Konfiguration und ermöglicht es. Andere Antworten (oder Fragen) wird Ihnen helfen, wenn es nicht.