Mediane in MySQL

Einleitung

OLAP Online Analytical Processing: Durchführung komplexer Analysevorhaben mit relative schnellen (“online ”) Systemantworten

OLTP Verarbeitung von Transaktionen (nicht nach Sammlung in einem „Batch“) mit schneller (“online ”) Umsetzung im System

MySQL  ist wohl mehr auf OLTP ausgerichtet als auf OLAP!

Das Ziel dieses SQL-Kurses ist es, die Kursteilnehmer so vertraut mit SQL  zu machen, daß sie in der Lage sind, die von ihnen gewünschte Definition des Medians selber  mit SQL  zu formulieren. Es geht hier nicht darum, daß der Dozent solche Dinge für sie programmiert, denn das wäre eine andere Vertragsart - kein Unterricht, sondern Auftragsprogrammierung.

Es gibt in SQL  leider keine Standardfunktion mit welcher der Median einer Stichprobe direkt notiert werden kann. Dies liegt vielleicht teilweise daran, daß es verschiedene Definitionen für den Median gibt und viele Anwendungsarten den Median nicht benötigen, so daß man es dem Programmierer überlassen wollte, den Median bei Bedarf selber zu programmieren.

Die Formulierung des Medians mit SQL ist tatsächlich nicht so einfach, daß sie bereits früh im Kurs behandelt werden kann. Eine der einfacheren Lösungswege ist:

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STICHPROBE ( STICHPROBE INTEGER, VALUE INTEGER );
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 2, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 3, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 4, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 5, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 6, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 7, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 8, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 9, 1 );
SELECT * FROM STICHPROBE;

SELECT   STICHPROBE.VALUE AS MEDIAN
FROM     STICHPROBE, STICHPROBE AS OTHER
GROUP BY STICHPROBE.VALUE
HAVING   SUM( SIGN( 1 - SIGN( STICHPROBE.VALUE - OTHER.VALUE ))) 
         = FLOOR( ( COUNT( * )+ 1 )/ 2 );

Ein Nachteil dieser Lösung ist, daß sie relativ langsam  ist (wohl quadratisch), da alle Werte des kartesischen Quadrates verwendet werden.

Außerdem zeigt das obige Beispiel, daß diese einfache Lösung auch nicht immer einen Median findet.

Die beste Lösungsweg

Beste Lösung: Ermittlung mit prozeduralem Klienten  oder SPSS  (ODBC ) oder R, da deklaratives SQL  hierfür keine effizienten Lösungen erlaubt (die besten Lösungen sind hier nur quadratisch). Bei kleinen Stichprobenmengen sollte es aber gehen.

Es gibt aber auch prozedurales  SQL.

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STICHPROBE ( STICHPROBE INTEGER, VALUE INTEGER );
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 2, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 3, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 4, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 5, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 6, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 7, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 8, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 9, 1 );
SELECT * FROM STICHPROBE;
SELECT STICHPROBE, VALUE FROM STICHPROBE;
DROP PROCEDURE IF EXISTS MEDPROC; -- based on publications by Jon LaBelle and Joe Wynne
DELIMITER |
CREATE PROCEDURE MEDPROC( tbl CHAR( 64 ), col CHAR( 64 ), OUT result DOUBLE )
BEGIN
  DECLARE arg CHAR( 64 );
  SET @source = CONCAT( 'SELECT ( ( COUNT( * ))/ 2 ) INTO @half FROM ', tbl );
  PREPARE statement FROM @source;
  EXECUTE statement;
  DROP PREPARE statement;
  SET @offset = CONVERT( FLOOR( @half ), SIGNED );
  IF @offset = @half THEN
    BEGIN
      SET @offset = @offset - 1;
      SET @rowcount = 2;
      SET arg = CONCAT( 'AVG(', col, ')' );
    END;
  ELSE
    BEGIN
      SET @rowcount = 1;
      SET arg = col;
    END;
  END IF;
  SET @source = CONCAT
  (
    'SELECT ',          arg, 
    ' INTO @res FROM ',
    ' ( SELECT ',       col, 
    ' FROM ',           tbl,
    ' ORDER BY ',       col, 
    ' LIMIT ?, ?',
    ' )',
    'as tmp' 
    );
  SELECT CONCAT( @source ); -- debug print!
  PREPARE statement FROM @source;
  EXECUTE statement USING @offset, @rowcount;
  DROP PREPARE statement;
  SET result = @res;
END;
|
DELIMITER ;
CALL MEDPROC( 'STICHPROBE', 'VALUE', @MEDVAL );
SELECT @MEDVAL;

Der Median nach Behrends 

Es gibt verschiedene Definitionen für den Begriff „Median“ in der Literatur. Hier eine von Behrends :

Eine Zahl ‹ m › heißt ein Median  einer Stichprobe ‹ x ›, wenn mindestens die Hälfte der ‹ xᵢ › größer-gleich x und gleichzeitig mindestens die Hälfte der ‹ xᵢ › kleiner-gleich x ist. (Ehrhard Behrends , 2010-07-15)

Eine deklarative Implementation des Behrends -Median

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE STICHPROBE ( STICHPROBE INTEGER, VALUE INTEGER );
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 2, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 3, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 4, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 5, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 6, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 7, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 8, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 9, 1 );
SELECT STICHPROBE, VALUE FROM STICHPROBE;
CREATE OR REPLACE VIEW STAT AS
SELECT 
  STICHPROBE, 
  VALUE,
  ( SELECT
      COUNT( * )
      FROM
        STICHPROBE AS STICHPROBE1 ) / 2 AS HALF,
  ( SELECT
      COUNT( VALUE )
      FROM
        STICHPROBE AS STICHPROBE1
      WHERE 
        STICHPROBE1.VALUE <= STICHPROBE.VALUE ) AS SMALLER,
  ( SELECT
      COUNT( VALUE )
      FROM
        STICHPROBE AS STICHPROBE1
      WHERE 
        STICHPROBE1.VALUE >= STICHPROBE.VALUE ) AS LARGER
FROM STICHPROBE;
SELECT DISTINCT VALUE FROM STAT WHERE SMALLER >= HALF AND LARGER >= HALF;
CREATE VIEW MEDIAN AS SELECT DISTINCT VALUE AS MEDIAN FROM STAT WHERE SMALLER >= HALF AND LARGER >= HALF;
SELECT MEDIAN FROM MEDIAN;
+--------+
| MEDIAN |
+--------+
|      1 |
+--------+
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
SELECT MEDIAN FROM MEDIAN;
+--------+
| MEDIAN |
+--------+
|      1 |
+--------+
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 2, 2 );
SELECT MEDIAN FROM MEDIAN;
+--------+
| MEDIAN |
+--------+
|      1 |
|      2 |
+--------+
DELETE FROM STICHPROBE;
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 1, 1 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 2, 2 );
INSERT INTO STICHPROBE ( STICHPROBE, VALUE ) VALUES ( 3, 3 );
SELECT MEDIAN FROM MEDIAN;
+--------+
| MEDIAN |
+--------+
|      2 |
+--------+