Horizonale statistken zu vertikal mit von bis

Fragen und Probleme rund um die Abfragespache SQL.
frastr
Benutzer
Beiträge: 33
Registriert: Di 9. Sep 2014, 12:20
Wohnort: Hoyerswerda, Sachsen

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von frastr » Fr 2. Jun 2017, 09:01

Hallo infromi,

nein das konnte meine erste Version noch nicht.

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

Code: Alles auswählen

insert into infromi values ( '2017-02-01', 'a', 1, 1);
Mit diesem sieht das Ergebnis mit dem alten select so aus:

Code: Alles auswählen

  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:

Code: Alles auswählen

when date - 1 != lag ( date) over ( order by id, date) then 1
Das Ergebnis sieht jetzt so aus:

Code: Alles auswählen

  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:

Code: Alles auswählen

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

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » Do 15. Jun 2017, 10:12

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.

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » Fr 16. Jun 2017, 17:56

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:

Code: Alles auswählen

-- 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.

frastr
Benutzer
Beiträge: 33
Registriert: Di 9. Sep 2014, 12:20
Wohnort: Hoyerswerda, Sachsen

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von frastr » Fr 16. Jun 2017, 22:17

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

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » Fr 16. Jun 2017, 22:46

Ja klar

Code: Alles auswählen


-- 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.

Code: Alles auswählen

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.

frastr
Benutzer
Beiträge: 33
Registriert: Di 9. Sep 2014, 12:20
Wohnort: Hoyerswerda, Sachsen

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von frastr » Sa 17. Jun 2017, 07:53

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

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » Sa 17. Jun 2017, 08:45

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

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » Sa 17. Jun 2017, 13:17

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

Code: Alles auswählen

"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

pogomips
Neuer Benutzer
Beiträge: 8
Registriert: Fr 18. Nov 2016, 09:55

Re: Horizonale statistken zu vertikal mit von bis

Beitrag von pogomips » So 18. Jun 2017, 09:12

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.

Antworten

Wer ist online?

Mitglieder in diesem Forum: Google [Bot] und 3 Gäste