Zusammenfassen von historisierten Daten

[funzel]

Hi,

brauch dringend Hilfa, da ich keine Idee mehr für mögl. Lösungswege habe.

Mal angenommen Daten liegen in historisierter Form vor und sollen jetzt auf die notwendigen Datenzeilen reduziert werden.

Daten in der Tabelle, die alle Möbel einer Wohnung enthält:
id startdatum enddatum was farbe beschreibung
1 1.1.2011 31.1.2011 sofa rot hart
2 1.2.2011 28.2.2011 sofa rot weich
3 1.3.2011 31.3.2011 sofa grün weich
4 1.4.2011 30.4.2011 sofa rot weich
5 1.5.2011 sofa rot hart

Im Ergebniss interessiert die Beschreibung und die ID nicht.
startdatum enddatum was farbe
1.1.2011 28.2.2011 sofa rot
1.3.2011 31.3.2011 sofa grün
1.4.2011 sofa rot

Wie kann ich aus der Tabelle das beschriebene Ergebniss generieren?

Mit min/max über das Datum (group by farbe) bin ich nicht zum Ziel gekommen und was besseres fällt mit nicht ein.

Bin dankbar für jede Idee!

/Funzel

[Wernfried]

Mit ist nicht ganz klar was du haben möchtest. Weshalb ist das Enddatum vom roten Sofa nicht der 30.4.2011? Das würde ich anhand deiner Beschreibung nämlich vermuten.

Gruss
Wernfried

[funzel]

Der letzte Datensatz (ohne Enddatum) ist der aktuell gültige Datensatz.

4 1.4.2011 30.4.2011 sofa rot weich
5 1.5.2011 sofa rot hart

wird also zu
1.4.2011 sofa rot

da ab dem 1.04. bis jetzt ein rotes Sofa in der Wohnung steht.

[Wernfried]

Kommst du mit dem Windowing-Clause der Analytischen Funktionen weiter:
Analytic Functions?

Gruss

[funzel]

Bis jetzt nicht so richtig.

first_value(startdatum) over(order by stardatum nulls last
range between farbe preceding and farbe following)

war der Versuch ausgehend vom aktuellen Datensatz eine Frame zu definieren, der durch die gleiche Farbe definiert ist. Also quasi nach vorn und nach hinten die Fraben zu vergleichen. Da kam aber totaler Käse zurück.

[Wernfried]

Ich hab's:

Mit DECODE(LAG(FARBE) OVER (ORDER BY ID), FARBE schaust du nach, ob sich die Farbe der vorherigen Zeile gegenüber der Farbe der aktuellen Zeile geändert hat. Wenn sich die Farbe ändert, nimmst du das aktuelle Datum, andernfalls das Datum der vorherigen Zeile. Beim Enddatum wird das Datum der aktuellen Zeile mit dem Datum der nachfolgenden Zeile verglichen.

[highlight=sql]SELECT DISTINCT
DECODE(LAG(FARBE) OVER (ORDER BY ID), FARBE, LAG(STARTDATUM) OVER (ORDER BY ID), STARTDATUM) AS STARTDATUM,
DECODE(LEAD(FARBE) OVER (ORDER BY ID), FARBE, LEAD(ENDDATUM) OVER (ORDER BY ID), ENDDATUM) AS ENDDATUM,
WAS, FARBE
FROM AA
ORDER BY 1[/highlight]

Wenn du das SQL Statement ein wenig abänderst erkennt man besser was gemacht wird:
[highlight=sql]SELECT ID,
DECODE(LAG(FARBE) OVER (ORDER BY ID), FARBE, LAG(STARTDATUM) OVER (ORDER BY ID), STARTDATUM) AS STARTDATUM,
DECODE(LEAD(FARBE) OVER (ORDER BY ID), FARBE, LEAD(ENDDATUM) OVER (ORDER BY ID), ENDDATUM) AS ENDDATUM,
WAS, FARBE
FROM AA
ORDER BY ID[/highlight]

ID    STARTDATUM    ENDDATUM    WAS    FARBE
1    01.01.2011    28.02.2011    sofa    rot
2    01.01.2011    28.02.2011    sofa    rot
3    01.03.2011    31.03.2011    sofa    grün
4    01.04.2011        sofa    rot
5    01.04.2011        sofa    rot

[funzel]

Super vielen Dank!
So ganz ist es das noch nicht. Für das Beispiel funktioniert es. Wenn ich aber mehr als zwei Zeilen zusammen fassen muss klappt es nicht mehr...

[highlight=sql]SELECT DISTINCT
with tab as(
select 1 ID, '1.1.2011' STARTDATUM, '31.1.2011' ENDDATUM, 'sofa' was, 'rot' farbe, 'hart' from dual union all
select 2 ID, '1.2.2011' STARTDATUM, '28.2.2011' ENDDATUM, 'sofa' was, 'rot' farbe, 'mittel' from dual union all
select 3 ID, '1.2.2011' STARTDATUM, '28.2.2011' ENDDATUM, 'sofa' was, 'rot' farbe, 'weich' from dual union all
select 4 ID, '1.3.2011' STARTDATUM, '31.3.2011' ENDDATUM, 'sofa' was, 'gruen' farbe, 'weich' from dual union all
select 5 ID, '1.4.2011' STARTDATUM, '30.4.2011' ENDDATUM, 'sofa' was, 'rot' farbe, 'weich' from dual union all
select 6 ID, '1.5.2011' STARTDATUM, '' ENDDATUM, 'sofa' was, 'rot' farbe, 'hart' from dual
) SELECT DISTINCT
DECODE(LAG(FARBE) OVER (ORDER BY ID), FARBE, LAG(STARTDATUM) OVER (ORDER BY ID), STARTDATUM) AS STARTDATUM,
DECODE(LEAD(FARBE) OVER (ORDER BY ID), FARBE, LEAD(ENDDATUM) OVER (ORDER BY ID), ENDDATUM) AS ENDDATUM,
WAS, FARBE
FROM tab
ORDER BY 1
[/highlight]

[ralfb]

Hallo, Funzel,

ich würde es so machen:

[highlight=sql]WITH daten AS (SELECT 1 id, DATE '2011-01-01' startdatum
, DATE '2011-01-31' enddatum, 'sofa' was,'rot' farbe
,'hart' beschreibung FROM dual
UNION ALL
SELECT 2,DATE '2011-02-01', DATE '2011-02-28', 'sofa','rot','weich'
FROM dual
UNION ALL
SELECT 3,DATE '2011-03-01', DATE '2011-03-31', 'sofa','grün','weich'
FROM dual
UNION ALL
SELECT 4,DATE '2011-04-01', DATE '2011-04-30', 'sofa','rot','weich'
FROM dual
UNION ALL
SELECT 5,DATE '2011-05-01', NULL,'sofa','rot','hart' FROM dual),
beginn AS (SELECT id,startdatum,enddatum,was,farbe
,CASE WHEN NVL(lag(enddatum) OVER (PARTITION BY was,farbe
ORDER BY startdatum),
DATE '2000-01-01') < startdatum-1
THEN row_number() OVER (ORDER BY id) END rn
FROM daten),
zusammen AS (SELECT startdatum,enddatum,was,farbe
,last_value(rn ignore nulls) OVER (PARTITION BY was,farbe
ORDER BY startdatum) grp
FROM beginn)
SELECT MIN(startdatum) startdatum
,CASE WHEN MAX(NVL(enddatum,DATE '2999-12-31'))=DATE '2999-12-31'
THEN NULL
ELSE MAX(enddatum) END enddatum
,was, farbe
FROM zusammen
GROUP BY grp,was,farbe ORDER BY was,grp;[/highlight]

Das sollte dann auch mit anderen Möbeln oder was auch immer klappen.

Schönes WE

Ralf

[Wernfried]

Ich habe auch noch was gefunden:

[highlight=sql]
WITH t1 AS
(SELECT ID, STARTDATUM, ENDDATUM, WAS, FARBE,
DECODE(LAG(FARBE) OVER (ORDER BY ID), FARBE, NULL, STARTDATUM) AS RED_STARTDATUM,
DECODE(LEAD(FARBE) OVER (ORDER BY ID), FARBE, NULL, ENDDATUM) AS RED_ENDDATUM
FROM T_Möbel_einer_Wohnung
ORDER BY ID),
t2 AS
(SELECT * FROM t1 WHERE RED_STARTDATUM IS NOT NULL OR RED_ENDDATUM IS NOT NULL)
SELECT DISTINCT WAS, FARBE,
NVL(RED_STARTDATUM, LAG(RED_STARTDATUM) OVER (ORDER BY ID)) AS STARTDATUM,
NVL(RED_ENDDATUM, LEAD(RED_ENDDATUM) OVER (ORDER BY ID)) AS ENDDATUM
FROM t2
ORDER BY STARTDATUM[/highlight]

Die Variante von Ralf bedingt, dass die Tage fortlaufend sind, d. h. es darf keine Lücken auf der Zeitachse geben. Ist das realistisch?

Gruss

[funzel]

Vielen Dank euch beiden!

Das mit der Zeitachse ist so eine Sache. Mann kann nicht davon ausgehen, dass sie durchgängig konsistent gefüllt ist. Was man mit den "Datenlöchern" macht hängt vom Anwendungsfall ab - Entweder erhalten oder interpolieren. Ich bin gerade dabei für beide Anwendungsfälle Skripte umzubauen.
War bis dato mit PL/SQL gelöst und super langsam. Ich hoffe das es mit den Abfragen die Skripte deutlich schneller laufen

Vielen Dank und noch eine schönes restliches WE

[ralfb]

Hallo, Wernfried,

Die Variante von Ralf bedingt, dass die Tage fortlaufend sind, d. h. es darf keine Lücken auf der Zeitachse geben.

Das stimmt nicht, ist ja ein < und kein = Startdatum - 1

Um die Möbelsammlung etwas realistischer zu machen, habe ich mal noch einen Sessel untergemischt und beim Sofa ein Datum vom 1.3. auf den 2.3. gestellt, um die Lücke zu erzeugen.

[highlight=sql]
WITH daten AS (SELECT 1 id, DATE '2011-01-01' startdatum
, DATE '2011-01-31' enddatum, 'sofa' was,'rot' farbe
,'hart' beschreibung FROM dual
UNION ALL
SELECT 2,DATE '2011-02-01', DATE '2011-02-28', 'sofa','rot','weich'
FROM dual
UNION ALL
SELECT 3,DATE '2011-03-02', DATE '2011-03-31', 'sofa','grün','weich'
FROM dual
UNION ALL
SELECT 4,DATE '2011-03-01', DATE '2011-03-31', 'sessel','grün','weich'
FROM dual
UNION ALL
SELECT 5,DATE '2011-04-01', DATE '2011-04-30', 'sofa','rot','weich'
FROM dual
UNION ALL
SELECT 6,DATE '2011-04-01', NULL , 'sessel','grün','weich'
FROM dual
UNION ALL
SELECT 7,DATE '2011-05-01', NULL,'sofa','rot','hart' FROM dual),
beginn AS (SELECT id,startdatum,enddatum,was,farbe
,CASE WHEN NVL(lag(enddatum) OVER (PARTITION BY was,farbe
ORDER BY startdatum),
DATE '2000-01-01') < startdatum-1
THEN row_number() OVER (ORDER BY id) END rn
FROM daten),
zusammen AS (SELECT startdatum,enddatum,was,farbe
,last_value(rn ignore nulls) OVER (PARTITION BY was,farbe
ORDER BY startdatum) grp
FROM beginn)
SELECT MIN(startdatum) startdatum
,CASE WHEN MAX(NVL(enddatum,DATE '2999-12-31'))=DATE '2999-12-31'
THEN NULL
ELSE MAX(enddatum) END enddatum
,was, farbe
FROM zusammen
GROUP BY grp,was,farbe ORDER BY was,grp
;[/highlight]
Als Ergebnis kommt das:

STARTDAT ENDDATUM WAS    FARB
01.03.11          sessel grün
01.01.11 28.02.11 sofa   rot
02.03.11 31.03.11 sofa   grün
01.04.11          sofa   rot

Kannst die Daten ja mal mit deiner Abfrage laufen lassen und mit dem Ergebnis vergleichen.

Gruß

Ralf