✔ lückenlose Analyse

baeri · 18. Dezember 2017

Hi,

ich habe eine Logtabelle, welche Fehlerhafte Jobs Dokumentiert...

also z.B.
01.12.2017 12:12 | JobA
01.12.2017 13:01 | JobB
01.12.2017 15:08 | JobA
02.12.2017 08:12 | JobA
03.12.2017 14:18 | JobB
03.12.2017 15:15 | JobA

jetzt will ich wissen "wie lange" mein Job schon Probleme macht, allerdings nur so lange, bis ein Tag "ohne" Fehler kommt.

also wäre der Abruf am 3.12. für JobA 4 Fehlerhafte Jobs 3 Tage infolge... bei JobB wäre es nur 1 Fehlerhafter Job, da er am 2. ja keinen Fehler hatte...

Das ganze soll immer Tagesbezogen ... also z.B. Heute sein, wenn der Aufwand nicht all zu komplex, wäre auch ein Abruf für ein Spezifizsches Datum 1.12. Interessant.

Ich Arbeite mit Oracle SQL 12c

vielen Dank

Kalito · 18. Dezember 2017

Hallo, ich glaube so wie du dir es vorstellst ist das mit sql alleine sehr schwierig. Warum werden nur fehlerhafte Jobs geloggt. Wird irgendwo geloggt, wann ein Job gelaufen ist bzw. ob dieser erfolgreich ist.

baeri · 18. Dezember 2017

> Warum werden nur fehlerhafte Jobs geloggt.

das hab ich nicht behauptet... aber in der einen Auswertung, haben nur diese Daten eine Relevanz... das ist eine Monitoring Sache, und win paar Fehler sind da mehr oder weniger egal...

wäre es einfach würde ich nicht fragen

ich hab da schon was mit Partition im Auge... aber noch kein gutes Konzept

Yaslaw · 18. Dezember 2017

Eine nette Herausforderung. Ich weiss grad nicht, ob es die eleganteste Version ist, aber es ist mal ein Ansatz
Test bei SQL-Fiddle[/ur]

Testdaten

SQL:

CREATE TABLE T_LOG
    ("TS" timestamp, "JOB" varchar2(4))
;

INSERT ALL 
    INTO T_LOG ("TS", "JOB") VALUES ('01-Dec-2017 01:12:00 PM', 'JobA')
    INTO T_LOG ("TS", "JOB") VALUES ('01-Dec-2017 02:01:00 PM', 'JobB')
    INTO T_LOG ("TS", "JOB") VALUES ('01-Dec-2017 04:08:00 PM', 'JobA')
    INTO T_LOG ("TS", "JOB") VALUES ('02-Dec-2017 09:12:00 AM', 'JobA')
    INTO T_LOG ("TS", "JOB") VALUES ('03-Dec-2017 03:18:00 PM', 'JobB')
    INTO T_LOG ("TS", "JOB") VALUES ('03-Dec-2017 04:15:00 PM', 'JobA')
SELECT * FROM dual
;

Lösung:

SQL:

/**
 * parameters
 * Stichtag as_of
 */
create or replace view vw_params as
select to_date('03.12.2017', 'DD.MM.YYYY') as as_of from dual;

/**
 * Eine Basistabelle zusammenstellen. Es wird auf den Tag aggregiert.
 * zusätzlich wird die Anazhl einträge des Vortages ermittel.
 * Ein weiter Filter: es werden nur Jobs berücksichtig, die am Stichtag min. einen Eintrag haben
 */
create or replace view vw_basis as
with logs as (
  -- Logs auf Tag zusammenfeassen und auch die Summe des Vortages ermitteln
  select l.job, trunc(l.ts) as log_day, count(l.ts) as cnt,
    (
      select count(l1.ts)
      from t_log l1
      where l1.job = l.job and trunc(l1.ts) = trunc(l.ts)-1
     ) as last_cnt
  from t_log l
  group by l.job, trunc(l.ts)
),
jobs as (
  -- betroffene Jobs des Stichtages
  select distinct l.job
  from vw_params p, t_log l
  where p.as_of = trunc(l.ts)
)
select l.job, l.log_day, l.cnt, l.last_cnt
from logs l, vw_params p, jobs j
where l.job = j.job and l.log_day <= p.as_of;

/**
 * Endgültige Auswertung
 */
with startDate as(
  -- Letzter Tag ermitteln, dessen Vortag keine Logeintrag hatte
  -- Das ist der Start der Periode
  select b.job, max(b.log_day) first_log_day
  from vw_basis b
  where b.last_cnt = 0
  group by b.job
 )
select b.job , sum(b.cnt) as cnt
from vw_basis b, startDate sd
where b.job = sd.job and b.log_day >= sd.first_log_day
group by b.Job

baeri · 18. Dezember 2017

Sehr sehr geil...

aber ein "kleiner Fehler" <- lag vermutlich an meiner Beschreibung ist noch includiert...

Die Jobs laufen ca. 50 - 1000 mal am Tag... ich möchte wissen, wieviele Tage "infolge" ist der Job > mit Fehlern < gelaufen...
d.h., NEBEN CNT brauch ich DAYS_IN_ROW (wobei CNT nicht uninteressant ist).

edit:
das kann man ja relativ leicht anhand des "letzten ermittelten tag" errechnen...

baeri · 18. Dezember 2017

ich geb noch "Fehler seit", mit aus und es ist perfekt...

1000 Dank <- mal sehen was die Datenbank sagt, in welcher zig tausende einträge sind

SQL:

/**
 * parameters
 * Stichtag as_of
 */
create or replace view vw_params as
select to_date('03.12.2017', 'DD.MM.YYYY') as as_of from dual;

/**
 * Eine Basistabelle zusammenstellen. Es wird auf den Tag aggregiert.
 * zusätzlich wird die Anazhl einträge des Vortages ermittel.
 * Ein weiter Filter: es werden nur Jobs berücksichtig, die am Stichtag min. einen Eintrag haben
 */
create or replace view vw_basis as
with logs as (
  -- Logs auf Tag zusammenfeassen und auch die Summe des Vortages ermitteln
  select l.job, trunc(l.ts) as log_day, count(l.ts) as cnt,
    (
      select count(l1.ts)
      from t_log l1
      where l1.job = l.job and trunc(l1.ts) = trunc(l.ts)-1
     ) as last_cnt
  from t_log l
  group by l.job, trunc(l.ts)
),
jobs as (
  -- betroffene Jobs des Stichtages
  select distinct l.job
  from vw_params p, t_log l
  where p.as_of = trunc(l.ts)
)
select l.job, l.log_day, l.cnt, l.last_cnt
from logs l, vw_params p, jobs j
where l.job = j.job and l.log_day <= p.as_of;

/**
 * Endgültige Auswertung
 */
with startDate as(
  -- Letzter Tag ermitteln, dessen Vortag keine Logeintrag hatte
  -- Das ist der Start der Periode
  select b.job, max(b.log_day) first_log_day
  from vw_basis b
  where b.last_cnt = 0
  group by b.job
 )
select b.job , sum(b.cnt) as cnt, first_log_day
from vw_basis b, startDate sd
where b.job = sd.job and b.log_day >= sd.first_log_day
group by b.job, first_log_day

baeri · 19. Dezember 2017

so, ich habe das für meine Zwecke jetzt noch leicht abgeändert (z.B. die Abhänigkeit bzw. das Trennen nach Errorlevel). Leider ist beim testen ein Problem aufgetaucht...

Ich habe einige Jobs, welche Samstag und Sonntag nicht laufen... <- das ganze sieht man aber "niemals" den Job an...
> welche Anpassung muss ich vornehmen, wenn ich Samstage und Sonntage GRUNDSÄTZLICH ignorieren möchte (next Level wären Feiertage ebenso, <- hierfür habe ich eine Tabelle in welcher alle Feiertage hinterlegt sind <- aber das wäre vermutlich "zu komplex"!?)?

das Phänomen ist bei JOB_A mit Errorlevel 4 zu beobachten:

SQL:

WITH PARAMS AS(
    --SELECT TO_DATE('15.12.2017', 'DD.MM.YYYY') AS ANLDAY FROM DUAL
    --SELECT TRUNC(SYSDATE-1) AS ANLDAY FROM DUAL
    SELECT TRUNC(SYSDATE) AS ANLDAY FROM DUAL
), TLOG AS(
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('13.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('14.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 10:09', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('16.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('16.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('17.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('17.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_C' JOB, 5 ERRORLEVEL, TO_DATE('17.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('18.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('18.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('18.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_C' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL
), LOGS AS (
  -- Logs auf Tag zusammenfeassen und auch die Summe des Vortages ermitteln
  SELECT L.JOB, L.ERRORLEVEL, TRUNC(L.TS) AS LOG_DAY, COUNT(L.TS) AS CNT,
    (
      SELECT COUNT(L1.TS)
      FROM TLOG L1
      WHERE 1=1
      AND L1.JOB = L.JOB
      AND TRUNC(L1.TS) = TRUNC(L.TS)-1
      AND L1.ERRORLEVEL = L.ERRORLEVEL
     ) AS LAST_CNT
  FROM TLOG L
  GROUP BY L.JOB, L.ERRORLEVEL, TRUNC(L.TS)
), JOBS AS (
  -- betroffene Jobs des Stichtages
  SELECT DISTINCT L.JOB, L.ERRORLEVEL
  FROM PARAMS P, TLOG L
  WHERE P.ANLDAY = TRUNC(L.TS)
), BASIS AS(
    SELECT L.JOB, L.ERRORLEVEL, L.LOG_DAY, L.CNT, L.LAST_CNT
    FROM LOGS L, PARAMS P, JOBS J
    WHERE 1=1
    AND L.JOB = J.JOB
    AND L.ERRORLEVEL = J.ERRORLEVEL
    AND L.LOG_DAY <= P.ANLDAY
), STARTDATE AS(
  -- Letzter Tag ermitteln, dessen Vortag keine Logeintrag hatte
  -- Das ist der Start der Periode
  SELECT B.JOB, B.ERRORLEVEL, MAX(B.LOG_DAY) MINDATUM
  FROM BASIS B
  WHERE B.LAST_CNT = 0
  GROUP BY B.JOB, B.ERRORLEVEL
)

SELECT B.JOB JOB_NAME, B.ERRORLEVEL, SUM(B.CNT) AS CNT, SD.MINDATUM, VP.ANLDAY MAXDATUM
FROM BASIS B, STARTDATE SD, PARAMS VP
WHERE 1=1
AND B.JOB = SD.JOB
AND B.ERRORLEVEL = SD.ERRORLEVEL
AND B.LOG_DAY >= SD.MINDATUM
GROUP BY B.JOB, B.ERRORLEVEL, SD.MINDATUM, VP.ANLDAY

;

1001 Dank

edit: ich hab das ganze mal gefiddlet => http://sqlfiddle.com/#!4/8829db/1

Yaslaw · 19. Dezember 2017

Möglicher Vorschlag.
Eine Ferientabelle inkl. Weekends

SQL:

CREATE TABLE T_HOLIDAY
    ("TS" timestamp, "TITLE" varchar2(50))
;
INSERT ALL INTO T_HOLIDAY ("TITLE", "TS")   
SELECT 'Saturday' title, TO_DATE('16.12.2017', 'DD.MM.YYYY') TS FROM DUAL UNION ALL
SELECT 'Sunday' title, TO_DATE('17.12.2017', 'DD.MM.YYYY') TS FROM DUAL
;

Dann kannst du diese Dazufügen.

SQL:

WITH PARAMS AS(
    --SELECT TO_DATE('15.12.2017', 'DD.MM.YYYY') AS ANLDAY FROM DUAL
    --SELECT TRUNC(SYSDATE-1) AS ANLDAY FROM DUAL
    SELECT TRUNC(SYSDATE) AS ANLDAY FROM DUAL
),
TLOG AS(
  -- Yaslaw: TestDaten ausgelagert
 SELECT * FROM T_LOG
),
TLOG_PLUS AS(
  -- Holidays hinzugefügt. Logs bekommen das Rating 1, Holidays 0
 SELECT L.JOB, L.ERRORLEVEL, L.TS, 1 RATING, 'LOG' SRC FROM TLOG L
 UNION ALL SELECT LB.JOB, LB.ERRORLEVEL, H.TS, 0 RATING, 'HOLIDAY' SRC
 FROM T_HOLIDAY H, (SELECT DISTINCT JOB, ERRORLEVEL FROM TLOG) LB
),
LOGS AS (
  -- Logs auf Tag zusammenfeassen und auch die Summe des Vortages ermitteln
  SELECT L.JOB, L.ERRORLEVEL, TRUNC(L.TS) AS LOG_DAY, COUNT(L.TS) AS CNT,
  SUM(L.RATING) AS RATING,
    (
      SELECT COUNT(L1.TS)
      FROM TLOG_PLUS L1
      WHERE 1=1
      AND L1.JOB = L.JOB
      AND TRUNC(L1.TS) = TRUNC(L.TS)-1
      AND L1.ERRORLEVEL = L.ERRORLEVEL
     ) AS LAST_CNT
  FROM TLOG_PLUS L
  GROUP BY L.JOB, L.ERRORLEVEL, TRUNC(L.TS)
),
JOBS AS (
  -- betroffene Jobs des Stichtages
  SELECT DISTINCT L.JOB, L.ERRORLEVEL
  FROM PARAMS P, TLOG L
  WHERE P.ANLDAY = TRUNC(L.TS)
),
BASIS AS(
    SELECT L.JOB, L.ERRORLEVEL, L.LOG_DAY, L.CNT, L.LAST_CNT, L.RATING
    FROM LOGS L, PARAMS P, JOBS J
    WHERE 1=1
    AND L.JOB = J.JOB
    AND L.ERRORLEVEL = J.ERRORLEVEL
    AND L.LOG_DAY <= P.ANLDAY
),
STARTDATE AS(
  -- Letzter Tag ermitteln, dessen Vortag keine Logeintrag hatte
  -- Das ist der Start der Periode
  SELECT B.JOB, B.ERRORLEVEL, MAX(B.LOG_DAY) MINDATUM
  FROM BASIS B
  WHERE B.LAST_CNT = 0
  GROUP BY B.JOB, B.ERRORLEVEL
)
-- Yaslaw: Neu, kein Count sondern ein SUM über das rating
SELECT B.JOB JOB_NAME, B.ERRORLEVEL, SUM(B.RATING) AS CNT, SD.MINDATUM, VP.ANLDAY MAXDATUM
FROM BASIS B, STARTDATE SD, PARAMS VP
WHERE 1=1
AND B.JOB = SD.JOB
AND B.ERRORLEVEL = SD.ERRORLEVEL
AND B.LOG_DAY >= SD.MINDATUM
GROUP BY B.JOB, B.ERRORLEVEL, SD.MINDATUM, VP.ANLDAY

Biber3 · 26. Dezember 2017

Moin baeri,

ich bin über die Feiertage über diesen Thread gestolpert und fand die Aufgabenstellung auch ganz interessant.
Reizvoll fand ich vor allem das Detail "Ich habe einige Jobs, welche Samstag und Sonntag nicht laufen"... da dachte ich, es wäre ja ganz nett, wenn berücksichtigt werden könnte, dass z.B. Job_A jeden Tag läuft, Job_B nicht Samstags/Sonntags und Job_C nur Dienstags und Donnerstags und dennoch ausgegeben werden würde, wieviele "Tage in Folge" die jeweiligen Jobs auf die Bretter gehen.

Meine Strategie dazu wäre eine kleine Timeline aufzubauen, in der die Soll-Lauftermine aller Jobs stehen (meinetwegen für den Zeitraum heute bis 100 Tage zurück). Dann lässt sich doch prima vergleichen, welcher Job wann hätte laufen sollen und wann er an diesen Tagen auch mit Fehler in den Logs auftaucht.

Habe auch ein SQLFiddle dazu http://sqlfiddle.com/#!4/8829db/66/0

Die linke Seite (das Build Schema) ist irgendwas.. anscheinend muss ein Schema angelegt werden, um die rechte Seite nutzen zu können.

Das eigentliche SQL funktioniert ohne Zugriff aus reale Tabellen.

SQL:

WITH PARAMS(Stichtag) AS(
   SELECT DATE'2017-12-26' from dual
  -- Für diesen Tag berichten...
)
-- select * from params
,
TLOG AS(
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('13.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('14.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 10:09', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('16.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('14.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('17.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_C' JOB, 5 ERRORLEVEL, TO_DATE('21.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('15.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('18.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('18.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_B' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_C' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('19.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('20.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('20.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('21.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('21.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('22.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('22.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('23.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('23.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('24.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('24.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 4 ERRORLEVEL, TO_DATE('26.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL UNION ALL
    SELECT 'JOB_A' JOB, 5 ERRORLEVEL, TO_DATE('26.12.2017 13:12', 'DD.MM.YYYY HH24:MI') TS FROM DUAL

), feiertage as (
select to_date('25.12.2017', 'dd.mm.yyyy') as feiertag from dual
-- mal zur Probe mal den 1.Weihnachtstag als einen Feiertag definieren
)
-- die Logdaten auf Tage zusammengefasst brauchen wir auf jeden Fall
, tlogbyday as (
    Select job, errorlevel, trunc(ts) as logdate, count(*) as cnt
    from tlog
    group by job, errorlevel, trunc(ts)
)
-- select * from tlogbyday
, solltermine as (
      Select rownum as rn, solltermin
      from (
         select trunc(sysdate)+1-rn as Solltermin
         from
         (   select rownum rn
             from   dual
             connect by level <= 100)
           -- die letzten 100 Tage...
       )
      where (1=1)
       -- alle Datumswerte <= <<stichtag
      and   solltermin <= (select stichtag from params)
      -- Feiertage ausfiltern. Annahme: Feiertage gelten für alle Jobs
      and solltermin not in (Select Feiertag from feiertage)
)
--  select * from solltermine
-- Hier festlegen, welche Jobs wann laufen sollen
, jobruntermine as (
Select jr.*
     , row_number() over (partition by jr.job
                          order by jr.solltermin desc) as jobrn
From (                            
    Select 'JOB_A' as job, solltermin from solltermine
    -- JOB_A läuft jeden Tag außer Feiertage
    UNION ALL
    Select 'JOB_B' as job, solltermin from solltermine
    -- JOB_B läuft nicht Samstags und Sonntags
          WHERE to_char(solltermin, 'D') between 2 and 6
    UNION ALL
    Select 'JOB_C' as job, solltermin from solltermine
    -- JOB_C läüft nur Dienstag und Donnerstag
          WHERE to_char(solltermin, 'D') in (3, 5)
    ) jr
) 
-- select * from jobruntermine  
, relevantRuns as (
    select t.job, t.errorlevel from tlogbyday t
    where trunc(t.logdate) in (select stichtag from params)
  )

-- endgültige Auswertung
select x.job, x.errorlevel
    , min(logdate) as vondatum
    , max(logdate) as bisdatum, sum(cnt) as sumfailed
    , sum(oneday) as days_in_row
from (
  select
    row_number() over (partition by t.job, t.errorlevel order by t.logdate desc) as trn
  , jr.jobrn
  , 1 as oneday
  , t.*
  from tlogbyday t
     inner join relevantruns rr on rr.job =t.job and rr.errorlevel=t.errorlevel
     left join jobruntermine jr on jr.job=t.job and jr.solltermin=t.logdate
 ) x
  where x.jobrn=x.trn
 group by x.job, x.errorlevel

Ich habe die TLOG-Daten etwas angepasst. damit die Jobs auch nur an den "geplanten Tagen" laut Jobruntermine laufen.
Ergebnis z.B.

Code:

JOB   ERRORLEVEL  VONDATUM  BISDATUM  SUMFAILED   DAYS_IN_ROW
JOB_A   4   2017-12-18T00:00:00Z  2017-12-26T00:00:00Z  8   8
JOB_A   5   2017-12-15T00:00:00Z  2017-12-26T00:00:00Z  12  11

... bei einem Stichtag 26.12.2017, wenn der 25.12. ein Feiertag ist.

Grüße
Biber

✔ lückenlose Analyse

baeri

Erfahrenes Mitglied

Kalito

Erfahrenes Mitglied

baeri

Erfahrenes Mitglied

Yaslaw

alter Rempler

baeri

Erfahrenes Mitglied

baeri

Erfahrenes Mitglied

baeri

Erfahrenes Mitglied

Yaslaw

alter Rempler

Biber3

Erfahrenes Mitglied

Neue Beiträge