Prozentualer Anteil berechnen

Ich habe Städte mit Telefonanschlüssen.
Es gibt 3 verschiedenen Anschlusstypen.

Jetzt möchte ich gerne die prozentuale Verteilung je Stadt herausbekommen.

Mathematisch ist es einfach: z.B. x = (A*100)/B
A ist die Menge der Anschlussart, B die Gesamtmenge.

Das Problem ist jetzt nur, dass A eine Variable sein muss.

Hier meine Daten:

CREATE TABLE adresse (
    id_adresse integer NOT NULL,
    adresse character varying(20),
    anschlussart character varying(10)
);

INSERT INTO adresse VALUES (1, 'München', 'Analog');
INSERT INTO adresse VALUES (2, 'München', 'Analog');
INSERT INTO adresse VALUES (3, 'München', 'Digital');
INSERT INTO adresse VALUES (4, 'München', 'Sat');
INSERT INTO adresse VALUES (5, 'München', 'Analog');
INSERT INTO adresse VALUES (8, 'München', 'Analog');
INSERT INTO adresse VALUES (9, 'Berlin', 'Digital');
INSERT INTO adresse VALUES (10, 'Berlin', 'Digital');
INSERT INTO adresse VALUES (11, 'Berlin', 'Digtal');
INSERT INTO adresse VALUES (12, 'Berlin', 'Sat');

ALTER TABLE ONLY adresse
    ADD CONSTRAINT adresse_pkey PRIMARY KEY (id_adresse);

Der Anfang:

SELECT
  adresse.adresse,
  count(adresse.anschlussart) AS Anschlusszahl,
  100 AS Gesamtprozent,
  (SELECT count(adresse.id_adresse)FROM adresse) AS Gesamtanschlusszahl
FROM
  adresse
GROUP BY
  adresse.adresse

Wie Rechne ich jetzt aber (4100)/10 und (6100)/10?
Kann ich in “unteren” Operationen Ergebnisse von “oben” verwenden?

Gruß
Karl

Hilft Dir dies hier weiter?

test=*# select *, count(1) over (partition by adresse) as anz_adressen , count(*) over (partition by anschlussart) as anz_anschlussarten from adresse ;
 id_adresse | adresse | anschlussart | anz_adressen | anz_anschlussarten
------------+---------+--------------+--------------+--------------------
          5 | München | Analog       |            6 |                  4
          1 | München | Analog       |            6 |                  4
          2 | München | Analog       |            6 |                  4
          8 | München | Analog       |            6 |                  4
          9 | Berlin  | Digital      |            4 |                  3
         10 | Berlin  | Digital      |            4 |                  3
          3 | München | Digital      |            6 |                  3
         11 | Berlin  | Digtal       |            4 |                  1
          4 | München | Sat          |            6 |                  2
         12 | Berlin  | Sat          |            4 |                  2
(10 Zeilen)

Andreas

Over?

PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

Warum kennt mein postgres das nicht?

Rieche mal drin, riecht es frisch, dann ist es 8.4 und auch mit drin. Riecht es schon gammelig, dann ist es kein 8.4.


Andreas

PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3

Mist.

ACK.

Damit geht es halt nicht - so gut. Du könntest eine SRF schreiben, die das macht. Oder einfach 8.4.3 oder besser nehmen.

Andreas

Okay, nach dem Update (vom Server und DB) geht die Funktion over.

SELECT
 adresse.adresse,
 adresse.anschlussart,
 count(adresse.anschlussart) AS anzahl,
--count(1) over (partition by adresse) as anz_adressen ,
count(*) over (partition by anschlussart) as anz_anschlussarten
FROM 
  adresse
GROUP BY 
  adresse.adresse,
  adresse.anschlussart
ORDER BY 
  adresse.adresse,
  adresse.anschlussart

Die Summe von count(*)… stimmt aber nicht.


Was ist SRF?

Was soll den rauskommen, die Anzahl von Anschlussarten je Adresse?

test=*# SELECT
 adresse.adresse,
 adresse.anschlussart,
 count(*) over() AS anzahl,

count(*) over (partition by anschlussart, adresse) as anz_anschlussarten
FROM
  adresse



ORDER BY
  adresse.adresse,
  adresse.anschlussart
;
 adresse | anschlussart | anzahl | anz_anschlussarten
---------+--------------+--------+--------------------
 Berlin  | Digital      |     10 |                  2
 Berlin  | Digital      |     10 |                  2
 Berlin  | Digtal       |     10 |                  1
 Berlin  | Sat          |     10 |                  1
 München | Analog       |     10 |                  4
 München | Analog       |     10 |                  4
 München | Analog       |     10 |                  4
 München | Analog       |     10 |                  4
 München | Digital      |     10 |                  1
 München | Sat          |     10 |                  1
(10 Zeilen)

sieht für mich korrekt aus. (Digital und Digtal, Type von Dir)

Was ist SRF?

Set Returning Function. Brauchst aber nicht, hast ja 8.4 nun.

Andreas

beim group by mit adresse, anschlussart stimmt dann die anz_anschlussarten nicht mehr.

Warum steht bei anz_anschlussarten nur 1, wenn ich nach der adresse und der anschlussart gruppiere?

[font=Courier New]adresse        anschlussart    anz_anschlussarten
----------------------------------------------------
Berlin         Digital        1
Berlin         Sat            1
München        Analog         1
München        Digital        1
München        Sat            1[/font]

unschoen, aber:

SELECT ad.adresse, (SELECT COUNT() FROM adresse WHERE adresse=ad.adresse) AS gesamt, COUNT(anschlussart) AS anz, 100/(SELECT COUNT() FROM adresse WHERE adresse=ad.adresse)::real*COUNT(ad.anschlussart)::real AS prozent, anschlussart FROM
adresse ad
GROUP BY ad.adresse, ad.anschlussart

Okay, ich biete:

select distinct adresse,
                count(*) over (partition by adresse) as gesamt,
                count(*) over (partition by adresse,anschlussart) as anz,
                (100* count(*) over (partition by adresse,anschlussart)::numeric/count(*) over (partition by adresse))::numeric(5,2) as prozent,
                anschlussart
from adresse
order by adresse,
         anschlussart;

Andreas

Nachdem ich neugierig :slight_smile: bin hab ich unsere Abfragen mal ueber eine “groessere” Tabelle rennen lassen. (Da ich das ‘over’ auch nicht kannte wollt ichs natuerlich genau wissen). Hab dazu die Tabelle mit dem PgScript gefuellt:

DECLARE @I, @T; – Variable names begin with a @
SET @I = 0; – @I is an integer
WHILE @I < 20000
BEGIN

INSERT INTO adresse VALUES (‘München’, ‘Analog’);
INSERT INTO adresse VALUES (‘München’, ‘Analog’);
INSERT INTO adresse VALUES (‘München’, ‘Digital’);
INSERT INTO adresse VALUES (‘München’, ‘Sat’);
INSERT INTO adresse VALUES (‘München’, ‘Analog’);
INSERT INTO adresse VALUES (‘München’, ‘Analog’);
INSERT INTO adresse VALUES (‘Berlin’, ‘Digital’);
INSERT INTO adresse VALUES (‘Berlin’, ‘Digital’);
INSERT INTO adresse VALUES (‘Berlin’, ‘Digital’);
INSERT INTO adresse VALUES (‘Berlin’, ‘Sat’);
SET @I = @I + 1;
END

Habe das Script dann abbrechen muessen, weil es immer langsamer geworden ist :wink:

Ergebnis mit 26750 Eintraegen:

mit ‘over’: 1331 ms, 3859 ms, 2055 ms.
ohne: 102 ms, 95 ms, 101 ms.

Hatte mir eigentlich genau das Gegenteil erwartet :confused:

wenn man den ersten Typecast (numeric) durch real ersetzt gehts interessanter Weise mindestens doppelt so schnell (976 ms)

Sowas macht man in einer Transaktion. Du hast immer (automatisch) ein COMMIT gemacht, das ist teuer.

Ergebnis mit 26750 Eintraegen:

mit ‘over’: 1331 ms, 3859 ms, 2055 ms.
ohne: 102 ms, 95 ms, 101 ms.

Hatte mir eigentlich genau das Gegenteil erwartet > :confused: >

Gut, müßte man sich mal das EXPLAIN ANALYSE anschauen…

Andreas

Deute ich das richtig, dass er die ‘over’ sequenziell abarbeitet?

Ich sehe kein EXPLAIN ANALYSE, was man könnte deuten. Ich sehe ein Script, welches z.B. eine Spalte mit 40% und 60% mit einem gleichen Wert füllt. Dies wird recht sicher immer zu einem Seq-Scan führen. Und ich sehe eine Abfrage ohne WHERE. Erwartest Du ernsthaft, dies ginge ohne einem Seq-Scan?

Andreas

ok,
vielleicht habe ich die Frage falsch gestellt :wink:
Deute ich das richtig, dass er bei meiner Abfrage einige Dinge parallel durchfuehren kann?

Gerald

parallel? Nein.

Andreas

Danke jungs!

FEHLER:  Syntaxfehler bei »:«
LINE 1: ... count(*) over (partition by adresse,anschlussart):numeric/c...

Lass ich den Typecast weg geht es.

Da gehören 4 Punkte hin: je 2 neben- und untereinander :wink:


Andreas