《卸甲筆記》-分組統計查詢對比

宋躍傑發表於2016-06-27
筆記

統計函式對比
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)資料庫的差異，以幫助更多讀者瞭解如何實現資料庫遷移！


相關文章