人力成本統(tǒng)計(jì)分析

1、人事管理2home back first prev next last 你們項(xiàng)目組現(xiàn)在負(fù)責(zé)開發(fā)一個(gè)人事管理系統(tǒng)，你你們項(xiàng)目組現(xiàn)在負(fù)責(zé)開發(fā)一個(gè)人事管理系統(tǒng)，你負(fù)責(zé)人力成本統(tǒng)計(jì)分析報(bào)表生成任務(wù)。負(fù)責(zé)人力成本統(tǒng)計(jì)分析報(bào)表生成任務(wù)。 項(xiàng)目經(jīng)理告訴你：項(xiàng)目經(jīng)理告訴你： 報(bào)表涉及的兩個(gè)數(shù)據(jù)表 DEPARTMENTS、EMPLOYEES 已經(jīng)設(shè)計(jì)好，可通過 dept_emp.txt 腳本創(chuàng)建 報(bào)表應(yīng)提供如下信息：各部門人數(shù)，工資總額，平均工資，最高工資，最低工資，工資差距指數(shù)（標(biāo)準(zhǔn)方差） 除報(bào)表外，應(yīng)采取餅圖、柱狀圖、曲線圖等直觀方式來(lái)顯示統(tǒng)計(jì)結(jié)果3home back first prev next las

2、t 項(xiàng)目經(jīng)理告訴你項(xiàng)目經(jīng)理告訴你 通過你開發(fā)的報(bào)表、圖表，人力資源部經(jīng)理可容易的了解：哪些部門工資水平高？哪些部門工資水平低？哪些部門工資差距比較大？哪些部門工資成本最高？ 參考下面的報(bào)表設(shè)計(jì)，有助于你完成任務(wù)4home back first prev next last 工資水平工資水平分析報(bào)表分析報(bào)表5home back first prev next last 工資差異分析報(bào)表工資差異分析報(bào)表6home back first prev next last 人力成本分析報(bào)表人力成本分析報(bào)表7home back first prev next last 分組（聚集）函數(shù)分組（聚集）函數(shù) 分組

3、函數(shù)介紹 APEX 圖表創(chuàng)建圖表創(chuàng)建 參見下面的構(gòu)建步驟8home back first prev next last 工資水平分析報(bào)表工資水平分析報(bào)表 創(chuàng)建頁(yè)，選擇創(chuàng)建頁(yè)，選擇 chart 頁(yè)類型頁(yè)類型9home back first prev next last 下一步選擇下一步選擇 Flash Chart 圖表類型圖表類型 下一步，下一步， page name 輸入：輸入： salary_analysis 下一步，選擇下一步，選擇 Do not use tabs 10home back first prev next last 下一步，選擇下一步，選擇 2D Line Chart Typ

4、e在本頁(yè)，選擇圖表類型后，點(diǎn)擊 Update 按鈕可預(yù)覽圖表效果11home back first prev next last 下一步輸入下一步輸入 SQL 查詢查詢Build Query 按鈕可激活查詢構(gòu)造向?qū)?，引?dǎo)你構(gòu)造查詢語(yǔ)句12home back first prev next last 創(chuàng)建并運(yùn)行頁(yè)創(chuàng)建并運(yùn)行頁(yè) 編輯本頁(yè)，添加報(bào)表區(qū)域編輯本頁(yè)，添加報(bào)表區(qū)域13home back first prev next last 選擇選擇 SQL Report14home back first prev next last 輸入查詢語(yǔ)句，并創(chuàng)建區(qū)域，運(yùn)行頁(yè)輸入查詢語(yǔ)句，并創(chuàng)建區(qū)域，運(yùn)行頁(yè)15

5、home back first prev next last 工資差異分析報(bào)表工資差異分析報(bào)表 重復(fù)創(chuàng)建圖表步驟 選擇 2D Column 圖表類型 用下面 SQL 語(yǔ)句來(lái)創(chuàng)建SELECT null link, department_name label, VARIANCE(SALARY) 工資方差FROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY department_name;16home back first prev next last 人力成本分析報(bào)表人力成本分析報(bào)表 重復(fù)創(chuàng)建圖

6、表步驟 選擇 3D Pie 圖表類型 用下面 SQL 語(yǔ)句來(lái)創(chuàng)建SELECT null link, department_name label, SUM(SALARY) 工資總額FROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY department_name;17home back first prev next last 根據(jù)你所創(chuàng)建的圖表，分析現(xiàn)有數(shù)據(jù)：根據(jù)你所創(chuàng)建的圖表，分析現(xiàn)有數(shù)據(jù)： 哪個(gè)部門工資水平最高？ 哪個(gè)部門工資水平差異最大？ 哪個(gè)部門人力成本最高？18home back

7、 first prev next last 計(jì)算平均提成計(jì)算平均提成 員工的提成為 salary*COMMISSION_PCT第一種方法：只計(jì)算有提成員工的平均提成第二種方法：計(jì)算所有員工的平均提成 計(jì)算員工總?cè)藬?shù)計(jì)算員工總?cè)藬?shù) 計(jì)算員工的不同工資數(shù)計(jì)算員工的不同工資數(shù) 計(jì)算員工的不同工作種類數(shù)目計(jì)算員工的不同工作種類數(shù)目(JOB_ID)19home back first prev next last 掌握分組函數(shù)使用方法掌握分組函數(shù)使用方法 掌握掌握 APEX 圖表使用方法圖表使用方法20home back first prev next last在在SQL 中，以下分組函數(shù)可對(duì)整個(gè)表或一組

8、特定的行執(zhí)行中，以下分組函數(shù)可對(duì)整個(gè)表或一組特定的行執(zhí)行運(yùn)算。每個(gè)函數(shù)返回一個(gè)結(jié)果。運(yùn)算。每個(gè)函數(shù)返回一個(gè)結(jié)果。 MIN 用于存儲(chǔ)任何數(shù)據(jù)類型的列，返回最小值。 MAX 用于存儲(chǔ)任何數(shù)據(jù)類型的列，返回最大值。 COUNT COUNT(表達(dá)式) 返回表達(dá)式列中非空值的數(shù)量。 COUNT(*) 返回表中滿足SELECT 語(yǔ)句的條件的行數(shù)。 AVG 用于存儲(chǔ)數(shù)值數(shù)據(jù)的列，計(jì)算平均值。 SUM 用于存儲(chǔ)數(shù)值數(shù)據(jù)的列，計(jì)算值的總和。21home back first prev next lastVARIANCE： 用于存儲(chǔ)數(shù)值數(shù)據(jù)的列，計(jì)算數(shù)用于存儲(chǔ)數(shù)值數(shù)據(jù)的列，計(jì)算數(shù)據(jù)在平均值左右的分布情況。例據(jù)在平

9、均值左右的分布情況。例如，如果班級(jí)上次測(cè)驗(yàn)的平均分如，如果班級(jí)上次測(cè)驗(yàn)的平均分是是82%，學(xué)生的分?jǐn)?shù)范圍在，學(xué)生的分?jǐn)?shù)范圍在40% 到到100% 之間，則此分?jǐn)?shù)的差異將比之間，則此分?jǐn)?shù)的差異將比學(xué)生的分?jǐn)?shù)范圍在學(xué)生的分?jǐn)?shù)范圍在78% 到到88% 的的情況下大。情況下大。STDDEV： 與與variance 類似，標(biāo)準(zhǔn)偏差測(cè)量數(shù)類似，標(biāo)準(zhǔn)偏差測(cè)量數(shù)據(jù)的分布情況。對(duì)于平均值大致?lián)姆植记闆r。對(duì)于平均值大致相同的兩組數(shù)據(jù)，分布越廣，標(biāo)相同的兩組數(shù)據(jù)，分布越廣，標(biāo)準(zhǔn)偏差就越大。準(zhǔn)偏差就越大。 例如，有四個(gè)數(shù)字例如，有四個(gè)數(shù)字: 1、7、4、 8最小值 MIN 是 1最大值 MAX 是 8總和 SUM

10、是 20 = 1+7+ 4+8數(shù)目 COUNT 是 4平均值 AVG 是 5 = SUM/ COUNT方差 VARIANCE 是 ( (1-AVG)* (1-AVG) + (7-AVG) * (7-AVG) + (4-AVG) * (4-AVG) + (8-AVG) * (8-AVG) )/ (COUNT-1) =10標(biāo)準(zhǔn)差 STDDEV 是方差 VARIANCE 的平方根 3.1622home back first prev next last 組函數(shù)使用要點(diǎn)： 組函數(shù)會(huì)忽略空值。因?yàn)槿魏沃岛涂罩颠M(jìn)行運(yùn)算，結(jié)果還是空值。忽略空值可確保得到一個(gè)運(yùn)算結(jié)果。 在下面的示例中，計(jì)算平均加班費(fèi)率時(shí)沒有

11、使用(null) 值。23home back first prev next last 組函數(shù)使用要點(diǎn)： SELECT 子句中可有多個(gè)基于相同或不同列的組函數(shù)。 還可使用WHERE 子句，將組函數(shù)限制在表的子集上。SELECT MAX(salary), MIN(salary), MIN(employee_id)FROM employeesWHERE department_id = 60;24home back first prev next last 組函數(shù)使用要點(diǎn)： MIN 和和MAX 兩個(gè)組函數(shù)可用于任何數(shù)據(jù)類型。兩個(gè)組函數(shù)可用于任何數(shù)據(jù)類型。 使用這些函數(shù)，可以找出列表中最后一個(gè)人的使用

12、這些函數(shù)，可以找出列表中最后一個(gè)人的姓名、最低薪金或最早的聘用日期。姓名、最低薪金或最早的聘用日期。 例如，很容易在按字母排序的雇員列表中找到例如，很容易在按字母排序的雇員列表中找到姓名列在第一行的雇員。姓名列在第一行的雇員。SELECT MIN(last_name)FROM employees;25home back first prev next last 組函數(shù)使用要點(diǎn)： 組函數(shù)忽略空值。組函數(shù)忽略空值。 MIN 和和MAX 可用于任何數(shù)據(jù)類型；可用于任何數(shù)據(jù)類型； SUM、AVG、STDDEV 和和VARIANCE 只能用于數(shù)只能用于數(shù)值數(shù)據(jù)類型。值數(shù)據(jù)類型。26home back f

13、irst prev next last組函數(shù)使用語(yǔ)法SELECT col1, GROUP_FUNCTION(col2) ,FROM table1,WHERE conditionGROUP BY col1,HAVING group_conditionORDER BY col1,; 如果有如果有 WHERE 子句，先執(zhí)行子句，先執(zhí)行 WHERE ，根據(jù)其條件排除掉無(wú)關(guān)記，根據(jù)其條件排除掉無(wú)關(guān)記錄錄 如果有如果有 GROUP 子句，則先按其指定列的值將剩余記錄分組；否則子句，則先按其指定列的值將剩余記錄分組；否則所有記錄被看作一組所有記錄被看作一組 對(duì)每組記錄計(jì)算分組函數(shù)對(duì)每組記錄計(jì)算分組函數(shù) 如果

14、有如果有 HAVING 子句，則根據(jù)其中條件選擇要顯示哪些分組的結(jié)果子句，則根據(jù)其中條件選擇要顯示哪些分組的結(jié)果 執(zhí)行執(zhí)行 SELECT 子句，返回相應(yīng)列子句，返回相應(yīng)列 執(zhí)行執(zhí)行 ORDER BY子句，對(duì)返回結(jié)果進(jìn)行排序子句，對(duì)返回結(jié)果進(jìn)行排序27home back first prev next last GROUP BY 可以使用可以使用GROUP BY 子子句將表中的行分成較小句將表中的行分成較小的組。然后，可以使用的組。然后，可以使用組函數(shù)返回每個(gè)組的匯組函數(shù)返回每個(gè)組的匯總信息?？傂畔?。 在所示的在所示的SELECT 語(yǔ)句語(yǔ)句中，根據(jù)中，根據(jù)“部門標(biāo)識(shí)部門標(biāo)識(shí)”對(duì)行進(jìn)行分組，然后將

15、對(duì)行進(jìn)行分組，然后將AVG 函數(shù)自動(dòng)應(yīng)用于每函數(shù)自動(dòng)應(yīng)用于每個(gè)組。個(gè)組。SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id;28home back first prev next last GROUP BY 如果想知道每個(gè)部門中雇員的最高薪金，該怎樣做？可使用GROUP BY 子句指定依據(jù)哪一列對(duì)行進(jìn)行分組？SELECT MAX(salary)FROM employeesGROUP BY department_id; 但如何區(qū)分哪個(gè)最高薪金屬于哪個(gè)部門呢？ 29home back first prev ne

16、xt last GROUP BY 通常，我們采用的方法是在SELECT 列表中包含GROUP BY 列。SELECT department_id, MAX(salary)FROM employeesGROUP BY department_id;30home back first prev next last 組函數(shù)要求，在SELECT 子句中所列出的任何不屬于組函數(shù)組成部分的列，都必須在GROUP BY 子句中列出。 此示例中有什么錯(cuò)誤？SELECT job_id, last_name, AVG(salary)FROM employeesGROUP BY job_id;31home back

17、first prev next last 還可使用WHERE 子句排除一些行，然后再將剩余行分組。SELECT department_id, MAX(salary)FROM employeesWHERE last_name KingGROUP BY department_id;32home back first prev next last GROUP BY 原則 使用GROUP BY 子句時(shí)，應(yīng)遵循的重要原則有：如果SELECT 子句中不僅包含組函數(shù)（AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE），還包含其它個(gè)體列，則這些個(gè)體列還必須出現(xiàn)在GROUP BY 子句

18、中。不能在GROUP BY 子句中使用列別名。WHERE 子句可在對(duì)行進(jìn)行分組之前排除某些行。33home back first prev next last 組內(nèi)組 有時(shí)需要將組分為更小的組。例如，您可能希望根據(jù)部門對(duì)所有雇員進(jìn)行分組；然后，在每個(gè)部門中，按職務(wù)進(jìn)行分組。 本示例說明每個(gè)部門中從事每項(xiàng)工作的雇員各有多少。 SELECT department_id, job_id, count(*)FROM employeesWHERE department_id 40GROUP BY department_id, job_id;34home back first prev next last

19、 嵌套組函數(shù) 使用GROUP BY 時(shí)，可以嵌套兩層組函數(shù)。SELECT max(avg(salary)FROM employeesGROUP by department_id; 此查詢將返回幾個(gè)值？答案是一個(gè)此查詢將找出每個(gè)部門的平均薪金，然后從該列表中選擇最大值。35home back first prev next last HAVING 假設(shè)我們想找出每個(gè)部門的最高薪金，但僅針對(duì)有多個(gè)雇員的部門進(jìn)行查找，那該怎么做？此示例中有什么錯(cuò)誤？SELECT department_id, MAX(salary)FROM employeesWHERE COUNT(*) 1GROUP BY depa

20、rtment_id;下一張幻燈片將解決此問題。36home back first prev next last 組函數(shù)使用要點(diǎn)： 不能在不能在WHERE 子句中使用組函數(shù)：子句中使用組函數(shù)：37home back first prev next last HAVING 與使用WHERE 子句限定所選行一樣，您可以使用HAVING 子句來(lái)限定組。 在使用GROUP BY 和HAVING 子句的查詢中，將首先對(duì)行進(jìn)行分組，然后應(yīng)用組函數(shù)，這樣將僅顯示與HAVING 子句匹配的組。 WHERE 子句用于限制行；HAVING 子句用于限制由GROUP BY 子句返回的組。 SELECT departm

21、ent_id, MAX(salary)FROM employeesGROUP BY department_idHAVING COUNT(*) 1;38home back first prev next last 在SELECT 語(yǔ)句中，盡管可將HAVING 子句用于GROUP BY 子句之前，但建議您按以下所示的順序放置每條子句。如果使用ORDER BY 子句，請(qǐng)始終將其放在最后！SELECT 列, 組函數(shù)FROM 表WHERE GROUP BY HAVING ORDER BY39home back first prev next last DISTINCT 關(guān)鍵字DISTINCT 用于僅返回

22、查詢中的非重復(fù)值或非重復(fù)值的組合。 請(qǐng)看右邊的查詢。如果不使用關(guān)鍵字DISTINCT，該查詢將返回D_CDS 表中的所有年份值。SELECT year as CD YearFROM d_cds;40home back first prev next last DISTINCT 要消除重復(fù)行，請(qǐng)按此處所示使用DISTINCT 關(guān)鍵字。 使用DISTINCT 關(guān)鍵字只返回了所有CD 年份一次，而沒有重復(fù)值。SELECT DISTINCT year AS CD YearFROM d_cds;41home back first prev next last DISTINCT 關(guān)鍵字DISTINCT 可

23、與所有組函數(shù)一起使用。使用DISTINCT 后，函數(shù)將僅考慮非重復(fù)值。 為什么右側(cè)的兩個(gè)語(yǔ)句生成不同的結(jié)果？SELECT SUM(salary)FROM employeesWHERE department_id = 90;SELECT SUM(DISTINCT salary)FROM employeesWHERE department_id = 90;42home back first prev next last DISTINCT 將DISTINCT 與組函數(shù)（如COUNT）一起使用時(shí)，結(jié)果集將返回非重復(fù)列值的數(shù)量。 雇員們一共從事多少種不同的工作？ 有多少種不同的薪金？SELECT COUNT (DISTINCT job_id)FROM employees;SELECT COUNT (DISTINCT salary)FROM employees;43home back first prev next last COUNT COUNT(表達(dá)式) 返回表達(dá)式列中非空值的數(shù)量。 COUNT(