Medianwert berechnen

[jojo05]

Hallo Leute,

ich bräuchte eine SQL-Funktion oder Prozedur, die es ermöglicht, aus aggregierten oder klassifizierten Daten einen Median zu berechnen.
In einem vereinfachten Beispiel würde es darum gehen, das mittlere Alter (Median) der Einwohner eines Ortes zu berechnen. Die Daten liegen folgendermaßen vor:

Anzahl Einwohner
Gemeinde Alter: 20 Jahre Alter: 21 Jahre Alter: 22 Jahre Alter: 23 Jahre Alter: 24 Jahre Alter: 25 Jahre
A 11 65 50 22 87 15
B 2 5 9 5 6 4
C 6 10 15 22 32 33

da ich noch ein Neuling bin und noch nicht so fit in SQL bin habe ich bis jetzt das hier:

create or replace PROCEDURE FindMedian AS

NRows INTEGER;
i INTEGER;
dummy NUMBER;
median NUMBER;

CURSOR empCursor IS
SELECT GemeindeA
FROM Einwohner
ORDER BY GemeindeA;


BEGIN

SELECT count(*) INTO NRows --
FROM Einwohner;

OPEN empCursor;

i := 1;
WHILE (i <= NRows/2)
LOOP
FETCH empCursor INTO dummy;
i := i + 1;
END LOOP;

FETCH empCursor INTO median;
CLOSE empCursor;

dbms_output.put_line('OUTPUT:');
dbms_output.put_line('The median = ' || median);

END;

Leider komm ich nicht weiter.. Kann mir Bitte jemand weiterhelfen????

 

[Hony%]

Hi jojo.

Auf die schnelle eine Lösung für deine Beispieltabelle:

sqlite> SELECT * FROM jojo;
A|11|65|50|22|87|15
B|2|5|9|5|6|4
C|6|10|15|22|32|33

SELECT  gemeinde,
CASE
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 THEN 20
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 + J21 THEN 21
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 + J21 + J22 THEN 22
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 + J21 + J22 + J23 THEN 23
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 + J21 + J22 + J23 + J24 THEN 24
  WHEN (J20 + J21 + J22+ J23 + J24+ J25) /2 <= J20 + J21 + J22 + J23 + J24 + J25 THEN 25
END AS median
FROM jojo

Ergebnis:

A|22
B|22
C|23

Das wird vermutlich noch nicht die Lösung sein nach der du suchst. Aber vielleicht liefert sie dir ja einen Denkansatz.

Gruß
Hony

 

[ukulele]

Ich habe eine recht, nunja, verwirrende Lösung unter MSSQL getestet die aber vom Ansatz her ähnlich gehen könnte. Leider kann der MSSQL 2008 noch kein lag(), das musste ich über einen Subselect machen. Dieser ist bei größeren Datenmengen sicherlich eine Performance-Bremse, also wenn möglich darauf verzichten. Mit WITH habe ich auch nur aus vereinfachungsgründen gearbeitet, hier wären auch Sichten von nützen.

/*
CREATE TABLE tabelle(
    Gemeinde CHAR(1),
    [20 Jahre] INT,
    [21 Jahre] INT,
    [22 Jahre] INT,
    [23 Jahre] INT,
    [24 Jahre] INT,
    [25 Jahre] INT
    )

INSERT INTO tabelle VALUES('A',11,65,50,22,87,15);
INSERT INTO tabelle VALUES('B',2,5,9,5,6,4);
INSERT INTO tabelle VALUES('C',6,10,15,22,32,33);
*/

WITH t2 AS (
SELECT    ROW_NUMBER() OVER (PARTITION BY t1.Gemeinde ORDER BY t1.[Alter]) AS Zeile,
        t1.Gemeinde,
        left(t1.[Alter],2) AS [Alter],
        t1.Anzahl
FROM (    SELECT    tabelle.Gemeinde,tabelle.[21 Jahre],tabelle.[22 Jahre],tabelle.[23 Jahre],tabelle.[24 Jahre],tabelle.[25 Jahre]
        FROM    tabelle ) p
UNPIVOT (    Anzahl FOR [Alter] IN ([21 Jahre],[22 Jahre],[23 Jahre],[24 Jahre],[25 Jahre]) )AS t1
)

SELECT    t5.Gemeinde,
        t5.[Alter] AS Median
FROM    t2 t5
INNER JOIN (

SELECT    t4.Gemeinde,
        min(asdf1) AS asdf2
FROM    (
SELECT    t3.Gemeinde,
        t3.[Alter],
        t3.Anzahl,
        abs(
        (    SELECT    sum(t2.Anzahl)
            FROM    t2
            WHERE    t2.Gemeinde = t3.Gemeinde
            AND        t2.Zeile <= t3.Zeile ) -
        (    SELECT    sum(t2.Anzahl)
            FROM    t2
            WHERE    t2.Gemeinde = t3.Gemeinde  ) / 2 ) AS asdf1
FROM    t2 t3
) t4
GROUP BY t4.Gemeinde

) t6
ON        t5.Gemeinde = t6.Gemeinde
AND        t6.asdf2 = abs(
        (    SELECT    sum(t2.Anzahl)
            FROM    t2
            WHERE    t2.Gemeinde = t5.Gemeinde
            AND        t2.Zeile <= t5.Zeile ) -
        (    SELECT    sum(t2.Anzahl)
            FROM    t2
            WHERE    t2.Gemeinde = t5.Gemeinde  ) / 2 )

Das ist sicherlich noch nicht ideal aber vom Prinzip her denke ich mal geht es. Hier mal ungefähr der Ablauf:
1) UNPIVOT erzeugt mir eine Tabelle mit Gemeinde,Alter,Anzahl
2) Zeilennummer mit Partitionierung auf Gemeinde und Sortierung nach Alter
3) Ich nehme die Anzahl und zähle hoch, hier wäre lag() gut platziert. Ich kenne dann die Anzahl aller Personen und weiß, wo die ungefähre Mitte ist. (Spalte asdf1)
4) Ich Ziehe von dem hoch gezählten Wert die Mitte ab und anuliere das Vorzeichen. Der Wert gibt sozusagen den absoluten Abstand zur Mitte an, der kleinste Wert ist am nähsten an der Mitte (Spalte asdf2).
5) Ich nehme nochmal die Ausgangstabelle mit asdf1 und mache einen INNER JOIN mit dem kleinsten Abstand zur Mitte. In dieser Zeile steht dann auch das Alter, das den Median wiedergeben sollte. Nachgerechnet habe ich es nicht

 

[akretschmer]

Hallo Leute,

ich bräuchte eine SQL-Funktion oder Prozedur, die es ermöglicht, aus aggregierten oder klassifizierten Daten einen Median zu berechnen.

Vielleicht hilft Dir ja https://wiki.postgresql.org/wiki/Aggregate_Median weiter.