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

统计函数对比
1查询出公司每个月支出的月工资总和
Oracle

SQL> select SUM(sal) from emp;

  SUM(SAL)
----------
     29025

PPAS

scott=# select SUM(sal) from emp;
   sum    
----------
 29025.00
(1 row)

2查询出公司的最高工资，最低工资，平均工资
Oracle

SQL> select AVG(sal),ROUND(AVG(sal),2),MAX(sal),MIN(sal) from emp;

  AVG(SAL) ROUND(AVG(SAL),2)   MAX(SAL)   MIN(SAL)
---------- ----------------- ---------- ----------
2073.21429         2073.21       5000        800

PPAS

scott=# select AVG(sal),ROUND(AVG(sal),2),MAX(sal),MIN(sal) from emp;
          avg          |  round  |   max   |  min   
-----------------------+---------+---------+--------
 2073.2142857142857143 | 2073.21 | 5000.00 | 800.00
(1 row)

3统计出公司最早雇佣和最晚雇佣的雇佣日期
Oracle

SQL> select MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 from emp;

最早雇佣日期 最晚雇佣日期
------------ ------------
17-DEC-80    23-MAY-87

PPAS

scott=# select MIN(hiredate) 最早雇佣日期,MAX(hiredate) 最晚雇佣日期 from emp;
    最早雇佣日期    |    最晚雇佣日期    
--------------------+--------------------
 17-DEC-80 00:00:00 | 23-MAY-87 00:00:00
(1 row)

4统计公司中间的工资值
Oracle

SQL> select MEDIAN(sal) from emp;

MEDIAN(SAL)
-----------
       1550

PPAS

PPAS中没有求中位数的函数

5统计工资的标准差与方差
Oracle

SQL> select STDDEV(sal),VARIANCE(sal) from emp;

STDDEV(SAL) VARIANCE(SAL)
----------- -------------
 1182.50322    1398313.87

PPAS

scott=# select STDDEV(sal),VARIANCE(sal) from emp;
      stddev       |       variance       
-------------------+----------------------
 1182.503223516272 | 1398313.873626373626
(1 row)

6统计出公司的雇员人数
Oracle

SQL> select COUNT(empno),COUNT(*) from emp;

COUNT(EMPNO)   COUNT(*)
------------ ----------
      14         14

PPAS

scott=# select COUNT(empno),COUNT(*) from emp;
 count | count 
-------+-------
    14 |    14
(1 row)

7验证COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的使用区别
Oracle

SQL>  select COUNT(*),COUNT(ename),COUNT(comm),COUNT(DISTINCT job) from emp;

  COUNT(*) COUNT(ENAME) COUNT(COMM) COUNT(DISTINCTJOB)
---------- ------------ ----------- ------------------
    14         14       4             5

PPAS

scott=# select COUNT(*),COUNT(ename),COUNT(comm),COUNT(DISTINCT job) from emp;
 count | count | count | count 
-------+-------+-------+-------
    14 |    14 |     4 |     5
(1 row)

8验证3中COUNT()函数的使用方式
Oracle

SQL> select COUNT(ename),AVG(sal),SUM(sal),MAX(sal),MIN(sal) from bonus; 

COUNT(ENAME)   AVG(SAL)   SUM(SAL)   MAX(SAL)    MIN(SAL)
------------ ---------- ---------- ---------- ----------
       0

PPAS

scott=# select COUNT(ename),AVG(sal),SUM(sal),MAX(sal),MIN(sal) from bonus; 
 count | avg | sum | max | min 
-------+-----+-----+-----+-----
     0 |     |     |     |    
(1 row)

单字段分钟统计
9统计出每个部门的人数
Oracle

SQL> select deptno,COUNT(*)
  2  from emp
  3  GROUP BY deptno;

DEPTNO   COUNT(*)
---------- ----------
    30        6
    20        5
    10        3

PPAS

scott=# select deptno,COUNT(*)
scott-# from emp
scott-# GROUP BY deptno;
 deptno | count 
--------+-------
     20 |     5
     30 |     6
     10 |     3
(3 rows)

10统计出每种职位的最低工资和最高工资
Oracle

SQL> select job,MIN(sal),MAX(sal)
  2  from emp
  3  GROUP BY job;

JOB         MIN(SAL)    MAX(SAL)
---------- ---------- ----------
CLERK          800        1300
SALESMAN     1250        1600
PRESIDENT     5000        5000
MANAGER      2450        2975
ANALYST      3000        3000

PPAS

scott=# select job,MIN(sal),MAX(sal)
scott-# from emp
scott-# GROUP BY job;
    job    |   min   |   max   
-----------+---------+---------
 SALESMAN  | 1250.00 | 1600.00
 CLERK     |  800.00 | 1300.00
 MANAGER   | 2450.00 | 2975.00
 PRESIDENT | 5000.00 | 5000.00
 ANALYST   | 3000.00 | 3000.00
(5 rows)

11在没有分组语句（GROUP BY）时使用统计函数后出现其他字段
Oracle

SQL> select deptno,COUNT(EMPNO) from emp;
select deptno,COUNT(EMPNO) from emp
       *
ERROR at line 1:
ORA-00937: not a single-group group function

PPAS

scott=# select deptno,COUNT(EMPNO) from emp;
ERROR:  column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select deptno,COUNT(EMPNO) from emp;

12在没有分组的时候只允许单独使用统计函数
Oracle

SQL>  select COUNT(empno) from emp;

COUNT(EMPNO)
------------
      14
PPAS
scott=# select COUNT(empno) from emp;
 count 
-------
    14
(1 row)

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