日常写代码经常会遇到数据统计的业务场景,分组查询 group by 结合 count 和 sum 的复杂语句写起来容易令人头大,在这里分享两个比较复杂的统计场景,提供详细分析思路和最终sql语句,希望能给大家带来帮助。
场景1:统计每个学生的加减分次数和总分
库表结构如下所示:
学生分数表:student_score
字段名 | 含义 | 字段类型 | 备注 |
---|---|---|---|
id | 自增编号 | bigint | |
student_name | 学生姓名 | varchar | |
score | 分数 | double | |
sort | 分数类型 | int | 1-加分;2-减分; |
is_delete | 删除标志位 | int | 默认为0; |
一上来不着急写 sql,先来分析一下:
因为统计的是每个学生的分数,所以根据学生名称 student_name 进行 group by 分组查询。
需要获取的字段包括 学生名称 student_name,加分次数 add_count,扣分次数 sub_count,总加分 add_score,总扣分 sub_score,其中,学生名称不需要计算,因此只需处理次数和分数。
我们知道,count() 主要用于求行的个数累计,所以当分数类型 sort 为 1,则增加加分次数,sort 为 2,则增加扣分次数;而 sum() 用于求和累加,因而使用 sum() 来计算总分,分数类型 sort 为 1,则加分,sort 为 2,则减分。
有了清晰的思路,sql就不难写了:
SELECT
student_name,
count(sort = 1 OR NULL) add_count,
count(sort = 2 OR NULL) sub_count,
sum(IF(sort = 1, score, 0)) add_score,
sum(IF(sort = 2, score, 0)) sub_score
FROM
student_score
WHERE
is_delete = 0
GROUP BY
student_name
原始表数据:
统计结果:
这里的次数和分数的条件判断是通过 if 语句来实现的,我们也可以通过 case when 语句来实现:
SELECT
student_name,
count(case when sort = 1 then 1 else null end) add_count,
count(case when sort = 2 then 1 else null end) sub_count,
sum(case when sort = 1 then score else 0 end) add_score,
sum(case when sort = 2 then score else 0 end) sub_score
FROM
student_score
WHERE
is_delete = 0
GROUP BY
student_name
场景2:按照权重统计每个学生的体测成绩
体育测试中我们要根据不同的指标对学生进行打分,目前分为5项指标:800米,50米,立定跳远,仰卧起坐和坐位体前屈。总分为100分,每项的分数权重占比不一样,如下表所示:
项目 | 分数占比(%) |
---|---|
800米 | 30 |
50米 | 15 |
立定跳远 | 20 |
仰卧起坐 | 15 |
坐位体前屈 | 20 |
其中立定跳远需要测试3次,每次都进行打分,需要根据这3次的得分计算出平均分作为最终得分,未来其他指标也可能采用这种方式进行打分。
需求明确了,先来设计数据库表,由于每项指标的权重是固定的,可以采用单独一个表来存储指标权重:
指标权重表:index_score
字段名 | 含义 | 字段类型 | 备注 |
---|---|---|---|
id | 自增编号 | bigint | |
index_code | 指标编号 | varchar | |
index_name | 指标名称 | varchar | |
share | 指标权重 | int | |
is_delete | 删除标志位 | int | 默认为0; |
由于库表中需要保存全量详细的体测记录,所以需要记录每个学生的指标和对应的评分。这里通过指标编号进行关联,结果记录表如下所示:
结果记录表:score_record
字段名 | 含义 | 字段类型 | 备注 |
---|---|---|---|
id | 自增编号 | bigint | |
student_name | 学生名称 | varchar | |
index_code | 指标编号 | varchar | |
score | 对应指标得分 | double | |
is_delete | 删除标志位 | int | 默认为0; |
我们需要通过一个sql关联 指标权重表 和 结果记录表 来获取每个学生的姓名和总得分。这个场景比较复杂,一步一步来分析:
首先先来处理最复杂的立定跳远项,先计算每个学生的3次得分的平均分,由于未来其他指标也可能采用这种方式进行打分,那么我们就要根据学生姓名和指标编号共同进行 group by 分组后再获取平均值:
SELECT
student_name,
index_code,
round(avg(score), 1) index_avg
FROM
score_record r
WHERE
r.is_delete = 0
GROUP BY
student_name, index_code
原始表数据:
统计结果:
统计出每个学生的各项指标得分就完成了第一步,第二步需要根据各项指标的权重结合得分计算总分。这就需要结合指标权重表来获取每项指标的权重占比 share,再乘以第一步计算出的平均值 index_avg 后,最后使用 sum() 来计算出总分。
当然这里需要根据学生姓名来进行分组,对于计算出的总分还要使用 round() 函数保留两位小数,最终 sql 如下:
SELECT
round(sum(s.share * t.index_avg) / 100, 2) score, t.student_name
FROM
(
SELECT
student_name,
index_code,
round(avg(score), 1) index_avg
FROM
score_record r
WHERE
r.is_delete = 0
GROUP BY
student_name,index_code
) t
LEFT JOIN index_score s ON t.index_code = s.index_code
GROUP BY
t.student_name
统计结果:
这样一个看起来有点复杂的 sql,采用从里到外逐步分析的方式,也就可以轻松写出来了。
更多推荐
sql分组查询group by结合count,sum统计语句的实现(附带sql详细分析步骤)
发布评论