精华内容
下载资源
问答
  • 上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE用法以及区别。本章测试环境...
    c3a179e9df02eca3afcfc2bddbd6acd7.pngc3a179e9df02eca3afcfc2bddbd6acd7.png

    上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次的基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE的用法以及区别。

    本章的测试环境:

    操作系统: 中标麒麟6  64位

    数据库版本:达梦8.1

    达梦数据库客户端:DM管理工具

    分析函数

    本次重点讲解相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT两个分析函数。

    相邻函数LAG和LEAD ●

    LAG和LEAD函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行数据筛选。这种操作可以代替表的自联接,不过LAG和LEAD有更高的效率。

    语法:

    LAG(EXP_STR,OFFSET,DEFVAL)OVER()LEAD(EXP_STR,OFFSET,DEFVAL)OVER()EXP_STR:要取的列OFFSET:取偏移后的第几行数据DEFVAL:没有符合条件的默认值

          ①比如获取某公司各员工薪资情况,并同时展示同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司比该员工高1级的员工薪资情况,总公司比该员工低1级的薪资情况,sql样例参考如下:

     select  dept.department_name,         emp.employee_name,         emp.salary,         lag(salary)over(partition by dept.department_name order by salary desc) dept_lag,         lead(salary)over(partition by dept.department_name order by salary desc) dept_lead,         lag(salary)over(order by salary desc) total_lag,         lead(salary)over(order by salary desc) total_lead    from employees emp, department dept   where emp.department_id = dept.department_id(+);
    输出结果展示如下(数值列依次为该部门员工薪资,同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司内比该员工高1级的员工薪资情况,总公司内比该员工低1级的薪资情况):38300a8f6bc86f694b4981568cdb3f17.png

    相邻函数也常用于公司年终财务报表计算同比和环比的场景。

          比如获取销售的业绩情况,同比分析相同月份上年和下年的情况,环比分析上月和下月的销售情况,sql样例参考如下:

    select to_char(trunc(logtime, 'mm'),'yyyy-mm-dd') "月份",       to_char(sum(price),'fm999990.00') "当月收入",       -- 环比分析,与上个月份进行比较       to_char(lag(sum(price), 1) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "环比上月",       -- 环比分析,与下个月份进行比较       to_char(lead(sum(price), 1) over(order by trunc(logtime, 'mm')),'fm999990.00') as "环比下月",       -- 同比分析,与上个年度相同月份进行比较          to_char(lag(sum(price), 12) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "同比上年",       -- 同比分析,与下个年度相同月份进行比较        to_char(lead(sum(price), 12) over(order by trunc(logtime, 'mm')),'fm999990.00') as "同比下年"  from orderproduct group by trunc(logtime, 'mm') order by 1 desc;
    输出结果展示如下(数值列依次为当月收入,环比上月收入,环比下月收入,同比上年收入,同比下年收入):ec34b223e87ee1e5d6598fc1ecc87781.png

    占比函数RATIO_TO_REPORT ●

    占比函数常用于财务中计算收支占比。用于取某个值占总和的百分比。

    语法:

    RATIO_TO_REPORT(EXP_STR)OVER()RATIO_TO_REPORT() 括号中表达式EXP_STR就是分子,OVER() 括号中就是分母,分母缺省就是整个占比。

    比如获取某公司每个城市员工人数分布和薪资待遇分布情况,sql样例参考如下:

     select  c.city_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by c.city_name;

    从输出结果中可以看出各个区域公司人员占用百分比,各区域薪资占用百分比;如下结果集可以作为报表饼图的原型。输出结果展示如下:

    c3e5146f23c551b7e57b371f6d1fa567.png

    RATIO_TO_REPORT 可以结合partition by 使用。

    比如获取公司每个员工薪资及员工薪资在所在部门的薪资占比,每个部门总薪资及部门薪资在公司的占比,sql样例参考如下:

    select department_name, employee_name, salary, g1,       sum(salary) over(partition by decode(g1, 0, department_name,  null), g1) sum_salary,       ratio_to_report(salary) over(partition by decode(g1, 0, department_name, null), g1) salaryrate from ( select dept.department_name,               emp.employee_name,               sum(emp.salary) salary,               grouping(dept.department_name) + grouping(emp.employee_name) g1          from employees emp, department dept         where emp.department_id = dept.department_id         group by rollup(dept.department_name, emp.employee_name))

    输出结果展示如下(数值列依次员工个人薪资/部门总薪资,分组值,公司薪资合计/部门薪资合计,薪资占比):

    7bacf9ba7f287d9c5ab1810e96693ebe.png

    从上面的例子中我们看到了group by rollup这个语句,这里也顺带讲解下分组函数group by子句的使用方法。

    分组函数GROUP BY子句 

    GROUP BY 子句是 SELECT 语句的可选项部分。

    它定义了分组表。

    GROUP BY 子句语法如下:

    f11dac9ba12daf642bd9934d8b8f24d7.png

    GROUP BY 子句

    6dad45fc81c82eb7e6ddbd653caeaec2.png

    ROLLUP 项

    884a73ff0b8b9398959edd428f301a5e.png

    CUBE 项

    5eaac068f985b6547cc61a297445b206.png

    GROUPING SETS 项

    cfca1c4dd421a5ff5aad4f43a5086a3f.png

    GROUP 项 ( )

    f10b6f2639903209a94338292e1698e4.png

    HAVING 子句

    这里介绍ROLLUP、CUBE、GROUPING SETS三项的用法和差异。

    用法group by [rollup|cube|grouping sets](colomn)

    要弄明白rollup,cube和grouping sets,就要知道group by的使用场景, group by 为对列进行分组,只展现分组统计的值,而rollup 为分层次展现,cube为展现列中所有层次,grouping sets只展现列中单一层次。三者都是group by子句的扩展。

    ROLLUP:可以为每个分组返回小计记录以及为所有分组返回总计记录。ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

    CUBE:可以返回每一个列组合的小计记录,同时在末尾加上总计记录。CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

    GROUPING SETS:可以返回每一个列的小计记录。GROUPING SETS生成的结果集显示了所选列中值的单一的聚合。

    例如:group by rollup(a, b, c) 首先会对(a, b, c)进行group by,然后对(a, b)进行group by,然后是(a)进行group by,最后对全表进行group by操作。

    group by cube(a, b, c),首先会对(a, b, c)进行group by,然后依次是(a, b),(a, c),(a),(b, c),(b),(c),最后对全表进行group by操作。

    grouping sets (a, b, c),首先会对(a)进行group by,然后依次是(b),(c),不对全表进行group by操作。

    具体区别可以查看如下样例。

    ● ROLLUP ●

    比如获取公司各城市每个部门员工人数分布和薪资分布情况,使用group by rollup的sql样例参考如下:
    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by rollup (c.city_name, dept.department_name);
    从结果集中可以看出group by rollup聚合了城市和部门组合,城市组合,所有组合三种情况。输出结果展示如下:19e6e961b40e77fde9620c1e606020d6.png

    CUBE ●

    上例中的sql将rollup换成cube,sql样例参考如下:

      select c.city_name,  dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by cube (c.city_name, dept.department_name);

    从结果集中可以看出group by cube聚合了城市和部门组合,城市组合,部门组合,所有组合四种情况。结果集展示如下:

    8e2b7219139807c7bf992345a50e3bdc.png

    GROUPING SETS ●

    将上例中sql的cube换成grouping sets,sql样例参考如下:

    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by grouping sets (c.city_name, dept.department_name);

    从结果集中可以看出grouping sets聚合了城市组合,部门组合两种情况,没有进行组合分组合计。输出结果展示如下:

    d3ac01933d683a48a465c8eb2efb490e.png

    好了,本次分享就到这里了,感谢大家的关注。后期精彩敬请期待!

    f3e62ca0b41f34f5a087aa63ec0463ec.png

    ——     END     ——

    往期精选

    a5bf04928eba446c8c838d667d0d99d4.png

    干货分享|达梦数据库常用库函数和分析函数(一)

    干货分享DM数据库如何查看和删除统计信息

    ea247b5a240e47b7db60204f7c902e2d.gif

    展开全文
  • 上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE用法以及区别。本章测试环境...
    ef4a4f6035331671735cce744c915cdc.pngef4a4f6035331671735cce744c915cdc.png

    上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次的基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE的用法以及区别。

    本章的测试环境:

    操作系统: 中标麒麟6  64位

    数据库版本:达梦8.1

    达梦数据库客户端:DM管理工具

    分析函数

    本次重点讲解相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT两个分析函数。

    相邻函数LAG和LEAD ●

    LAG和LEAD函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行数据筛选。这种操作可以代替表的自联接,不过LAG和LEAD有更高的效率。

    语法:

    LAG(EXP_STR,OFFSET,DEFVAL)OVER()LEAD(EXP_STR,OFFSET,DEFVAL)OVER()EXP_STR:要取的列OFFSET:取偏移后的第几行数据DEFVAL:没有符合条件的默认值

          ①比如获取某公司各员工薪资情况,并同时展示同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司比该员工高1级的员工薪资情况,总公司比该员工低1级的薪资情况,sql样例参考如下:

     select  dept.department_name,         emp.employee_name,         emp.salary,         lag(salary)over(partition by dept.department_name order by salary desc) dept_lag,         lead(salary)over(partition by dept.department_name order by salary desc) dept_lead,         lag(salary)over(order by salary desc) total_lag,         lead(salary)over(order by salary desc) total_lead    from employees emp, department dept   where emp.department_id = dept.department_id(+);
    输出结果展示如下(数值列依次为该部门员工薪资,同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司内比该员工高1级的员工薪资情况,总公司内比该员工低1级的薪资情况):f22b6f9c6d1d17beb53406f8839765e9.png

    相邻函数也常用于公司年终财务报表计算同比和环比的场景。

          比如获取销售的业绩情况,同比分析相同月份上年和下年的情况,环比分析上月和下月的销售情况,sql样例参考如下:

    select to_char(trunc(logtime, 'mm'),'yyyy-mm-dd') "月份",       to_char(sum(price),'fm999990.00') "当月收入",       -- 环比分析,与上个月份进行比较       to_char(lag(sum(price), 1) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "环比上月",       -- 环比分析,与下个月份进行比较       to_char(lead(sum(price), 1) over(order by trunc(logtime, 'mm')),'fm999990.00') as "环比下月",       -- 同比分析,与上个年度相同月份进行比较          to_char(lag(sum(price), 12) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "同比上年",       -- 同比分析,与下个年度相同月份进行比较        to_char(lead(sum(price), 12) over(order by trunc(logtime, 'mm')),'fm999990.00') as "同比下年"  from orderproduct group by trunc(logtime, 'mm') order by 1 desc;
    输出结果展示如下(数值列依次为当月收入,环比上月收入,环比下月收入,同比上年收入,同比下年收入):8602de6312321014dde94224e9dc19fc.png

    占比函数RATIO_TO_REPORT ●

    占比函数常用于财务中计算收支占比。用于取某个值占总和的百分比。

    语法:

    RATIO_TO_REPORT(EXP_STR)OVER()RATIO_TO_REPORT() 括号中表达式EXP_STR就是分子,OVER() 括号中就是分母,分母缺省就是整个占比。

    比如获取某公司每个城市员工人数分布和薪资待遇分布情况,sql样例参考如下:

     select  c.city_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by c.city_name;

    从输出结果中可以看出各个区域公司人员占用百分比,各区域薪资占用百分比;如下结果集可以作为报表饼图的原型。输出结果展示如下:

    d18fa53815893d872f9b45936dab98e3.png

    RATIO_TO_REPORT 可以结合partition by 使用。

    比如获取公司每个员工薪资及员工薪资在所在部门的薪资占比,每个部门总薪资及部门薪资在公司的占比,sql样例参考如下:

    select department_name, employee_name, salary, g1,       sum(salary) over(partition by decode(g1, 0, department_name,  null), g1) sum_salary,       ratio_to_report(salary) over(partition by decode(g1, 0, department_name, null), g1) salaryrate from ( select dept.department_name,               emp.employee_name,               sum(emp.salary) salary,               grouping(dept.department_name) + grouping(emp.employee_name) g1          from employees emp, department dept         where emp.department_id = dept.department_id         group by rollup(dept.department_name, emp.employee_name))

    输出结果展示如下(数值列依次员工个人薪资/部门总薪资,分组值,公司薪资合计/部门薪资合计,薪资占比):

    398bd1111e1f997c7f93bf7239ad733f.png

    从上面的例子中我们看到了group by rollup这个语句,这里也顺带讲解下分组函数group by子句的使用方法。

    分组函数GROUP BY子句 

    GROUP BY 子句是 SELECT 语句的可选项部分。

    它定义了分组表。

    GROUP BY 子句语法如下:

    5e9ac97bf03dcd12015f0d6cb6b8c2c4.png

    GROUP BY 子句

    6997d080e44bc1d715fa63022c6f0c5a.png

    ROLLUP 项

    2b0768edc7c1bcebe9251fb13a480f68.png

    CUBE 项

    d073a4726607395d43814d96d557b960.png

    GROUPING SETS 项

    8723aee2920ce49012ff854a23ec7d28.png

    GROUP 项 ( )

    a2d09d3ab3d4b131f437c750aa7a480d.png

    HAVING 子句

    这里介绍ROLLUP、CUBE、GROUPING SETS三项的用法和差异。

    用法group by [rollup|cube|grouping sets](colomn)

    要弄明白rollup,cube和grouping sets,就要知道group by的使用场景, group by 为对列进行分组,只展现分组统计的值,而rollup 为分层次展现,cube为展现列中所有层次,grouping sets只展现列中单一层次。三者都是group by子句的扩展。

    ROLLUP:可以为每个分组返回小计记录以及为所有分组返回总计记录。ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

    CUBE:可以返回每一个列组合的小计记录,同时在末尾加上总计记录。CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

    GROUPING SETS:可以返回每一个列的小计记录。GROUPING SETS生成的结果集显示了所选列中值的单一的聚合。

    例如:group by rollup(a, b, c) 首先会对(a, b, c)进行group by,然后对(a, b)进行group by,然后是(a)进行group by,最后对全表进行group by操作。

    group by cube(a, b, c),首先会对(a, b, c)进行group by,然后依次是(a, b),(a, c),(a),(b, c),(b),(c),最后对全表进行group by操作。

    grouping sets (a, b, c),首先会对(a)进行group by,然后依次是(b),(c),不对全表进行group by操作。

    具体区别可以查看如下样例。

    ● ROLLUP ●

    比如获取公司各城市每个部门员工人数分布和薪资分布情况,使用group by rollup的sql样例参考如下:
    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by rollup (c.city_name, dept.department_name);
    从结果集中可以看出group by rollup聚合了城市和部门组合,城市组合,所有组合三种情况。输出结果展示如下:599dca68d523111228ac1ad866ca009d.png

    CUBE ●

    上例中的sql将rollup换成cube,sql样例参考如下:

      select c.city_name,  dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by cube (c.city_name, dept.department_name);

    从结果集中可以看出group by cube聚合了城市和部门组合,城市组合,部门组合,所有组合四种情况。结果集展示如下:

    8ff9c1c9fd56b447d5e6bb66c505e394.png

    GROUPING SETS ●

    将上例中sql的cube换成grouping sets,sql样例参考如下:

    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by grouping sets (c.city_name, dept.department_name);

    从结果集中可以看出grouping sets聚合了城市组合,部门组合两种情况,没有进行组合分组合计。输出结果展示如下:

    5396a7406dd9b2720c92c8d80619dfca.png

    好了,本次分享就到这里了,感谢大家的关注。后期精彩敬请期待!

    0a1f700fe0806cc0a5248f77a43d3f5b.png

    ——     END     ——

    5c364e90fd8daefc5729a1efa16f863f.png

    展开全文
  • 上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE用法以及区别。本章测试环境...
    15cd14f75578945dadfd202657bd4f5b.png15cd14f75578945dadfd202657bd4f5b.png

    上次主要介绍达梦数据库的常用集函数和分析函数,这次在上次的基础上继续介绍相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT等分析函数,并补充介绍分组函数GROUP BY、ROLLUP、CUBE的用法以及区别。

    本章的测试环境:

    操作系统: 中标麒麟6  64位

    数据库版本:达梦8.1

    达梦数据库客户端:DM管理工具

    分析函数

    本次重点讲解相邻函数 LAG 和 LEAD,占比函数RATIO_TO_REPORT两个分析函数。

    相邻函数LAG和LEAD ●

    LAG和LEAD函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行数据筛选。这种操作可以代替表的自联接,不过LAG和LEAD有更高的效率。

    语法:

    LAG(EXP_STR,OFFSET,DEFVAL)OVER()LEAD(EXP_STR,OFFSET,DEFVAL)OVER()EXP_STR:要取的列OFFSET:取偏移后的第几行数据DEFVAL:没有符合条件的默认值

          ①比如获取某公司各员工薪资情况,并同时展示同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司比该员工高1级的员工薪资情况,总公司比该员工低1级的薪资情况,sql样例参考如下:

     select  dept.department_name,         emp.employee_name,         emp.salary,         lag(salary)over(partition by dept.department_name order by salary desc) dept_lag,         lead(salary)over(partition by dept.department_name order by salary desc) dept_lead,         lag(salary)over(order by salary desc) total_lag,         lead(salary)over(order by salary desc) total_lead    from employees emp, department dept   where emp.department_id = dept.department_id(+);
    输出结果展示如下(数值列依次为该部门员工薪资,同部门比该员工高1级的员工薪资,同部门比该员工低1级的员工薪资,总公司内比该员工高1级的员工薪资情况,总公司内比该员工低1级的薪资情况):4f19e92eb99b683e576917276a4d7a93.png

    相邻函数也常用于公司年终财务报表计算同比和环比的场景。

          比如获取销售的业绩情况,同比分析相同月份上年和下年的情况,环比分析上月和下月的销售情况,sql样例参考如下:

    select to_char(trunc(logtime, 'mm'),'yyyy-mm-dd') "月份",       to_char(sum(price),'fm999990.00') "当月收入",       -- 环比分析,与上个月份进行比较       to_char(lag(sum(price), 1) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "环比上月",       -- 环比分析,与下个月份进行比较       to_char(lead(sum(price), 1) over(order by trunc(logtime, 'mm')),'fm999990.00') as "环比下月",       -- 同比分析,与上个年度相同月份进行比较          to_char(lag(sum(price), 12) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "同比上年",       -- 同比分析,与下个年度相同月份进行比较        to_char(lead(sum(price), 12) over(order by trunc(logtime, 'mm')),'fm999990.00') as "同比下年"  from orderproduct group by trunc(logtime, 'mm') order by 1 desc;
    输出结果展示如下(数值列依次为当月收入,环比上月收入,环比下月收入,同比上年收入,同比下年收入):491e36633e9fe933468a0db331d4f77f.png

    占比函数RATIO_TO_REPORT ●

    占比函数常用于财务中计算收支占比。用于取某个值占总和的百分比。

    语法:

    RATIO_TO_REPORT(EXP_STR)OVER()RATIO_TO_REPORT() 括号中表达式EXP_STR就是分子,OVER() 括号中就是分母,分母缺省就是整个占比。

    比如获取某公司每个城市员工人数分布和薪资待遇分布情况,sql样例参考如下:

     select  c.city_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by c.city_name;

    从输出结果中可以看出各个区域公司人员占用百分比,各区域薪资占用百分比;如下结果集可以作为报表饼图的原型。输出结果展示如下:

    b874106e8e2cc9a438e8e1ea98ca35b2.png

    RATIO_TO_REPORT 可以结合partition by 使用。

    比如获取公司每个员工薪资及员工薪资在所在部门的薪资占比,每个部门总薪资及部门薪资在公司的占比,sql样例参考如下:

    select department_name, employee_name, salary, g1,       sum(salary) over(partition by decode(g1, 0, department_name,  null), g1) sum_salary,       ratio_to_report(salary) over(partition by decode(g1, 0, department_name, null), g1) salaryrate from ( select dept.department_name,               emp.employee_name,               sum(emp.salary) salary,               grouping(dept.department_name) + grouping(emp.employee_name) g1          from employees emp, department dept         where emp.department_id = dept.department_id         group by rollup(dept.department_name, emp.employee_name))

    输出结果展示如下(数值列依次员工个人薪资/部门总薪资,分组值,公司薪资合计/部门薪资合计,薪资占比):

    f4c6704a71d90f8a88dee535d7e5a395.png

    从上面的例子中我们看到了group by rollup这个语句,这里也顺带讲解下分组函数group by子句的使用方法。

    分组函数GROUP BY子句 

    GROUP BY 子句是 SELECT 语句的可选项部分。

    它定义了分组表。

    GROUP BY 子句语法如下:

    64565b3f2d7c92aaef339eaf4ae4777b.png

    GROUP BY 子句

    456eabf7194b2ccc363abe47374b6c99.png

    ROLLUP 项

    5455346ed538b954060c31e6068774f0.png

    CUBE 项

    36482da4fa62b26fcebe39b24e0f475b.png

    GROUPING SETS 项

    e68e96730902787016dbdb83f52bef39.png

    GROUP 项 ( )

    30f59f3f4f5068cdff12346c1929807f.png

    HAVING 子句

    这里介绍ROLLUP、CUBE、GROUPING SETS三项的用法和差异。

    用法group by [rollup|cube|grouping sets](colomn)

    要弄明白rollup,cube和grouping sets,就要知道group by的使用场景, group by 为对列进行分组,只展现分组统计的值,而rollup 为分层次展现,cube为展现列中所有层次,grouping sets只展现列中单一层次。三者都是group by子句的扩展。

    ROLLUP:可以为每个分组返回小计记录以及为所有分组返回总计记录。ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

    CUBE:可以返回每一个列组合的小计记录,同时在末尾加上总计记录。CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

    GROUPING SETS:可以返回每一个列的小计记录。GROUPING SETS生成的结果集显示了所选列中值的单一的聚合。

    例如:group by rollup(a, b, c) 首先会对(a, b, c)进行group by,然后对(a, b)进行group by,然后是(a)进行group by,最后对全表进行group by操作。

    group by cube(a, b, c),首先会对(a, b, c)进行group by,然后依次是(a, b),(a, c),(a),(b, c),(b),(c),最后对全表进行group by操作。

    grouping sets (a, b, c),首先会对(a)进行group by,然后依次是(b),(c),不对全表进行group by操作。

    具体区别可以查看如下样例。

    ● ROLLUP ●

    比如获取公司各城市每个部门员工人数分布和薪资分布情况,使用group by rollup的sql样例参考如下:
    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by rollup (c.city_name, dept.department_name);
    从结果集中可以看出group by rollup聚合了城市和部门组合,城市组合,所有组合三种情况。输出结果展示如下:68d988385d23ec8ae9c16fc7dd07de95.png

    CUBE ●

    上例中的sql将rollup换成cube,sql样例参考如下:

      select c.city_name,  dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by cube (c.city_name, dept.department_name);

    从结果集中可以看出group by cube聚合了城市和部门组合,城市组合,部门组合,所有组合四种情况。结果集展示如下:

    fc4863e05bf64fbdfca836375948420b.png

    GROUPING SETS ●

    将上例中sql的cube换成grouping sets,sql样例参考如下:

    select   c.city_name,         dept.department_name,         count(*) toal_personnum,         round((ratio_to_report(count(*))over())*100, 2)  person_ratio,         sum(emp.salary) total_salary,         round((ratio_to_report(sum(emp.salary))over())*100, 2)  sal_ratio    from employee emp, department dept, location l, city c   where emp.department_id = dept.department_id(+)     and dept.location_id = l.location_id(+)     and l.city_id = c.city_id(+)   group by grouping sets (c.city_name, dept.department_name);

    从结果集中可以看出grouping sets聚合了城市组合,部门组合两种情况,没有进行组合分组合计。输出结果展示如下:

    ae1c9e5cb37625798e718f9b1ab9cc2e.png

    好了,本次分享就到这里了,感谢大家的关注。后期精彩敬请期待!

    351a14063aa0d10cdfae33a6c75a2165.png

    ——     END     ——

    往期精选

    b554f8cf08aa80700839de0258ebeafc.png

    干货分享|达梦数据库常用库函数和分析函数(一)

    干货分享DM数据库如何查看和删除统计信息

    0ec1af17ec417a8d4c0da9f21e3d04cf.gif

    展开全文
  • 今天主要介绍达梦数据库的常用集函数和分析函数,及各个函数使用场景,希望大家在sql编写上能做到游刃有余。本章测试环境:操作系统: 中标麒麟6 64位数据库版本:达梦8.1达梦数据库客户端:DM管理工具集函数...

    今天主要介绍达梦数据库的常用集函数和分析函数,及各个函数的使用场景,希望大家在sql的编写上能做到游刃有余。

    本章的测试环境:操作系统: 中标麒麟6  64位数据库版本:达梦8.1达梦数据库客户端:DM管理工具c27024036a967a4a85de2b8704fde201.png集函数为了方便用户的使用,增强查询能力,达梦SQL 语言提供了多种内部集函数。集函数又称库函数,当根据某一限制条件从表中导出一组行集时,使用集函数可对该行集作统计操作。集函数可分为 8 类:1. COUNT(*);2. 相异集函数 AVG|MAX|MIN|SUM|COUNT(DISTINCT);3. 完全集函数 AVG|MAX|MIN| COUNT|SUM([ALL]);4.方差集函数 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、 STDDEV;5. 协方差函数 COVAR_POP、COVAR_SAMP、CORR;6. 首行函数 FIRST_VALUE7. 求区间范围内最大值集函数 AREA_MAX;8FIRST/LAST 集函数 AVG|MAX|MIN| COUNT|SUM([ALL] ) KEEP (DENSE_RANK FIRST|LAST ORDER BY 子句);9.字符串集函数 LISTAGG/LISTAGG2。这里介绍最常用集函数AVG|MAX|MIN| COUNT|SUM和LISTAGG/LISTAGG2。92233d6ef349355d4c1870415e130c2b.gifAVG|MAX|MIN|COUNT|SUMMAX:求最大值集函数;MIN:求最小值集函数;AVG:求平均值集函数;SUM:总和集函数;COUNT:求总个数集函数。以查询DMHR样例数据库中某公司每个部门的总人数,部门最高、最低、平均薪资水平,薪资总和为例,来展示各函数的查询用途。查询sql样例如下: select dept.department_name,        count(*) person_num,        max(emp.salary) max_sal,        min(emp.salary) min_sal,        avg(emp.salary) avg_sal,        sum(emp.salary) sum_sal   from employee emp, department dept  where emp.department_id = dept.department_id(+)  groupby dept.department_name;输出结果展示如下(数值列依次为本部门员工数,最大薪资数,最小薪资数,平均薪资数,薪资总和):c50cf31403c127ef7390360bbc642990.png92233d6ef349355d4c1870415e130c2b.gif字符串函数LISTAGG/LISTAGG2LISTAGG/LISTAGG2(exp1, exp2)集函数先根据 sql 语句中的 group by 分组(如果没有指定分组则所有结果集为一组),然后在组内按照 WITHIN GROUP 中的ORDER BY进行排序,最后将表达式exp1用表达式exp2串接起来。LISTAGG2 跟LISTAGG的功能是一样的,区别就是LISTAGG2返回的是clob类型,LISTAGG 返回的是 VARCHAR 类型。LISTAGG 的用法:([,]) WITHIN GROUP()LISTAGG2 的用法:([,]) WITHIN GROUP()以获取某公司各区域部门的员工名单为例,各区域各部门一条记录,sql样例参考如下:select c.city_name,dept.department_name,    listagg(emp.employee_name,',') within group(orderby emp.employee_id)as employees,count(*) empnumfrom employee emp, department dept,location l, city cwhere emp.department_id = dept.department_id(+)and dept.location_id = l.location_id(+)and l.city_id = c.city_id(+)groupby c.city_name, dept.department_name;输出结果展示如下(依次为区域名称、部门名称、部门员工名单,部门员工总数):efff288ecbfd9fe39176dd23cc267a4e.png0791cd9f58cd4b71c6ceedcdd8f9213a.gifc27024036a967a4a85de2b8704fde201.png分析函数分析函数主要用于计算基于组的某种聚合值。达梦数据库分析函数为用户分析数据提供了一种更加简单高效的处理方式。如果不使用分析函数,则必须使用连接查询、子查询或者视图,甚至复杂的存储过程实现。引入分析函数后,只需要简单的 SQL 语句,并且执行效率方面也有大幅提高。与集函数的主要区别是,分析函数对于每组返回多行,而集函数对于每个分组只返回一行。多行形成的组称为窗口,窗口决定了执行当前行的计算范围,窗口的大小可以由组中定义的行数或者范围值滑动。分析函数可分为 11 类:1. COUNT(*);2.完全分析函数 AVG|MAX|MIN| COUNT|SUM([ALL]),这 5 个分析 函数的参数和作为集函数时的参数一致;3. 方差函数 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、 STDDEV;4. 协方差函数 COVAR_POP、COVAR_SAMP、CORR;5首尾函数 FIRST_VALUE、LAST_VALUE;6 相邻函数 LAG 和 LEAD;7分组函数 NTILE;8排序函数 RANK、DENSE_RANK、ROW_NUMBER;9 百分比函数 PERCENT_RANK、CUME_DIST、RATIO_TO_REPORT、 PERCENTILE_CONT、NTH_VALUE;10 字符串函数 LISTAGG;11 指定行函数 NTH_VALUE。分析函数语法如下:b2663b3437e95cd809b2b54d687e0f89.png分析子句71b9aa13aa44aedcded05ca937e52e06.pngpartition by 项899ef9d67c7a22fc530535e3b866a448.pngorder by 项3bbf349cea9319eadc673c67c53ddac3.png这里重点讲解常用的几个聚合分析函数。92233d6ef349355d4c1870415e130c2b.gifAVG|MAX|MIN|COUNT|SUM平均值|最大值|最小值|总个数|求总和也是最常用的分析函数。① 以查询DMHR样例数据库中某公司部门人员薪资,部门最高薪资,区域最高薪资,公司最高薪资为例,来展示聚合分析函数的查询用途。查询sql样例如下:select c.city_name,dept.department_name deptname,emp.employee_name empname,emp.salary,max(emp.salary)over(partitionby c.city_name, dept.department_name) deptmaxsal,      max(emp.salary)over(partitionby c.city_name) citymaxsal,        max(emp.salary)over() maxsalfrom employees emp, department dept,location l, city cwhere emp.department_id = dept.department_id(+)and dept.location_id = l.location_id(+)and l.city_id = c.city_id(+);输出结果展示如下(数值项依次为员工个人薪资、部门最高薪资、区域最高薪资、公司最高薪资):da74ddef37a4c91978fd8314732bfa45.png② 以查询DMHR样例数据库中某公司区域部门人员总数,部门人员总数,区域人员总数,总司总员工人数为例,来展示聚合分析函数的查询用途。查询sql样例如下:select distinct c.city_name,dept.department_name,count(*)over(partitionby c.city_name,dept.department_name) citydept_empnum,        count(*)over(partitionby dept.department_name) dept_empnum,count(*)over(partitionby c.city_name) city_empnum,count(*)over() totalempnum from employee emp, department dept,location l, city cwhere emp.department_id = dept.department_id(+)  and dept.location_id = l.location_id(+)  and l.city_id = c.city_id(+);输出结果展示如下(数值项依次为各区域部门员工数、各部门员工数、各区域员工数、公司员工总数):ba8790297269542f329772179e632a30.png

    92233d6ef349355d4c1870415e130c2b.gif字符串函数LISTAGG

    LISTAGG用于做字符串之间的连接,即可以做集函数,也可以做分析函数。LISTAGG2不支持分析函数。比如查询公司员工信息,并同步获取相同工种的员工名单,sql样例参考如下: select c.city_name,dept.department_name deptname,emp.employee_name empname, j.job_title,listagg(emp.employee_name,',') within group(orderby emp.employee_id)over(partitionby j.job_title)as job_employeefrom employee emp, department dept,location l, city c, job jwhere emp.department_id = dept.department_id(+)and dept.location_id = l.location_id(+)and l.city_id = c.city_id(+)and emp.job_id = j.job_id;输出结果展示如下(最后一列为该员工相同工种的员工名单):68a6d8d5c62251dff4f344669e94376a.png92233d6ef349355d4c1870415e130c2b.gif排序函数RANK,DENSE_RANK,ROW_NUMBER此三个函数用于对数据排序,生成排行榜的场景,为每条记录产生一个从1开始至n的自然数,n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。ROW_NUMBER:当碰到相同数据时,排名按照记录集中记录的顺序依次递增。DENSE_RANK: 当碰到相同数据时,此时所有相同数据的排名都是一样的。RANK:当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。① 以查询DMHR样例数据库中公司每个员工薪水在公司排行情况为例,sql参考如下:select emp.employee_name,dept.department_name,emp.salary,rank()over(orderby salary desc) rank,dense_rank()over(orderby salary desc) dense_rank,        row_number()over(orderby salary desc) rownumberfrom employee emp, department deptwhere emp.department_id = dept.department_id(+);从输出结果可以看出此三个函数的区别,输出结果展示如下:9b3bccc9073c45e81a638f39daeab9fc.png② 以查询DMHR样例数据库中公司每个员工薪水及薪水在部门和公司排行情况为例,sql样例参考如下:select dept.department_name,emp.employee_name,emp.salary,dense_rank()over(partitionby dept.department_name orderby salary desc) dept_rank,dense_rank()over(orderby salary desc) total_rankfrom employees emp, department deptwhere emp.department_id = dept.department_id(+);从输出结果中,我们可以看到每个员工薪资在自己部门和总公司的排行情况,输出结果展示如下(数值项依次为员工个人薪资、所在部门排名、所在公司排名):ba7df9cff71d76462537b55608ac0349.png5bc1f498564a719883c4791cdc8ddd0b.gif73a349e615c44e83805ba0f517f03f48.png

    好了,本次常用集函数和分析函数就介绍到这了,大家有没有掌握呢。下次为大家继续介绍占比函数RATIO_TO_REPORT、相邻函数 LAG 和 LEAD等分析函数的使用,敬请期待!

    9fd20f4a8ab0836e7b1229cfe09b882f.png

    79f64eeca81f5e9daf75d9a342b9c81e.png

    展开全文
  • 今天主要介绍达梦数据库的常用集函数和分析函数,及各个函数使用场景,希望大家在sql编写上能做到游刃有余。本章测试环境:操作系统: 中标麒麟6 64位数据库版本:达梦8.1达梦数据库客户端:DM管理工具集函数...
  • 今天主要介绍达梦数据库的常用集函数和分析函数及各个函数使用场景,希望大家在sql编写上能做到游刃有余。本章测试环境:操作系统: 中标麒麟6 64位数据库版本:达梦8.1达梦数据库客户端:DM管理工具集函数为了...
  • 今天小编就为大家分享一篇关于Oracle数据库常用分析函数与聚合函数用法,小编觉得内容挺不错,现在分享给大家,具有很好参考价值,需要朋友一起跟随小编来看看吧。应之前计划,今天完成这篇记录,也借此...
  • 今天主要介绍达梦数据库的常用集函数和分析函数,及各个函数使用场景,希望大家在sql编写上能做到游刃有余。本章测试环境:操作系统: 中标麒麟6 64位数据库版本:达梦8.1达梦数据库客户端:DM管理工具集函数...
  • 主要介绍了CI框架AR数据库操作常用函数,结合实例形式总结分析了基于CI框架的数据库增删改查与缓存、结果集等相关操作函数与技巧,需要朋友可以参考下
  • 窗口分析函数在做数据分析时十分常用,但是MySQL却不支持。不过我们可以通过sql形式实现这个功能构造表数据create table rank_over(id int,subid int,curd timestamp);……insert into rank_over values(8,3,now()...
  • 我用数据库学习中常用的scott里面的emp表分析一下sql对数据库的高级查询 首先查询的基本语法: select [distinct] * | 指定列名 from 表名 [where 条件] --分组前筛选 [group by 分组字段] [having 条件] --...
  • 自学时候,碰到数据库提取数据时候十分困惑,然后查找手册,从手册上总结了一下。当时我用php大版本是5.5,自从5.7后已经不再使用这种方法,强烈建议直接使用PDO或者是mysqli类。 /** mysql_fetch_assoc...
  • 文章目录数据库函数数据库函数的种类1,数学函数2,聚合函数3,字符...以上就是常用的数学函数,那么我们就演示下怎么使用的 第一个 abs(x) 返回x的绝对值、、那么我们来演示依一下吧 第二个,rand 返回0到1的随机数
  • ** Oracle高级函数 ...以上列举的是我们常用的函数,还有一些不常用的就不做赘述,比如求方差,求标准偏差。这些函数基本都会和group by 一起使用,把给出的记录汇总成一条记录,但是在我们的开发过程中我
  • 统计分析时常常会被一些复杂求和平均方式搞得头大,自从了解窗口函数解决了不少常见问题,废话不多说,直接看最易懂实例。
  • Python框框架架Flask的的基基本本数数据据库库操操作作方方法法分分析析 这篇文章主要介绍了Python框架Flask基本数据库操作方法,结合实例形式分析了Flask框架数据库操作常用函数 功能用法及 关注意事项,需要朋友...
  • 主要介绍了Python框架Flask基本数据库操作方法,结合实例形式分析了Flask框架数据库操作常用函数功能、用法及相关注意事项,需要朋友可以参考下
  • 数据库函数

    2020-05-24 20:54:59
    聚合函数 报表典型用途是从全部数据中提取出代表一种趋势值或者汇总值,这就是聚合意义。...第7章将详细介绍这个主题,这里仅讨论简单SELECT查询中一些常用函数。 聚合函数不仅可用在SELECT查询中
  • MySQL函数是MySQL数据库提供内置函数,这些内置函数可以更方便处理表中数据。下面简单介绍一下MySQL中包含几类常用函数。PART01聚合函数聚合函数可实现根据一组数据求出一个值,聚合函数的结果值只根据选定...
  • 文章目录mysqlmysql常用的函数mysql注入样例分析 mysql mysql常用的函数 函数名称 函数功能 system_user() 系统用户名 user() 用户名 current_user() 当前用户名 session_user() 连接数据库的用户名 ...
  • php下比较常用的就是数据库的操作,这里是php下比较常用的代码与函数
  • 分享给大家供大家参考,具体如下:流程函数是MySQL相对常用的一类函数, 用户可以使用这类函数在一个SQL语句中实现条件选择, 这样能够提高效率.下面列出了MySQL跟条件有关的流程函数函数功能IF(expr1,expr2,expr3)...
  • php用户注册时常用的一些常用检验函数总结,包括有检测提交的数据是否符合用户名格式,检测参数的值是否相同,检测参数是否为中文,检验邮箱的地址是否正确,检验参数是否为数字等等,这些提交到数据库之前的验证,在正则...
  • Hive的分析函数

    2018-03-14 23:19:38
    Hive的分析函数参考地址 Hive的常用函数和平常我们使用的关系型数据库基本都差不多,这里只是记录一下,方便后面回顾。 准备测试数据.. [hadoop@hadoop apache-hive-0.13.1-bin]$ cat emp.txt 7369 SMITH ...
  • 1、常用函数一般指数据库的系统函数数据库函数是指当需要分析数据清单中数值是否符合特定条件时,使用数据库函数来完成目标操作; 2、函数还可以指自己定义的函数,定义一般方法及使用: ①、类似与java...
  • 这里我们就介绍一下常用内置函数,不太常用的将不做太多介绍。 1 数学常用函数 -- abs(m),返回m的绝对值 select abs(-16); --16 -- mod(m,n),返回m被n除的余数,n可以为0返回null,且有警告 select mod(11..
  • 对于sqlite3一些函数 1、int sqlite3_open(const char * filename, sqlite3 **ppDb) filename:需要打开的数据库的文件名 ppDb:一个数据库连接句柄被返回到这个参数。即是发生错误,唯一异常是如果sqlite不能...
  • Oracle 中的分析函数

    2017-01-07 16:25:00
    Oracle常用分析函数介绍(排名函数+窗口函数) 2014年11月30日⁄数据库⁄ 共 3903字 ⁄暂无评论⁄ 阅读 7,772 次 评级函数 常见评级函数如下: RANK():返回数据项在分组中排名,在排名相等时会在名次...
  • 本文涉及pandas最常用的36个函数,通过这些函数介绍如何完成数据生成和导入、数据清洗、预处理,以及最常见的数据分类,数据筛选,分类汇总,透视等最常见的操作。生成数据表 常见的生成数据表的方法有两种,第一种...
  • 前言MySQL函数是MySQL数据库提供内置函数,这些内置函数可以更方便处理表中数据。下面简单介绍一下MySQL中包含几类常用函数。聚合函数聚合函数可实现根据一组数据求出一个值,聚合函数的结果值只根据选定数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 861
精华内容 344
关键字:

数据库常用的分析函数