UNBOUNDED PRECEEDING erweitern oder mit Subselect anpassen!?

[timb83]

Hallo,

seit einigen Tagen experimentiere ich an einem Problem herum, wofür ich einfach keine Lösung finde

Die Tabelle t1 enthält für jeden Code 12 Datensätze, d.h. für jeden Monat einen eigenen Value. Nun möchte ih gern ein Select, dass mir pro Code die Nullwerte selektiert (value=0). Wenn ein Feld einen Nullwert hat, dann wird geschaut, ob das folgende Feld auch einen hat. Wenn das so ist, dann wird hochgezählt, wenn nicht wird in dieses eine Feld mit der Null eine 1 geschrieben. Wenn also ein einzelnes Feld den Wert 0 hat, bekommt es eine 1. Wenn 2 aufeinanderfolgende Felder eine 0 besitzen, wird in das zweite Feld eine 2 geschrieben. Gibt es 3 aufeinanderfolgende Felder mit 0 wird in das dritte Feld eine 3 geschrieben usw... Er soll mir quasi die aufeinanderfolgenden Nulldatensätze zählen und ins letzte Feld reinschreiben.


Hier mal ein Beispiel:

Tabelle t1 mit folgendem Inhalt:
code, month, value
1,1,20
1,2,0
1,3,30
1,4,0
1,5,40
1,6,0
1,7,0
1,8,20
1,9,0
1,10,10
1,11,0
1,12,0
5,1,0
5,2,20
5,3,10
...


Beschreibung:
Januar value = 20
Februar value = 0 (=>Null wird gezählt, da allein => neuer Wert 1)
März value = 30
April value = 0 (=>Null wird gezählt, da allein => neuer Wert 1)
Mai value = 40
Juni value = 0
Juli value = 0 (=>Null wird gezählt, da es die zweite Null in Folge ist => neuer Wert 2)
August value = 20
September value = 0 (=>Null wird gezählt, da allein => neuer Wert 1)
OKtober value = 10
November value = 0
Dezember value = 0 (=>Null wird gezählt, da es die zweite Null in Folge ist => neuer Wert 2)

Das Select sollte daher folgende Werte ausgeben:
code, month, value
1,1,20
1,2,1
1,3,30
1,4,1
1,5,40
1,6,0
1,7,2
1,8,20
1,9,1
1,10,10
1,11,0
1,12,2
5,1,1
5,2,20
5,3,10
...


Ich hab das ganze mit folgendem Code versucht:

select code, month
sum(value) over (
order by month
rows between unbounded preceding and current row
) as final_value
from table1 order by month;

Das select addiert mir alle aufeinanderfolgenden Values...aber ich möchte ja eigentlich nur alle aufeinanderfolgenden Nullwerte counten...

Gibt es irgendeine Funktion oder eine Idee um das "unbounded preceding" zu erweitern bzw. durch ein Subselect oder so anzupassen!?


Viele Grüße,



Tim

[ralfb]

Hallo, Tim,

das ist zwar wirklich ein etwas merkwürdiges Verfahren, was du da betreiben willst, aber mir fällt dazu so etwas ein:

SELECT code
      ,month
      ,CASE WHEN (lead(value,1,1) OVER (PARTITION BY code ORDER BY month))<>0 
                  AND value=0
            THEN max_letzt-max_erst+1 ELSE value END wert
FROM (
SELECT code
      ,month
      ,value
      ,erst
      ,letzt
      ,MAX(erst) OVER (PARTITION BY code ORDER BY month) max_erst
      ,MAX(letzt) OVER (PARTITION BY code ORDER BY month) max_letzt
FROM (
SELECT code
      ,month
      ,value
      ,CASE WHEN (lag(value,1,1) OVER (PARTITION BY code ORDER BY month))<>0 
                  AND value = 0 
            THEN month ELSE 0 END erst
      ,CASE WHEN (lead(value,1,1) OVER (PARTITION BY code ORDER BY month))<>0 
                  AND value = 0 
            THEN month ELSE 0 END letzt
FROM t1
)
)
ORDER BY code, month
;

Gruß

Ralf