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

人力成本統(tǒng)計(jì)分析任務(wù)人事管理第一頁(yè)，共四十七頁(yè)。任務(wù)背景2-1你們項(xiàng)目組現(xiàn)在負(fù)責(zé)開發(fā)一個(gè)人事管理系統(tǒng)，你負(fù)責(zé)人力成本統(tǒng)計(jì)分析報(bào)表生成任務(wù)。項(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)采取餅圖、柱狀圖、曲線圖等直觀方式來顯示統(tǒng)計(jì)結(jié)果第二頁(yè)，共四十七頁(yè)。任務(wù)背景2-2項(xiàng)目經(jīng)理告訴你通過你開發(fā)的報(bào)表、圖表，人力資源部經(jīng)理可容易的了解：哪些部門工資水平高？哪些部門工資水平低？哪些部門工資差距比較大？哪些部門工資成本最高？參考下面的報(bào)表設(shè)計(jì)，有助于你完成任務(wù)第三頁(yè)，共四十七頁(yè)。報(bào)表設(shè)計(jì)3-1工資水平分析報(bào)表第四頁(yè)，共四十七頁(yè)。報(bào)表設(shè)計(jì)3-2工資差異分析報(bào)表第五頁(yè)，共四十七頁(yè)。報(bào)表設(shè)計(jì)3-3人力成本分析報(bào)表第六頁(yè)，共四十七頁(yè)。關(guān)鍵技術(shù)分組（聚集）函數(shù)分組函數(shù)介紹

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

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

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

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