MSSQL: Funktion mit mehreren Rückgabewerten "verbauen"

[jensw_2000]

Ich habe mich "verknotet" und komm einfach nicht weiter ...

Hier ein vereinfachtes Beispiel für eine Tabelle, für die ich eine statistische Aufbereitung machen muß.

SQL-Code:

Tabelle1:
ID uniqueidentifier,
Kategorie varchar(10),
offen bit,
in_Abreit bit,
erledigt bit


ID                           Kategorie              offen  in_Arbeit  erdeligt
-------------------------------------------------------------------------------
{B6CCC3A9-..-038050EF26EB}   Wartung                0      0          1
{7D2AD4B3-..-01478978BB1F}   Planung                0      1          0
{B2818771-..-78C56A5C02BD}   Service                0      1          0
{DED90065-..-414F3B72A8A3}   Planung                1      0          0
{69379091-..-7D041369A2CE}   Montage                0      0          1
{D897C153-..-786F335E84EE}   Service                1      0          0
{65E617FE-..-02905AAF53CB}   Service                0      0          1
{1D24EE50-..-39D5C5417DA9}   Planung                0      1          0

Pro Kategorie brauche ich jetzt eine Zusammenfassung mit (Anzahl Gesamt, Anzahl offen, Anzahl in Arbeit, Anzahl erledigt, Prozent Erledigt)

Im ersten Step habe ich mit eine Funktion erstellt, die mir für eine bestimmte Kategorie die Statistik zurückgibt ...

SQL-Code:

select * from stat_Table1(':Kategorie')             -- :Kategorie = 'Planung'
go
Kategorie gesamt   offene    in_Arbeit    erledigte   Prozent_erledigt
----------------------------------------------------------------------
Planung      200       25           75          100                 50

Die Funktion ...

SQL-Code:

CREATE FUNCTION stat_Tabelle1 (@Kategorie varchar(10))
RETURNS TABLE
AS
RETURN
    -- Anzahl ermitteln
    select Kategorie, count(Kategorie) as 'gesamt' ,
    (select count(*) from Tabelle1 where offen = 1 AND 
                                         RTRIM(Kategorie)=RTRIM(@Kategorie)) as 'offene',
    (select count(*) from Tabelle1 where in_Arbeit = 1 AND 
                                         RTRIM(Kategorie)=RTRIM(@Kategorie)) as 'in_Arbeit',
    (select count(*) from Tabelle1 where erledigt = 1 AND 
                                         RTRIM(Kategorie)=RTRIM(@Kategorie)) as 'erledigte',
    -- Prozent_erledigt ermitteln und Division durch 0 verhindern
    CASE
      WHEN (select count(*) from Tabelle1 where erledigt=1 AND 
                                                RTRIM(Kategorie)=RTRIM(@Kategorie)) <> 0 then 
        cast((select count(*) from Tabelle1 where erledigt = 1 AND RTRIM(Kategorie)=RTRIM(@Kategorie)) 
              * 100 as float) / (select count(*) from Tabelle1 WHERE RTRIM(Kategorie)=RTRIM(@Kategorie))
      ELSE '0'
    END as 'Prozent_erledigt'    from Tabelle1
    from Tabelle1 WHERE RTRIM(Kategorie)=RTRIM(@Kategorie)

Da die Funktion bereits bei 50.000 Testdatensätzen in der Tabelle1 0,85 Sekunden läuft, und die Abfrage der Statistik recht häufig durch mein Programm aufgerufen wird, wollte ich mir eine 'Statistik-Tabelle' erstellen, in der nur die, durch STAT_Tabelle1, errechneten Werte gespeichert werden.
Dazu habe ich mit nun folgende Tabelle erstellt:

SQL-Code:

Tabelle: stat_Tabelle1_Kategorien
Kategorie        varchar(10),
gesamt           int
offene           int
in_Arbeit        int
erledigte        int
Prozent_erledigt float

Soweit funktionierts ...
jetzt zum "Knoten"

Eigentlich wollte ich jetzt eine Stored Procedure schreiben, die ich via SQLServer-Agent alle 5 Minuten automatisch ausführen lasse. Diese SP soll die Statistiken für alle Kategorien in Tabelle1 zusammensammeln und in die Tabelle stat_Tabelle1_Kategorien schreiben ...

Hier der Ansatz der nicht funktioniert ...

SQL-Code:

Create Procedure UPDATE_Tabelle1_Stats
AS
begin
  -- alte statistik löschen
  DELETE FROM stat_Tabelle1_Kategorien

  -- neue statistik schreiben (Statistik pro Kategorie mit funktion stat_Tabelle1(Kategorie)
  Select  * from stat_Tabelle1(select distinct Kategorie from tabelle1) INTO stat_Tabelle1_Kategorien

end

Mit dem "select distinct Kategorie from tabelle1" als Parameter kommt der SQL-Server nicht klar ...

Hilfe ...

Ich habe noch gaaaanz viele Statistiken vor mir ...

Wie macht man das richtig ?


Danke,
Jens

[Leuselator]

Vergiss den Ansatz - mach das SQL-Statement ordentlich und ich schwör Dir, es wird rattenschnell!
Dein Problem sind die vielen Subselects - die machen das ganze Arsch-Lahm.
Eine Funktion, die Dir das gewünschte liefert sieht so aus:

SQL-Code:

 
CREATE FUNCTION stat_Tabelle1 (@Kategorie varchar(10))
RETURNS TABLE
AS
RETURN
      SELECT Kategorie
           , count(*)                          AS Gesamt
           , sum(erledigt)                     AS Erledigt
           , CASE WHEN count(*) > 0
                  THEN sum(erledigt)*100/count(*)
                  ELSE NULL
             END                               AS ProzentErledigt
           , sum(offen)                        AS Offen
           , CASE WHEN count(*) > 0
                  THEN sum(offen)*100/count(*)
                  ELSE NULL
             END                               AS ProzentOffen
           , sum(in_Arbeit)                    AS InArbeit
           , CASE WHEN count(*) > 0
                  THEN sum(in_Arbeit)*100/count(*)
                  ELSE NULL
             END                               AS ProzentInArbeit
        FROM Tabelle1
    GROUP BY Kategorie
      HAVING RTRIM(Kategorie) = RTRIM(@Kategorie)

Das selbe Ergebnis, mit einem Bruchteil der Operationen Deiner Funktion im Bruchteil der Zeit. Du solltest wirklich nochmal ein Sql-Tutorial durcharbeiten (ist nicht bös gemeint!). Dein Ansatz geht ziemlich prozedural vor und Du wirfst dabei die ganzen Vorteile einer mengenorientierten Sprache wie SQL über Bord - das war ok bei Desktopdatenbanken ist, aber für echte DBMS so, als ob Du einen GPS-Empfänger als Lineal zum malen auf der Landkarte benutzt
Gruß

PS: um nicht in den Verruf zu geraten, keine Antwort auf Deine eigentliche Frage zu geben:

SQL-Code:

Create Procedure UPDATE_Tabelle1_Stats_Was_Keine_Gute_Idee_Ist
AS
begin
  -- lokale Variable, die Du unten benötigst
  DECLARE @Kategorie varchar(10)

  -- alte statistik löschen
  DELETE FROM stat_Tabelle1_Kategorien

  -- neue statistik schreiben (Statistik pro Kategorie mit funktion stat_Tabelle1(Kategorie)

  -- Du brauchst einen Cursor:
    DECLARE csrKategorien
     CURSOR FOR
     SELECT DISTINCT Kategorie
       FROM Tabelle1
  -- den öffnest Du:
  OPEN csrKategorien
  -- und holst Dir die erste Kategorie
  FETCH NEXT 
   FROM csrKategorien 
   INTO @Kategorie
  -- und wenn es mindestens eine Kategorie gibt, dann:
  WHILE @@FETCH_STATUS = 0 BEGIN 
    -- solange Inserten...
    INSERT INTO stat_Tabelle1_Kategorien
         SELECT * 
           FROM stat_Tabelle1(@Kategorie)
     -- bis holen der nexten Kategorie fehlschlägt
     FETCH NEXT 
      FROM csrKategorien 
      INTO @Kategorie
  END
  -- fettich
end

bekomme Zahnschmerzen bei dieser Prozedur, da Du im zweifel immer 5 Minuten alte Werte bekommst (meist genau 5 Minuten zu alt) und Du den armen SQL-Server vergewaltigst...

[jensw_2000]

Danke! 0,12 Sekunden bei 50000 Datensätzen ....

Zitat:

als ob Du einen GPS-Empfänger als Lineal zum malen auf der Landkarte benutzt

***Schenkelklopf***

Das bei meinem Ansatz der Karren im Dreck steckte, war mir klar.
Habe aber keinen echten Ausweg gefunden ...

Zitat:

Du solltest wirklich nochmal ein Sql-Tutorial durcharbeiten (ist nicht bös gemeint!).

Bin dabei ... nur manchmal fällt es schwehr umzudenken, bzw. die richtigen Ansätze zu finden ...

Vor einem Jahr hätte ich die Tabelle1 als TadoTable in mein Projekt eingebunden, alle Datensätze abgerufen und die Werte in einer in einer "while not table1.eof ..." Schleife aufaddiert ...



Danke,
hast mir echt geholfen ...

[Leuselator]

immer gern