PDA

Vollständige Version anzeigen : Gruppierungs - *Bereich* (TSQL)


Joachim Hofmann
23.05.2001, 14:39
Hallo,

ich möchte eine Abfrage (Stromkunden) mit einer Gruppierung über (Strom-)Verbräuche erstellen.
Bei der Gruppierung (den Verbräuchen) handelt es sich um *Bereiche*.

z.B.
0-2500
2500-5000
5000-7500
...
usw. usw.

Jetzt kann ich natürlich X mal die gleiche Abfrage dahernemen, und jedesmal den Bereich in der WHERE-Klausel ändern.
Gibt es nicht vielleicht einen eleganteren Trick in (T)SQL, wie man alle Bereiche mit einer Abfrage erschlägt?

MarioR
28.05.2001, 20:36
Hallo Achim,

vielleicht ist das ein Lösungsansatz:

SELECT ..., INT([Verbrauch]/2500) AS Bereich FROM ...

Joachim Hofmann
29.05.2001, 11:29
geniale Idee eigentlich :-) und so schön spartanisch!

es müßte bei ms server nur heißen
cast([bisheriger_Jahresverbrauch_HT]/2500

ich habe mir in der Zwischenzeit eine mammutartige Abfrage mit CASE und COMPUTE ausgedacht (mit der man nicht mal ohne eine zweite Abfrage gruppieren kann):

SELECT Vertraege.bisheriger_Stromversorger, Preissysteme.Preissystem,
'Jahresverbrauch' =
case
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T < 2499 THEN '1: < 2500'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 2499 AND bisheriger_Jahresverbrauch_HT< 4999 THEN '2: 2500 - 4999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 4999 AND bisheriger_Jahresverbrauch_HT< 7499 THEN '3: 5000 - 7499'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 7499 AND bisheriger_Jahresverbrauch_HT< 9999 THEN '4: 7500 - 9999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 9999 AND bisheriger_Jahresverbrauch_HT< 14999 THEN '5: 10000 - 14999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 14999 AND bisheriger_Jahresverbrauch_HT < 19999 THEN '6: 15000 - 19999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 19999 AND bisheriger_Jahresverbrauch_HT< 29999 THEN '7: 20000 - 29999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 29999 AND bisheriger_Jahresverbrauch_HT< 39999 THEN '8: 30000 - 39999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 39999 AND bisheriger_Jahresverbrauch_HT< 49999 THEN '9: 40000 - 49999'
WHEN bisheriger_Jahresverbrauch_HT + bisheriger_Jahresverbrauch_NT + bisheriger_Jahresverbrauch_3T > 49999 THEN 'Zuletzt:> 49999'
END
FROM Vertraege INNER JOIN Vertraege_Zaehler ON Vertraege.Vertraege_KEY = Vertraege_Zaehler.Vertraege_KEY INNER JOIN Vertraege_Abrechnung ON Vertraege.Vertraege_KEY = Vertraege_Abrechnung.Vertraege_KEY INNER JOIN Preissysteme ON Vertraege_Abrechnung.Preissysteme_KEY = Preissysteme.Preissysteme_KEY
WHERE (((Vertraege.Datum_Sperrung) Is Null) AND ((Vertraege.Datum_Kuendigung) Is Null) AND ((Vertraege.Datum_Umstellung) Is Not Null) AND ((Vertraege_Abrechnung.aktiv)=1)
AND (Preissysteme.Preissystem LIKE '17%' OR Preissysteme.Preissystem LIKE 'Business%'))
order BY Jahresverbrauch, Preissysteme.Preissystem, bisheriger_Stromversorger
compute count(Preissysteme.Preissystem) by Jahresverbrauch

MarioR
29.05.2001, 20:56
:D Danke für die Blumen :D

Jan
31.05.2001, 12:17
hier noch ein vorschlag :

1) hilfstabelle 'tab_bereich' erstellen :
Felder : bereich_id INT, Primary Key,
grenze_o INT
grenze_u INT

/** obere(grenze_o) und untere(grenze_u) grenze für ein intervall **/

2) einträge für diese tabelle :
1.Datensatz : grenze_o = 2500, grenze_u = 0, 2.Ds : grenze_o = 5000, grenze_u = 2501

3) tabelle tab_verbrauch um das feld bereich_id erweitern, ref. integrität zu tab_bereich herstellen

4) trigger (insert, update) für tab_verbrauch erstellen :

CREATE TRIGGER trgSetBereich ON tab_verbrauch
FOR INSERT, UPDATE
AS
declare @bereich int;

-- welcher bereich ist betroffen ? --
select @bereich = b.bereich_id
from tab_bereich b, inserted
where inserted.verbrauch >= b.grenze_u
and inserted.verbrauch <= b.grenze_o;

-- gefundenen bereich eintragen --

update tab_verbrauch
set bereich_id = @bereich
from inserted
where tab_verbrauch.verbrauch = inserted.verbrauch;

dieser trigger stellt bei jedem eintrag eines stromverbrauches fest, zu welchem verbrauchsintervall die verbrauchte strommenge gehört. das gleiche geschieht beim update.

6) die bedingungen für eine abfrage sind jetzt geschaffen : select... group by tab_verbrauch.bereich_id

ich hoffe, das bringt jetzt noch was. ich würde mich freuen, zu hören, ob das das problem getroffen hat.

jan

Joachim Hofmann
05.06.2001, 16:10
Hallo Jan,

auch gut. Das wäre wohl sinnvoll, wenn solche Auswertungen ständig und auch noch möglichst schnell gemacht werden müssen.
Nebenbei: mit Triggern habe ich noch nicht viel gearbeitet,
ist das UPDATE in
...
FOR INSERT, UPDATE
...
hier nötig? bzw müßte es nicht dann auch getrennte anweisungen geben, einmal wie hier für
...
FROM tab_bereich b, inserted
...
und eine für
...
FROM tab_bereich b, updated
...
?

Zweitens: ginge das eingentlich auch, wenn man auf die Idee käme, mittels eines UPDATE oder INSERT INTO einen Haufen Zeilen gleichzeitig zu ändern/einzufügen? weil die Variable @bereich nur für einen Datensatz da ist (oder?).

Guter Contest bisher. Ich gratuliere.

NilsK
07.06.2001, 09:14
Hallo Achim,

weder die Triggerlösung noch die CASE-Abfrage halte für das geschilderte Problem für sinnvoll. Beide verursachen viel zu viel Serverlast und Overhead.

Bei der CASE-Abfrage ist es klar: Für jede Zeile muss ein Riesenwust an Code durchlaufen werden; abgesehen davon, beschränkst du damit ohne Not die Bereiche, weil du nur eine feste Zahl von Bereichen prüfen kannst.

Die Triggerlösung schießt mit Kanonen auf Spatzen. Nur um eine gruppierte Abfrage durchzuführen, ist es unsinnig, zusätzliche Tabellen einzuführen, die noch dazu in einer Join-Beziehung zur Haupttabelle stehen. Wenn dann auch noch bei jeder Änderung der Haupttabelle per Trigger die Nebentabelle geändert wird, hat man sozusagen das Maximum getan, um den Server in die Knie zu zwingen.

Da es im Kern um ein simples Rechenproblem geht, ist Marios Ansatz der beste. Es fehlt aber noch die Gruppierung, die aber leicht implementiert werden kann.

Am Beispiel der Products-Tabelle aus der Northwind-Datenbank hier die Lösung:

select count(productname) as Anzahl
, (cast (unitprice/2.5 as int)+1)*2.5-.01 as Bereich

from products

group by ((cast (unitprice/2.5 as int)+1)*2.5-.01)

Damit werden die Produkte in Preisgruppen zu 2,5$ eingeteilt, und die Anzahl der Produkte jeder Gruppe wird ausgegeben. Das ist leicht auf dein Beispiel anzupassen.

Erläuterungsbedrüftig ist die etwas komplexe CAST-Funktion: Zunächst wird unitprice durch den Gruppierungswert 2,5 geteilt. Mit CAST wird die Zahl abgeschnitten, dann wird 1 hinzugezählt, um immer auf den nächsthöheren Wert zu kommen (wir wollen ja jede Zahl in einen Bereich bringen, der bis zum nächsthöheren Wert reicht). Am Ende wird der Optik halber ein Wert für die Bereichseinteilung abgezogen: Der erste Bereich reicht von 0 bis 2,49; der zweite von 2,5 bis 4,99 usw.

Danach kann dann gruppiert werden, allerdings leider nicht mit dem virtuellen Spaltennamen, sondern nur mit der Funktion.

A>auch gut. Das wäre wohl sinnvoll, wenn
A>solche Auswertungen ständig und auch noch
A>möglichst schnell gemacht werden müssen.

Ich habe es nicht getestet, bin mir aber sicher, dass diese Abfrage performanter ist. Man kann sie zusätzlich auch noch als Stored Procedure einbringen, was für häufige Verwendung weitere Performancevorteile bringt.

A> ist das UPDATE [...] hier nötig?

Wenn du Änderungen und nicht nur Einfügungen prüfen willst: Ja.

A>FROM tab_bereich b, updated

Eine Tabelle "updated" gibt es nicht, nur "inserted" und "deleted", weil ein update logisch ein delete mit folgendem insert ist.

A>Zweitens: ginge das eingentlich auch, wenn
A>man auf die Idee käme, mittels eines
A>UPDATE oder INSERT INTO einen Haufen
A>Zeilen gleichzeitig zu ändern/einzufügen?

Ja. Ein Trigger arbeitet zeilenweise, auch bei größeren Änderungen. Das ist auch ein Grund, warum ein Trigger nur in bestimmten Situationen eingesetzt werden sollte und nicht als Generalmittel.

Schöne Grüße, Nils

Jan
12.06.2001, 13:48
hi joachim !
tut mir leid, dass ich dir so einen blödsinn vorgeschlagen habe ! aber immerhin hast du jetzt angefangen, dich für trigger zu interessieren. das ist doch auch was ?!
schöne grüsse,

jan

Joachim Hofmann
12.06.2001, 14:41
Hallo Jan,

ich denke nicht, daß hier irgendein ernst gemeinter Beitrag Blödsinn sein kann. Dadurch kann doch jeder nur lernen, wenn man verschiedene Lösungen liest.
Das gibt es in den "herkömmlichen" Newsgroups für manche Gebiete, (zB C oder reguläre Ausdrücke), sogar regelrechte "Contests" wo es u.A. das Ziel ist möglichst viele verschiedenartige Lösungen zu finden, wobei gelegentlich eine gewisse Kuriosität des Lösungsansatzes hoch angerechnet wird.
So was hat meine Sympathie.

Jan
12.06.2001, 15:49
das denke ich auch !
vielen dank !

jan

NilsK
15.06.2001, 13:47
Hallo Jan,

ich finde nicht, dass deine Anregung Blödsinn ist.

Schöne Grüße, Nils

Jan
19.06.2001, 14:31
ok, danke !

grüsse zurück,

jan

Joachim Hofmann
20.06.2001, 09:25
Hallo,

das ist hier keine Grußsendung
:-)