✔ Oracle - Anzahl aufeinanderfolgender Datensätze

W

warface

Mitglied
Hallo Zusammen,

ich benötige eine SQL-Abfrage die mir ausgibt wie oft ein Arbeitsgang bei der gleichen Kostenstelle + Arbeitsplatz nach einander bearbeitet wird.

das ist meine aktuelle Abfrage
SQL: 
select
    main.aplidentnr,
    main.aplnr,
    main.kostst,
    main.arbplatz,
    main.tmp_id as Anzahl
from
    (select
        aplidentnr,
        aplnr,
        agpos,
        kostst,
        ARBPLATZ,
        row_number() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id,
        row_number() OVER (PARTITION BY aplnr, kostst, arbplatz ORDER BY aplidentnr, agpos) tmp_id       
       
    from
        DEMO.ARBEITSPLAN) main
    left join
    (select
        aplidentnr,
        aplnr,
        agpos,
        kostst,
        ARBPLATZ,
        row_number() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id,
        row_number() OVER (PARTITION BY aplnr, kostst, arbplatz ORDER BY aplidentnr, agpos) tmp_id       
       
    from
        DEMO.ARBEITSPLAN) sub on (main.aplnr=sub.aplnr and main.agpos_id=sub.agpos_id-1)
where
    main.aplnr
        in
        (select
            aplnr
        from
            DEMO.Fertigungsauftrag
        where
            Fertigungsauftrag.fi_nr = '1' and
            Fertigungsauftrag.datneu >= to_date(sysdate-730, 'DD.MM.YY'))
order by Main.aplidentnr,main.aplnr,Main.agpos

das Problem bei dieser Abfrage ist das der Counter (tmp_id) nicht resetet wird(siehe Beispiel)

aktueller Zustand:

ID | ARTIKELNUMMER | ARBEITSPLANTNR | KOSTENSTELLE | ARBEITSPLATZ | ARBEITSGANG | ANZAHL
agpos_ID | (aplidentnr) | (aplnr) | (kostst) | (arbplatz) |(agpos) | tmp_id
1 | 123456 | 111111 | 200 | 100 | 10 | 1
2 | 123456 | 111111 | 200 | 100 | 20 | 2
3 | 123456 | 111111 | 300 | 100 | 30 | 1
4 | 123456 | 111111 | 300 | 111 | 40 | 1
5 | 123456 | 111111 | 200 | 100 | 50 | 3
6 | 123456 | 111111 | 200 | 100 | 60 | 4
7 | 123456 | 111111 | 300 | 100 | 70 | 2

gewünschter Zustand
ID | ARTIKELNUMMER | ARBEITSPLANTNR | KOSTENSTELLE | ARBEITSPLATZ | ARBEITSGANG | ANZAHL
agpos_ID | (aplidentnr) | (aplnr) | (kostst) | (arbplatz) |(agpos) | tmp_id
1 | 123456 | 111111 | 200 | 100 | 10 | 1
2 | 123456 | 111111 | 200 | 100 | 20 | 2
3 | 123456 | 111111 | 300 | 100 | 30 | 1
4 | 123456 | 111111 | 300 | 111 | 40 | 1
5 | 123456 | 111111 | 200 | 100 | 50 | 1
6 | 123456 | 111111 | 200 | 100 | 60 | 2
7 | 123456 | 111111 | 300 | 100 | 70 | 1

Wenn jemand einen Tipp hätte, würde ich mich sehr freuen.
 
Moin warfatz,

mir ist die Hierarchie zwischen Artikelnr, ArbeitsplanTNR und Arbeitsgang nicht ganz klar.
Was auch daran liegt, dass im Beispiel Artikelnr immer 123456 und Arbeitsplantnr immer 111111 sind.

Aber im echten Leben, wie ist da der Zusammenhang?

Eine Arbeitsplantnr 111111 hat eine Reihe von benannten Arbeitsgängen ( bei dir 10, 20,...70 -> geben die Reihenfolge an)
Kann jetzt innerhalb einer ArbeitsplanTnr die Artikelnr ändern?

Denn das hätte natürlich Auswirkungen auf das hier:
---
-- ROW_NUMBER() OVER (PARTITION BY aplnr, kostst, arbplatz ORDER BY aplidentnr, agpos) tmp_id

-> hier hätte ich erwartet: "OVER (PARTITION BY aplnr, kostst, arbplatz ORDER BY agpos)"
Also eine Sortierung nur nach dem Arbeitsgang, nicht nach Artikelnummer und Arbeitsgang.

Kannst du das bitte noch mal klarstellen?
Denn davon hängt ja ab, ob dieselbe Kombination Kostenstelle/Arbeitsplatz mehrmals nacheinander involviert ist.

Grüße
Biber
 
@Biber3
Ein Artikel kann mehrere Arbeitspläne haben, diese sind eindeutig und können nicht bei mehreren Artikeln vorkommen, daher habe ich die Artikelnummer nicht hinzugefügt.
Ein Arbeitsplan beinhaltet mehrere Arbeitsgänge, die von verschiedenen (Kostenstelle+Arbeitsplatz) bearbeitet werden.
Zwecks Optimierung will ich nun herausfinden wie viele Arbeitsgänge hintereinander von der gleichen Kostenstelle+Arbeitsplatz bearbeitet werden.

Da ich später eine Liste von allen Artikeln und Arbeitsplänen die in den letzten 2 Jahren genutzt wurden erstellen möchte, muss bei der Liste auch die Arbeitplannr berücksichtigt werden.

danke schon mal für deine Unterstützung.
 
Moin warfatz,

dann würde ich es so machen:
SQL: 
Select main.*
 ,       ROW_NUMBER() OVER (PARTITION BY aplnr, station ORDER BY aplidentnr, agpos) AnzBeiKostArbplatz
from (
select
    ROW_NUMBER() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id
  , ap.aplidentnr
  , ap.aplnr
  , ap.kostst
  , ap.arbplatz
  , ap.agpos
  , case when  lag(  aplidentnr||aplnr||kostst||arbplatz, 1, aplidentnr||aplnr||kostst||arbplatz ) over (order by aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
         then lag(agpos, 1, agpos) over (order by aplnr, agpos)
         else agpos
         end as station
from demo.arbeitsplan ap
) main
WHERE main.aplnr IN
   (SELECT aplnr
    FROM
        DEMO.Fertigungsauftrag fa
    WHERE
        fa.fi_nr = '1' AND
        fa.datneu >= sysdate-730
    )       
       
ORDER BY Main.aplidentnr, main.aplnr, Main.agpos
;

Anmerkung:
Ich bin das Problem angegangen durch Schaffung einer künstlichen "Station", die momentan auch im Resultset angezeigt wird (aber natürlich nur "unter der Haube" benötigt wird und in der Auswertung nicht angezeigt werden muss. kennt und braucht ja keiner und verwirrt nur).
Ist jetzt nur zur Nachvollziehbarkeit meiner Strategie.

Diese "Station" ist immer gleich den "Agpos"-Werten, also gleich dem Arbeitsgang, ausgenommen Kostenstelle+Arbeitsplatz bleibt gleich.
Dann wird die Agpos des Vorgängerdatensatzes genommen.

Ich zeige dann ein Feld "AnzBeiKostArbplatz" an, welches eigentlich heissen sollte "Anzahl hintereinander bei dieser Kostenstellen-Arbeitsplatzkombination". Das war mir zu lang. Entspricht deiner tmp_id.


Ich habe in der WHERE..Treffer-in-Fertigungsauftrag diese Bedingung
SQL: 
 ... Fertigungsauftrag.datneu >= to_date(sysdate-730, 'DD.MM.YY'))
.. angepasst.
IMHO sollte es dir sofort auf die Bretter gehen, weil eigentlich ein "TO_Date(sysdate...)" ein Datum in ein Datum konvertieren soll.
Oder anders gesagt: ich versteh nicht, wat du da machst.... "datneu" sollte ein Datum sein und "Sysdate-irgendwas" ist auch ein Datum.
Bitte nochmal auf diese Bedingung schauen.

Teste mal bitte an und schreib, ob es passt.

Ich kann es nicht testen, ist also alles nur ein ungeprüftes theoretisches Konstrukt.
(Nicht hauen, wenn ich irgendwo ein Komma oder eine Klammer vergessen habe).

Grüße
Biber
 
Zuletzt bearbeitet:
@Biber3
danke für die viele Arbeit.
mit "LAG" hab ich es auch schon mal getestet, aber de müsste man die zahl irgendwie mit hochzählen.

mit dem Vorschlag von die zählt es nur bis 2 hoch.

Code: 
AGPOS_ID    APLIDENTNR    APLNR    KOSTST    ARBPLATZ    AGPOS    STATION    ANZBEIKOSTARBPLATZ
1    111111121    X141    324    110    10    10    1
2    111111121    X141    317    110    20    20    1
3    111111121    X141    324    110    30    30    1
4    111111121    X141    324    110    40    30    2
5    111111121    X141    351    120    50    50    1
6    111111121    X141    324    110    60    60    1
7    111111121    X141    324    110    70    60    2
8    111111121    X141    324    110    80    70    1
9    111111121    X141    324    110    90    80    1
10    111111121    X141    324    110    100    90    1
11    111111121    X141    324    110    110    100    1
12    111111121    X141    324    110    120    110    1
13    111111121    X141    324    110    130    120    1
14    111111121    X141    324    110    140    130    1
15    111111121    X141    324    110    150    140    1
16    111111121    X141    324    110    160    150    1
17    111111121    X141    324    110    170    160    1
18    111111121    X141    324    110    180    170    1
19    111111121    X141    500    100    190    190    1
20    111111121    X141    324    110    200    200    1
21    111111121    X141    610    110    210    210    1
22    111111121    X141    324    110    220    220    1
 
Ja nee, is' klar...

Da muss dann noch ein LAG() drumherum, ungefähr so:
SQL: 
Select main.agpos_id
      , main.Aplidentnr
      , main.aplnr
      , main.kostst
      , main.arbplatz
      , ROW_NUMBER() OVER (PARTITION BY aplnr, station2 ORDER BY aplidentnr, agpos) AnzBeiKostArbplatz
from (
Select x.*
  , case when  lag(  aplidentnr||aplnr||kostst||arbplatz, 1, aplidentnr||aplnr||kostst||arbplatz ) over (order by aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
         then lag(station, 1, station) over (order by aplnr, agpos)
         else station
         end as station2
from (
select
    ROW_NUMBER() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id
  , ap.aplidentnr
  , ap.aplnr
  , ap.kostst
  , ap.arbplatz
  , ap.agpos
  , case when  lag(  aplidentnr||aplnr||kostst||arbplatz, 1, aplidentnr||aplnr||kostst||arbplatz ) over (order by aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
         then lag(agpos, 1, agpos) over (order by aplnr, agpos)
         else agpos
         end as station
from demo.arbeitsplan ap
) x
) main
WHERE main.aplnr IN
   (SELECT aplnr
    FROM
        DEMO.Fertigungsauftrag fa
    WHERE
        fa.fi_nr = '1' AND
        fa.datneu >= sysdate-730
    )           
ORDER BY Main.aplidentnr, main.aplnr, Main.agpos
;

Aber wir tasten uns ran... ;-)

Grüße
Biber
 
@Biber3 danke :)
jetzt gehts nur bis 3 hoch, bei diesem Vorgehen müsste man das 20 mal im SQL-Befehl wiederholen.

Man könnte es auch so machen, aber auch hier müsste man 20 mal mit "when" abfragen :D

SQL: 
SELECT main.agpos_id
      , main.Aplidentnr
      , main.aplnr
      , main.kostst
      , main.arbplatz,
      main.agpos,
      main.Anzahl
FROM (
SELECT
    ROW_NUMBER() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id
  , ap.aplidentnr
  , ap.aplnr
  , ap.kostst
  , ap.arbplatz
  , ap.agpos
  , CASE
        WHEN lag(  aplidentnr||aplnr||kostst||arbplatz, 4, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz 
                 and lag(  aplidentnr||aplnr||kostst||arbplatz, 3, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
                 and lag(  aplidentnr||aplnr||kostst||arbplatz, 2, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
                 and lag(  aplidentnr||aplnr||kostst||arbplatz, 1, 1||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
        THEN
            5
        ELSE
            CASE WHEN lag(  aplidentnr||aplnr||kostst||arbplatz, 3, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz 
                 and lag(  aplidentnr||aplnr||kostst||arbplatz, 2, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
                 and lag(  aplidentnr||aplnr||kostst||arbplatz, 1, 1||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
            THEN 
                4
            ELSE
                CASE WHEN 
                    lag(  aplidentnr||aplnr||kostst||arbplatz, 2, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz 
                    and lag(  aplidentnr||aplnr||kostst||arbplatz, 1, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
                THEN 
                    3
                ELSE 
                    CASE WHEN 
                        lag(  aplidentnr||aplnr||kostst||arbplatz, 1, aplidentnr||aplnr||kostst||arbplatz ) OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
                    THEN 
                        2 
                    ELSE 
                        1
                    end 
                end
            end
         END AS Anzahl
FROM demo.arbeitsplan ap
) main
WHERE 
    main.aplnr IN
   (SELECT aplnr
    FROM
        demo.arbeitsplan fa
    WHERE
        fa.fi_nr = '1' AND
        fa.datneu >= sysdate-730
    )         
ORDER BY Main.aplidentnr, main.aplnr, Main.agpos
;
 
Zuletzt bearbeitet:
*lach*

Ok, ok, das kann nicht der Weg sein.
Da sollten schon dann Windowing-Functions rein .. irgendein "ROWS BETWEEN x PROCEDING AND.."
Oder ein Last_value() statt Lag().
Aber da muss ich auch erst mal eine Nacht drüber schlafen.
Vielleicht hat ja auch eine(r Mitleser/in die richtige Funktion parat.

Sonst gehts morgen weiter.

Grüße
Biber
 
Nachtrag.

kann doch kein Hexenwerk sein...
Probier mal einen letzten für heute:
SQL: 
Select main.agpos_id
      , main.Aplidentnr
      , main.aplnr
      , main.kostst
      , main.arbplatz
      , ROW_NUMBER() OVER (PARTITION BY aplnr, station2 ORDER BY aplidentnr, agpos) AnzBeiKostArbplatz
from (
Select x.*
  , last_value(station) ignore nulls over(partition by aplnr ORDER BY aplidentnr, agpos) as station2     
from (
select
    ROW_NUMBER() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id
  , ap.aplidentnr
  , ap.aplnr
  , ap.kostst
  , ap.arbplatz
  , ap.agpos
  , case when  lag(  aplidentnr||aplnr||kostst||arbplatz)  over (order by aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
          then null
         else agpos
         end as station
from DEMO.arbeitsplan ap
) x
) main
WHERE main.aplnr IN
   (SELECT aplnr
    FROM
        DEMO.Fertigungsauftrag fa
    WHERE
        fa.fi_nr = '1' AND
        fa.datneu >= sysdate-730
    )         
ORDER BY Main.aplidentnr, main.aplnr, Main.agpos

In der Theorie sollte das helfen, aber das habe ich auch in den letzten zwei Kommentaren gedacht.
Sonst schul' ich um auf Bäcker... menno, ist das peinlich...

Grüße
Biber
 
@Biber3 danke für deine Unterstützung
Ich hab eine gute Nachricht für dich, du kannst deine Karriere als Bäcker wieder vergessen.
Es hat funktioniert :)

Ich habe die Abfrage nun noch etwas erweitert, damit in nur noch den letzten Datensatz der aufeinanderfolgenden Datensätze sehe.
SQL: 
SELECT 
    agpos_id
    , Aplidentnr
    , aplnr
    , kostst
    , arbplatz
    , station2 as von_ag
    , agpos as bis_ag
    , Anzahl
FROM(
SELECT main.agpos_id
      , main.Aplidentnr
      , main.aplnr
      , main.kostst
      , main.arbplatz
      , main.agpos
      , main.station2
      , lead(main.station2,1) OVER (PARTITION BY aplnr, station2 order by main.agpos_id) next_station2
      , ROW_NUMBER() OVER (PARTITION BY aplnr, station2 ORDER BY aplidentnr, agpos) Anzahl
    
FROM (
SELECT x.*
  , last_value(station) IGNORE NULLS OVER(partition BY aplnr ORDER BY aplnr, agpos) AS station2
FROM (
SELECT
    ROW_NUMBER() OVER (PARTITION BY aplnr ORDER BY aplidentnr, agpos) agpos_id
  , ap.aplidentnr
  , ap.aplnr
  , ap.kostst
  , ap.arbplatz
  , ap.agpos
  , CASE WHEN  lag(  aplidentnr||aplnr||kostst||arbplatz)  OVER (ORDER BY aplnr, agpos  ) = aplidentnr||aplnr||kostst||arbplatz
          THEN NULL
         ELSE agpos
         END AS station
FROM DEMO.arbeitsplan ap
) x
) main
WHERE
    main.aplnr IN
   (SELECT aplnr
    FROM
        DEMO.arbeitsplan fa
    WHERE
        fa.fi_nr = '1' AND
        fa.datneu >= sysdate-730
    ))
where
    next_station2 is null;
 
Zuletzt bearbeitet:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück