数据分析中常用的SQL语句

付永刚发布于2019-07-01 16:13 / 2043人阅读

摘要：分组选择数据问题如何获得每个分组前项结果场景假设有一个表，记录了学生所有科目的成绩，那么现在要取出每个科目分数最高的位同学的考试成绩。

分组选择数据 问题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的条件么？

参考资料：慕课网MySQL开发技巧（一）

随机获取N条记录

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

select *
from table1
order by rand()
limit n

专线服务私有云常用sql语句常用sql查询语句常用sql语句大全 mysql常用sql语句

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/32394.html

Mysql 常用SQL语句集锦

摘要：以下是工作中常用的语句查询时间，友好提示日期类型时间戳类型一个返回多个总数同上。以下是工作中常用的SQL语句 : 查询时间，友好提示 timestamp 日期类型$sql = select date_format(create_time, %Y-%m-%d) as day from table_name; int 时间戳类型$sql = select from_unixtime(...

LittleLiByte 2019-07-31 16:19 评论0 收藏0
Mysql 常用SQL语句集锦

摘要：以下是工作中常用的语句查询时间，友好提示日期类型时间戳类型一个返回多个总数同上。以下是工作中常用的SQL语句 : 查询时间，友好提示 timestamp 日期类型$sql = select date_format(create_time, %Y-%m-%d) as day from table_name; int 时间戳类型$sql = select from_unixtime(...

bang590 2019-06-27 14:15 评论0 收藏0
关系数据库常用SQL语句语法大全

摘要：创建表语法表名列名数据类型列级完整性约束条件列名数据类型列级完整性约束条件列级完整性约束条件有可为空不为空唯一，可以组合使用，但是不能重复和对立关系同时存在。创建表语法 CREATE TABLE ( [列级完整性约束条件] [, [列级完整性约束条件]]…); 列级完整性约束条件有NULL[可为空]、NOT NULL[不为空]、UNIQUE[唯一...

tianyu 2019-08-06 13:57 评论0 收藏0
关系数据库常用SQL语句语法大全

摘要：创建表语法表名列名数据类型列级完整性约束条件列名数据类型列级完整性约束条件列级完整性约束条件有可为空不为空唯一，可以组合使用，但是不能重复和对立关系同时存在。创建表语法 CREATE TABLE ( [列级完整性约束条件] [, [列级完整性约束条件]]…); 列级完整性约束条件有NULL[可为空]、NOT NULL[不为空]、UNIQUE[唯一...

Dr_Noooo 2019-06-25 18:18 评论0 收藏0
关系数据库常用SQL语句语法大全

摘要：创建表语法表名列名数据类型列级完整性约束条件列名数据类型列级完整性约束条件列级完整性约束条件有可为空不为空唯一，可以组合使用，但是不能重复和对立关系同时存在。创建表语法 CREATE TABLE ( [列级完整性约束条件] [, [列级完整性约束条件]]…); 列级完整性约束条件有NULL[可为空]、NOT NULL[不为空]、UNIQUE[唯一...

XboxYan 2019-07-01 15:35 评论0 收藏0
项目中常用的19条MySQL优化

摘要：那如何查询如下图所示，虽然给字段添加了索引，但在结果果并没有使用那么如何解决这个问题呢，答案使用全文索引在我们查询中经常会用到。创建全文索引的语法是使用全文索引的语句是注意在需要创建全文索引之前，请联系确定能否创建。在写文章之前，首先感谢飞友科技陆老师提供的文档。。声明一下：下面的优化方案都是基于 Mysql-索引-BTree类型的一、EXPLAIN 做MySQL优化，...

荆兆峰 2019-07-31 17:31 评论0 收藏0