[SQL] 히스토그램 데이터의 백분위

히스토그램 데이터의 백분위

시험의 수에 다음 표 캡처 학생 수준의 데이터입니다.

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

성적은 의미에서 정렬 좋은> 좋은> 공정> 가난한

그것은 가능할 것이다 (또는 감각 만들 것)이 데이터를 각 학생에 대한 성적 백분위 50를 찾을 수 있습니까? 예를 들어 - 학생 이름 룬의 경우 우리는 등급 분류의 일련의 데이터를 생각하면 - 50 백분위 수는 grade_fair 될 것이다.

해결법

1. ==============================

   1.먼저이 피벗 해제해야합니다. 우리는이 같은 그렇게 할 수 ...

   먼저이 피벗 해제해야합니다. 우리는이 같은 그렇게 할 수 ...

   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}
   

   그리고 우리가 성적에 인덱스 필요 ... 우리는 십자가와 그렇게는 측면 가입. 우리는 우리는 4 개 * 4 행을 원하는 4의 배열과 4 개 행을 가지고있다.

   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)
   

   이제 남아있는 것을, CROSS는 X (우리의 등급)을 재현하는 측면 다시 가입에 우리가 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)
   

   작업을 완료 percent_disc 이제 우리 GROUP BY 이름과 우리는 정렬 된 설정 집계 함수를 사용 ..

   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)
   

   더 그것으로 가서 꽤 그것을 만들고 싶어 ...

   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)
   

   우리는 새로운 사용자를 잭 경우 우리는 조금 더 밖으로 다양 넣어를 얻을 수 있습니다.

   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)
   

2. ==============================

   2.

   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;
   

   네하 때문에 다운이 라운드의 관계는 "grade_fair"을 득점하고 라다는 "grade_good"를 점수 것입니다. 당신은 <로 변경 <=을 모아합니다.

from https://stackoverflow.com/questions/42547358/percentiles-from-histogram-data by cc-by-sa and MIT license