Avatar billede joemoz Nybegynder
08. november 2009 - 13:55 Der er 11 kommentarer

MSSQL løbende beregning af felter

Jeg har en tabel i en MSSQL 2005-server hvor jeg gerne løbende og automatisk vil opdatere nogle felter med aggregerede værdier fra en korresponderende anden tabel.

Helt konkret har jeg en tabel med salgsmuligheder. Til hver salgsmulighed hører så et antal individuelle salg, og jeg vil så gerne opdatere et felt i salgsmulighedstabellen med det sammenlagte forventede salg pr salgsmulighed (givet ved summen af hvert undersalg ganget med den forventede sandsynlighed).

Denne select query giver mig det ønskede resultat i query analyzer:

SELECT opmgr.opid, dt1.forvsalg FROM salgsmuligheder INNER JOIN (SELECT loprecid AS noegle, SUM(number1*duration/100) AS forvsalg FROM salgstabel WHERE rectype ='S' GROUP BY loprecid) AS dt1 ON opmgr.opid=dt1.noegle WHERE opmgr.rectype ='O'

Men hvordan får jeg sat det ind i en stored procedure eller lign. der løbende kan opdatere kolonnen i salgsmulighedslisten?
Avatar billede arne_v Ekspert
08. november 2009 - 14:07 #1
Jeg vil fraråde at gemme data i databasen som er beregnet fra andre dat ai databasen.

Lad din applikation beregne det og evt. cache det, hvis det er et performance problem.

Hvis det skal være i databasen så lav et materialized view (kaldet indexed i SQLServer).
Avatar billede joemoz Nybegynder
08. november 2009 - 14:20 #2
Tak men hvorfor det? I det her tilfælde er det nødvendigt at opdatere felter i databasen for at de kan blive vist korrekt - det er ikke et performanceproblem, men skyldes begrænsninger på den applikation der kører oven på databasen - der er netop sat specielle felter op i den pågældende tabel til at indeholde brugerdefineret data - det er dem jeg gerne vil udfylde med det beregnede data.
Avatar billede janus_007 Nybegynder
08. november 2009 - 17:00 #3
hej joemoz

Jeg ville gøre sådan her, først en funktion til forecasten:

create function dbo.fnCalculateForecast(@opid int, @rectype char(1))
returns int
begin

    declare @rt int
    if @rectype = 'O'
    begin
        SELECT @rt = SUM(number1*duration/100) FROM salgstabel
            WHERE rectype ='S'
            and noegle = @opid
    end

return @rt
end


og bagefter en computed column i din tabel:

alter tabel salgsmuligheder
add forecast int as dbo.CalculateForecast(opid, rectype)

Performance mv. kan muligvis blive et issue, men det hænger meget sammen med antallet af rækker, indexes osv.
Avatar billede janus_007 Nybegynder
08. november 2009 - 17:01 #4
Hov...
Datatypen skal ikke deklareres på en computed :)

Sådan istedet:
alter tabel salgsmuligheder
add forecast as dbo.CalculateForecast(opid, rectype)
Avatar billede joemoz Nybegynder
09. november 2009 - 10:00 #5
Det ser meget fornuftigt ud, men når nu jeg gerne vil bruge værdier fra salgsmulighedstabellen, skal jeg så ikke henføre til dem snarere end at bruge @opid og @rectype f.eks.?
Avatar billede joemoz Nybegynder
09. november 2009 - 10:14 #6
Og kan jeg ikke lave det som en stored procedure?
Avatar billede janus_007 Nybegynder
09. november 2009 - 10:19 #7
Funktioner har ikke kendskab til rækken, derfor er parametrer nødvendige.
Funktionen skal vide hvilke opid det drejer sig om. Rectype er lavet med en if-sætning fordi den åbenbart er begrænset af selve tabellens indhold. Dvs. der hvor rectype <> 'O' vil der stå NULL i feltet.

Hvis den computed column skal laves på en anden tabel, jamen så er det jo bare at gøre det :)

Håber det gav mening, ellers skirv igen :)
Avatar billede janus_007 Nybegynder
09. november 2009 - 10:21 #8
Hvis du vil lave det som en stored procedure så skal du kalde den stored procedure fra din kode! Hvad programmerer du i?
Avatar billede joemoz Nybegynder
09. november 2009 - 11:22 #9
Tak for hjælpen - det kører nu - jeg lavede det som en stored procedure som jeg sætter til at køre via SQL agenten på serveren, så det løbende opdaterer.

Den ser sådan her ud, hvis nogen skulle være interesseret:

update opmgr

set
forecast = (select sum(salgstabel.number1*salgstabel.duration/100) from salgstabel where salgstabel.rectype='S' and salgstabel.loprecid=opmgr.opid)
from opmgr
inner join salgstabel
on salgstabel.loprecid=opmgr.opid
where opmgr.rectype='O',

END

Mange tak for hjælpen - send gerne et svar.
Avatar billede janus_007 Nybegynder
09. november 2009 - 11:43 #10
hej joemoz

Nu kender jeg ikke til datamængderne, men en god regel er altid at undgå sådanne schedulerede jobs - det skaber mange afhængigheder. Det er klart hvis vi taler mange millioner af rækker og komplicerede beregninger at man planlægger sin update, men i bund og grund kan man komme langt med enten computed columns eller som arne_v nævnte.. at klare det i business laget.

Anyway.. godt du kom videre :)

Intet svar fra mig!
Avatar billede joemoz Nybegynder
11. november 2009 - 20:50 #11
Tak for inputs - hvad mener du præcis med 'afhængigheder'? Altså at man pludselig bliver afhængig af at SQL-agenten ikke går i stå osv.?
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester