AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Delphi MSSQL: Funktion mit mehreren Rückgabewerten "verbauen"

MSSQL: Funktion mit mehreren Rückgabewerten "verbauen"

Ein Thema von jensw_2000 · begonnen am 23. Jan 2005 · letzter Beitrag vom 23. Jan 2005
Antwort Antwort
jensw_2000
(Gast)

n/a Beiträge
 
#1

MSSQL: Funktion mit mehreren Rückgabewerten "verbauen&q

  Alt 23. Jan 2005, 21:10
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

  Mit Zitat antworten Zitat
Benutzerbild von Leuselator
Leuselator

Registriert seit: 18. Mär 2003
Ort: Berlin
589 Beiträge
 
Delphi 8 Architect
 
#2

Re: MSSQL: Funktion mit mehreren Rückgabewerten "verbau

  Alt 23. Jan 2005, 21:51
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...
Tim Leuschner
Programmierer = moderner Sysiphos: stets wenn er meint, den Stein seiner Dummheit auf den Berg des Wissens gewuchtet zu haben, erblickt er einen völlig neuen Aspekt und der Dummfels poltert mit Getöse zurück ins Tal der Unwissenheit...
  Mit Zitat antworten Zitat
jensw_2000
(Gast)

n/a Beiträge
 
#3

Re: MSSQL: Funktion mit mehreren Rückgabewerten "verbau

  Alt 23. Jan 2005, 22:41
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 ...

  Mit Zitat antworten Zitat
Benutzerbild von Leuselator
Leuselator

Registriert seit: 18. Mär 2003
Ort: Berlin
589 Beiträge
 
Delphi 8 Architect
 
#4

Re: MSSQL: Funktion mit mehreren Rückgabewerten "verbau

  Alt 23. Jan 2005, 22:43
immer gern
Tim Leuschner
Programmierer = moderner Sysiphos: stets wenn er meint, den Stein seiner Dummheit auf den Berg des Wissens gewuchtet zu haben, erblickt er einen völlig neuen Aspekt und der Dummfels poltert mit Getöse zurück ins Tal der Unwissenheit...
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 21:08 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz