Kombinieren Zeilen, wenn die Endzeit der einen ist die Startzeit eines anderen (Oracle)

Ich kann einfach nicht scheinen, um diese Abfrage herausgefunden. Ich brauche kombinieren, um Reihen von Zeit-aufeinander folgende Staaten in einer einzigen Staates.

Diese Frage ist ähnlich der Frage gefunden, außer hier arbeite ich mit Oracle 10 nicht-SQL Server: Kombinieren Zeilen, wenn die Endzeit der einen ist die Startzeit eines anderen

Beispiel Daten:

name      start_inst         end_inst            code     subcode
Person1 9/12/2011 10:55 9/12/2011 11:49           161   50
Person1 9/12/2011 11:49 9/12/2011 11:55           107   28
Person1 9/12/2011 11:55 9/12/2011 12:07           161   50
Person1 9/12/2011 12:07 9/12/2011 12:26           161   50
Person1 9/12/2011 12:26 9/12/2011 12:57           161   71
Person1 9/12/2011 12:57 9/12/2011 13:07           161   71
Person1 9/12/2011 13:07 9/12/2011 13:20            52   50

Und ich möchte die folgende Ausgabe erhalten:

name       start_inst       end_inst            code     subcode
Person1 9/12/2011 10:55     9/12/2011 11:49     161     50
Person1 9/12/2011 11:49     9/12/2011 11:55     107     28
Person1 9/12/2011 11:55     9/12/2011 12:26     161     50
Person1 9/12/2011 12:26     9/12/2011 13:07     161     71
Person1 9/12/2011 13:07     9/12/2011 13:20     52      50

Hier ist Beispiel SQL:

CREATE TABLE Data (
    name varchar2(132 BYTE) not null,
    start_inst DATE not null,
    end_inst DATE not null,    
code number(3) not null,
subcode number(3) not null
);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 10:55','9/12/2011 11:49',161, 50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:49','9/12/2011 11:55',107,28);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:55','9/12/2011 12:07',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:07','9/12/2011 12:26',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:26','9/12/2011 12:57',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:57','9/12/2011 13:07',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 13:07','9/12/2011 13:20',52,50);

Vielen Dank im Voraus!

Um Ihr Beispiel SQL korrekt ausgeführt wird, möglicherweise die folgenden änderungen vornehmen: a) fügen Sie ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI'; vor dem INSERT-Anweisungen, so dass die Datums-Zeichenketten korrekt interpretiert werden (oder explizit TO_DATE mit diesem format in die INSERT-Anweisungen), und b) ändern Sie die INSERT-Anweisungen zu verwenden "subcode" statt "code2", so dass Sie Stimme mit der CREATE TABLE-Anweisung.
Pflege zu wählen und akzeptieren Sie die Antwort, die Ihnen geholfen?

InformationsquelleAutor ScottCollier | 2011-09-14

Vielleicht? (Ich habe keinen SQL-Maschine, um es zu laufen)

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY name                ORDER BY start_inst) NameSequenceID,
    ROW_NUMBER() OVER (PARTITION BY name, code, subcode ORDER BY start_inst) NameStateSequenceID,
    *
  FROM
    data
)
SELECT
  name,
  MIN(start_inst) start_inst,
  MAX(end_inst)   end_inst,
  code,
  subcode
FROM
  sequenced_data
GROUP BY
  name,
  code,
  subcode,
  NameSequenceID - NameStateSequenceID

Vielen Dank für diese. Die tatsächliche Abfrage, die ich arbeite, hat eine Menge mehr als diese, aber dies ist der Teil, den ich stecken. Ich weiß es zu schätzen.

InformationsquelleAutor MatBailie

3

Hier ist eine Lösung mit einer rekursiven Abfrage anstelle der analytischen Funktionen (wie vorgeschlagen von @wildplasser):
```
SELECT   name, code, subcode, MIN(start_inst) AS start_inst, MAX(end_inst) AS end_inst
FROM     (SELECT     name,
                     start_inst,
                     end_inst,
                     code,
                     subcode,
                     MIN(CONNECT_BY_ROOT (start_inst)) AS root_start
          FROM       data d
          CONNECT BY PRIOR name = name 
                 AND PRIOR end_inst = start_inst 
                 AND PRIOR code = code 
                 AND PRIOR subcode = subcode
          GROUP BY   name, start_inst, end_inst, code, subcode)
GROUP BY name, code, subcode, root_start;
```
Den connect by - Klausel in der inneren Abfrage werden die Daten zurückgegeben werden, die in einer hierarchischen Weise. connect_by_root gibt uns den Wert an der Wurzel jeder Verzweigung. Denn wir haben nicht ein guter Kandidat für eine start with Klausel, wir werden alle untergeordneten Zeilen (wo end_inst gleich eine weitere Zeile start_inst und alle anderen Spalten sind die gleichen) mehrfach: einmal als root und einmal (oder mehr) wie ein Zweig. Unter der min von der Wurzel beseitigt diese extra Zeilen, während er uns einen Wert für eine Gruppe auf, die in der äußeren Abfrage.

In der äußeren Abfrage, führen wir ein weiteres group by zu konsolidieren, die Reihen. Der Unterschied ist, dass, in diesem Fall, wir haben root_start es auch zu identifizieren, welche Zeilen aufeinanderfolgend sind und daher konsolidiert werden müssen.

InformationsquelleAutor Allan

Hier ist ein anderer Ansatz:

SELECT
    name,
    min(start_inst) AS start_inst,
    max(end_inst) AS end_inst,
    code,
    subcode
FROM
    (
        SELECT
            A.*,
            COUNT
            (
                CASE WHEN start_inst = previous_end_inst THEN NULL
                ELSE 1
                END
            )
            OVER
            (
                ORDER BY
                    start_inst,
                    name,
                    code,
                    subcode
            ) AS group_number
        FROM
            (
                SELECT
                    name,
                    start_inst,
                    end_inst,
                    LAG
                    (
                      end_inst
                    )
                    OVER
                    (
                        PARTITION BY
                            name,
                            code,
                            subcode
                        ORDER BY
                            start_inst
                    ) AS previous_end_inst,
                    code,
                    subcode
                FROM
                    data
            ) A
        ) B
GROUP BY
    name,
    code,
    subcode,
    group_number
ORDER BY
    group_number

Grundsätzlich:

Für jede Zeile Eine Unterabfrage findet die frühere Endzeit für die der gegebene name, code und subcode.
Für jede Zeile eine Unterabfrage B berechnet die "Gruppe Anzahl" -- eine laufende Zählung der vorhergehenden Zeilen (in der Reihenfolge der start_inst, name, code und subcode), wo die früheren Ende der Zeit berechnet, die in Schritt 1 ist nicht gleich der start-Zeit.
Der äußeren Abfrage Aggregate von Gruppe Anzahl.

Für besser oder schlechter, ist dieser Ansatz, im Gegensatz zu @stevo ist, wird eine neue "Gruppe", wenn es eine "Lücke" zwischen der Endzeit der einen Aufnahme und die Anfangszeit der nächsten. Zum Beispiel, wenn Sie waren, um eine Lücke zu schaffen zwischen 12:57 und 13:00 Uhr so...

UPDATE data
SET start_inst = TO_DATE('9/12/2011 13:00', 'MM/DD/YYYY HH24:MI')
WHERE start_inst = TO_DATE('9/12/2011 12:57', 'MM/DD/YYYY HH24:MI');

...die obige Abfrage würde return zwei Zeilen wie diese...

NAME                 START_INST       END_INST               CODE    SUBCODE
-------------------- ---------------- ---------------- ---------- ----------
.
.
.
Person1              09/12/2011 12:26 09/12/2011 12:57        161         71
Person1              09/12/2011 13:00 09/12/2011 13:07        161         71
.
.
.

...in der Erwägung, dass @stevo ' s query zurückgeben würde eine Zeile wie diese...

NAME                 START_INST       END_INST               CODE    SUBCODE
-------------------- ---------------- ---------------- ---------- ----------
.
.
.
Person1              12/09/2011 12:26 12/09/2011 13:07        161         71
.
.
.

Hoffe, das hilft.

Wenn Sie sagen, Stevo ' s Lösung, meinst du er stammt aus einer anderen Antwort? 😉
Die andere Lösung besser funktioniert für mich in dieser situation. Ich bin halten Sie diese für zukünftige Referenz, obwohl. Danke!
Eine wirklich clevere Lösung, mit sehr interessanten Techniken, die sollte nützlich sein, in einige andere ähnliche Probleme. Ich denke, es ist ein Fehler im code, aber, wo es heißt ORDER BY start_inst, name, code, subcode in der ZÄHLUNG, es sollte sagen ORDER BY name, code, subcode, start_inst, so imitiert, wird die Reihenfolge durch die LAG unten. Mit den Beispiel-Daten (modifiziert durch @brianc oben) gibt es keinen Unterschied in den Ergebnissen, sondern mit den real-Daten ich für mein problem diese änderung wurde notwendig, um die richtigen Ergebnisse. Danke!!!
vielen Dank für Ihren Kommentar. Die ORDER BY im COUNT beabsichtigt war, so dass wir (auch) es nutzen kann, um die gewünschte Reihenfolge der endgültigen Ergebnisse in der original-poster der Frage. Vielleicht ist das problem, das Sie konfrontiert sind, ist etwas anders? Zu imitieren, die LAG, man könnte hinzufügen, ein PARTITION BY zu den COUNT und bewegen Sie den name, code, subcode um es aus seiner ORDER BY. Sie müssen möglicherweise ändern Sie den ORDER BY in der äußeren Abfrage, obwohl...
OK, ich weiß, wo der Unterschied ist, in meinen Daten kann ich n code-subcodes zur gleichen Zeit aktiv und ich habe zu berücksichtigen. Wenn Sie INSERT INTO Data VALUES('Person1','9/12/2011 12:00','9/12/2011 12:01',99,99), wodurch diese situation in den OP-Daten, werden Sie sehen, dass 161-50 Zeilen beginnend um 11:55 und 12:07 nicht mehr in derselben Gruppe, obwohl Sie aufeinander, sondern meine änderung, um Ihren code legen Sie Sie wieder in der gleichen Gruppe. Also du hast Recht, es gibt keine Fehler in deinem code, nur unterschiedliche Daten. Danke für die Lösung & für die Zeit nehmen für meine Anliegen.
danke für die Erklärung.

InformationsquelleAutor Brian Camire

1

Anpassung desm s-Abfrage, ich denke, das sollte funktionieren
```
WITH
  sequenced_data AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY name                ORDER BY start_inst) NameSequenceID,
ROW_NUMBER() OVER (PARTITION BY name, code, subcode ORDER BY start_inst)     NameStateSequenceID,
d.*
FROM
data d
) 
SELECT
  name,
  to_char(MIN(start_inst),'DD/MM/YYYY HH24:MI') start_inst,
  to_char(MAX(end_inst),'DD/MM/YYYY HH24:MI')   end_inst,
  code,
  subcode
FROM
  sequenced_data
GROUP BY
  name,
  code,
  subcode,
  NameSequenceID - NameStateSequenceID
ORDER BY name,start_inst
```
- Vielen Dank für diese. Die tatsächliche Abfrage, die ich arbeite, hat eine Menge mehr als diese, aber dies ist der Teil, den ich stecken. Ich weiß es zu schätzen.
- Ich würde eigentlich empfehlen gegen diese. Es sollte keine Notwendigkeit, zu konvertieren, datetime, um ein string auf dem server. Wenn die Ergebnisse verwendet werden innerhalb des Servers, zu halten Daten ermöglicht eine einfachere manipulation. Wenn die Ergebnisse ar ebeing auf dem client benutzt, pass zurück, ein Datentyp, der nicht falsch verstanden wird, verwendet weniger Platz, ist leichter formatiert werden, wenn der Kunde änderungen in der Benutzeroberfläche, etc, etc. Es sei denn, es gibt sehr gute Gründe, string-Formatierung ist in der Regel am besten ekpt client-Seite.
- Ich würde Zustimmen, dass, nur versuchen zu veranschaulichen, die Ergebnisse
InformationsquelleAutor steve godfrey
0

Kannst du das mit einer rekursiven query (irgendwas mit VERBINDUNG VON /VOR in oracle, IIRC) habe ich die gleiche Sache für Postgres in diesem thread : Holen Sie total time Intervall von mehreren Zeilen, wenn die Sequenz nicht gebrochen

Müssen Sie möglicherweise ein bisschen Nacharbeiten, damit es passt in die oracle-syntax.

InformationsquelleAutor wildplasser

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.