sql - poligono - interpretar histograma minitab

percentiles de datos de histograma

Question

La siguiente tabla captura los datos de grado del alumno en una serie de exámenes.

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);

las calificaciones están ordenadas en el sentido de que vgood> bueno> justo> pobre

¿Sería posible (o tendría sentido) encontrar la nota del percentil 50 para cada estudiante con estos datos? Por ejemplo, en el caso del nombre del estudiante arun si pensamos en los datos como una serie de categorías de calificación, el percentil 50 sería grade_fair .

Answer 1

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;

Estas rondas se amarran, por lo que Neha puntuará "grade_fair" y Radha obtendrá "grade_good". Si desea redondear, cambie <= en < .

Answer 2

Primero necesitas desvirtuar esto. Podemos hacer eso así ...

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}

Luego tenemos que indexar en grados ... Hacemos eso con CROSS JOIN LATERAL . Tenemos 4 filas con una matriz de 4. Queremos 4 * 4 filas.

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)

Ahora lo que queda es que CROSS JOIN LATERAL vuelva a reproducirse x (nuestro grado), sobre gradeqty

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)

Ahora tenemos el GROUP BY name y luego usamos un conjunto ordenado de Funciones Agregadas percent_disc para finalizar el trabajo.

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)

Quieres ir más allá y hacerlo bonito ...

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)

Podemos obtener un out un poco más variado si aumentamos la velocidad de un nuevo usuario.

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)