tutorial - postgresql vs mysql




Perzentile aus Histogrammdaten (2)

In der folgenden Tabelle werden die Daten der Schüler über mehrere Prüfungen erfasst.

CREATE TABLE grades
AS
  SELECT name, exams, grade_poor, grade_fair, grade_good, grade_vgood
  FROM ( VALUES
    ( 'arun'  , 8  , 1 , 4 , 2 , 1 ),
    ( 'neha'  , 10 , 3 , 2 , 1 , 4 ),
    ( 'ram'   ,  5 , 1 , 1 , 3 , 0 ),
    ( 'radha' ,  8 , 0 , 3 , 1 , 4 )
  ) AS t(name,exams,grade_poor,grade_fair,grade_good,grade_vgood);

Die Noten sind in dem Sinne geordnet, dass gut, gut, gerecht, schlecht ist

Wäre es möglich (oder wäre es sinnvoll), die 50. Perzentile für jeden Schüler mit diesen Daten zu finden? Zum Beispiel - im Fall des Schülernamens arun wenn wir die Daten als eine Reihe von grade_fair - wäre das 50. Perzentil grade_fair .


SELECT name, exams,
       CASE WHEN 0.5 * exams <= grade_poor
                THEN 'grade_poor'
            WHEN 0.5 * exams <= grade_poor + grade_fair
                THEN 'grade_fair'
            WHEN 0.5 * exams <= grade_poor + grade_fair + grade_good
                THEN 'grade_good'
            ELSE 'grade_vgood' END AS median_grade;

Dies führt dazu, dass neha "grade_fair" und radha "grade_good" erhält. Wenn Sie aufrunden möchten, ändern Sie <= in < .


Zuerst müssen Sie dies aufheben. Das können wir so machen ...

SELECT name,
  ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
FROM grades

 name  |   array   
-------+-----------
 arun  | {1,4,2,1}
 neha  | {3,2,1,4}
 ram   | {1,1,3,0}
 radha | {0,3,1,4}

Dann müssen wir in die Noten CROSS JOIN LATERAL ... Das machen wir mit einem CROSS JOIN LATERAL . Wir haben 4 Zeilen mit einem Array von 4. Wir wollen 4 * 4 Zeilen.

SELECT name, grades, gs1.x, grades[gs1.x] AS gradeqty
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
  CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
ORDER BY name, x;


 name  |  grades   | x |  gradeqty
-------+-----------+---+----------
 arun  | {1,4,2,1} | 1 |        1
 arun  | {1,4,2,1} | 2 |        4
 arun  | {1,4,2,1} | 3 |        2
 arun  | {1,4,2,1} | 4 |        1
 neha  | {3,2,1,4} | 1 |        3
 neha  | {3,2,1,4} | 2 |        2
 neha  | {3,2,1,4} | 3 |        1
 neha  | {3,2,1,4} | 4 |        4
 radha | {0,3,1,4} | 1 |        0
 radha | {0,3,1,4} | 2 |        3
 radha | {0,3,1,4} | 3 |        1
 radha | {0,3,1,4} | 4 |        4
 ram   | {1,1,3,0} | 1 |        1
 ram   | {1,1,3,0} | 2 |        1
 ram   | {1,1,3,0} | 3 |        3
 ram   | {1,1,3,0} | 4 |        0
(16 rows)

Nun, was bleibt, müssen wir CROSS JOIN LATERAL erneut wiederholen, um x (unsere Note) über Gradeqty zu reproduzieren

SELECT name,
  gs1.x
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
ORDER BY name, gs1.x;

 name  | x 
-------+---
 arun  | 1
 arun  | 2
 arun  | 2
 arun  | 2
 arun  | 2
 arun  | 3
 arun  | 3
 arun  | 4
 neha  | 1
 neha  | 1
 neha  | 1
 neha  | 2
 neha  | 2
 neha  | 3
 neha  | 4
 neha  | 4
 neha  | 4
 neha  | 4
 radha | 2
 radha | 2
 radha | 2
 radha | 3
 radha | 4
 radha | 4
 radha | 4
 radha | 4
 ram   | 1
 ram   | 2
 ram   | 3
 ram   | 3
 ram   | 3
(31 rows)

Nun wir GROUP BY name und dann verwenden wir eine Ordered-Set Aggregat Funktionen percent_disc , um den Job zu beenden ..

SELECT name, percentile_disc(0.5) WITHIN GROUP (ORDER BY gs1.x)
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
GROUP BY name ORDER BY name;

 name  | percentile_disc 
-------+-----------------
 arun  |               2
 neha  |               2
 radha |               3
 ram   |               3
(4 rows)

Willst du weiter gehen und es schön machen ...

SELECT name, (ARRAY['Poor', 'Fair', 'Good', 'Very Good'])[percentile_disc(0.5) WITHIN GROUP (ORDER BY gs1.x)]
FROM (
  SELECT name,
    ARRAY[grade_poor, grade_fair, grade_good, grade_vgood]
  FROM grades
) AS t(name, grades)
CROSS JOIN LATERAL generate_series(1,4) AS gs1(x)
CROSS JOIN LATERAL generate_series(1,grades[gs1.x]) AS gs2(x)
GROUP BY name
ORDER BY name;

 name  | array 
-------+-------
 arun  | Fair
 neha  | Fair
 radha | Good
 ram   | Good
(4 rows)

Wir können einen etwas abwechslungsreicheren Ausgang bekommen, wenn wir einen neuen Benutzer aufbieten.

INSERT INTO grades (name,grade_poor,grade_fair,grade_good,grade_vgood)
VALUES ('Bob', 0,0,0,100);

 name  |   array   
-------+-----------
 arun  | Fair
 Bob   | Very Good
 neha  | Fair
 radha | Good
 ram   | Good
(5 rows)




postgresql