《卸甲笔记》-分组统计查询对比之二

13在分组查询的SELECT子句中出现其他字段（ename）
Oracle

SQL select deptno,ename,COUNT(empno)

 2 from emp

 3 GROUP BY deptno;

select deptno,ename,COUNT(empno)

ERROR at line 1:

ORA-00979: not a GROUP BY expression

PPAS

scott=# select deptno,ename,COUNT(empno)

scott-# from emp

scott-# GROUP BY deptno;

ERROR: column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: select deptno,ename,COUNT(empno)

14在SELECT子句之后只出现分组字段和统计函数
Oracle

SQL select deptno,COUNT(empno)

 2 from emp

 3 GROUP BY deptno;

 DEPTNO COUNT(EMPNO)

---------- ------------

 30 6

 20 5

 10 3

PPAS

scott=# select deptno,COUNT(empno)

scott-# from emp

scott-# GROUP BY deptno;

 deptno | count 

--------+-------

 20 | 5

 30 | 6

 10 | 3

(3 rows)

15求出每个部门平均工资最高的工资
Oracle

SQL select MAX(AVG(sal)) from emp GROUP BY deptno;

MAX(AVG(SAL))

-------------

 2916.66667

PPAS

PPAS不支持聚合函数嵌套

scott=# select MAX(AVG(sal)) from emp GROUP BY deptno;

ERROR: aggregate function calls cannot be nested

LINE 1: select MAX(AVG(sal)) from emp GROUP BY deptno;

16错误的语句
Oracle

SQL select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;

select deptno,MAX(AVG(sal)) from emp GROUP BY deptno

ERROR at line 1:

ORA-00937: not a single-group group function

PPAS

scott=# select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;

ERROR: aggregate function calls cannot be nested

LINE 1: select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;

17统计函数嵌套分析
Oracle

SQL select deptno,SUM(sal) from emp GROUP BY deptno;

 DEPTNO SUM(SAL)

---------- ----------

 30 9400

 20 10875

 10 8750

PPAS

scott=# select deptno,SUM(sal) from emp GROUP BY deptno;

 deptno | sum 

--------+----------

 20 | 10875.00

 30 | 9400.00

 10 | 8750.00

(3 rows)

18查询每个部门的名称、部门人数、部门平均工资、平均服务年限
Oracle

SQL select d.dname,COUNT(e.empno),ROUND(AVG(e.sal),2) avgsal,

 2 ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12),2) avgyear

 3 from dept d,emp e

 4 where e.deptno(+)=d.deptno

 5 GROUP BY d.dname;

DNAME COUNT(E.EMPNO) AVGSAL AVGYEAR

--------------- -------------- ---------- ----------

ACCOUNTING 3 2916.67 34.69

OPERATIONS 0

RESEARCH 5 2175 32.71

SALES 6 1566.67 34.99

PPAS

scott=# select d.dname,COUNT(e.empno),ROUND(AVG(e.sal),2) avgsal,

scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12),2) avgyear

scott-# from dept d,emp e

scott-# where e.deptno(+)=d.deptno

scott-# GROUP BY d.dname;

 dname | count | avgsal | avgyear 

------------+-------+---------+---------

 ACCOUNTING | 3 | 2916.67 | 34.69

 RESEARCH | 5 | 2175.00 | 32.71

 OPERATIONS | 0 | | 

 SALES | 6 | 1566.67 | 34.98

(4 rows)

19查询公司各个工资等级雇员的数量、平均工资
Oracle

SQL select s.grade,COUNT(e.empno),ROUND(AVG(e.sal),2)

 2 from emp e,salgrade s

 3 where e.sal between s.losal and s.hisal

 4 GROUP BY s.grade;

 GRADE COUNT(E.EMPNO) ROUND(AVG(E.SAL),2)

---------- -------------- -------------------

 1 3 950

 2 3 1266.67

 4 5 2855

 5 1 5000

 3 2 1550

PPAS

scott=# select s.grade,COUNT(e.empno),ROUND(AVG(e.sal),2)

scott-# from emp e,salgrade s

scott-# where e.sal between s.losal and s.hisal

scott-# GROUP BY s.grade;

 grade | count | round 

-------+-------+---------

 5 | 1 | 5000.00

 1 | 3 | 950.00

 3 | 2 | 1550.00

 4 | 5 | 2855.00

 2 | 3 | 1266.67

(5 rows)

20统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限，雇员人数
Oracle

SQL select 不领取佣金,ROUND(AVG(sal),2) avgsal,

 2 ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

 3 COUNT(empno) count

 4 from emp

 5 where comm IS NOT NULL

 6 UNION

 7 select 领取佣金,ROUND(AVG(sal),2) avgsal,

 8 ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

 9 COUNT(empno) count

 10 from emp

 11 where comm IS NULL;

不领取佣金 AVGSAL AVGYEAR COUNT

------------------------------ ---------- ---------- ----------

不领取佣金 1400 35.05 4

领取佣金 2342.5 33.73 10

PPAS

scott=# select 不领取佣金,ROUND(AVG(sal),2) avgsal,

scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

scott-# COUNT(empno) count

scott-# from emp 

scott-# where comm IS NOT NULL

scott-# UNION

scott-# select 领取佣金,ROUND(AVG(sal),2) avgsal,

scott-# ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

scott-# COUNT(empno) count

scott-# from emp

scott-# where comm IS NULL;

 ?column? | avgsal | avgyear | count 

------------+---------+---------+-------

 不领取佣金 | 1400.00 | 35.05 | 4

 领取佣金 | 2342.50 | 33.73 | 10

(2 rows)

多字段分组统计
21查询出每个部门的详细信息
Oracle

SQL select d.deptno,d.dname,d.loc,

 2 NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg,

 3 NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) min

 4 from emp e,dept d

 5 where e.deptno(+)=d.deptno

 6 GROUP BY d.deptno,d.dname,d.loc;

 DEPTNO DNAME LOC COUNT AVG SUM MAX MIN

---------- --------------- ---------- ---------- ---------- ---------- ---------- ----------

 20 RESEARCH DALLAS 5 2175 10875 3000 800

 40 OPERATIONS BOSTON 0 0 0 0 0

 10 ACCOUNTING NEW YORK 3 2916.67 8750 5000 1300

 30 SALES CHICAGO 6 1566.67 9400 2850 950

PPAS

scott=# select d.deptno,d.dname,d.loc,

scott-# NVL(COUNT(e.empno),0) count,NVL(ROUND(AVG(sal),2),0) avg,

scott-# NVL(SUM(sal),0) sum,NVL(MAX(sal),0) max,NVL(MIN(sal),0) min 

scott-# from emp e,dept d 

scott-# where e.deptno(+)=d.deptno

scott-# GROUP BY d.deptno,d.dname,d.loc;

 deptno | dname | loc | count | avg | sum | max | min 

--------+------------+----------+-------+---------+----------+---------+---------

 10 | ACCOUNTING | NEW YORK | 3 | 2916.67 | 8750.00 | 5000.00 | 1300.00

 30 | SALES | CHICAGO | 6 | 1566.67 | 9400.00 | 2850.00 | 950.00

 40 | OPERATIONS | BOSTON | 0 | 0 | 0 | 0 | 0

 20 | RESEARCH | DALLAS | 5 | 2175.00 | 10875.00 | 3000.00 | 800.00

(4 rows)

HAVING子句
22查询出所有平均工资大于2000元的职位信息，平均工资，雇员人数
Oracle

SQL select job,ROUND(AVG(sal),2),COUNT(empno)

 2 from emp

 3 GROUP BY job

 4 HAVING AVG(sal) 2000;

JOB ROUND(AVG(SAL),2) COUNT(EMPNO)

---------- ----------------- ------------

PRESIDENT 5000 1

MANAGER 2758.33 3

ANALYST 3000 2

PPAS

scott=# select job,ROUND(AVG(sal),2),COUNT(empno)

scott-# from emp

scott-# GROUP BY job

scott-# HAVING AVG(sal) 2000;

 job | round | count 

-----------+---------+-------

 MANAGER | 2758.33 | 3

 PRESIDENT | 5000.00 | 1

 ANALYST | 3000.00 | 2

(3 rows)

23列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资，最高工资
Oracle

SQL select d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal)

 2 from emp e,dept d 

 3 where e.deptno(+)=d.deptno

 4 GROUP BY d.deptno,d.dname,d.loc

 5 HAVING COUNT(e.empno) 

 DEPTNO DNAME ROUND(AVG(E.SAL),2) MIN(E.SAL) MAX(E.SAL)

---------- --------------- ------------------- ---------- ----------

 20 RESEARCH 2175 800 3000

 10 ACCOUNTING 2916.67 1300 5000

 30 SALES 1566.67 950 2850

PPAS

scott=# select d.deptno,d.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal)

scott-# from emp e,dept d 

scott-# where e.deptno(+)=d.deptno

scott-# GROUP BY d.deptno,d.dname,d.loc

scott-# HAVING COUNT(e.empno) 

 deptno | dname | round | min | max 

--------+------------+---------+---------+---------

 10 | ACCOUNTING | 2916.67 | 1300.00 | 5000.00

 30 | SALES | 1566.67 | 950.00 | 2850.00

 20 | RESEARCH | 2175.00 | 800.00 | 3000.00

(3 rows)

24显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000元，输出结果按月工资的合计升序排列
Oracle

SQL select job,SUM(sal) sum

 2 from emp

 3 where job SALESMAN

 4 GROUP BY job

 5 HAVING SUM(sal) 5000

 6 ORDER BY sum ASC;

JOB SUM

---------- ----------

ANALYST 6000

MANAGER 8275

PPAS

scott=# select job,SUM(sal) sum

scott-# from emp

scott-# where job SALESMAN

scott-# GROUP BY job

scott-# HAVING SUM(sal) 5000

scott-# ORDER BY sum ASC;

 job | sum 

---------+---------

 ANALYST | 6000.00

 MANAGER | 8275.00

(2 rows)

本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！

【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式 怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高 怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本篇文章主要介绍的两个方面，第一个方面曾经有好几个网友和同事问我，第二个问题真的是很多同行的通病，认为分析函数是万金油，一股脑用。
【python数据分析】数据的分组，遍历，统计 数据的分组，遍历，统计 俗话说：“人与类聚，物以群分”，到这里我们将学习数据的分组以及分组后统计。Pandas的分组相对于Excel会更加简单和灵活。
Python爬虫中的数据筛选 爬虫处理之结构化数据操作 正则表达式提取数据 正则表达式案例操作 Xpath提取数据 Xpath案例操作 BeautifulSoup4提取数据 BeautifulSoup4案例操作