Order by timestamp

Fragen und Probleme rund um die Abfragespache SQL.
simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Order by timestamp

Beitrag von simar »

Hallo ich ab mal wieder ein für mich unlösbares Problem.
Ich habe x Personen von denen jeder 30 unterschiedlichen Einzelergebnissen produziert.
Diese Einzelergebnisse sollen nach laufender Nummer zu Zwischensummen aus 10 Einzelergebnissen summiert werden
Die Zwischensummen möchte ich nach dem Zeitstempel ordnen.

Ausgangspunkt:Tabelle mit folgenden Spalten
| ID | Name | Nummer | Timestamp | Einzelergebnis |


hoffe dass es verständlich rüberkommt

Gruß simar

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

Re: Order by timestamp

Beitrag von frastr »

Hallo simar,

unter Annahme folgender Testdaten:

Code: Alles auswählen

id|name|nummer|timestamp|einzelergebnis
1|Meier|1|01.01.2020 10:02:45|10
2|Meier|2|01.01.2020 10:03:03|11
3|Meier|3|01.01.2020 10:03:21|8
4|Meier|4|01.01.2020 10:03:39|9
5|Meier|5|01.01.2020 10:03:57|12
6|Meier|6|01.01.2020 10:04:15|6
7|Lehmann|1|01.01.2020 10:04:33|8
8|Lehmann|2|01.01.2020 10:04:52|9
9|Lehmann|3|01.01.2020 10:05:10|10
10|Lehmann|4|01.01.2020 10:05:28|12
11|Lehmann|5|01.01.2020 10:05:46|11
12|Lehmann|6|01.01.2020 10:06:04|8
13|Schulze|1|01.01.2020 10:06:22|9
14|Schulze|2|01.01.2020 10:06:40|7
15|Schulze|3|01.01.2020 10:06:59|8
16|Schulze|4|01.01.2020 10:07:17|10
17|Schulze|5|01.01.2020 10:07:35|11
18|Schulze|6|01.01.2020 10:07:53|8
würde mein 1. Versuch so aussehen:

Code: Alles auswählen

with ergebnisse_x as (
	select 	e.*
			, ((e.nummer - 1) / 3) + 1 as runde
			, case when mod(e.nummer, 3) = 0 then 1 else 0 end as zw_flag
	  		, sum(einzelergebnis) over (partition by name, (e.nummer - 1) / 3 order by timestamp) as zw_sum
		from ergebnisse e
)
select timestamp, name, runde, zw_sum
	from ergebnisse_x
 	where zw_flag = 1
  	order by timestamp;
Dabei habe ich die Abfrage auf 3 Ergebnisse pro "Runde" eingekürzt, um nicht zu viele Testdaten generieren zu müssen. Das heißt du müsstes die 3 in der Abfrage durch eine 10 tauschen.

Im Prinzip berechne ich für jede Zeile die Zwischensumme durch die Windowing Funktion in der with Klausel.
Dann filtere ich nur die gewünschten Zwischenstops raus.

Das Ergebnis sieht dann so aus:

Code: Alles auswählen

        timestamp        |  name   | runde | zw_sum
-------------------------+---------+-------+--------
 2020-01-01 10:03:21 | Meier   |     1 |     29
 2020-01-01 10:04:15 | Meier   |     2 |     27
 2020-01-01 10:05:10 | Lehmann |     1 |     27
 2020-01-01 10:06:04 | Lehmann |     2 |     31
 2020-01-01 10:06:59 | Schulze |     1 |     24
 2020-01-01 10:07:53 | Schulze |     2 |     29
(6 Zeilen)

Gruß frastr

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Order by timestamp

Beitrag von akretschmer »

bin mir nicht sicher, ob ich dich richtig verstanden habe...

Code: Alles auswählen

test=# create table simar(person int, ts timestamp, wert int);
CREATE TABLE
test=*# insert into simar select p.p, '2020-03-15 01:00:00'::timestamp + t.t * '10minutes'::interval, (random()*100)::int from (select * from generate_series(1,4)p) p cross join (select * from generate_Series(1,20)t) t;
INSERT 0 80
test=*# select * from simar ;

 person |         ts          | wert 
--------+---------------------+------
      1 | 2020-03-15 01:10:00 |   65
      1 | 2020-03-15 01:20:00 |   94
      1 | 2020-03-15 01:30:00 |   90
      1 | 2020-03-15 01:40:00 |   81
      1 | 2020-03-15 01:50:00 |   24
      1 | 2020-03-15 02:00:00 |   36
      1 | 2020-03-15 02:10:00 |   51
      1 | 2020-03-15 02:20:00 |   95
      1 | 2020-03-15 02:30:00 |   46
      1 | 2020-03-15 02:40:00 |   42
      1 | 2020-03-15 02:50:00 |   91
      1 | 2020-03-15 03:00:00 |   63
      1 | 2020-03-15 03:10:00 |   61
      1 | 2020-03-15 03:20:00 |   27
      1 | 2020-03-15 03:30:00 |   11
      1 | 2020-03-15 03:40:00 |   95
      1 | 2020-03-15 03:50:00 |   16
      1 | 2020-03-15 04:00:00 |   57
      1 | 2020-03-15 04:10:00 |   10
      1 | 2020-03-15 04:20:00 |   83
      2 | 2020-03-15 01:10:00 |   16
      2 | 2020-03-15 01:20:00 |   57
      2 | 2020-03-15 01:30:00 |   52
      2 | 2020-03-15 01:40:00 |   95
      2 | 2020-03-15 01:50:00 |   53
      2 | 2020-03-15 02:00:00 |   23
      2 | 2020-03-15 02:10:00 |   68
      2 | 2020-03-15 02:20:00 |   44
      2 | 2020-03-15 02:30:00 |   53
      2 | 2020-03-15 02:40:00 |   18
      2 | 2020-03-15 02:50:00 |   43
      2 | 2020-03-15 03:00:00 |   71
      2 | 2020-03-15 03:10:00 |   54
      2 | 2020-03-15 03:20:00 |   50
      2 | 2020-03-15 03:30:00 |   83
      2 | 2020-03-15 03:40:00 |   61
      2 | 2020-03-15 03:50:00 |   70
      2 | 2020-03-15 04:00:00 |   10
      2 | 2020-03-15 04:10:00 |   78
      2 | 2020-03-15 04:20:00 |    9
      3 | 2020-03-15 01:10:00 |   41
      3 | 2020-03-15 01:20:00 |   89
      3 | 2020-03-15 01:30:00 |   77
      3 | 2020-03-15 01:40:00 |   88
      3 | 2020-03-15 01:50:00 |   29
      3 | 2020-03-15 02:00:00 |   60
      3 | 2020-03-15 02:10:00 |   63
      3 | 2020-03-15 02:20:00 |    2
      3 | 2020-03-15 02:30:00 |   70
      3 | 2020-03-15 02:40:00 |   27
      3 | 2020-03-15 02:50:00 |   21
      3 | 2020-03-15 03:00:00 |   39
      3 | 2020-03-15 03:10:00 |   62
      3 | 2020-03-15 03:20:00 |   70
      3 | 2020-03-15 03:30:00 |    7
      3 | 2020-03-15 03:40:00 |    4
      3 | 2020-03-15 03:50:00 |    2
      3 | 2020-03-15 04:00:00 |   26
      3 | 2020-03-15 04:10:00 |   13
      3 | 2020-03-15 04:20:00 |    8
      4 | 2020-03-15 01:10:00 |    2
      4 | 2020-03-15 01:20:00 |    5
      4 | 2020-03-15 01:30:00 |   31
      4 | 2020-03-15 01:40:00 |   64
      4 | 2020-03-15 01:50:00 |   88
      4 | 2020-03-15 02:00:00 |   19
      4 | 2020-03-15 02:10:00 |   43
      4 | 2020-03-15 02:20:00 |   23
      4 | 2020-03-15 02:30:00 |   50
      4 | 2020-03-15 02:40:00 |   91
      4 | 2020-03-15 02:50:00 |   62
      4 | 2020-03-15 03:00:00 |   43
      4 | 2020-03-15 03:10:00 |   49
      4 | 2020-03-15 03:20:00 |   73
      4 | 2020-03-15 03:30:00 |   34
      4 | 2020-03-15 03:40:00 |   42
      4 | 2020-03-15 03:50:00 |   22
      4 | 2020-03-15 04:00:00 |   80
      4 | 2020-03-15 04:10:00 |   78
      4 | 2020-03-15 04:20:00 |   77
(80 rows)
test=*# select *, div(row_number() over (partition by person order by ts )-1, 5) from simar limit 25;;
 person |         ts          | wert | div 
--------+---------------------+------+-----
      1 | 2020-03-15 01:10:00 |   65 |   0
      1 | 2020-03-15 01:20:00 |   94 |   0
      1 | 2020-03-15 01:30:00 |   90 |   0
      1 | 2020-03-15 01:40:00 |   81 |   0
      1 | 2020-03-15 01:50:00 |   24 |   0
      1 | 2020-03-15 02:00:00 |   36 |   1
      1 | 2020-03-15 02:10:00 |   51 |   1
      1 | 2020-03-15 02:20:00 |   95 |   1
      1 | 2020-03-15 02:30:00 |   46 |   1
      1 | 2020-03-15 02:40:00 |   42 |   1
      1 | 2020-03-15 02:50:00 |   91 |   2
      1 | 2020-03-15 03:00:00 |   63 |   2
      1 | 2020-03-15 03:10:00 |   61 |   2
      1 | 2020-03-15 03:20:00 |   27 |   2
      1 | 2020-03-15 03:30:00 |   11 |   2
      1 | 2020-03-15 03:40:00 |   95 |   3
      1 | 2020-03-15 03:50:00 |   16 |   3
      1 | 2020-03-15 04:00:00 |   57 |   3
      1 | 2020-03-15 04:10:00 |   10 |   3
      1 | 2020-03-15 04:20:00 |   83 |   3
      2 | 2020-03-15 01:10:00 |   16 |   0
      2 | 2020-03-15 01:20:00 |   57 |   0
      2 | 2020-03-15 01:30:00 |   52 |   0
      2 | 2020-03-15 01:40:00 |   95 |   0
      2 | 2020-03-15 01:50:00 |   53 |   0
(25 rows)
test=*# with x as (select *, div(row_number() over (partition by person order by ts )-1, 5) from simar) select *, sum(wert) over(partition by person, div) from x limit 25;
 person |         ts          | wert | div | sum 
--------+---------------------+------+-----+-----
      1 | 2020-03-15 01:10:00 |   65 |   0 | 354
      1 | 2020-03-15 01:20:00 |   94 |   0 | 354
      1 | 2020-03-15 01:30:00 |   90 |   0 | 354
      1 | 2020-03-15 01:40:00 |   81 |   0 | 354
      1 | 2020-03-15 01:50:00 |   24 |   0 | 354
      1 | 2020-03-15 02:00:00 |   36 |   1 | 270
      1 | 2020-03-15 02:10:00 |   51 |   1 | 270
      1 | 2020-03-15 02:20:00 |   95 |   1 | 270
      1 | 2020-03-15 02:30:00 |   46 |   1 | 270
      1 | 2020-03-15 02:40:00 |   42 |   1 | 270
      1 | 2020-03-15 02:50:00 |   91 |   2 | 253
      1 | 2020-03-15 03:00:00 |   63 |   2 | 253
      1 | 2020-03-15 03:10:00 |   61 |   2 | 253
      1 | 2020-03-15 03:20:00 |   27 |   2 | 253
      1 | 2020-03-15 03:30:00 |   11 |   2 | 253
      1 | 2020-03-15 03:40:00 |   95 |   3 | 261
      1 | 2020-03-15 03:50:00 |   16 |   3 | 261
      1 | 2020-03-15 04:00:00 |   57 |   3 | 261
      1 | 2020-03-15 04:10:00 |   10 |   3 | 261
      1 | 2020-03-15 04:20:00 |   83 |   3 | 261
      2 | 2020-03-15 01:10:00 |   16 |   0 | 273
      2 | 2020-03-15 01:20:00 |   57 |   0 | 273
      2 | 2020-03-15 01:30:00 |   52 |   0 | 273
      2 | 2020-03-15 01:40:00 |   95 |   0 | 273
      2 | 2020-03-15 01:50:00 |   53 |   0 | 273
(25 rows)

Mit Zwischenschritten, zum besseren Nachvollziehen. Vielleicht hilft es ja.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

Hallo frastr vielen Dank für deine schnelle, übersichtliche, und reproduzierbare Hilfe
habe deine Lösung auf meine Tabellenstruktur angepasst und siehe da das Ergebnis
sieht genau so aus wie ich es brauche.


@ akreschmar werde mir deinen Lösungsweg auch anschauen und auf meine Daten umsetzen.
Auch dir Danke für deine Unterstützung

Danke und eine Corona freie Zeit

simar

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

@akretschmer
Jetzt hab ich noch eine Frage wie kann ich bei deiner Abfrage die Spalte "nr" hinzufügen


test=*# with x as (select *, div(row_number() over (partition by person order by ts )-1, 5) from simar) select *, sum(wert) over(partition by person, div) from x limit 25;
person | ts | wert | div | nr
--------+---------------------+------+-----
1 | 2020-03-15 01:10:00 | 65 | 0 | 1
1 | 2020-03-15 01:20:00 | 94 | 0 | 2
1 | 2020-03-15 01:30:00 | 90 | 0 | 3
1 | 2020-03-15 01:40:00 | 81 | 0 | 4
1 | 2020-03-15 01:50:00 | 24 | 0 | 5
1 | 2020-03-15 02:00:00 | 36 | 1 | 1
1 | 2020-03-15 02:10:00 | 51 | 1 | 2
1 | 2020-03-15 02:20:00 | 95 | 1 | 3
1 | 2020-03-15 02:30:00 | 46 | 1 | 4
1 | 2020-03-15 02:40:00 | 42 | 1 | 5
1 | 2020-03-15 02:50:00 | 91 | 2 | 1
1 | 2020-03-15 03:00:00 | 63 | 2 | 2
1 | 2020-03-15 03:10:00 | 61 | 2 | 3
1 | 2020-03-15 03:20:00 | 27 | 2 | 4
1 | 2020-03-15 03:30:00 | 11 | 2 | 5
1 | 2020-03-15 03:40:00 | 95 | 3
1 | 2020-03-15 03:50:00 | 16 | 3
1 | 2020-03-15 04:00:00 | 57 | 3
1 | 2020-03-15 04:10:00 | 10 | 3
1 | 2020-03-15 04:20:00 | 83 | 3
2 | 2020-03-15 01:10:00 | 16 | 0
2 | 2020-03-15 01:20:00 | 57 | 0
2 | 2020-03-15 01:30:00 | 52 | 0
2 | 2020-03-15 01:40:00 | 95 | 0
2 | 2020-03-15 01:50:00 | 53 | 0

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

Mein letzter Post hat sich erledigt, mit bisschen Abstand löst sich vieles.

Aber wie ich die Spalte div mit 1 beginnen lassen kann ist mir noch unklar, wenn ich 1+ row_number schreibe bekomme ich seltsamerweise nur 30 Zeilen anstatt der tatsächlichen 40 Zeilen

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

kann man die Lösung vom frastr um die Spalte flag erweitern wobei die flag "1" immer auf max(runde) group by Name gesetzt wird?

wäre super wenn da auch machbar wäre

Gruß Simar


with ergebnisse_x as (
select e.*
, ((e.nummer - 1) / 3) + 1 as runde
, case when mod(e.nummer, 3) = 0 then 1 else 0 end as zw_flag
, sum(einzelergebnis) over (partition by name, (e.nummer - 1) / 3 order by timestamp) as zw_sum
from ergebnisse e
)
select timestamp, name, runde, zw_sum
from ergebnisse_x
where zw_flag = 1
order by timestamp;

timestamp | name | runde | zw_sum |flag
-------------------------+---------+-------+--------............
2020-01-01 10:03:21 | Meier | 1 | 29 | 0
2020-01-01 10:04:15 | Meier | 2 | 27 | 1
2020-01-01 10:05:10 | Lehmann | 1 | 27 | 0
2020-01-01 10:06:04 | Lehmann | 2 | 31 | 1
2020-01-01 10:06:59 | Schulze | 1 | 24 | 0
2020-01-01 10:07:53 | Schulze | 2 | 29 | 1
(6 Zeilen)

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Order by timestamp

Beitrag von akretschmer »

bin mir nicht sicher, Dich zu verstehen; aber Dinge wie CASE existieren. Notfalls als Subselect / CTE.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

Das mit case hab ich, aber ich schaffe die Gruppierung nach dem höchsten Wert von Spalte runde pro Name nicht

simar
Benutzer
Beiträge: 92
Registriert: Di 9. Okt 2018, 17:40

Re: Order by timestamp

Beitrag von simar »

simar hat geschrieben:
Sa 21. Mär 2020, 11:47
Das mit case hab ich, aber ich schaffe die Gruppierung nach dem höchsten Wert von Spalte runde pro Name nicht

timestamp | name | runde | zw_sum |flag
-------------------------+---------+-------+--------............
2020-01-01 10:03:21 | Meier | 1 | 29 | 0
2020-01-01 10:04:15 | Meier | 2 | 27 | 1
2020-01-01 10:05:10 | Lehmann | 1 | 27 | 0
2020-01-01 10:06:04 | Lehmann | 2 | 31 | 0
2020-01-01 10:06:04 | Lehmann | 3 | 31 | 1
2020-01-01 10:06:59 | Schulze | 1 | 24 | 0
2020-01-01 10:07:53 | Schulze | 2 | 29 | 0
2020-01-01 10:06:59 | Schulze | 3 | 24 | 0
2020-01-01 10:07:53 | Schulze | 4 | 29 | 1

soll heisen der Wert von rund ist unterschiedlich

Antworten