一、概述

经常会遇到一种需求要计算某个分组内各小分组所占的比例,比如:列出一所学校内所有的班级及班级内男生和女生所占的比例。

通常的做法是从数据库中取出所有的班级、性别及数量,然后遍历计算每个班级中男生和女生的占比。用SQL实现的方式实现就显得简单很多。

二、操作

1. 构造数据源

--创建表
CREATE TABLE TEST (
  ID     NUMBER(10, 0),
  CLASS  NVARCHAR2(50),
  NAME   VARCHAR2(50 BYTE),
  GENDER NVARCHAR2(50)
)


--插入数据
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(1, N'一班', '张三', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(2, N'一班', '李四', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(3, N'一班', '王五', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(4, N'一班', '赵六', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(5, N'一班', '麻七', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(6, N'一班', '小明', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(7, N'一班', '小花', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(8, N'一班', '小微', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(9, N'一班', '玲玲', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(10, N'四班', '琳琳', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(11, N'四班', '张三', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(12, N'四班', '李四', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(13, N'四班', '王五', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(14, N'四班', '赵六', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(15, N'四班', '麻七', N'男');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(16, N'四班', '小明', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(17, N'四班', '小花', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(18, N'四班', '小微', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(19, N'四班', '玲玲', N'女');
INSERT INTO TEST(ID, CLASS, NAME, GENDER) VALUES(20, N'四班', '琳琳', N'女');

2. SQL语句

通过班级和性别进行分组,每个分组的数量除以整个班级的数量:

SELECT CLASS,
       GENDER,
       COUNT(*) / CAST(SUM(COUNT(*)) OVER (PARTITION BY CLASS) AS FLOAT) precent
  FROM TEST
  GROUP BY CLASS,
           GENDER
  ORDER BY CLASS

最终效果:

保留两位小数:

SELECT CLASS,
       GENDER,
       ROUND(COUNT(*) / CAST(SUM(COUNT(*)) OVER (PARTITION BY CLASS) AS FLOAT), 2) precent
  FROM TEST
  GROUP BY CLASS,
           GENDER
  ORDER BY CLASS

三、参考文章

  1. Calculating percentage within a group

本文为原创文章,转载请注明出处!欢迎关注任前程博客 https://renqiancheng.com/,第一时间看后续精彩文章。