6、Oracle中的分組函式

画个一样的我發表於2024-06-16
Oracle函式

最近專案要用到Oracle，奈何之前沒有使用過，所以在B站上面找了一個學習影片，用於記錄學習過程以及自己的思考。
影片連結：
【尚矽谷】Oracle資料庫全套教程，oracle從安裝到實戰應用
如果有侵權，請聯絡刪除，謝謝。

學習目標：

  • 瞭解組函式。
  • 描述組函式的用途。
  • 使用GROUP BY 子句對資料分組。
  • 使用HAVING 子句過濾分組結果集。

1、什麼是分組函式

分組函式作用於一組資料，並對一組資料返回一個值。（也叫作多行函式）

1.1、組函式型別

  • AVG
  • COUNT
  • MAX
  • MIN
  • STDDEV： 標準差
  • SUM

1.2、組函式語法

SELECT	[column,] group_function(column), ...
FROM		table
[WHERE	condition]
[GROUP BY	column]
[ORDER BY	column];

2、常用組函式

2.1、AVG（平均值）和 SUM （合計）函式

可以對數值型資料使用AVG 和 SUM 函式。

SELECT AVG(salary), MAX(salary),
              MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

2.2、MIN（最小值）和 MAX（最大值）函式

可以對任意資料型別的資料使用 MIN 和 MAX 函式。


SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

2.3、COUNT（計數）函式

COUNT(*) 返回表中記錄總數,適用於任意資料型別。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

COUNT(expr) 返回expr不為空的記錄總數。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

2.4、組函式與空值

組函式忽略空值。

SELECT AVG(commission_pct)
FROM   employees;


例如：
Select avg(commission_pct),sum(commission_pct)/107,
sum(commission_pct)/count(commission_pct)
From employees;
檢視結果的不同。

2.5、在組函式中使用NVL函式

在組函式中使用NVL函式

SELECT AVG(NVL(commission_pct, 0))
FROM   employees;

2.6、DISTINCT 關鍵字

COUNT(DISTINCT expr)返回expr非空且不重複的記錄總數

SELECT COUNT(DISTINCT department_id)
FROM   employees;

3、分組資料

3.1、分組資料: GROUP BY 子句語法

可以使用GROUP BY子句將表中的資料分成若干組

SELECT	column, group_function(column)
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[ORDER BY	column];

　明確：WHERE一定放在FROM後面

3.2 、GROUP BY 子句

在SELECT 列表中所有未包含在組函式中的列都應該包含在 GROUP BY 子句中。

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;
  • 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

3.3、使用多個列分組

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

3.4、非法使用組函式

  • 所有包含於SELECT 列表中，而未包含於組函式中的列都必須包含於 GROUP BY 子句中。
SELECT department_id, COUNT(last_name)
FROM   employees;

執行報錯，報錯如下：

SELECT department_id, COUNT(last_name)
       *
ERROR at line 1:
ORA-00937: not a single-group group function
  • 不能在 WHERE 子句中使用組函式。
  • 可以在 HAVING 子句中使用組函式。
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;

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

WHERE  AVG(salary) > 8000
       *
ERROR at line 3:
ORA-00934: group function is not allowed here

3.5、過濾分組： HAVING 子句

使用 HAVING 過濾分組:

  1. 行已經被分組。
  2. 使用了組函式。
  3. 滿足HAVING 子句中條件的分組將被顯示。
SELECT	column, group_function
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[HAVING	group_condition]
[ORDER BY	column];

-- 求各部門的平均工資大於 100000 的部門id和平均薪資
SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;

3.6、巢狀組函式

-- 顯示各部門平均工資的最大值

SELECT   MAX(AVG(salary))
FROM     employees
GROUP BY department_id;

課後習題：
10.查詢公司在1995-1998年之間，每年僱用的人數，結果類似下面的格式

select count(*) 'Total',
       count(decode(to_char(hira_date, 'yyyy'), '1995', 1, null)) '1995' ,
       count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
       count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
       count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

相關文章