常用Oracle分析函數詳解

學習步驟：
1. 擁有Oracle EBS demo 環境 或者 PROD 環境
2. copy以下代碼進 PL/SQL
3. 配合解釋分析結果
4. 如果網頁有點亂請複製到TXT中查看

/*假設一個經理代表了一個部門
*/
SELECT emp.full_name,
       emp.salary,
       emp.manager_id,
       row_number() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) row_number_dept, --部門排行
       rownum row_number, --行號
       round((rownum + 1) / 4) page_number, --每4行一頁
       ntile(2) over(ORDER BY emp.salary DESC) page_number_nt, --平均分成兩類
      
       AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department, --該部門薪水均值
       SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, --該部門薪水總額
       COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department, --部門所有的員工
       dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, --該人員的部門薪水排行
       dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company, --該人員的全公司排行
      
       MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept, --部門的最低薪水      
       MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, --部門的最低薪水
       first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, --部門的最低薪水     
      
       MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept, --部門的最高薪水
       MAX(emp.salary) keep(dense_rank LAST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept_last, --部門的最高薪水
       last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv, --部門的最高薪水
      
       lag(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) last_persion, --薪水在自己前一位的人
       lead(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) next_persion --薪水在自己後一位的人
  FROM fwk_tbx_employees emp
ORDER BY emp.salary DESC
  
  1. 基本概念理解
  
     分析函數
        1. 顧名思義，分析函數是在主查詢結果的基礎上進行一定的分析，如分部門匯總，分部門求均值等等。
     
     資料視窗
        1. Oracle 分析函數建立在所謂的資料視窗之上，資料視窗可以理解為一個資料集合。主查詢的資料可以按照不同的標準分割成不同的資料集。比如partition BY manager_id
     按照manager_id將主查詢的資料分成N(N代表有多少個不同的Manager_id)個不同的資料視窗。
        2. 其次，資料視窗內部還應該與一定的順序通過 ORDER BY 實現
     
     分析函數和GROUP BY的區別和聯絡
        1. 分析函數的功能大部分都可以通過GROUP BY 來彙總完成
        2. 分析函數查詢出來的行數是由主查詢決定的，GROUP BY 的行數結果是由GROUP BY 後面的集合構成的唯一性組合決定的，通常比主查詢的結果行數少。
         
     
  2. 典型格式詳解
     
     SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, --該部門薪水總額
   
   功能簡介：
     當前行對應人員所在部門的薪水總額
     AVG，count與之類似
     
   過程理解
     1. 首先將查詢出來的資料集按照MANAGER_ID分割
     2. 尋找到當前行的MANAGER_ID對應的資料集
     3. 對以上資料集合求和，產生一個結果附在新添加的列中
     
         
     dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, --該人員的部門薪水排行
     
   功能簡介：
     當前行對應人員在所在部門的薪水排名(不出現並列情況，相同的值也會依次有不同的排序，且排序連續)
     RANK 函數與之相反，要出現並列的情況啊，且並列將導致排名不連續如A和B並列第一，那麼將沒有第二名，而直接出現第三名
   過程理解
     1. 首先將查詢出來的資料集按照MANAGER_ID分割
     2. 對當前行MANAGER_ID對應的資料集進行排序
     3. 將本行對應的行號提取並附在附加列中
     
     MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id)  min_salary_dept_first, --部門的最低薪水
     
   功能簡介：
     當前行對應人員在所在部門的最低薪水
     MAX函數與之類似
   過程理解
     1. 首先將查詢出來的資料集按照MANAGER_ID分割
     2. 對當前行MANAGER_ID對應的資料集進行排序，提取最前面的行，最前面的行的值有相等的，那麼返回多行
     3. 在返回的多行中，提取薪水最小的行，並提取salary欄位
     
     first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, --部門的最低薪水  
     
  功能簡介：
     當前行對應人員在所在部門的最低薪水
     last_value與之相反，求的是最後一個值
   
   過程理解
     1. 首先將查詢出來的資料集按照MANAGER_ID分割
     2. 對當前行MANAGER_ID對應的資料集進行排序
     3. 提取第一行的salary欄位
     
     
   LAG(EMP.FULL_NAME, 1, '00') OVER (ORDER BY EMP.SALARY DESC)  LAST_PERSION, --薪水在自己前一位的人
     
   功能簡介：
     總體薪水排名中，比自己高一位的人的名字
     lead 函數與之相反求的在自己後面的人
   
   參數介紹：
     LAG(p_segment, p_distance, p_defaualt_val)
     1. p_segment: 需要提取的欄位
     2. p_distance：>=0的數，表示比當前人員前面了幾位
     3. p_defaualt_val: 噹噹前行沒有比它前的行的時候，顯示預設值
   
   過程理解
     1. 首先將查詢出來的資料集按照薪水進行降序排序
     2. 提取前p_distance位的p_segment欄位