数据分析中常用的SQL语句

分组选择数据

问题1:如何获得每个分组前n项结果?

场景:假设有一个表,记录了学生所有科目的成绩,那么现在要取出每个科目分数最高的2位同学的考试成绩。
表名为student_grade
表中字段为:course_id,course_name, student_id, student_name, grade

方法一:利用row_number()函数

select course_id,course_name, student_id, student_name, grade,
    row_number() over(partition by course_id order by grade desc) as rank
from student_grade
where rank <= 2

注:MySQL中不支持使用row_number()函数

方法二:利用嵌套函数

select * from (
select course_id, course_name, student_id, student_name, grade,
    (select count(*) from student_grade as t2
        where t1.course_id=t2.course_id and t1.grade<=t2.grade) 
        as rank
from student_grade as t1) as t3
where rank <=2
order by course_id, rank

在嵌套查询中,利用两个表的course_id字段相等,可以得到这个分组下每条记录在分组中的排名(rank)

问题2:如何获得每个分组前n项的和?

相应的要获取分组中,前n项的和或者平均值之类的,只需要以查询的结果为一个新的表,在该表上查询相应的聚合值。

select sum(grade) from (
select course_id, course_name, student_id, student_name, grade,
    (select count(*) from student_grade as t2
        where t1.course_id=t2.course_id and t1.grade<=t2.grade) 
        as rank
from student_grade as t1) as t3
where rank <=2
group by course_id

问题3: 如何获得分组内每一项在分组和中所占的百分比?

select *, grade/course_grade_sum as percent from(
select course_id, course_name, student_id, student_name, grade,
         (select sum(grade) from student_grade as t2
            where t1.course_id=t2.course_id) as course_grade_sum
from student_grade as t1) as b

Join语句

问题1:如何使用join语句优化子查询?

查询每个人考试分数最高的科目的记录

方法一:使用子查询的方式

select *
from student_grade as t1
where grade = (select max(grade) from student_grade as t2
where t1.student_id=t2.student_id)

方法二:使用join语句

select t1.student_id, t1.student_name, t1.course_id, t1.course_name, t1.grade
from student_grade as t1
left join student_grade as t2
on t1.student_id= t2.student_id
group by t1.student_id,t1.student_name, t1.course_id, t1.course_name, t1.grade
having t1.grade=max(t2.grade)

最后的having条件中max(t2.grade)基于的范围是什么?是join的条件么?

随机获取N条记录

mysql中,可以利用rand()函数排序,然后取前n条结果

select *
from table1
order by rand()
limit n
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值