分箱
学习如何在 SQL 中使用分箱（binning）。
CREATE TABLE grades ( grade INT );
INSERT INTO grades (grade) VALUES (24), (41), (45), (54), (55), (56), (61), (65), (62), (68), (71), (72), (71), (71), (74), (76), (72), (77), (82), (84), (82), (88), (89), (81), (82), (90), (91), (94), (92), (100) ;
SELECT * FROM grades;
SELECT CASE WHEN grade < 60 THEN 'F' WHEN grade < 70 THEN 'D' WHEN grade < 80 THEN 'C' WHEN grade < 90 THEN 'B' ELSE 'A' END AS letter_grade, COUNT(*) FROM grades GROUP BY letter_grade ORDER BY letter_grade;
SELECT grade, percent_rank() OVER (ORDER BY grade) as percent_grade FROM grades ORDER BY 1;
WITH grades_with_percent_grade AS ( SELECT grades., percent_rank() OVER (ORDER BY grade) as percent_grade FROM grades ) SELECT CASE WHEN percent_grade < 0.6 THEN 'F' WHEN percent_grade < 0.7 THEN 'D' WHEN percent_grade < 0.8 THEN 'C' WHEN percent_grade < 0.9 THEN 'B' ELSE 'A' END AS letter_grade, COUNT() FROM grades_with_percent_grade GROUP BY letter_grade ORDER BY letter_grade;