精华内容
下载资源
问答
  • 一. 背景信息:工具:mysql数据库+navicat数据库管理客户端;数据库&报表:School数据库,包含4张报表分别是student、score、course和teacher报表明细信息:别看表格很小,其实能量... 阅读指南:为巩固SQL所学...

    b9668b9d77183721b01ac35143aa2452.png

    一. 背景信息:

    • 工具:mysql数据库+navicat数据库管理客户端;
    • 数据库&报表:School数据库,包含4张报表分别是student、score、course和teacher
    • 报表明细信息:别看表格很小,其实能量巨大,真实业务场景会涉及更多报表以及海量数据,两者本质一样,区别在于数据量的多寡。倘若能够把4张报表实操顺溜,那么日后遇到巨量数据也不怕不怕啦~

    7e5e1276490e928fb60c1f44c7fd8832.png

    二. 阅读指南:

    为巩固SQL所学习的知识点,同时也期望通过刷题来提升写SQL语句的速度,因此将自己在刷题过程中所积累的感悟写下,与大家分享交流。恰如标题所述,刷题的感觉你懂的,对于工具性技能,唯有通过不断地实操来训练自己,以达到有一天可以不假思索直接写出。

    以知识点作为各版块内容,每版块内容分别包含3部分:例题解析,所涉及知识点和举一反三,最后以“如何提高SQL查询效率?”为总结。

    2.1 简单查询

    例题解析: 查询姓“孟”的老师的个数

    a9cd1f2d2ef2fb2d25bd47333f063162.png

    所涉及知识点:

    • 日常Sql解决业务问题的步骤:翻译成大白话→写出分析思路→写出对应sql语句;
    • 字符串模糊查询:like + % + _ 组合,%表示任意字符串,_特定单一字符串,%和_使用位置根据问题的要求而定;
    • Count()函数:与计数相关,常与distinct联用,以区分是否剔除重复值;
    • As:为表格原始字段或表名设置简单别名,方便书写
    • Sql语句执行顺序:from子句→where子句→select子句;

    举一反三:

    在业务场景中,简单查询适用于提取临时数据,比如活动中某一时刻的订单数量、赠品数量和下单用户量等,在分析问题时也可以基于某个分论点快速提取相关数据进行验证。

    2.2 汇总分析

    例题解析: 统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序

    b7ab287e1606ef075dd3abfd74c1e1ec.png

    所涉及知识点:

    • 聚合函数有:count()、sum()、avg()、sum()、Max()、Min();
    • Group by 分组:分组后跟的列名与select后保持一致,分组可有多个列名,但多个列名保持1对1关系,避免因关系紊乱而报错;
    • Where子句与having子句区别:having后可跟聚合函数,where不可以,having位置跟在group by后;
    • Order by 排序:desc降序、asc升序,默认asc可省略,多个列名同一排序规则可只写一个desc/asc,不同列名不同排序需对应指明;

    举一反三:

    在业务场景中,可查看各大类产品的年、季度、月和日销量,根据产品的排名情况判断产品的生命周期,计算客户的下单频率可对客户进行分组管理等。

    2.3 复杂查询

    例题解析:查询没有学全所有课的学生的学号、姓名

    54f6d0eb7d0d5ee636cb9587089e257d.png

    所涉及知识点:

    • 子查询:判断是否需要子查询和需要哪种类型,普通子查询(返回多行结果)、标量子查询(返回单行单列)和关系子查询(每组之间进行比较);
    • Sql语句书写顺序:先写子查询,再把子查询嵌套主查询中,若有多层嵌套,注意嵌套顺序,如上题子查询中还有子查询;
    • Sql运行顺序:先运行子查询,其次运行from、where、group by 和having,再select,最后是order by和limit子句;

    举一反三:

    在实际业务中,遇到的业务问题往往比较复杂,通常一个查询结果作为另一个查询条件,所以对于类似针对销售贡献占比最高的客户在最近1年中销售情况分析,就需要先把贡献占比最高的客户找出,再找出对应的1年中销售记录,由此可以分析这些客户的客单价区间,销量区间,产品偏好等;

    2.4 topN问题

    例题解析:按课程号分组取成绩最大值所在行的数据

    4784ccdd0e3ec894daa19bee2bce4188.png

    所涉及知识点:

    关联子查询:判断条件是同组对象之间的比较,如上题是同一组中每一个对象与公认的对象之间的比较,关联条件被放置在子查询中,相当于是拿外部查询中的每一个对象与关联子查询中的特定对象进行比较。

    举一反三:

    在数据分析中,对比分析方法很常用,这就意味着关联子查询在实际业务中也会频繁涉及,比如在对店铺访客的分析中,有哪些新客对页面访问是高于同类其他新客的平均访问页面频次的,以此可以判断这些新客是潜在顾客。

    2.5 多表查询

    例题解析:查询各科成绩前两名的记录

    82e42d990241c82c37d681d52031a833.png

    所涉及知识点:

    • Order by 排序:分辨升序和降序的区别,asc是数字由小到大,desc是数字由大到小;
    • Limit 提取特定行数:limit n = limit 0,n,比如limit 5 是提取前5行,limit 2,3是从第3行开始提取3行(包括第3行);
    • Union all:表的加法,区别于union,union all允许重复值存在;

    举一反三:

    Order by +limit 类似excel中排序+筛选功能,适用于业务中要求提供不同类别的特定数据组合,比如要提供最近半年内各档活动中销量排名前五的商品,或者是每个月成交金额最高的3位用户的信息。

    例题解析:查询平均成绩大于85的所有学生的学号、姓名和平均成绩

    68259a3377046b25cdf76de9dbc94d55.png

    所涉及知识点:

    多表联结:判断涉及几张表,通过哪列进行联结和联结方式是哪种,联结方式和对应语句见下图:

    ee3062e4f9c1cdad65216159056c7f97.png
    图片来源于:猴子聊数据分析

    常规是2表联结,但有时也会3表联结,这时就需要通过中间表来实现联结。

    举一反三:

    现实业务场景中,企业的数据库报表都会分门别类,因此多表联结是必须要会的技能点,具体联结类型则要从业务需求方来判断,比如说在电商业务场景中,经常要分析的报表有订单和产品,当要分析某些商品是否热销时就要将2表进行联结。

    2.6 Case表达式

    例题解析:使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称

    a3e7a698a80031399a0c7756b8944731.png

    所涉及知识点:

    Case表达式:用于多条件判断,比如上题的成绩分组,但要注意end不能遗忘。

    举一反三:

    业务场景中,数据分类是不可或缺的一项工作,比如RFM模型,电商业务中会对产品、用户和竞品进行分类,由此来看这些对象的变化趋势。

    补充:case表达式+group by+max()等函数可以实现表格行列互换,见下:

    2c27553320cce5e0d3c71d4a494d4b98.png

    那么,在提高SQL查询的效率上,我们需要知晓哪些呢?

    c4274eb9f9d8cd024174927ce1a31825.png

    以上Sql题是我从众多练习题中挑选而出,包含知识点相对较全。在实际技能提升中,我们要不断练习,且练习需要持之以恒,而不是三天打鱼两天晒网,与同行者共勉!

    展开全文
  • 前几天,刷数据分析的面试题,期间遇到几个SQL题,其中有一道需要用到Oracle中的nvl函数,还有关于数据类型转换,时间格式转换的函数等等;“虽说有印象,但确实不会写了!可要是看一眼,马上就能做出来!”我就是...

    c85931a99b7a40a083b7157e1db582d9.png

    前几天,刷数据分析的面试题,期间遇到几个SQL题,其中有一道需要用到Oracle中的nvl函数,还有关于数据类型转换,时间格式转换的函数等等;

    “虽说有印象,但确实不会写了!可要是看一眼,马上就能做出来!”我就是这种感觉。

    所以,就有了接下来这篇文章:复盘之前写过的SQL函数!

    只需要看这篇文章,理解这些函数就可以了,不必要自己敲代码,因为我已经将运行结果截图了,理解为上!
    重要提示:大家用电脑看效果比较好,手机屏幕太小,sql看不完整!

    关于数据库介绍的,请点求知鸟:分析视角之主流数据库

    关于mysql的,请点求知鸟:数据分析:你需要知道的mysql相关知识

    业务上常用的SQL模型,请点求知鸟:数据分析之SQL:常用模型

    SQL实战项目,请点求知鸟:实战:SQL分析+Excel可视化

    Oracle篇

    1、Null是没有,不参与计算。可以将null转换为0参与计算;

    nvl(字段,0):字段非空显示本身,null补为0!nvl(字段,1,0):字段非空替换为1,null补为0!注意:''是空字符串,空也是字符!

    2、链接符||与“q”

    select first_name||‘’ from employees;--right  
    select first_name||q'[]' from employees;--right 与上等价
    select first_nameq'[]' from employees;--wrong
    select first_name||"abc" from employees;--wrong
    
    链接符q需要在||后使用;除了起别名,会用到“”;在其他任何地方,都使用‘’

    3、order by双层排序

    select * from employees order by department_id asc , salary desc;
    先对department_id按照升序排序,如果department_id相等则再对排序结果按照salary降序排列 。
    select * from employees order by salary;
    select * from employees order by 8;
    select first_name ,last_name from employees order by salary;
    select first_name ,last_name from employees order by 8;
    
    1,2句:此时二者都是对全表结果排序(*),所以效果相同
    3,4句:第4句查询结果只有两个字段,8就是无效的。

    4、substr函数

    select first_name ||‘ .'||last_name ,substr(first_name ||' '||last_name,2,2 ) from employees;
    
    SUBSTR(列|字符串,开始点[,⻓度]):字符串|数字截取

    dad6aee9dd640f470ae618abb89632ee.png

    5、replace函数

    select first_name ,replace(first_name,'E','$$$$') from employees;
    

    ea23e901a135c5830853d30c86c6e14e.png

    6、Instr

    select first_name ,instr(first_name,'e',4,1) from employees;
    
    Instr 从第几个开始,第几次出现在整个字符串的位置;

    d0a0efce2e427f90832908b57bdad984.png

    7、round

    select round(59.866,2) from dual;--59.87
    select round(59.866,-1) from dual;--60
    
    round :四舍五入只能针对数字

    8、trunc

    select trunc(59.866,2) from dual;--59.86
    select trunc(59.866,-1) from dual;--50
    
    trunc:可以对数字或日期起作用,不能对字符串起作用!

    cb7c78897a0a994e09139ed3acf58ae7.png

    99752dfe501c6f2ba0e33f5a394474b7.png

    9、to_number

    select to_number('$12332123.12','$99999999.00') from dual;--right 12332123.12 前后格式一致,位数一致;前是数字,后接数字类型
    select to_number('$12332123.12','99999999.00') from dual;--wrong qianhou format type 前有 $,后没有,就报错!
    

    10、To_char &to_number to_char &to_date 字符,数字,时间格式互转!

    select to_char(to_number('$12332123.12','$99999999.00'),'$99999999.00') from dual;-- $12332123.12
    select to_date('2018-01-01','yyyy-mm-dd')+3 from employees;
    select to_char(to_date('2018-01-01','yyyy-mm-dd'),'2018-01-01') from employees;--wrong '2018-01-01'不是日期类型!
    select to_char(to_date('2018-01-01','yyyy-mm-dd'),'yyyy-mm-dd') from employees;
    select to_char(to_date('2018-01-01','yyyy-mm-dd'),'yyyy-mm-dd')+3 from employees;--wrong 
    

    小练习:求一年多少天&简单嵌套

    Select to_char((add_months(trunc(sysdate,'yyyy'),12))-1),'ddd') from dual;
    
    分解步骤:trunc(sysdate,'yyyy')—显示结果2018-01-01
    add_months(trunc(sysdate,'yyyy'),12)—显示结果2019-01-01
    (add_months(trunc(sysdate,'yyyy'),12))-1—显示结果2018-12-31
    to_char((add_months(trunc(sysdate,'yyyy'),12))-1),'ddd')—ddd表示一年的第几天

    扩展为:求一个员工工作了多少年,零多少月,零多少天

    这个题比较绕,即便你能抠出来,也不优雅!

    下面是比较优雅的写法:

    select   trunc( months_between(sysdate,hire_date)/12) ,   trunc(mod(  months_between(sysdate,hire_date),12 )  ) ,trunc(sysdate-add_months(hire_date,months_between(sysdate,hire_date))) from employees;
    
    利用了add_months的特征:忽略小数

    11、nullif

    select first_name ,length(first_name) "export1" ,
           last_name ,length(last_name) "export2" ,
           nullif (length(first_name)❶,length(last_name)❷) "result"
           from employees;
    
    nullif 若❶与❷相等,则返回空;若不等,则返回❶

    1849bdba41568e18e3eb2992414f7b7c.png

    12、关于求平均工资中的最高工资,并显示部门号的讨论

    select deptno,avg(sal) from emp group by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno);--right
    select * from (select deptno,avg(sal) sal from emp group by deptno order by sal DESC) where rownum = 1;--right
    
    第一句用的是having
    第二句用的是子查询+伪列!
    伪列:rownum,rowid

    13、分析函数:sum(字段)over()

    select empno,sal,sum(sal)over(order by sal) from emp;
    

    e7958dbc6cb181fe31e76c3e777625d4.png
    先执行over后的排序,再对排序结果执行累加!
    select empno,ename,sal,sum(sal) over( partition by deptno order by sal) ,deptno from emp;
    

    db3a2dbbd9bc8e1252ef96f5840e58c2.png
    先执行over后的pytition by分组,然后对分组执行求和!
    partition by和group by的区别:group by 后的分组键限制select 后的的字段!而partition by不会!Group by 有分组去重复的作用;partition by分组求和不去重

    14、Row_number()over():会排名不会并列

    select empno,deptno,sal,row_number() over(partition by deptno order by sal desc) from emp;
    

    72bb45881818a6db10c16e43ad904ca2.png
    只要上图清清楚楚,实在没有解释的必要!

    练习:取每个部门员工sal第一的人

    select empno,deptno,sal from (select empno,deptno,sal ,row_number()over(partition by deptno order by sal desc) "addup" from emp) where addup=1;
    

    9ff16282c1f4640d7e7d9756521296d7.png
    取每个部门员工第一,我们最先想到的可能是对deptno部门号分组,然后取第一!
    可这个需求还需要提供empno员工编号,以及sal工资的信息,所以就考虑用row_number()over(partition by deptno order by sal desc)这个函数!
    此外,相似的排序函数还有:
    row_number()over():不显示并列!出现:1,2,3,4,5的排次
    rank()over():区别是,显示并列,并且占名次!也就是会出现:1,1,3,4,5的排次
    dense_rank()over():显示并列,并列不占名次!也就是会出现:1,1,2,3,4的排次

    15、高级分组rollup

    Select department_id,job_id,sum(salary) from employees where department_id<60
    Group by rollup(department_id,job_id);
    

    16d9395b28926e41082e2623c8d8bd78.png
    什么,看不懂?!这么清楚,你还看不懂?
    rollup(a,b) 统计列包含:(a,b)、(a)、()
    rollup(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a)、()
    roll up(deptno,job)= group by (deptno,job)+ group by (deptno)+ sum(sal)
    这下看懂了没?要还不懂,我也没招了!

    16、高级分组cube

    select deptno,job ,sum(sal) from emp group by cube(deptno,job);
    

    43562f5e92adf922e25363505a752dae.png
    cube(a,b) 统计列包含:(a,b)、(a)、(b)、()
    cube(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
    cube(deptno,job)= group by (deptno,job)+ group by deptno
    + group by job+ sum(sal);

    总结:rollup/cube都是跟着group by的后面,依旧要遵循group by的逻辑(select 字段要是连接键!);牛逼之处,排序范围更大了,更接地气了!

    17、牛逼的函数grouping sets

    •Grouping sets 是对group by 子句的进一步扩充
    •使用Grouping sets在同一个查询中定义多个分组集
    •Oracle中对grouping sets 子句指定的分组集进行分组后用union all 操作将各分组结果结合起来
    •Grouping sets的优点:
    •只进行一次分组即可
    •不必书写复杂的union语句
    •Grouping sets 中包含的分组越多性能越好

    目前为止,至少已经学过1个oracle独有的函数nvl(,),至于row_number(),rank() over(order by 列名),grouping 和 grouping set函数,cube,rollup这些函数是Oracle特有的吗?我也不知道啊,小伙伴们要是知道,请在评论区留言哦!

    与case when 类似功能的decode (Oracle独有)!以及对nvl的补充,nvl2(,,)
    select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);
    

    8c5ed833d150c5100fb8b808bf80582a.png
    select deptno,job,ename,avg(sal) from emp group by grouping sets ((deptno,job),(job,ename));
    

    8ece4d60039c0c36809bc7bdd02f19a0.png
    group by grouping sets ((deptno,job),(job,ename));
    =group by (deptno,job) union all group by (job,ename)

    再看另一种变形:

    select deptno,job,mgr,ename,avg(sal) from 
    emp group by grouping sets (deptno,job),
    grouping sets (mgr,ename);
    
    与上题不同的是:此题grouping sets()又写了一遍!
    那么此题分组的原则是:
    (a+b)*(c+d)=ac+ad+bc+bd;最终以union all形式显示分配结果的四种情况

    54acfbea76c2897c33c3059d0c42497e.png

    18,伪列rownum

    rownum和rowid都是伪列,但是两者的根本是不同的,rownum是根据sql查询出的结果给每行分配一个逻辑编号,所以你的sql不同也就会导致最终rownum不同,但是rowid是物理结构上的,在每条记录insert到数据库中时,都会有一个唯一的物理记录 .

    •我们要查找emp表格中第5到第10行的数据;

    select rownum,e.* from emp e where rownum>5 and rownum<10 ---wrong
    select * from (select rownum rn,e.* from emp e) where rn between  5 and 10--right
    
    rownum要查找的范围必须包含1;因为rownum是从1开始的;通常都是将rownum 起别名作为一个子查询!

    同样的,我们要查找emp表格中倒数第5行到第8行的值

    select * from (select rownum rn,e.* from emp e ) where rn between (select count(*)-7 from emp ) and (select count(*)-4 from emp ) ; --between 小 and 大
    

    19,正则表达式

    768a3da365980d0aded0692118af3a41.png
    select first_name,last_name from employees where regexp_like (first_name,‘^Ste(v|ph)en$’);---^ 起始位置$结束位置 |或
    select first_name from employees where regexp_like(first_name,‘^al(an|yss)a$’,‘i’);--不分大小写;
    select first_name from employees where regexp_like(first_name,‘^al(.){2}a$’,‘i’);--任意字符出现2次
    select first_name from employees where regexp_like(first_name,‘^al[^y]+a$’,‘i’);--[y]该位置就只能出现y;[^y]该位置不能是y
    

    总结:

    95e68ce61e68528070667507430b7e84.png

    tips:

    整理不易,我说这篇文档包含我5个月时间的集训精华,大家可能不信!但的确是集训内容之一,还有其它的东西都糅杂在我的专栏里面了

    数据分析zhuanlan.zhihu.com
    574e53c54f8ee6667db496dcf1b60732.png

    总之,花了我不少心血,点赞收藏大家看着办吧,就是要赞又咋滴,我不要,你能给吗?!哈哈

    展开全文
  • 现有这样的产品月销售明细表希望按下面的样子,按类别分组统计各产品销售额,销售额从高到低排序,并增加组内累积及占比。数据文件:产品销售明细.xls制作报表过程1、 打开润乾报表工具并新建空白报2、 设置报表的...

    现有这样的产品月销售明细表

    希望按下面的样子,按类别分组统计各产品销售额,销售额从高到低排序,并增加组内累积及占比。

    数据文件:产品销售明细.xls

    制作报表过程

    1、 打开润乾报表工具并新建空白报

    2、 设置报表的数据集为 Excel 文件

    3、 绘制报表,包括样式、报表单元格的表达式等

    其中表达式部分:

    A2:=ds1.group(产品类别; 产品类别:1)

    B2:=ds1.group(产品名称:1;;ds1.sum( 订单金额):-1)

    C2:=ds1.sum(订单金额)

    D2:=C2+D2[B2:-1]

    E2:=D2/A3

    A3:=sum(C2{}),并将 A3 左主格设置为 A2,如下

    且,勾选“隐藏行”,A3 辅助 E2 计算占比,不需要显示

    4、 保存,预览报表效果

    5、 导出报表结果

    看导出效果

    再用新的月份数据绘制同样报表时,只需要将第 2 步引用的 Excel 数据文件替换或重新选择新的 Excel 数据文件即可,报表无需重画,方便快捷。

    展开全文
  • 索引优化分析性能下降SQL慢查询语句写的差索引失效单值索引多值索引关联查询太多join(设计缺陷或不得已的需求)服务器调优以及各个参数设置(缓冲、线程数等)执行时间长等待时间长常见通用的JOIN查询共有的INNER JOIN...

    索引优化分析

    • 性能下降SQL慢

      • 查询语句写的差

      • 索引失效

        • 单值索引

        • 多值索引

      • 关联查询太多join(设计缺陷或不得已的需求)a61223da759679ffa513b1eba29cc90b.png

      • 服务器调优以及各个参数设置(缓冲、线程数等)

    • 执行时间长

    • 等待时间长

    • 常见通用的JOIN查询

      • 共有的INNER JOIN

      • 左表的共有 LEFT JOIN

      • 右表的共有 RIGHT JOIN

      • 左边独有 where b.key is null

      • 全链接 full outer join

      • ab都没用a.key is null or b.key is null

    索引简介

    • 是什么

      • 排好序。

      • 查询快。

      • 数据库系统还维护满足特定查找算法的数据结构。B+TREE索引(此处不展开讨论)

      • 索引:是帮助MySQL高效获取数据的数据结构,可以得到索引的本质。索引是一种数据结构。

      • 高效查询,类似新华字段。

      • 排好序的快速查找数据结构。(order by …)

    • 优势

      • 提高索引的检索效率,降低数据库的IO成本。

      • 通过数据进行排序,降低数据的排序成包,降低了cpu的消耗。

    • 劣势

      • 实际上索引也是一张表,该表保存了主键索引字段,并指向实体表的数据,占空间。

      • 索引提高了查询,但是降低了更新。

      • 提高效率的一个因素,如果有大数据量的表,花时间建立最优秀的索引,或者优化查询。

    • MySQL索引分类

      • 单值索引

        • 一个索引只包含单个列,一个表可以有多个单列索引。

      • 唯一索引

        • 索引列的值必须唯一,但允许有空值。

      • 复合索引

        • 一个索引包含索格列

      • 基本语法

      • 哪些需要创建索引

        • 主键索引

        • 频繁查询

        • 外键管理

        • 组合索引

        • 排序字段

        • 统计或分组

      • 哪些情况不需要创建索引

        • 表记录少

        • 300百万就差不多开始优化

        • 经常增删改的不创建索引

        • 重复的值

    • MySQL索引结构

      • BTree索引

      • Hash索引

      • full-text全文索引

      • R-TREE索引

    性能分析

    一、MySQL常见的性能瓶颈
    • cpu饱和

    • io内存

    • 硬件瓶颈,top,free,iostat,vmstat

    • 是什么

      • 模拟优化器执行的SQL查询语句,从而知道MySQL是如何处理SQL语句的,分析查询语句或者表结构。

    • 能干什么

      • Explain + sql

      • 表的读取顺序

      • 数据读取操作的操作类型

      • 哪些索引可以被使用

      • 哪些哪些索引实际被使用

      • 表之间的引用

      • 每张表有多少行优化器查询

    二、Explain每个字段的详细描述
    • id

      • 数字大的优先级高。(derived衍生)

      • id不同,如果是子查询,id的需要会递增,id值越大优先级越高,先被执行。

      • id相同,执行顺序从上往下

      • id相同与不同,同时存在。

    • select_type

      • 有哪些?

        • simple

          • 简单的select查询,查询中不包含子查询或者union

        • primary

          • 查询中若包含任何复杂的子部分,最外层查询则被标记为primary

        • subquery

          • 在select 或者where 列表中包含了子查询

        • derived(衍生)

          • 在from列表中包含的子查询被标记为derived,MySQL会递归执行这些子查询,把结果放在临时表里。

        • union

          • 若第二个select出现在union之后,则被标记为union。若union包含在from子句的子查询中,外select被标记为derived.

        • union result

          • 两个union查询的结果。

      • 查询的类型,主要是用于区别普通查询,联合查询,子查询等复杂查询

    • type

      • 类型有:all,index,arnge,ref,eq_ref,const,system,null

    • system

      • 表只有一行记录,平时不会出现,忽略不计

    • const

      • 一次索引,用于比较primary 或者union 索引。因为只匹配一行记录。例如将主键置于where列表,MySQL就能将该查询转换成一个常量

    • eq_ref

      • 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或者唯一索引

    • ref

      • 非唯一性索引扫描,返回匹配某个单独的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行。可能会找到多个符合条件的行,所以属于查找和扫描的混合体。

    • range

      • 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就算在where语句中出现的between,<>,in,等的查询。这种范围扫描比全表扫描要好,因为它只需要开始结束于索引的两个点,不用全表索引。

    • index

      • index与all区别为Index只遍历索引树。

    • all

      • 全表扫描

    • possible_keys

      • 可能用到的索引。一个,多个。

      • 不一定被实际查询引用。

    • key

      • 实际使用到的索引。

      • 查询中若使用到了全文索引

      • 覆盖索引:查询的字段和索引字段一一对应。(查询列要被所建的索引覆盖)

      • 8ab68305ff162f608666123874e18b7a.png

    • key_len

      • 索引使用的字节数。索引字段的最大可能的长度,不是实际。(越小越好)

    • ref

      • 显示索引的那一列被使用。最好是常量

    • rows

      • 大致读到的行数。

    • Extra

      • 在没用group by 子句的情况,基于索引优化min/max操作或者对用MyIAAM存储引擎优化count()操作,不必等到执行阶段再进行计算。查询执行计划生成阶段即完成优化。

      • 表示使用了覆盖索引,避免了访问表的数据行。如果同时出现了using where,表明索引被用来执行索引键值的查找。如果没用同时现象using where ,表明缩影用来读取数据而非执行查询动作。

      • 新建了内部的临时表,常见于order by 和分组查询group by

      • mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为文件排序。(排序未命中索引)

      • using firesort

      • sql+\G

      • using temporary

      • using index(还可以哦)

      • using where

      • using join buffer:使用了连接缓存

      • impossible where:不能获取到任何元素

      • select tables optimized away

      • distinct:找到第一个匹配的元组后就收工。

    • 从最好到最差system>const>eq_ref>ref>i>range>index>all

    • 一般来说,保证查询至少达到range级别,最好能达到ref

    性能分析

    一、索引分析

    • 单表索引

    • 两张表

      • 左链接加到右表

      • 右链接加到左表

    • 三张表

      • 加到后面两张表

    • 结论

      • 小结果集驱动大结果集

      • 优先优化内部循环

      • join buffer的设置

    二、避免索引失效

    • 全值索引最好

    • 最左匹配法则

    • 不在索引列上做任何操作(计算,函数,类型转换,会导致索引失效而转向全表扫描)

    • 存储引擎不能使用索引范围条件右边的列(范围之后全失效)

    • 尽量使用覆盖索引。不要select *

    • mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描

    • is null,is not null也无法使用索引(索引失效)(%加右边)

      • 解决办法:覆盖索引。

    • like 以通配符开通(%aaa)索引也会失效,变成全表扫描

    • 字符串不加单引号索引失效

    • 少用or,用它来连接时会索引失效

    我是失忆,一个热爱技术的宅男,文章有任何问题您都可以在留言中指出。欢迎留言。
    展开全文
  • SQL实现占比、同比、环比指标分析

    千次阅读 2019-09-27 14:29:15
    基本思路:求出分组、汇总–>笛卡尔积连接–>相除 1.品类销量占比(mysql和oracle两个版本) -- mysql 版本 SELECT NAME AS 品类名称, num AS 销售量, concat( format( num / total * 100, 2 ),'%') ...
  • sql占比,求完成度,比例百分比

    千次阅读 2020-04-01 14:00:50
    表格数据大概这样 status ... 需求大概就是占比完成度,从表格看 分组666的总共4条,完成2个,未完成2个 分组777的总共2条,完成1个,未完成1个 SELECT sum(case when status=0 then 1...
  • 使用评级函数评级函数(ranking function)用于计算等级、百分点、n分片等等,下面是几个常用到的评级函数:RANK():返回数据项在分组中的排名。特点:在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的...
  • mysql分组统计占比/百分比

    万次阅读 2019-05-16 10:12:35
    需求:统计当前数据库user_tasks表中,不同status字段值对应的记录条数及占比 sql语句: SELECT `status`, number, concat(round(number / total * 100.00, 2), '%') percent FROM ( SELECT * FROM ( ...
  • mysql分组统计及占比

    千次阅读 2019-11-23 18:47:26
    统计各个渠道的人数占比sql实现: select channel, g_cnt/cnt from(select * from(select channel, count(distinct uid) g_cnt from user_profile group by channel)t1 join (select count(*)cnt from ...
  • 就是用部门来进行分组,比如 a部门有2个商品,金额总额为300 ,那么001商品就占a部门的销售总额的0.33 。(100/300这样) ,求大神赐教![img=...
  • sql 计算某个值的占比 在本文中,我将重点介绍使用简单的SQL SELECT语句对满足特定条件的表中的行数进行计数,并将结果按表的特定列分组。 这些都是SQL的基本概念,但是将它们混合使用就可以对关系数据库中存储的...
  • 需求:计算EMP表按照deptno分组,计算每组的SAL总和与所以记录总和中的占比 解决方法:通过每组SUM(SAL)窗口函数再除以记录总和来实现. 注: 数据库数据集SQL脚本详见如下链接地址 员工表结构和数据初始化SQL...
  • 需求:计算EMP表按照deptno分组,计算SAL在每组总和中的占比 解决方法:通过每行的SAL除以每组的SUM(SAL)来实现. 注: 数据库数据集SQL脚本详见如下链接地址 员工表结构和数据初始化SQL脚本 SQL代码 -- SQL ...
  • 已经对试题做了化名处理。 表记录如下: ...意思就是,题目要求就是按照deptno来分组,然后在分组内将记录按照sal来排序,并且求得每条记录的sal在该分组内总和sal的占比。 select deptno,  empn
  • Oracle分析函数使用总结 1.使用评级函数 评级函数(ranking function)用于计算等级、百分点、n分片等等,下面是几个常用到的评级函数: RANK():返回数据项在分组中的排名。特点:在排名相等的情况下会在名次...
  • 本次分享内容为SQL的窗口函数,内容主要有以下几点:什么是窗口函数如何使用窗口函数聚合函数作为窗口函数窗口函数的移动平均什么是窗口函数窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合...
  • SQL语句总结

    2021-04-07 20:28:43
    SQL语句 一个班级分数大于90分的人数占比,round函数,coucat select round(T1.co / T2.totalCo * 100, 1) from (select count() AS co from student s where s.score >= 90 ) T1, (select count() AS co from ...
  • Oracle分组统计,rollup函数使用示例

    千次阅读 2018-05-25 15:45:26
    要查询的表结构以及测试数据如下:rollup_test_tableyyyyMMcum_user(累计用户)new_user(新用户)2018/1862018/286分组统计并计算新用户占比的查询sql:select nvl(yyyyMM,'总计') yyyyMM, sum(cum_user) ...
  • 一.窗口函数(分析函数)的 常用分析场景某个...• 如何计算各商品累销售占比?• 大于等当前订单金额的订单比例有多少?• 每个会员某段时间内最大,最小的订单金额?• …….二.窗口函数的特点具有同时分组(pa...
  • sql server 计算男女比例 百分比

    万次阅读 2016-01-25 17:23:40
    --分组列出性别 计算男女各人数 和所占比例 select tsex ,count(*) from person_info where ttype='在职人员' group by tsex --总人数 select count(*) from person_info --占比 select cast( convert (decimal(18,2...
  • --分组列出性别 计算男女各人数 和所占比例select tsex ,count(*) from person_info where ttype='在职人员' group by tsex--总人数select count(*) from person_info--占比select cast( convert (decimal(18,2),100...
  • --分组列出性别 计算男女各人数 和所占比例select tsex ,count(*) from person_info where ttype='在职人员' group by tsex--总人数select count(*) from person_info--占比select cast( convert (decimal(18,2),100...
  • 关于inner join 与 left join 之间的区别,以前以为自己搞懂了,今天从前端取...需求是从数据库查数据,在前端以柱形图的形式展现出来,查到的数据按行业分组,显示每个行业的户数及户数占比,涉及到的字段有A表的用...
  • 需求:根据商品大类对商品按金额从小到大排序,前30%为低档,30% 80%为中档,高于80%为高档,打上标签 数据: 商品大类 商品名称 商品金额 商品档次标签 ...3、与产品表左连接后,通过排序值与最大值来计算排序占比
  • cellReport 功能介绍

    千次阅读 2008-04-18 10:34:00
    基本功能简介:1、支持从多数据库取数2、支持多数据集...因此,自动支持累加、占比、移动平均等功能8、支持所有的基本报表:清单式、一次分组、多次分组、交叉表等9、支持dashborad,同一页面显示多张相关或不相关报表1
  • 代码飞检01

    千次阅读 2016-03-01 10:45:49
    结果分析: 本次飞检采取专家分组,线下sonar工具和会审结合的形式,听取技术人员讲解代码的业务功能,数据流,后台调用,代码规模在2000行左右,每个...3、检出的性能等问题占比30%,主要集中在SQL的索引缺少,SQL
  • 需求是从数据库查数据,在前端以柱形图的形式展现出来,查到的数据按行业分组,显示每个行业的户数及户数占比,涉及到的字段有A表的用户数、总用户数和B表的行业名称。本来是不管查不查的到数据,在X轴都应该显示...
  • 数据分析的入门技巧

    2020-10-18 15:14:58
    2.Excel透视表(完成数据的分组统计、排序、求平均、行列计算占比等常用数据分析功能) 小结:动手实践>遇到问题>网上搜索>官网help>再实践>记录核心技巧案例 2、 Hive SQL篇 两个重要常用技巧:行列...

空空如也

空空如也

1 2
收藏数 33
精华内容 13
关键字:

sql分组占比