Alternative zu dynamic sql-cursor problematik

[omata]

Hier nochmal ein neuer Versuch...

SQL-Code:

SELECT datum,
       SUM(CASE WHEN typ_id = 1 THEN wert ELSE 0 END)
      +SUM(CASE WHEN typ_id = 2 THEN wert ELSE 0 END)
      -SUM(CASE WHEN typ_id = 3 THEN wert ELSE 0 END)
      -SUM(CASE WHEN typ_id = 4 THEN wert ELSE 0 END)
      +SUM(CASE WHEN typ_id = 5 THEN wert ELSE 0 END)
      - CASE WHEN duration IN (15, 20) THEN SUM(CASE WHEN typ_id = 6 THEN wert ELSE 0 END) ELSE 0 END
      - CASE WHEN duration = 20        THEN SUM(CASE WHEN typ_id = 7 THEN wert ELSE 0 END) ELSE 0 END AS wert
FROM element e, (SELECT DISTINCT duration 
                 FROM config 
                 WHERE domain = :domain) c
WHERE datum BETWEEN :von AND :bis
  AND pilot_id = :pilot_id
GROUP BY datum

[spaniac]

hi,

werde mich der thematik nachher noch einmal annehmen, war bis heute im urlaub. vielen dank schonmal für die hinweise!

Nachtrag: Tatsache, die *6-Problematik scheint an der Anzahl der Config-Einträge zu liegen, da ich jetzt einen Testeintrag angelegt habe und das Ganze jetzt *7 genommen wird...


EDIT: Erneuter Nachtrag: Wenn man aus der FROM-Klausel einfach das "FROM config c" entfernt, dann funktioniert die Funktion einwandfrei. used_duration wird an anderer Stelle eingelesen, somit ist "FROM config c" komplett überflüssig...

:wall:

[spaniac]

so, wie gesagt, das obere problem ist gelöst worden, vielen dank für den input.

dennoch würde ich gerne noch einmal omatas anmerkung bezüglich des füllens der tabelle aufgreifen:

die von mir oben gezeigt funktion liefert nur die daten für die berechnung eines wertes (sagen wir wert1), ich habe jedoch eine hauptmethode, die alle werte zusammenfasst:

SQL-Code:

procedure main

ruft mehrere, verschiedene untermethoden auf, die jeweils eine tabelle mit der struktur

SQL-Code:

|datum|berechneter wert|

zurückliefern, also genau wie die oben bereits besprochene stored procedure.

SQL-Code:

procedure main

liefert also eine tabelle mit der struktur

SQL-Code:

|datum|berechneter wert von unterprocedure1|berechneter wert von unterprocedure2|berechneter wert von unterprocedure3|...

Dies mache ich eigentlich analog der oberen procedure:

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `main_os` $$
CREATE PROCEDURE `main_os`(in_startdate DATE, in_enddate DATE)
    MODIFIES SQL DATA
BEGIN

  /*Declare variables*/
  DECLARE done_offeredcalls INT DEFAULT 0;
  DECLARE done_handledcalls  INT DEFAULT 0;
  DECLARE done_answerquota INT DEFAULT 0;

  DECLARE temp_datum DATE;
  DECLARE temp_offeredcalls FLOAT(7,2);
  DECLARE temp_handledcalls FLOAT(7,2);
  DECLARE temp_answerquota FLOAT(7,2);

  /*Declare cursor to access temporary tables from underlying stored procedures*/

  DECLARE cur_offeredcalls CURSOR FOR
    SELECT *
    FROM temp_table_offeredcalls;

  DECLARE cur_handledcalls CURSOR FOR
    SELECT e.datum, SUM(e.wert)
    FROM element e WHERE e.pilot_id = 1 AND e.typ_id=1 AND e.datum BETWEEN in_startdate AND in_enddate GROUP BY e.datum;

  DECLARE cur_answerquota CURSOR FOR
    SELECT *
    FROM temp_table_answerquota;


  /*Declare continue handler to exit loop on empty row*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_offeredcalls=1, done_handledcalls=1, done_answerquota=1;

  /*Create temporary table to store all values from underlying stored procedures*/
  DROP TEMPORARY TABLE IF EXISTS temp_table_complete_os;
  CREATE TEMPORARY TABLE temp_table_complete_os (datum DATE, offeredcalls FLOAT(7,2), handledcalls FLOAT(7,2), answerquota FLOAT (7,2));


  /*Call stored procedure*/
  CALL offeredcalls_cur ('OS', in_startdate, in_enddate);

  /*Fetch offeredcalls-cursor values into temporary-table - uses sql-update-command to just add the values for the offeredcalls-column to the temporary table*/
  OPEN cur_offeredcalls;
  offeredcalls_loop:LOOP

    FETCH cur_offeredcalls INTO temp_datum, temp_offeredcalls;

    IF done_offeredcalls=1 THEN

      LEAVE offeredcalls_loop;

    END IF;

    IF done_offeredcalls=0 THEN

      INSERT INTO temp_table_complete_os (datum, offeredcalls)

      VALUES (temp_datum, temp_offeredcalls);

    END IF;

  END LOOP offeredcalls_loop;

  CLOSE cur_offeredcalls;


   /*Reset continue handler*/
  SET done_handledcalls=0;

  /*Fetch handledcalls-cursor values into temporary-table - no sql-update command needed*/
  OPEN cur_handledcalls;
  handledcalls_loop:LOOP

    FETCH cur_handledcalls INTO temp_datum, temp_handledcalls;

    IF done_handledcalls=1 THEN

      LEAVE handledcalls_loop;

    END IF;

    IF done_handledcalls=0 THEN

      UPDATE temp_table_complete_os

      SET handledcalls = temp_handledcalls

      WHERE datum = temp_datum;

    END IF;

  END LOOP handledcalls_loop;

  CLOSE cur_handledcalls;

  /*Reset continue handler*/
  SET done_answerquota=0;

  /*Call stored procedure*/
  CALL answerquota_cur ('OS', in_startdate, in_enddate);

  /*Fetch answerquota-cursor values into temporary-table - no sql-update command needed*/
  OPEN cur_answerquota;
  answerquota_loop:LOOP

    FETCH cur_answerquota INTO temp_datum, temp_answerquota;

    IF done_answerquota=1 THEN

      LEAVE answerquota_loop;

    END IF;

    IF done_answerquota=0 THEN

      UPDATE temp_table_complete_os

      SET answerquota = temp_answerquota

      WHERE datum = temp_datum;

    END IF;

  END LOOP answerquota_loop;

  CLOSE cur_answerquota;


  /*Returning the full temporary table*/
  SELECT *
  FROM temp_table_complete_os;


END $$

DELIMITER ;

Wie kann ich die nach der Erzeugung des temp_table_complete_os die Werte aus den anderen temporären Tabellen bzw. Cursorn einfacher einlesen bzw. in einem Durchlauf? Ich würde gerne vermeiden, für jede einzelne Subprocedur einen neuen LOOP zu machen.

Wie immer schon einmal vielen Dank im Voraus!

EDIT: Hier das ganze noch einmal grafisch dargestellt (siehe anhang)

[spaniac]

Hallo,

ich möchte noch kurz meinen Lösungsweg skizzieren, den ich jetzt für die ganzen Unterberechnungen angestellt habe:

Der Wert wird mit der Untermethode wie folgt berechnet. Hierbei wird ein temporary table erzeugt, der aus einem Datum+einem Wert besteht.

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `slacd_cur` $$
CREATE PROCEDURE `slacd_cur`(in_domain VARCHAR(45), in_startdate DATE, in_enddate DATE)
    MODIFIES SQL DATA
BEGIN

    /*Call procedure to create temporary table with all pilots used in the specific domain*/
    /*get_used_pilots creates temporary table temp_table_used_pilots*/
    CALL get_used_pilots(in_domain);


      DROP TEMPORARY TABLE IF EXISTS temp_table_slacd;
      CREATE TEMPORARY TABLE temp_table_slacd AS SELECT datum,
           (SUM(CASE WHEN typ_id = 1 THEN wert ELSE 0 END)
           /SUM(CASE WHEN typ_id = 2 THEN wert ELSE 0 END))*100 AS slacd
      FROM element e, temp_table_used_pilots t
      WHERE e.datum BETWEEN in_startdate AND in_enddate
      AND e.pilot_id = t.pilot
      GROUP BY datum;

END $$

DELIMITER ;

Die Hauptmethode ruft danach mehrere dieser Unterprozeduren auf, füllt ein temporary table damit und gibt diesen table zurück:

SQL-Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_main_os` $$
CREATE DEFINER=`pentaho`@`%` PROCEDURE `test_main_os`(in_domain VARCHAR(45), in_agg VARCHAR(45),in_startdate DATE, in_enddate DATE)
    MODIFIES SQL DATA
BEGIN

  /*Create temporary table to store all values from underlying stored procedures*/

  /*Call stored procedure*/
  CALL slacd_cur (in_domain, in_agg, in_startdate, in_enddate);

    DROP TEMPORARY TABLE IF EXISTS temp_table_test_full;
    CREATE TEMPORARY TABLE temp_table_test_full
    AS SELECT a.datum, a.slacd, b.slcd
    FROM temp_table_slacd a
    JOIN temp_table_slacd b
    ON a.datum=b.datum;

  /*Returning the full temporary table*/
  SELECT *
  FROM temp_table_test_full;

END $$

DELIMITER ;

Ergebnis:

|datum | slacd1 | slcad2 |
|01.01.2010 | 11 | 23 |
|02.01.2010 | 12 | 27 |


Die Lösung ist DEUTLICH performanter als die zuerst genutzt Variante mit Cursorn und zudem durch einfacheren, reduzierteren Code zu erreichen :)

Besten Dank für die Hilfe! :)