Horizonale statistken zu vertikal mit von bis

Hallo damit wir die Datensaetze reduzueren möchten wir von horizonalen Statistiken auf tagesbasis auf verical mit von bis umstellen

Viele der Einträge sind ift gleich ausser das der Tag aendert

Zb

Date id count1 count2
01.01.2017 a 1 1
02.01.2017 a 1 1
03.01.2017 a 1 1
04.91.2017 a 2 1
05.91.2017 a 1 1
01.01.2017 b 2 2
02.01.2017 b 2 1


Gibt

Von bis id count1 coun2
01.01.2017 03.01.2017 a 1 1
04.01.2017 04.01.2017 a 2 1
05.01.2017 01.01.2017 b 2 2
02.01.2017 02.01.2017 a 2 1

Wir rechnen damit in einer Reduzierung um 3/4 der rows und damit massiv schnellete selects

Das selektieren sollte ja kein problem sein

Kann man dass mit window function nachen oder crosstab
Oder mit partition by

Also solange id und counts gleich sind und i und date feld in der serie vortag und 1 Tag

Besten dank

Man kann es drehen und wenden, aber “04.91.2017” aus Deinem Beispiel ist nun mal (in unserem Kalender) kein valides Datum.

test=*# select * from infromi ;
    date    | id | c1 | c2 
------------+----+----+----
 2017-01-01 | a  |  1 |  1
 2017-01-02 | a  |  1 |  1
 2017-01-03 | a  |  1 |  1
 2017-01-04 | a  |  2 |  1
 2017-01-05 | a  |  1 |  1
 2017-01-01 | b  |  2 |  2
 2017-01-02 | b  |  2 |  1
(7 Zeilen)

test=*# select min(date), max(date), id, c1, c2 from infromi group by 3,4,5;
    min     |    max     | id | c1 | c2 
------------+------------+----+----+----
 2017-01-01 | 2017-01-05 | a  |  1 |  1
 2017-01-02 | 2017-01-02 | b  |  2 |  1
 2017-01-04 | 2017-01-04 | a  |  2 |  1
 2017-01-01 | 2017-01-01 | b  |  2 |  2
(4 Zeilen)

test=*#

Ich bin mir aber nicht wirklich sicher, Dein Problem anhand Deines kapotten Beispiels wirklich verstanden zu haben …

Hallo das war ein typo
Batuerlich sollte das 04.01 sein


Aber mit min max geht das was uch will nicht
Darum habe ich ja auch bei der id a fuer deb 04.01 einen anderen wert fuer c1 genommen

Wennich meine Eintrage mit von bis abspeichern möchte dann sind die were c1 und c2 fuer id a ja nicht gültig vom 01.01 bis 05.01 sondern nur vom 01.01 bis 03.01
Fuer den 04.01 ist ja dann c1 2 und c2 1 gultig

Und die werte c1 1 und c2 1 sind erst wieder fuer den 05.01 gueltig

Dein Beispiel ist aber dann auch falsch, da fehlt im gezeigten Ergebnis der Eintrag für “05.91.2017 a 1 1”.

Ich gehe mal davon aus, daß das ein kontinuierlicher Prozess ist und Du eigentlich IMMER willst, daß auch bei neu eingegebenen Daten diese ‘Zusammenfassung’ schon gleich mit erfolgt, oder?

create table infromi2(von_bis daterange, id char, c1 int, c2 int);

Diese Tabelle hat nun gleich ein Feld von_bis, das das gültige Datum anzeigt. Ich gehe mal davon aus, daß Dir Range-Typen bekannt sind.

Für die Eingabe von Daten verwendest Du von nun an eine Funktion:

create or replace function insert_infromi2(in _date date, in _id char, in _c1 int, in _c2 int) returns bool as $$
declare r infromi2%rowtype;
        _von_bis daterange;
begin
  select * from infromi2 where von_bis @> _date - 1 and id = _id and c1 = _c1 and c2 = _c2 into r;
  if found then
    update infromi2 set von_bis = daterange(lower(r.von_bis),_date,'[]') where von_bis @> _date - 1 and id = _id and c1 = _c1 and c2 = _c2 ;
  else
    _von_bis := daterange(_date,_date+1);
    insert into infromi2 values(_von_bis,_id,_c1,_c2);
  end if;
return true;
end; $$language plpgsql;

Demo:

test=*# select insert_infromi2('2017-01-01'::date,'a',1,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-02'::date,'a',1,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-03'::date,'a',1,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-04'::date,'a',2,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-05'::date,'a',1,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-01'::date,'b',2,2);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select insert_infromi2('2017-01-02'::date,'b',2,1);
 insert_infromi2 
-----------------
 t
(1 Zeile)

test=*# select * from infromi2;
         von_bis         | id | c1 | c2 
-------------------------+----+----+----
 [2017-01-01,2017-01-04) | a  |  1 |  1
 [2017-01-04,2017-01-05) | a  |  2 |  1
 [2017-01-05,2017-01-06) | a  |  1 |  1
 [2017-01-01,2017-01-02) | b  |  2 |  2
 [2017-01-02,2017-01-03) | b  |  2 |  1
(5 Zeilen)

Ach ja: damit das ganze auch bei größeren Datenmengen noch performant ist, erstellen wir die Tabelle gleich mit passenden Index und einem Constraint, der verhindert, daß sich Einträge überlappen:

create table infromi2(von_bis daterange, id char, c1 int, c2 int, exclude using gist(von_bis with &&, id with =));

Das kann man noch zuende würzen, indem man dem User das Insert und Update - Recht auf die Tabelle entzieht und die Funktion als User mit diesen rechten erstellt und diese als security definer markiert. Auch den Rückgabewert der Funktion kann noch angepaßt werden, daß er z.B. anzeigt, ob es nun letztendlich ein Insert oder ein Update war.

Ausgangslage:

frastr@test.[local]# select * from infromi;
    date    | id | c1 | c2
------------+----+----+----
 2017-01-01 | a  |  1 |  1
 2017-01-02 | a  |  1 |  1
 2017-01-03 | a  |  1 |  1
 2017-01-04 | a  |  2 |  1
 2017-01-05 | a  |  1 |  1
 2017-01-01 | b  |  2 |  2
 2017-01-02 | b  |  2 |  1
(7 Zeilen)

Hier mein Vorschlag einer Lösung: Keine Ahnung ob das elegant oder effizient ist. Zumindest funktioniert es. Hoffe zumindest das es das gewünschte Ergebnis bringt. Und funktioniert mit reinem SQL:

with result as (
    with infromi_ext as (
        select  date, id, c1, c2
                , id||'|'||c1::text||'|'||c2::text test1
                , coalesce( lag ( id||'|'||c1::text||'|'||c2::text) over ( order by id, date), ''::text) test2
                , row_number() over ( order by id, date) rownumber
                , case
                    when id||'|'||c1::text||'|'||c2::text != coalesce( lag ( id||'|'||c1::text||'|'||c2::text) over ( order by id, date), ''::text) then 1
                    else 0
                  end as changeflag
            from infromi
    )
    select  main.*
            , (select max( rownumber) from infromi_ext where rownumber <= main.rownumber and changeflag = 1) as changerow
        from infromi_ext as main
        order by id, date
)
select  min( date) as fromdate
        , max( date) as todate
        , max( id) id
        , max( c1) c1
        , max( c2) c2
    from result
    group by changerow
    order by id, fromdate
;

Das Ergebnis:

  fromdate  |   todate   | id | c1 | c2
------------+------------+----+----+----
 2017-01-01 | 2017-01-03 | a  |  1 |  1
 2017-01-04 | 2017-01-04 | a  |  2 |  1
 2017-01-05 | 2017-01-05 | a  |  1 |  1
 2017-01-01 | 2017-01-01 | b  |  2 |  2
 2017-01-02 | 2017-01-02 | b  |  2 |  1
(5 Zeilen)

Wie funktioniert es:

  • Prinzipiell wird nach id und date in den Window Functions sortiert.


  • Dabei wird eine eindeutige Rownumber vergeben


  • Und ein Testfeld aus id, c1, c2 gebildet, um zu vergleichen ob sich was gegenüber dem vorherigen Datensatz was geändert hat. Dabei wird ein Feld changeflag befüllt, welches bei einer Änderung 1 sonst 0 ist


  • Das Ergebnis dieser Abfrage wird dann mehrfach verwendet um die aktuelle Gruppe zu ermitteln (mittlerer select)


  • Der unterste Select ermittelt dann fromdate und todate für eine Gruppe (id, c1, c2)

Nice :wink:

Thx :slight_smile:

genau es soll quasi immer eine zeile geschrieben werden von bis id und counter in welchem die werte sich nicht verändert haben

also statt das ich horizontal 0…x mal eine row habe habe ich eine zeile in der die id 1 vom 01.01-03.01 immer die gleichen counter gehabt hat

ändern sich die counter dann gibt es eine neue row und natürlich auch wenn es einen gab in der datumsreihe gibt (also wenn ein 1 tag fehlt)

@akretschmer die prozedure ist gut für neue einträge die dazukommen
es geht aber vorerst einmal darum eine bestehende tabelle mit schon x mio Datensätze in die neue Struktur zu migrieren

@frastr besten dank genau sowas habe ich gesucht und schon gedacht das es mit row_number oder mit window function hinzukriegen ist
die frage ist dann noch, ob es performantere varianten gibt

man könnte ja sowas auch über einen cursor in einer storedprocedure ausprogrammieren aber denke das wird sicherlich langsamer sein als die veriante mit row_number

könnte man auch was mit lag und window function mit PARTITION by machen?
denke das müsste auch gehen
muss mal mich damit befassen

könnte man auch was mit lag und window function mit PARTITION by machen?

Genau so macht es meine Variante.

Inwieweit das mit Millionen von Datensätzen noch funktioniert, muss man probieren.
Klingt ja danach, das es einmal umgestellt werden soll, und dann wird nur noch in das neue Tabellen-Design geschrieben.

Wobei es tricky werden kann mit der Pflege, wenn dann mal historische Daten importiert werden sollen. Wenn die Daten allerdings chronologisch kommt, sollte das insert or update relativ einfach sein.

@frastr

ja ist jetzt mal als Idee gedacht um die Unmengen von rows zu reduzieren und die Performance der Abfragen zu erhöhen
wir merken einfach das die SQL über die Index gehen aber das laden der Daten (falls noch nicht gecached) einfach unheimlich lange dauert und die Tabelle braucht halt auch jede Menge Platz und auch die Indexes

wir haben historische Daten und da ändert sich über die zeit nur der tag von der gleichen id die counter sind meistens die gleichen
so könnte man um ein xfaches reduzieren

ja mit der menge wird es sicherlich noch ein frage der performance

wir sind uns bewusst das wir dann auf bestehenden rows updates machen müssen etc und man da mehr kaputt machen kann als wenn man die neuen tage einfach hinten “anhängt”
aber der job selbst zum updaten und insereten sollte nicht komplex werden

bei deiner Abfrage ist das sichergestellt das wenn es eine Zeitlücke hat und die gleichen Werte dann mit einem neueren Datum für die gleiche ID kommen es eine neue Row gibt
?
das habe ich noch nicht gesehen?

Besten Dank

Hallo infromi,

nein das konnte meine erste Version noch nicht.

Für den Test habe ich einen neuen Datensatz angelegt:

insert into infromi values ( '2017-02-01', 'a', 1, 1);

Mit diesem sieht das Ergebnis mit dem alten select so aus:

  fromdate  |   todate   | id | c1 | c2
------------+------------+----+----+----
 2017-01-01 | 2017-01-03 | a  |  1 |  1
 2017-01-04 | 2017-01-04 | a  |  2 |  1
 2017-01-05 | 2017-02-01 | a  |  1 |  1
 2017-01-01 | 2017-01-01 | b  |  2 |  2
 2017-01-02 | 2017-01-02 | b  |  2 |  1
(5 Zeilen)

Man sieht das es mit in Zeile 3 einfließt. Durch eine zusätzliche Bedingung auf Zeitlücken habe ich einen Gruppenwechsel erzwungen:

when date - 1 != lag ( date) over ( order by id, date) then 1

Das Ergebnis sieht jetzt so aus:

  fromdate  |   todate   | id | c1 | c2
------------+------------+----+----+----
 2017-01-01 | 2017-01-03 | a  |  1 |  1
 2017-01-04 | 2017-01-04 | a  |  2 |  1
 2017-01-05 | 2017-01-05 | a  |  1 |  1
 2017-02-01 | 2017-02-01 | a  |  1 |  1
 2017-01-01 | 2017-01-01 | b  |  2 |  2
 2017-01-02 | 2017-01-02 | b  |  2 |  1
(6 Zeilen)

Nun bildet der Datensatz nach einer Zeitlücke eine eigene Gruppe (Zeile 4)

Hier noch der vollständige neue Select:

with result as (
    with infromi_ext as (
        select  date, id, c1, c2
                , id||'|'||c1::text||'|'||c2::text test1
                , coalesce( lag ( id||'|'||c1::text||'|'||c2::text) over ( order by id, date), ''::text) test2
                , row_number() over ( order by id, date) rownumber
                , case
                    when id||'|'||c1::text||'|'||c2::text != coalesce( lag ( id||'|'||c1::text||'|'||c2::text) over ( order by id, date), ''::text) then 1
                    when date - 1 != lag ( date) over ( order by id, date) then 1
                    else 0
                  end as changeflag
            from infromi
    )
    select  main.*
            , (select max( rownumber) from infromi_ext where rownumber <= main.rownumber and changeflag = 1) as changerow
        from infromi_ext as main
        order by id, date
)
select  min( date) as fromdate
        , max( date) as todate
        , max( id) id
        , max( c1) c1
        , max( c2) c2
    from result
    group by changerow
    order by id, fromdate
;

Gruß frastr

Ich bin zufällig über diesen Thread gestolpert und finde den Ansatz interessant. Ich hätte selber einen etwas anderen Ansatz, den ich noch posten werde.
Auch wenn es dem TE so erstmal nicht hilft, erlaube ich mir ein paar Fragen dazu zu stellen:

Mich würde interessieren, wie groß die Tabelle tatsächlich ist und ob sich der “Spareffekt” wirklich einstellt.
Ich gehe mal davon aus, die Werte A, B, Count… sind eher symbolisch zu verstehen, also nicht entsprechend der Realdaten.
Gibt es sowas wie ein Histogramm? Müsste ja, sonst wäre die erwartete Einsparung nicht zu berechnen.
Dann würde mich noch interessieren, wie das dargestellte SQL für die initale Umstellung “performt” und wie überhaupt diese Daten genutzt werden, also welche Art Abfragen, Reports darauf gefahren werden.

Ok, weiß nicht ob noch mal jemand hier drauf schaut, aber wie auch immer, ich habe auch einen Versuch gemacht:

Testdaten
Ich hab 1,2 Mio Testdaten erzeugt und die Statements ausprobiert. (alter i7 Laptop mit 9.5 oder so)
Die Testdaten haben einen unique Index über alle Spalten, damit Eindeutigkeit gegeben ist. Den Window Functions bringt das aber nicht viel.

ca 365 distinct Datumswerte über 1 Jahr verteilt, keine Lücken
ca 570 distinct ID Werte mit einigen Häufungen bei count1, count2
9 count1,count2 Kombinationen von 1:1 bis 3:3 mit max 185T Vorkommen bei 2:2
Transformiert bleiben 333T Datensätze übrig.


Testläufe:
Das Statement von oben habe ich irgendwann nach 45 min abgebrochen. Als bloßes count(*) hab ich nach ca 20 min abgebrochen.

Meine Version braucht ca 30 Sekunden:

-- edit: call once on session start
select nextval('group_flag');

select min(date) as aDate,                                                                      -- orig. Date
       case when sum(calc) = 0 
            then min(date) 
            else min(date)+ (sum(calc)-1)::int 
       end as zDate,                                                                            -- calc. Date  
       --sum(calc) as Just4Control, 
       id, count1, count2                                                                     -- other fields
  from ( select date, 
	        case when lgdate =1 or lddate =1 
	             then 1 else 0 end as calc,                               -- summed up later for follow up days
	        case when lgdate !=1 or lgdate is null and lddate =1 
	             then nextval('group_flag') 
	             else currval('group_flag') end as gflag,                                -- flag a group change   
	        lgdate, lddate,                                      -- forward/backward distance in number of days
	        id, count1, count2 
	   from ( select date, 
                   date - lag(date) over (partition by id, count1, count2 order by date)        as lgDate, 
                   lead(date)       over (partition by id, count1, count2 order by date) - date as ldDate,  
		         id, count1, count2 
		    from infromi
                ) x 
       ) y
 group by id, gflag, count1, count2
 order by 1,2,3,4,5

Die Beispieldaten aus dem Thread liefern das gleiche Ergebnis. Trotzdem sind die Ergebnisse nicht äquivalent, wenn man bspw. mit 10000 Datensätzen arbeitet.
D.h. mein Statement oder das im Thread oder beide sind unsauber. Ich habe deshalb einige Stichproben gemacht, aber keine Fehler gefunden.

Diese Lösung nutzt zwar auch Window Functions, es gibt aber ca 3 grobe Unterschiede:
1 die Textkoncatenierung durch Partition by ersetzt
2 die explizite “bisDatum” Verwendung vermieden und eine relative Angabe von “Anzahl Folgetagen” berechnet
3 Gruppenwechselerkennung durch Auswertung voriger/nächster Datensatz mit lag/lead und sequence

Die Nutzung von relativen Tagen statt expliziten Datum erlaubt eine einfache Berechnung per Count/Sum und einmaligem Group.

zu 1: Das Lag over Partition ist der Kern, das Lead habe ich noch dazugefrickelt für die Abgrenzung des Startzeitpunkts
zu 2: In der Ausgabe fällt das fast nicht auf, man kann aus dem Startdatzn und den relativen Tagen problemlos das “bisDatum” berechnen.
Bedeutet für eine Übernahme in die Ergebnistabelle, dass man für das bisDatum einfach ein Smallint für die Anzahl der Folgetage nehmen könnte.
Muss man sehen, wie es für darauf aufsetzendde Abfragen am besten passt, Indizierung usw.
zu 3: Auf die Schnelle ist mir nichts besseres eingefallen, als die Sequenz, geht wahscheinlich auch anders mit rownumber oder so, fraglich ob es dann beim one pass bleibt.

Würde mich freuen dazu noch mal was vom TE zu hören.

Könntest du mal das Skript zum Erstellen der Testdaten bitte bereits stellen.
Ich würde gerne auch mal den Test durchführen und verstehen wollen, wo genau der Unterschied liegt.

Danke om voraus

Gruß frastr

Ja klar

-- testtable orig
-- drop table infromi; drop sequence group_flag; 

create table infromi (date date, id char(3), count1 smallint, count2 smallint);
create sequence group_flag;
create unique index dtidx on infromi (date, id , count1 , count2 );
select nextval('group_flag');

-- dest table (new)
create table infromj (date date, days smallint, id char(3), count1 smallint, count2 smallint);

-- minimal test data 
insert into infromi (Date, id, count1, count2)
values 
('01.01.2017', 'a', 1, 1),
('02.01.2017', 'a', 1, 1),
('03.01.2017', 'a', 1, 1),
('04.01.2017', 'a', 2, 1),
('05.01.2017', 'a', 1, 1),
('01.01.2017', 'b', 2, 2),
('02.01.2017', 'b', 2, 1);

insert into infromi values ( '2017-02-01', 'a', 1, 1);

Das Script muss man je nach gewünschter Verteilung in den Random Faktoren anpassen. Das hier ist mein letzter Stand. Das Distinct ist etwas brutal, aber erfüllt seinen Zweck.

insert into infromi 
  select distinct * from (
  select date(date ('1991-01-01') + (((random()*364)::int)::text ||' days')::interval ) as aDate,
         x.*, 
         (random())::int+1+((random()*5)::integer)/4, 
         (random())::int+1+((random()*5)::integer)/4
    from generate_series(1,330) , 
         (select chr(ascii('A') + (random() * 1)::integer)||chr(ascii('A') + (random() * 10)::integer)||chr(ascii('A') + (random() * 25)::integer) as aID from generate_series(1,7100)) x
) u

Mich würde wie gesagt vor allem interessieren, wie das alte Mengengerüst und die Laufzeiten aussehen. Und daraus folgend, ob man das nicht in den Abfragen optimieren kann. Dieser Umbau in dem Thread hier, liefert per Definition nur den Faktor an Verbesserung, den die “Kompression” bringt, also 4, 5 oder sowas. Ggf kann man die original Abfragen mit original Struktur viel besser tunen und spart sich noch den Insert/Update Aufwand nach der Ummodellierung.

Super, vielen Dank für die schnelle Antwort. Ich persönlich werde aber erst frühestens nächste Woche zum probieren kommen.

Kein Problem, vielleicht findest Du ja was oder der TE.

Also die Differenz zwischen meinem und Deinem Ergebnis tritt auf, wenn am gleichen (Start?)Tag eine identische ID mit anderen Counts im gleichen Gesamtzeitraum anfällt. Wobei eine Count Kombi mindestens 2 Tage vorhanden sein muss und mindestens eine der anderen Count Kombis mindestens an einem Tag auftritt.

Mit einem 10T Datenset kann man ganz gut die beiden Mengen “online” vergleichen, ohne große Wartezeit auf die Antwort.

Hier ist ein Beispiel

"1991-07-23";"BCA";2;1
"1991-07-23";"BCA";2;2
"1991-07-24";"BCA";2;2
"1991-07-25";"BCA";2;2
"1991-07-26";"BCA";2;1
"1991-07-26";"BCA";2;2

Mir ist grad aufgefallen, dass die generierten Werte so sicher gar nicht zulässig sind.
Man muss also die beiden Counts Spalten aus dem unique Index rausnehmen und aus dem Distinct beim Insert.
Dann ist wahrscheinlich alles ok.