Kumulieren mit Bedingung

Ludwigmller

SQL-Guru
Beiträge
168
Hallo,
Eine View v gibt pro datum, flaechen_id und art jeweils Arbeitsstunden aus. Außerdem die zugehörige saison, da diese nicht mit den Jahresgrenzen übereinstimmt. Mit einer Window-Function ermittel ich die kumulierten Arbeitstunden je Saison.
Code:
SELECT
   datum,
   saison,
   art,
   SUM(t.stunden) oVER (PARTITION BY saison ORDER BY datum) AS kumuliert_ges
FROM
    view1 as v
GROUP BY saison, art, t.stunden, t.datum, t.flaechen_id, t.flaeche
order by datum
Nun möchte ich eine kumulierte Fläche berechnen, die t.flaeche summiert, allerdings nur einmal je flaechen_id und saison. Außerdem müssen mindestens 10 Arbeitsstunden für die flaechen_id und saison bis zu dem jeweiligen Datum kumuliert vorhanden sein .

Bsp.:
datumsaisonflaechen_idflaechestundenkumuliert_geskum_flaeche
20.12.2021​
2022​
1​
1​
8​
8​
0​
21.12.2021​
2022​
2​
2.5​
2​
10​
2.5​
21.12.2021​
2022​
1​
1​
1​
11​
2.5​
02.01.2022​
2022​
1​
1​
2​
13​
3.5​
02.01.2022​
2022​
2​
2.5​
1​
14​
3.5​
03.01.2023​
2023​
1​
1​
5​
5​
0​
03.01.2023​
2023​
2​
2.5​
5​
10​
0​
04.01.2023​
2023​
2​
2.5​
10​
20​
2.5​
Wie mache ich das am besten?
 
Werbung:
alles schwer nachvollziehbar. Dein Select scheint schon falsch, Du sagst FROM view1 as v, dann aber ein GROUP BY auf Spalten von t.

Wenn Du einfach eine Tabelle mit Beispieldaten und Wunschresultat zeigen könntest, wäre es einfacher ...
 
Sorry es muss alles der selbe Alias, also v sein.
v hat die Spalten datum, saison, flaechen_id, flaeche und stunden.
Wunschresultat sind die Spalten kumuliert_ges und kum_flaeche
 
Nachfolgend ein reproduzierbares Beispiel. Das Prinzip sollte deutlich werden, wenn nicht kann ich noch mehr Daten einfügen. Die eingangs erwähnte art wird vereinfacht außenvorgelassen.
https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/7850

Code:
    CREATE TABLE example (
      datum DATE NOT NULL,
      saison INTEGER NOT NULL,
      flaechen_id INTEGER NOT NULL,
      flaeche NUMERIC(5,2) NOT NULL,
      stunden INTEGER NOT NULL
    );
 
    create table result (
      datum date,
      saison integer,
      kumuliert_gesamt numeric(4,1),
      kumuliert_flaeche numeric(3,1)
    )
 
    INSERT INTO example (datum, saison, flaechen_id, flaeche, stunden)
    VALUES
      ('2021-12-20', 2022, 1, 1.0, 8),
      ('2021-12-21', 2022, 2, 2.5, 2),
      ('2021-12-21', 2022, 1, 1.0, 1),
      ('2022-01-02', 2022, 1, 1.0, 2),
      ('2022-01-02', 2022, 2, 2.5, 1),
      ('2022-01-03', 2022, 2, 2.5, 5),
      ('2022-01-04', 2022, 2, 2.5, 5),
      ('2022-01-05', 2022, 2, 2.5, 4),
   
      ('2023-01-03', 2023, 1, 1.0, 5),
      ('2023-01-03', 2023, 2, 2.5, 5),
      ('2023-01-04', 2023, 2, 2.5, 10),
      ('2023-01-05', 2023, 1, 1.0, 15);
   
insert into result(datum, saison, kumuliert_gesamt, kumuliert_flaeche) values
('2021-12-20', 2022, 8, 0), -- keine flache_id mit >= 10h in Saison 2022 bisher
('2021-12-21', 2022, 11, 0), -- keine flache_id mit >= 10h in Saison 2022 bisher
('2022-01-02', 2022, 14, 1), --flaechen_id=1 11>=10h in Saison 2022 (flache=1.0)
('2022-01-03', 2022, 19, 1), --nur flaechen_id=1 ist >=10h, wurde schon kumuliert
('2022-01-04', 2022, 24, 3.5), --flaechen_id=2 13>=10
('2022-01-05', 2022, 28, 3.5), --flaechen_id=2 17>=10, aber schon kumuliert
-- Saison 2023
('2023-01-03', 2023, 5, 0),
('2023-01-04', 2023, 20, 2.5), --flaechen_id=2 15>=10h in Saison 2023 (flache=2.5)
('2023-01-05', 2023, 35, 3.5); --flaechen_id=1 20>=10h in Saison 2023 (flache=1.0),
 
    select * from example; --letzte Spalte die kumulierte Fläche je flachen_id und saison

| datum                    | saison | flaechen_id | flaeche | stunden |
| ------------------------ | ------ | ----------- | ------- | ------- |
| 2021-12-20T00:00:00.000Z | 2022   | 1           | 1.00    | 8       |1:8, 2: 0
| 2021-12-21T00:00:00.000Z | 2022   | 2           | 2.50    | 2       |1:8. 2:2
| 2021-12-21T00:00:00.000Z | 2022   | 1           | 1.00    | 1       |1:9, 2:2
| 2022-01-02T00:00:00.000Z | 2022   | 1           | 1.00    | 2       |1:11, 2:2
| 2022-01-02T00:00:00.000Z | 2022   | 2           | 2.50    | 1       |1:11, 2:3
| 2022-01-03T00:00:00.000Z | 2022   | 2           | 2.50    | 5       |1:11, 2:8
| 2022-01-04T00:00:00.000Z | 2022   | 2           | 2.50    | 5       |1:11, 2:13
| 2022-01-05T00:00:00.000Z | 2022   | 2           | 2.50    | 4       |1:11, 2: 17
| 2023-01-03T00:00:00.000Z | 2023   | 1           | 1.00    | 5       |1:5, 2:0
| 2023-01-03T00:00:00.000Z | 2023   | 2           | 2.50    | 5       |1:5, 2:5
| 2023-01-04T00:00:00.000Z | 2023   | 2           | 2.50    | 10      |1:5, 2:15
| 2023-01-05T00:00:00.000Z | 2023   | 1           | 1.00    | 15      |1:20, 2:15

 select * from result;

| datum                    | saison | kumuliert_gesamt | kumuliert_flaeche |
| ------------------------ | ------ | ---------------- | ----------------- |
| 2021-12-20T00:00:00.000Z | 2022   | 8.0              | 0.0               |
| 2021-12-21T00:00:00.000Z | 2022   | 11.0             | 0.0               |
| 2022-01-02T00:00:00.000Z | 2022   | 14.0             | 1.0               |
| 2022-01-03T00:00:00.000Z | 2022   | 19.0             | 1.0               |
| 2022-01-04T00:00:00.000Z | 2022   | 24.0             | 3.5               |
| 2022-01-05T00:00:00.000Z | 2022   | 28.0             | 3.5               |
| 2023-01-03T00:00:00.000Z | 2023   | 5.0              | 0.0               |
| 2023-01-04T00:00:00.000Z | 2023   | 20.0             | 2.5               |
| 2023-01-05T00:00:00.000Z | 2023   | 35.0             | 3.5               |
 
Zuletzt bearbeitet:
Was genau ist denn jetzt die Frage bzw. welches Ergebnis erwartest du und wo hakt es? Ich bin nicht ganz sicher warum du gruppierst, das geben die Testdaten nicht sinnvoll her. Das kann notwendig sein und dann zu Problemen führen wenn z.B. für die kumuliert_stunden und kumuliert_flaeche unterschiedliche Gruppierungen relevant sind. Ich würde das erstmal weg lassen.

Oder suchst du jetzt nur die Summe der Fläche die in der Saison betroffen war?
Code:
SELECT    v.datum,
        v.saison,
        v.stunden,
--        v.art,
        SUM(v.stunden) OVER (PARTITION BY v.saison ORDER BY v.datum) AS kumuliert_stunden,
        v.flaechen_id,
        v.flaeche,
        SUM(v.flaeche) OVER (PARTITION BY v.saison ORDER BY v.datum) AS kumuliert_flaeche,
        (    SELECT    sum(t.flaeche)
            FROM (    SELECT    DISTINCT
                            example.flaechen_id,
                            example.flaeche
                    FROM    example
                    WHERE    example.saison = v.saison ) t
        ) AS flaeche_der_saison
FROM    example AS v
 
Oder suchst du jetzt nur die Summe der Fläche die in der Saison betroffen war?
Die Fläche die in der Saison bis zu jedem Tag betroffen war an dem es Daten gibt, mit der Einschränkung das mindestens 10h in der Saison bis zu dem Tag gespeichert sind. Dadurch soll ein Fortschritt dargestellt werden, wie viel Fläche bereits bearbeitet wurde. Jede Fläche (flachen_id) darf nur einmal addiert werden, kann aber an mehreren Tagen mit geringen Stunden vorhanden sein. Da liegt für mich die größte Schwierigkeit.
welches Ergebnis erwartest du und wo hakt es?
Schau mal in #4, da habe ich das gewünschte Resultat mit Kommentaren gepostet.
 
Werbung:
Jetzt verstehe ich erst was mir die result Tabelle sagen soll :)

Ein bisschen Gehirnakrobatik am Morgen aber okay (getestet auf MSSQL):
Code:
SELECT    v.datum,
        v.saison,
        sum(sum(v.stunden)) OVER (PARTITION BY v.saison ORDER BY v.datum) AS kumuliert_stunden,
        (    SELECT    sum(t.flaeche)
            FROM (    SELECT    example.flaechen_id,
                            example.flaeche
                    FROM    example
                    WHERE    example.saison = v.saison
                    AND        example.datum <= v.datum
                    GROUP BY example.flaechen_id,example.flaeche
                    HAVING sum(example.stunden) > 10 ) t
        ) AS kumuliert_flaeche
FROM    example AS v
GROUP BY v.saison, v.datum
ORDER BY v.saison, v.datum
Ich bin nicht ganz sicher ob an meiner Lieblingsstelle sum(sum()) Postgre und MSSQL gleich ticken. Das innere sum() aggregiert die v.stunden zum GROUP BY, das äußere sum() kumuliert das Aggregat. MSSQL kann nicht einfach v.stunden kumulieren weil sie ja nicht gruppiert sind.

Die "kumulierte Fläche" habe ich jetzt quasi zu Fuß gebaut, ich krieg dabei so schon einen Knoten im Kopf. Ob das auch noch eleganter mit sum() OVER geht bezweifle ich aber ich denke der Subselect ist flott genug.
 
Zurück
Oben