Tags:

Generierung von Bitlisten mit Hilfe von SQL am Beispiel einer Datum zu Monat Konvertierung

Von Tobias Arnhold 12.09.2015
Ich hatte vor kurzem die Aufgabe erhalten eine Bitliste auf Basis eines Monats zu generieren.
Bedeutet, ich habe einen String mit 31 Zeichen der je Zeichen den Zustand 1 oder 0 einnehmen kann.
 - 1 steht für aktiv
 - 0 steht für inaktiv

Beispieldaten:
'0000000001010001000000000000000'
'0000000000000000111111000011010'

Um dies anhand eines verständlichen Beispiels zu verifizieren, habe ich mir eine Dienstplan-Tabelle ausgedacht.

Beispiel:
Ein Arzt arbeitet an einem Tag in einem speziellen Krankenhausabteil.
Ziel ist es, für Abteil und Arzt eine Monats-Bitliste zu erstellen.
Heißt, an welchen Tagen im Monat arbeitet der Arzt in dem jeweiligen Abteil.



Beispiel-DDL und -DML:
SET DEFINE OFF;

-- DDL
  CREATE TABLE "DIENSTPLAN" 
   ( "ORT" VARCHAR2(100), 
 "ARZT" VARCHAR2(50), 
 "TAG" DATE
   ) ;

-- DML
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('01.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('01.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Lucky Luke',to_date('01.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Modesty Blaise',to_date('03.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('17.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('18.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('19.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('20.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('21.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('22.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('02.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('03.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('10.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('12.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('14.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Christopher Robins',to_date('16.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Lucky Luke',to_date('17.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Lucky Luke',to_date('15.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Christopher Robins',to_date('04.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Christopher Robins',to_date('06.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Lucky Luke',to_date('10.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Lucky Luke',to_date('12.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Christopher Robins',to_date('30.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Lucky Luke',to_date('16.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('17.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('15.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('04.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Donald Duck',to_date('06.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Donald Duck',to_date('04.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Modesty Blaise',to_date('02.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('30.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('28.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('28.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('28.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Hautklinik','Dr. Theodor Pussel',to_date('27.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('27.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('27.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Lucky Luke',to_date('18.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Chirurgische Klinik','Dr. Donald Duck',to_date('19.10.2015 00:00','DD.MM.YYYY HH24:MI'));
Insert into DIENSTPLAN (ORT,ARZT,TAG) values ('Kinder- und Jugendmedizin Zentrum','Dr. Modesty Blaise',to_date('18.10.2015 00:00','DD.MM.YYYY HH24:MI'));

Nun habe ich mir zwei Ansätze überlegt, um dieses Problem zu lösen.

1. SQL mit Hilfe von CONNECT BY
SELECT 
  DP_MONAT.ORT,
  DP_MONAT.ARZT,
  LISTAGG(CASE WHEN DP_TAG.TAG IS NOT NULL THEN '1' ELSE '0' END , '') WITHIN GROUP (ORDER BY DP_MONAT.TAG) AS TAGESLEISTE
FROM (/* DIENSTPLAN Monat bilden */
      SELECT 
           ORT,
           ARZT,
           TAG
      FROM
       ( /* Liste mit 31 Zeilen bilden */
         SELECT TO_DATE(TO_CHAR(ROWNUM,'00')||'.10.2015','dd.mm.yyyy') TAG 
         FROM DUAL 
         CONNECT BY LEVEL <= 31
       ) 
       CROSS JOIN
       ( /* Eindeutige Liste aus DIENSTPLAN, ohne Datum */
         SELECT 
           DISTINCT 
           ORT,
           ARZT
         FROM DIENSTPLAN  
       ) 
       ORDER BY 1,2,3
      ) DP_MONAT
LEFT JOIN DIENSTPLAN DP_TAG 
ON (
       DP_MONAT.ORT = DP_TAG.ORT
       AND DP_MONAT.ARZT = DP_TAG.ARZT
       AND DP_MONAT.TAG = DP_TAG.TAG
     )
GROUP BY 
  DP_MONAT.ORT,
  DP_MONAT.ARZT

2. SQL mit "ausgeklügelter" CASE WHEN Logik
SELECT
 ORT,
 ARZT,
 CASE WHEN INSTR(TL,';01;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';02;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';03;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';04;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';05;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';06;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';07;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';08;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';09;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';10;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';11;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';12;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';13;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';14;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';15;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';16;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';17;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';18;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';19;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';20;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';21;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';22;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';23;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';24;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';25;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';26;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';27;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';28;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';29;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';30;') > 0 THEN '1' ELSE '0' END || 
   CASE WHEN INSTR(TL,';31;') > 0 THEN '1' ELSE '0' END AS TAGESLEISTE
FROM (
  SELECT 
    DP.ORT,
    DP.ARZT,
    ';'||LISTAGG(TO_CHAR(DP.TAG,'dd') , ';') WITHIN GROUP (ORDER BY DP.TAG)||';' AS TL
  FROM DIENSTPLAN DP
  GROUP BY 
    DP.ORT,
    DP.ARZT
)

In beiden Fällen kommt das gleiche Ergebnis raus.


IMHO:
Ich würde Lösung 2 verwenden, da hier der wenigste DB-Traffic erzeugt wird.

Die Oracle DB kann aber noch mehr.
Von Haus aus bringt die Oracle DB auch eigene BIT Verarbeitungsfunktionen (BIT_AND, BIT_OR,...) mit sich. Mit deren Hilfe Sie BIT Strings vergleichen können.

Hierzu ein einfaches Beispiel:
SELECT utl_raw.BIT_AND( t.A, t.B )                                                 SET_IN_A_AND_B,
       length(replace(utl_raw.BIT_AND( t.A, t.B ), '0', ''))                       SET_IN_A_AND_B_COUNT,
       utl_raw.BIT_AND( t.A, utl_raw.bit_complement(t.B) )                         ONLY_SET_IN_A,
       length(replace(utl_raw.BIT_AND( t.A, utl_raw.bit_complement(t.B) ),'0','')) ONLY_SET_IN_A_COUNT,
       utl_raw.BIT_AND( t.B, utl_raw.bit_complement(t.A) )                         ONLY_SET_IN_A,
       length(replace(utl_raw.BIT_AND( t.B, utl_raw.bit_complement(t.A) ),'0','')) ONLY_SET_IN_A_COUNT,
       utl_raw.BIT_OR( t.A, t.B )                                                  SET_IN_A_OR_B 
  FROM (SELECT '1110000001010100001000000011000' A, '0000000000000010110000000000000' B FROM dual) t



Fazit:
Sie müssen keine komplexen PL/SQL Funktionen bauen um BIT-Listen zu erstellen oder zu verarbeiten. Mit etwas SQL und der Verwendung von Oracle Funktionen ist sehr viel möglich.

Post Tags:

No Comment to " Generierung von Bitlisten mit Hilfe von SQL am Beispiel einer Datum zu Monat Konvertierung "