Beispiel Statistische Auswertung mit SQL (MySQL)

Teilnehmerfrage 2013-03 Statistische Auswertung

Sind Meßwerte zu Objekten A, B, C … signifikant verschieden?

DROP TABLE IF EXISTS m;
CREATE TABLE m( m CHAR NOT NULL,  v DOUBLE NOT NULL );
SELECT * FROM m;
INSERT INTO m 
( m,   v   ) VALUES  
( 'A', 1.2 ),  
( 'A', 1.5 ),  
( 'A', 1.0 ),  
( 'A', 1.1 ), 
( 'B', 2.2 ),  
( 'B', 2.5 ),  
( 'B', 2.0 ),  
( 'B', 2.1 ),  
( 'B', 2.1 ), 
( 'C', 2.0 ),  
( 'C', 2.7 ),  
( 'C', 1.9 );
SELECT * FROM m;
DROP VIEW IF EXISTS ad;
CREATE VIEW ad AS 
SELECT 
  m, 
  AVG( v ) AS A, 
  AVG( v )-STDDEV_SAMP( v ) AS u, 
  AVG( v )+STDDEV_SAMP( v ) AS o 
FROM m 
GROUP BY m;
SELECT * FROM ad;
DROP VIEW IF EXISTS g;
CREATE VIEW g AS 
SELECT 
  ad.m AS m, 
  ad1.m AS m1 
FROM  
  ad INNER JOIN ad AS ad1 
WHERE 
  ad.a >= ad1.u AND 
  ad.a <= ad1.o AND 
  ad1.m > ad.m;
SELECT * FROM g;

mysql> SELECT * FROM m;
+---+-----+
| m | v   |
+---+-----+
| A | 1.2 |
| A | 1.5 |
| A |   1 |
| A | 1.1 |
| B | 2.2 |
| B | 2.5 |
| B |   2 |
| B | 2.1 |
| B | 2.1 |
| C |   2 |
| C | 2.7 |
| C | 1.9 |
+---+-----+
12 rows in set (0.00 sec)
mysql> SELECT * FROM ad;
+---+--------------------+--------------------+--------------------+
| m | A                  | u                  | o                  |
+---+--------------------+--------------------+--------------------+
| A | 1.2000000000000002 | 1.0129171306613032 | 1.3870828693386972 |
| B |               2.18 | 2.0079534946591475 |  2.352046505340853 |
| C | 2.1999999999999997 | 1.8440973915989558 | 2.5559026084010434 |
+---+--------------------+--------------------+--------------------+
3 rows in set (0.12 sec)
mysql> SELECT * FROM g;
+---+----+
| m | m1 |
+---+----+
| B | C  |
+---+----+
1 row in set (0.09 sec)

Nicht drei verschiedene Tabellen anlegen! Symptom: Die Frage, wie man über Tabellennamen iteriert.