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

人力成本統(tǒng)計分析任務(wù)人事管理第一頁，共四十七頁。任務(wù)背景2-1你們項目組現(xiàn)在負責(zé)開發(fā)一個人事管理系統(tǒng)，你負責(zé)人力成本統(tǒng)計分析報表生成任務(wù)。項目經(jīng)理告訴你：報表涉及的兩個數(shù)據(jù)表DEPARTMENTS、EMPLOYEES已經(jīng)設(shè)計好，可通過dept_emp.txt

腳本創(chuàng)建報表應(yīng)提供如下信息：各部門人數(shù)，工資總額，平均工資，最高工資，最低工資，工資差距指數(shù)（標準方差）除報表外，應(yīng)采取餅圖、柱狀圖、曲線圖等直觀方式來顯示統(tǒng)計結(jié)果第二頁，共四十七頁。任務(wù)背景2-2項目經(jīng)理告訴你通過你開發(fā)的報表、圖表，人力資源部經(jīng)理可容易的了解：哪些部門工資水平高？哪些部門工資水平低？哪些部門工資差距比較大？哪些部門工資成本最高？參考下面的報表設(shè)計，有助于你完成任務(wù)第三頁，共四十七頁。報表設(shè)計3-1工資水平分析報表第四頁，共四十七頁。報表設(shè)計3-2工資差異分析報表第五頁，共四十七頁。報表設(shè)計3-3人力成本分析報表第六頁，共四十七頁。關(guān)鍵技術(shù)分組（聚集）函數(shù)分組函數(shù)介紹

APEX圖表創(chuàng)建參見下面的構(gòu)建步驟第七頁，共四十七頁。報表構(gòu)建9-1工資水平分析報表創(chuàng)建頁，選擇chart頁類型第八頁，共四十七頁。報表構(gòu)建9-2下一步選擇FlashChart圖表類型下一步，pagename輸入：salary_analysis下一步，選擇Donotusetabs第九頁，共四十七頁。報表構(gòu)建9-3下一步，選擇2DLineChartType在本頁，選擇圖表類型后·，點擊Update按鈕可預(yù)覽圖表效果第十頁，共四十七頁。報表構(gòu)建9-4下一步輸入SQL查詢BuildQuery按鈕可激活查詢構(gòu)造向?qū)?，引?dǎo)你構(gòu)造查詢語句第十一頁，共四十七頁。報表構(gòu)建9-5創(chuàng)建并運行頁編輯本頁，添加報表區(qū)域第十二頁，共四十七頁。報表構(gòu)建9-6選擇SQLReport第十三頁，共四十七頁。報表構(gòu)建9-7輸入查詢語句，并創(chuàng)建區(qū)域，運行頁第十四頁，共四十七頁。報表構(gòu)建9-8工資差異分析報表重復(fù)創(chuàng)建圖表步驟選擇2DColumn圖表類型用下面SQL語句來創(chuàng)建SELECTnulllink,department_namelabel,VARIANCE(SALARY)工資方差FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYdepartment_name;第十五頁，共四十七頁。報表構(gòu)建9-9人力成本分析報表重復(fù)創(chuàng)建圖表步驟選擇3DPie圖表類型用下面SQL語句來創(chuàng)建SELECTnulllink,department_namelabel,SUM(SALARY)工資總額FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYdepartment_name;第十六頁，共四十七頁。圖表測試根據(jù)你所創(chuàng)建的圖表，分析現(xiàn)有數(shù)據(jù)：哪個部門工資水平最高？哪個部門工資水平差異最大？哪個部門人力成本最高？第十七頁，共四十七頁。作業(yè)與練習(xí)計算平均提成員工的提成為salary*COMMISSION_PCT第一種方法：只計算有提成員工的平均提成第二種方法：計算所有員工的平均提成計算員工總?cè)藬?shù)計算員工的不同工資數(shù)計算員工的不同工作種類數(shù)目(JOB_ID)第十八頁，共四十七頁?？偨Y(jié)掌握分組函數(shù)使用方法掌握APEX圖表使用方法第十九頁，共四十七頁。分組（聚集）函數(shù)27-1在SQL中，以下分組函數(shù)可對整個表或一組特定的行執(zhí)行運算。每個函數(shù)返回一個結(jié)果。MIN用于存儲任何數(shù)據(jù)類型的列，返回最小值。MAX用于存儲任何數(shù)據(jù)類型的列，返回最大值。

COUNTCOUNT(表達式)返回表達式列中非空值的數(shù)量。COUNT(*)返回表中滿足SELECT語句的條件的行數(shù)。AVG用于存儲數(shù)值數(shù)據(jù)的列，計算平均值。SUM用于存儲數(shù)值數(shù)據(jù)的列，計算值的總和。第二十頁，共四十七頁。分組（聚集）函數(shù)27-2VARIANCE：用于存儲數(shù)值數(shù)據(jù)的列，計算數(shù)據(jù)在平均值左右的分布情況。例如，如果班級上次測驗的平均分是82%，學(xué)生的分數(shù)范圍在40%到100%之間，則此分數(shù)的差異將比學(xué)生的分數(shù)范圍在78%到88%的情況下大。STDDEV：與variance類似，標準偏差測量數(shù)據(jù)的分布情況。對于平均值大致相同的兩組數(shù)據(jù)，分布越廣，標準偏差就越大。

例如，有四個數(shù)字:1、7、4、8

最小值MIN是1 最大值MAX是8 總和SUM是20=1+7+4+8 數(shù)目COUNT是4 平均值A(chǔ)VG是5=SUM/COUNT 方差VARIANCE是 ((1-AVG)*(1-AVG)+ (7-AVG)

*(7-AVG)+ (4-AVG)

*(4-AVG)+ (8-AVG)

*(8-AVG))/(COUNT-1)=10 標準差STDDEV是方差VARIANCE的平方根3.16第二十一頁，共四十七頁。分組（聚集）函數(shù)27-3組函數(shù)使用要點：組函數(shù)會忽略空值。因為任何值和空值進行運算，結(jié)果還是空值。忽略空值可確保得到一個運算結(jié)果。在下面的示例中，計算平均加班費率時沒有使用(null)值。第二十二頁，共四十七頁。分組（聚集）函數(shù)27-4組函數(shù)使用要點：SELECT子句中可有多個基于相同或不同列的組函數(shù)。還可使用WHERE子句，將組函數(shù)限制在表的子集上。SELECTMAX(salary),MIN(salary),MIN(employee_id)FROMemployeesWHEREdepartment_id=60;第二十三頁，共四十七頁。分組（聚集）函數(shù)27-5組函數(shù)使用要點：MIN和MAX兩個組函數(shù)可用于任何數(shù)據(jù)類型。使用這些函數(shù)，可以找出列表中最后一個人的姓名、最低薪金或最早的聘用日期。例如，很容易在按字母排序的雇員列表中找到姓名列在第一行的雇員。SELECTMIN(last_name)FROMemployees;第二十四頁，共四十七頁。分組（聚集）函數(shù)27-6組函數(shù)使用要點：組函數(shù)忽略空值。MIN和MAX可用于任何數(shù)據(jù)類型；SUM、AVG、STDDEV和VARIANCE只能用于數(shù)值數(shù)據(jù)類型。第二十五頁，共四十七頁。分組（聚集）函數(shù)27-7組函數(shù)使用語法SELECT[col1,]GROUP_FUNCTION(col2)[,…]FROMtable1,…[WHEREcondition][GROUPBYcol1,…][HAVINGgroup_condition][ORDERBYcol1,…];如果有WHERE子句，先執(zhí)行WHERE，根據(jù)其條件排除掉無關(guān)記錄如果有GROUP子句，則先按其指定列的值將剩余記錄分組；否則所有記錄被看作一組對每組記錄計算分組函數(shù)如果有HAVING子句，則根據(jù)其中條件選擇要顯示哪些分組的結(jié)果執(zhí)行SELECT子句，返回相應(yīng)列執(zhí)行ORDERBY子句，對返回結(jié)果進行排序第二十六頁，共四十七頁。分組（聚集）函數(shù)27-8GROUPBY可以使用GROUPBY子句將表中的行分成較小的組。然后，可以使用組函數(shù)返回每個組的匯總信息。在所示的SELECT語句中，根據(jù)“部門標識”對行進行分組，然后將AVG函數(shù)自動應(yīng)用于每個組。SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYdepartment_id;第二十七頁，共四十七頁。分組（聚集）函數(shù)27-9GROUPBY如果想知道每個部門中雇員的最高薪金，該怎樣做？可使用GROUPBY子句指定依據(jù)哪一列對行進行分組？SELECTMAX(salary)FROMemployeesGROUPBYdepartment_id;但如何區(qū)分哪個最高薪金屬于哪個部門呢？第二十八頁，共四十七頁。分組（聚集）函數(shù)27-10GROUPBY通常，我們采用的方法是在SELECT列表中包含GROUPBY列。SELECTdepartment_id,MAX(salary)FROMemployeesGROUPBYdepartment_id;第二十九頁，共四十七頁。分組（聚集）函數(shù)27-11組函數(shù)要求，在SELECT子句中所列出的任何不屬于組函數(shù)組成部分的列，都必須在GROUPBY子句中列出。此示例中有什么錯誤？SELECTjob_id,last_name,AVG(salary)FROMemployeesGROUPBYjob_id;第三十頁，共四十七頁。分組（聚集）函數(shù)27-12還可使用WHERE子句排除一些行，然后再將剩余行分組。SELECTdepartment_id,MAX(salary)FROMemployeesWHERElast_name<>‘King’GROUPBYdepartment_id;第三十一頁，共四十七頁。分組（聚集）函數(shù)27-13GROUPBY原則使用GROUPBY子句時，應(yīng)遵循的重要原則有：如果SELECT子句中不僅包含組函數(shù)（AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE），還包含其它個體列，則這些個體列還必須出現(xiàn)在GROUPBY子句中。不能在GROUPBY子句中使用列別名。WHERE子句可在對行進行分組之前排除某些行。第三十二頁，共四十七頁。分組（聚集）函數(shù)27-14組內(nèi)組有時需要將組分為更小的組。例如，您可能希望根據(jù)部門對所有雇員進行分組；然后，在每個部門中，按職務(wù)進行分組。本示例說明每個部門中從事每項工作的雇員各有多少。SELECTdepartment_id,job_id,count(*)FROMemployeesWHEREdepartment_id>40GROUPBYdepartment_id,job_id;第三十三頁，共四十七頁。分組（聚集）函數(shù)27-15嵌套組函數(shù)使用GROUPBY時，可以嵌套兩層組函數(shù)。SELECTmax(avg(salary))FROMemployeesGROUPbydepartment_id;此查詢將返回幾個值？答案是一個——此查詢將找出每個部門的平均薪金，然后從該列表中選擇最大值。第三十四頁，共四十七頁。分組（聚集）函數(shù)27-16HAVING假設(shè)我們想找出每個部門的最高薪金，但僅針對有多個雇員的部門進行查找，那該怎么做？此示例中有什么錯誤？SELECTdepartment_id,MAX(salary)FROMemployeesWHERECOUNT(*)>1GROUPBYdepartment_id;下一張幻燈片將解決此問題。第三十五頁，共四十七頁。分組（聚集）函數(shù)27-17組函數(shù)使用要點：不能在WHERE子句中使用組函數(shù)：第三十六頁，共四十七頁。分組（聚集）函數(shù)27-18HAVING與使用WHERE子句限定所選行一樣，您可以使用HAVING子句來限定組。在使用GROUPBY和HAVING子句的查詢中，將首先對行進行分組，然后應(yīng)用組函數(shù)，這樣將僅顯示與HAVING子句匹配的組。WHERE子句用于限制行；HAVING子句用于限制由GROUPBY子句返回的組。

SELECTdepartment_id,MAX(salary)FROMemployeesGROUPBYdepartment_idHAVINGCOUNT(*)>1;第三十七頁，共四十七頁。分組（聚集）函數(shù)27-19在SELECT語句中，盡管可將HAVING子句用于GROUPBY子句之前，但建議您按以下所示的順序放置每條子句。如果使用ORDERBY子句，請始終將其放在最后！SELECT列,組函數(shù)FROM表WHEREGROUPBYHAVINGORDERBY第三十八頁，共四十七頁。分組（聚集）函數(shù)27-20DISTINCT關(guān)鍵字DISTINCT用于僅返回查詢中的非重復(fù)值或非重復(fù)值的組合。請看右邊的查詢。如果不使用關(guān)鍵字DISTINCT，該查詢將返回D_CDS表中的所有年份值。SELECTyearas‘CDYear’FROMd_cds;第三十九頁，共四十七頁。分組（聚集）函數(shù)27-21DISTINCT要消除重復(fù)行，請按此處所示使用DISTINCT關(guān)鍵字。使用DISTINCT關(guān)鍵字只返回了所有CD年份一次，而沒有重復(fù)值。SELECTDISTINCTyearAS‘CDYear’FROMd_cds;第四十頁，共四十七頁。分組（聚集）函數(shù)27-22DISTINCT關(guān)鍵字DISTINCT可與所有組函數(shù)一起使用。使用DISTINCT后，函數(shù)將僅考慮非重復(fù)值。為什么右側(cè)的兩個語句生成不同的結(jié)果？SELECTSUM(salary)FROMemployeesWHEREdepartment_id=90;SELECTSUM(DISTINCTsalary)FROMemployeesWHEREdepartment_id=90;第四十一頁，共四十七頁。分組（聚集）函數(shù)27-23DISTINCT將DISTINCT與組函數(shù)（如COUNT）一起使用時，結(jié)果集將返回非重復(fù)列值的數(shù)量。雇員們一共從事多少種不同的工作？有多少種不同的薪金？SELECTCOUNT(DISTINCTjob_id)FROMemployees;SELECTCOUNT(DISTINCTsalary)FROMemployees;第四十二頁，共四十七頁。分組（聚集）函數(shù)27-24COUNTCOUNT(表達式)返回表達式列中非空值的數(shù)量。COUNT(DISTINCT表達式)返回表達式列中唯一且非空值的數(shù)量。SELECTCOUNT(YEAR)FROMd_cdsWHEREyear<2001;SELECTCOUNT(DISTINCTyear)FROMd_cdsWHEREyear<2001;第四十三頁，共四十七頁。分組（聚集）函數(shù)27-25COUNT第一個查詢?yōu)槭裁捶祷豱ull值？共列出了六條備注，但count函數(shù)只返回了5。為什么？因為COUNT忽略列中的null值。SELECTcommentsFROMd_play_list_items;SELECTCOUNT(comments)FROMd_play_list_items;第四十