« Habe ich heute per Mail bekommen | Main| Abwrackprämie -> ! »

Summen über Tabellenspalten

0
Category
In SQL ist es ja ziemlich einfach mittels einem Select die Summe über mehrere Tabellenspalten zu berechnen ->

select sum(C_1+C_2+C_3+C_4+C_5+C_6+C_7+C_8+C_9+C_10+C_11+C_12),product from product_revenue

Die Zahlen 1..12 sollen in diesem Fall die Monate representieren, was ist aber wenn ich nur den Umsatz der ersten 3 Monate ermitteln möchte?
Man könnte es so versuchen, definiere folgende Funktion ->
CREATE FUNCTION SF_CHECK_REVENUE_IN_MONTH
(
     @Revenue float,
     @RevenueMonth int,
     @RevenueLastMonth in
)
RETURNS float
AS
BEGIN
        RETURN case when @RevenueMonth <= @RevenueLastMonth then @Revenue else 0.0 end
END

das select sieht dann wie folgt aus ->

select sum(dbo.SF_CHECK_REVENUE_IN_MONTH(C_1,1,3)+dbo.SF_CHECK_REVENUE_IN_MONTH(C_2,2,3) + ... + dbo.SF_CHECK_REVENUE_IN_MONTH(C_12,12,3), product from product_revenue

Ist von Performance-Aspekten, OK...

Gruß JJR
P.S.: Hoffe jemand hat eine elegantere Lösung!

Comments

Gravatar Image1 - Also C_1 .. C_12 sind die Spalten der Tabelle und du möchtest über Parameter gesteuert sagen "summiere Spalte 1 bis 3" oder "summiere Spalte 5 bis 7" usw. je Produkt, richtig?

Gravatar Image2 - Hallo Sebastian,

jo 1..n wobei n variabel!

Gruß JJR
P.S.: Hat alles gefunzt?!

Gravatar Image3 - Hallo Jörg,

ich glaube das geht (zumindest in TSQL) nur mit dynamischem SQL - was anderes würde mir da nicht einfallen.

Hab mal was ausprobiert:

ALTER PROCEDURE [dbo].[SpEarningsForProduct]
@Product varchar(50),
@StartMonth int,
@EndMonth int
AS
BEGIN
DECLARE @VDefinition nvarchar(500);
DECLARE @Stmt nvarchar(500);
DECLARE @Counter int;

SET @VDefinition = N'@VProduct varchar(50)';
SET @Stmt = 'select product, sum(';
SET @Counter = @StartMonth;
WHILE @Counter <= @EndMonth
BEGIN
SET @Stmt = @Stmt + 'C_';
SET @Stmt = @Stmt + cast(@Counter as nvarchar);
IF @Counter < @EndMonth
BEGIN
SET @Stmt = @Stmt + '+';
END
SET @Counter = @Counter + 1;
END
SET @Stmt = @Stmt + ') from turnover where product = @VProduct group by product';

exec sp_executesql @Stmt, @VDefinition, @VProduct = @Product;
END

Die Tabelle dazu ist:

CREATE TABLE [dbo].[TURNOVER](
[PRODUCT] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[C_1] [float] NULL,
[C_2] [float] NULL,
[C_3] [float] NULL,
[C_4] [float] NULL,
[C_5] [float] NULL,
[C_6] [float] NULL,
[C_7] [float] NULL,
[C_8] [float] NULL,
[C_9] [float] NULL,
[C_10] [float] NULL,
[C_11] [float] NULL,
[C_12] [float] NULL
) ON [PRIMARY]

Das sollte zumindestens etwas performanter als der Funktionsaufruf für jede Spalte sein - auch wenn es sicherlich noch besser geht.

Funktioniert natürlich nur wenn die Spalten auch so schön fortlaufend benannt sind - wenn die jetzt aber Jan-Feb-Mar usw. heißen... Emoticon



Grüße,
Sebastian

P.S.: hat alles wunderbar funktioniert Emoticon

Gravatar Image4 - Hallo Sebastian,

versuche das mal auszuprobieren und berichte was schneller ist Emoticon

Gruß JJR
P.S.: Auf einer Datenbank, wo ein bisserl was drinne ist Emoticon

Gravatar Image5 - Hallo Sebastian,

von der Performance bei 2000 Produkten ist mir nix aufgefallen nur das ich meine Funktion wie folgt änder sollte ->
CREATE FUNCTION SF_CHECK_REVENUE_IN_MONTH
(
@Revenue float,
@RevenueMonth int,
@RevenueLastMonth int
)
RETURNS float
AS
BEGIN
RETURN case when @Revenue is not null and @RevenueMonth <= @RevenueLastMonth then @Revenue else 0.0 end
END

Weil sonst die NULL - Einträge ein wenig Ärger bereiten Emoticon
Gruß JJR

Gravatar Image6 - Hallo Jörg,

offenbar sind 2.000 Sätze noch zu wenig um da einen Unterschied zu merken - hatte mich schon gewundert und habe es deswegen mal mit einer Tabelle mit 100.000 Datensätzen ausprobiert (so viele Produkte sollte ein ordentlicher Konzern schon haben Emoticon).

Da du anscheinend die Funktion für alle Artikel anwenden willst vereinfacht sie sich natürlich:

ALTER PROCEDURE [dbo].[SpEarningsForAllProducts]
-- Add the parameters for the stored procedure here
@StartMonth int,
@EndMonth int
AS
BEGIN
DECLARE @Stmt nvarchar(500);
DECLARE @Counter int;

SET @Stmt = 'select product, sum(';
SET @Counter = @StartMonth;
WHILE @Counter <= @EndMonth
BEGIN
SET @Stmt = @Stmt + 'IsNull(C_';
SET @Stmt = @Stmt + cast(@Counter as nvarchar);
SET @Stmt = @Stmt + ', 0.0)';
IF @Counter < @EndMonth
BEGIN
SET @Stmt = @Stmt + '+';
END
SET @Counter = @Counter + 1;
END
SET @Stmt = @Stmt + ') from turnover group by product';

exec sp_executesql @Stmt;
END

Und nun der Vergleich:

-- Lösung 1
select sum(dbo.SF_CHECK_REVENUE_IN_MONTH(C_1,1,3)+
dbo.SF_CHECK_REVENUE_IN_MONTH(C_2,2,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_3,3,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_4,4,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_5,5,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_6,6,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_7,7,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_8,8,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_9,9,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_10,10,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_11,11,3) +
dbo.SF_CHECK_REVENUE_IN_MONTH(C_12,12,3)), product
from turnover group by product;

--> 4 Sek.

-- Lösung 2
exec SpEarningsForAllProducts 1, 3;

--> 1 Sek.


75% schneller kann sich schon sehen lassen.

Und sieht dabei noch eleganter aus Emoticon

Grüße,
Sebastian

P.S.: mit den NULL-Werten ist natürlich richtig, müssen abgefangen werden

Gravatar Image7 - Hallo Sebastian,

ja ich gebe Dir recht der Aufruf sieht eleganter aus. Ob dies hat jedoch m.E. einen negativen Effekt auf die Wartbarkeit. Angenommen es gibt noch eine Spalte Year und man möchte darüber grouppieren, dann muß ich das dynamic sql anpassen. Wenn ich es in einer View mit einem Join benutzen möchte auch... von daher sprechen jetzt 75% mehr performance gegen evtl. Mehraufwand bei CR's... Was meinst Du? Gruß JJR

Post A Comment

:-D:-o:-p:-x:-(:-):-\:angry::cool::cry::emb::grin::huh::laugh::lips::rolleyes:;-)

Amazon


Impressum

Firmenname: Peanuts-Soft
Straße Nummer: Biinger Strasse 8
PLZ Ort: 55263 Wackernheim
Telefon: +491772134526
E-Mail: joerg.reck @ peanuts-soft.de
Disclaimer: Peanuts-Soft übernimmt keine Garantie dafür, dass die auf dieser Website bereitgestellten Informationen vollständig, richtig und stets aktuell sind. Dies gilt auch für alle Links, auf die verwiesen wird. Peanuts-Soft ist für die Inhalte, auf die per Link verwiesen wird, nicht verantwortlich. Peanuts-Soft haftet nicht für konkrete, mittelbare und unmittelbare Schäden oder Schäden, die durch fehlende Nutzungsmöglichkeiten, Datenverluste oder entgangene Gewinne – sei es aufgrund der Nichteinhaltung vertraglicher Verpflichtungen, durch Fahrlässigkeit oder eine andere unerlaubte Handlung – im Zusammenhang mit der Nutzung von Dokumenten oder Informationen bzw. der Erbringung von Dienstleistungen entstehen, die auf dieser Web Site zugänglich sind.
Datenschutz: Inhalt und Gestaltung der Internetseiten sind urheberrechtlich geschützt. Eine Vervielfältigung der Seiten oder deren Inhalte bedarf der vorherigen schriftlichen Zustimmung von Peanuts-Soft.


Locations of visitors to this page

Powered By

Domino BlogSphere
Version 3.0.2