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
Hallo simar,
unter Annahme folgender Testdaten:
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:
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:
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
bin mir nicht sicher, ob ich dich richtig verstanden habe…
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.
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
@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
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
****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)
bin mir nicht sicher, Dich zu verstehen; aber Dinge wie CASE existieren. Notfalls als Subselect / CTE.
Das mit case hab ich, aber ich schaffe die Gruppierung nach dem höchsten Wert von Spalte runde pro Name nicht
Ich kenne mich zuwenig aus um dir zu widerspechen, wie könnte dann eine Lösung aussehen dass ich zum Ziel komme.
Du beginnst damit die Max() Funktion für die Spalte Runde zu verwenden.
Dann ergänzt Du nach und nach die restlichen Spalten für die Ausgabe.
Wenn Du nicht weiterkommst, nimmst Du deine Beispieldaten von oben und postest sie erneut mit dem zugehörigen Wunschergebnis und dem Fehler, den Du bei Deinem Versuch erhälst.
Aktuelle Situation
die suche nach Max( Runde) bringt mir unteres Ergebnis anstatt die als Wunsch angefügte Spalte
select *
,max(runde)
from
(
with x as (select *,
div(row_number() over (partition by id_training order by timestamp )-1, 10) as runde
from t_transfer,
t_namen
where
id_name = id_namen
and id_namen = id_namen
and id_typ =2
and CAST(timestamp AS VARCHAR ) > ‘2020-03-%’
)
select
runde,
name,
vorname,
id_training
from x
) nn
group by
id_training,
runde,
name,
vorname
Runde |Name|Vorname|Training|max WUNSCH
“0” “Groll” “Oli” 3272 “0” |0
“1” “Groll” “Oli” 3272 “1” |0
“2” “Groll” “Oli” 3272 “2” |0
“3” “Groll” “Oli” 3272 “3” |1
“0” “Maier” “Horst” 3273 “0” |0
“1” “Maier” “Horst” 3273 “1” |0
“2” “Maier” “Horst” 3273 “2” |1
“0” “Müller” “Güni” 3274 “0” |0
“1” “Müller” “Güni” 3274 “1” |1
“2” “Müller” “Günther” 3274 “2”
Hast Du schon mal Code Tags benutzt? Irgendwo? Hast Du Dich noch nie gefragt, wie andere ordentlich aussehende und verständliche Beiträge posten?
Zu Deiner Abfrage:
Wie soll ein Maximum von etwas ermittelt werden, wenn der nicht maximierte Wert mit ausgegeben wird?
Max() kann dann nichts anderes anzeigen als den Wert aus der aktuellen Zeile.
Lasse die Spalte “Runde” am Anfang der Select Clause weg. Konkret:
Entferne Select *
Ersetze es durch
Select name, vorname, id_training
passe Group By entsprechend an.
Danke dir bei dem minnimierten Beispiel funktioniert es
hab aber übersehen dass dass ich noch diverse Bedingungen benötige die auf die Spalte Runde zugreifen
Mein Problem ich möchte etwas zusammenfassen und markieren und anschließend wieder trennen aber das geht so wohl nicht
muss mir etwas anderes überlegen
Hallo simar,
Das mit case hab ich, aber ich schaffe die Gruppierung nach dem höchsten Wert von Spalte runde pro Name nicht
Ich habe das mal so interpretiert, das du zusätzlichen Spalten möchtest, wo markiert ist:
Ich habe es an meinen obigen Beispiel-Daten gemacht.
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
),
ergebnisse_x2 as (
select timestamp, name, runde, zw_sum
, case when max(zw_sum) over (partition by name) = zw_sum then 1 end as best_pro_user
, case when max(zw_sum) over (partition by runde) = zw_sum then 1 end as best_pro_runde
, case when max(zw_sum) over () = zw_sum then 1 end as best_pro_all
from ergebnisse_x
where zw_flag = 1
)
select *
from ergebnisse_x2
order by timestamp;
Im Prinzip habe ich eine 2. CTE ergebnisse_x2 eingebaut, welche die nötigen “erweiterten” Auswertung auf Basis von dem 1. CTE ergebnisse_x vornimmt.
Das Ergebnis sieht dann so aus:
timestamp | name | runde | zw_sum | best_pro_user | best_pro_runde | best_pro_all
---------------------+---------+-------+--------+---------------+----------------+--------------
2020-01-01 10:03:21 | Meier | 1 | 29 | 1 | 1 |
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 | 1 | 1 | 1
2020-01-01 10:06:59 | Schulze | 1 | 24 | | |
2020-01-01 10:07:53 | Schulze | 2 | 29 | 1 | |
(6 Zeilen)
Gruß frastr