上次主要介紹達夢數據庫的常用集函數和分析函數，這次在上次的基礎上繼續介紹相鄰函數 LAG 和 LEAD，占比函數RATIO_TO_REPORT等分析函數，并補充介紹分組函數GROUP BY、ROLLUP、CUBE的用法以及區別。

本章的測試環境：

操作系統: 中標麒麟6? 64位

數據庫版本：達夢8.1

達夢數據庫客戶端：DM管理工具

分析函數

本次重點講解相鄰函數 LAG 和 LEAD，占比函數RATIO_TO_REPORT兩個分析函數。

● 相鄰函數LAG和LEAD?●

LAG和LEAD函數是跟偏移量相關的兩個分析函數，通過這兩個函數可以在一次查詢中取出同一字段的前N行的數據(lag)和后N行的數據(lead)作為獨立的列，從而更方便地進行數據篩選。這種操作可以代替表的自聯接，不過LAG和LEAD有更高的效率。

語法：

LAG(EXP_STR,OFFSET,DEFVAL)OVER()LEAD(EXP_STR,OFFSET,DEFVAL)OVER()EXP_STR：要取的列OFFSET：取偏移后的第幾行數據DEFVAL：沒有符合條件的默認值

? ? ? ①比如獲取某公司各員工薪資情況，并同時展示同部門比該員工高1級的員工薪資，同部門比該員工低1級的員工薪資，總公司比該員工高1級的員工薪資情況，總公司比該員工低1級的薪資情況，sql樣例參考如下：

select dept.department_name, emp.employee_name, emp.salary, lag(salary)over(partition by dept.department_name order by salary desc) dept_lag, lead(salary)over(partition by dept.department_name order by salary desc) dept_lead, lag(salary)over(order by salary desc) total_lag, lead(salary)over(order by salary desc) total_lead from employees emp, department dept where emp.department_id = dept.department_id(+);輸出結果展示如下(數值列依次為該部門員工薪資，同部門比該員工高1級的員工薪資，同部門比該員工低1級的員工薪資，總公司內比該員工高1級的員工薪資情況，總公司內比該員工低1級的薪資情況)：

相鄰函數也常用于公司年終財務報表計算同比和環比的場景。

? ? ? ②比如獲取銷售的業績情況，同比分析相同月份上年和下年的情況，環比分析上月和下月的銷售情況，sql樣例參考如下：

select to_char(trunc(logtime, 'mm'),'yyyy-mm-dd') "月份", to_char(sum(price),'fm999990.00') "當月收入", -- 環比分析，與上個月份進行比較 to_char(lag(sum(price), 1) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "環比上月", -- 環比分析，與下個月份進行比較 to_char(lead(sum(price), 1) over(order by trunc(logtime, 'mm')),'fm999990.00') as "環比下月", -- 同比分析，與上個年度相同月份進行比較 to_char(lag(sum(price), 12) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "同比上年", -- 同比分析，與下個年度相同月份進行比較 to_char(lead(sum(price), 12) over(order by trunc(logtime, 'mm')),'fm999990.00') as "同比下年" from orderproduct group by trunc(logtime, 'mm') order by 1 desc;輸出結果展示如下(數值列依次為當月收入，環比上月收入，環比下月收入，同比上年收入，同比下年收入)：

● 占比函數RATIO_TO_REPORT?●

占比函數常用于財務中計算收支占比。用于取某個值占總和的百分比。

語法：

RATIO_TO_REPORT(EXP_STR)OVER()RATIO_TO_REPORT() 括號中表達式EXP_STR就是分子，OVER() 括號中就是分母，分母缺省就是整個占比。

①比如獲取某公司每個城市員工人數分布和薪資待遇分布情況，sql樣例參考如下：

select c.city_name, count(*) toal_personnum, round((ratio_to_report(count(*))over())*100, 2) person_ratio, sum(emp.salary) total_salary, round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio from employee emp, department dept, location l, city c where emp.department_id = dept.department_id(+) and dept.location_id = l.location_id(+) and l.city_id = c.city_id(+) group by c.city_name;

從輸出結果中可以看出各個區域公司人員占用百分比，各區域薪資占用百分比；如下結果集可以作為報表餅圖的原型。輸出結果展示如下：

RATIO_TO_REPORT 可以結合partition by 使用。

②比如獲取公司每個員工薪資及員工薪資在所在部門的薪資占比，每個部門總薪資及部門薪資在公司的占比，sql樣例參考如下：

select department_name, employee_name, salary, g1, sum(salary) over(partition by decode(g1, 0, department_name, null), g1) sum_salary, ratio_to_report(salary) over(partition by decode(g1, 0, department_name, null), g1) salaryrate from ( select dept.department_name, emp.employee_name, sum(emp.salary) salary, grouping(dept.department_name) + grouping(emp.employee_name) g1 from employees emp, department dept where emp.department_id = dept.department_id group by rollup(dept.department_name, emp.employee_name))

輸出結果展示如下(數值列依次員工個人薪資/部門總薪資，分組值，公司薪資合計/部門薪資合計，薪資占比)：

從上面的例子中我們看到了group by rollup這個語句，這里也順帶講解下分組函數group by子句的使用方法。

分組函數GROUP BY子句?

GROUP BY 子句是 SELECT 語句的可選項部分。

它定義了分組表。

GROUP BY 子句語法如下：

GROUP BY 子句

ROLLUP 項

CUBE 項

GROUPING SETS 項

GROUP 項 ( )

HAVING 子句

這里介紹ROLLUP、CUBE、GROUPING SETS三項的用法和差異。

用法group by [rollup|cube|grouping sets](colomn)

要弄明白rollup，cube和grouping sets，就要知道group by的使用場景， group by 為對列進行分組，只展現分組統計的值，而rollup 為分層次展現，cube為展現列中所有層次，grouping sets只展現列中單一層次。三者都是group by子句的擴展。

ROLLUP：可以為每個分組返回小計記錄以及為所有分組返回總計記錄。ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合。

CUBE：可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。

GROUPING SETS：可以返回每一個列的小計記錄。GROUPING SETS生成的結果集顯示了所選列中值的單一的聚合。

例如：group by rollup(a, b, c) 首先會對(a, b, c)進行group by，然后對(a, b)進行group by，然后是(a)進行group by，最后對全表進行group by操作。

group by cube(a, b, c)，首先會對(a, b, c)進行group by，然后依次是(a, b)，(a, c)，(a)，(b, c)，(b)，(c)，最后對全表進行group by操作。

grouping sets (a, b, c)，首先會對(a)進行group by，然后依次是(b)，(c)，不對全表進行group by操作。

具體區別可以查看如下樣例。

●?ROLLUP?●

比如獲取公司各城市每個部門員工人數分布和薪資分布情況，使用group by rollup的sql樣例參考如下：select?? c.city_name, dept.department_name, count(*) toal_personnum, round((ratio_to_report(count(*))over())*100, 2) person_ratio, sum(emp.salary) total_salary, round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio from employee emp, department dept, location l, city c where emp.department_id = dept.department_id(+) and dept.location_id = l.location_id(+) and l.city_id = c.city_id(+) group by rollup (c.city_name, dept.department_name);從結果集中可以看出group by rollup聚合了城市和部門組合，城市組合，所有組合三種情況。輸出結果展示如下：

● CUBE?●

上例中的sql將rollup換成cube，sql樣例參考如下：

select c.city_name, dept.department_name, count(*) toal_personnum, round((ratio_to_report(count(*))over())*100, 2) person_ratio, sum(emp.salary) total_salary, round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio from employee emp, department dept, location l, city c where emp.department_id = dept.department_id(+) and dept.location_id = l.location_id(+) and l.city_id = c.city_id(+) group by cube (c.city_name, dept.department_name);

從結果集中可以看出group by cube聚合了城市和部門組合，城市組合，部門組合，所有組合四種情況。結果集展示如下：

● GROUPING SETS?●

將上例中sql的cube換成grouping sets，sql樣例參考如下：

select c.city_name, dept.department_name, count(*) toal_personnum, round((ratio_to_report(count(*))over())*100, 2) person_ratio, sum(emp.salary) total_salary, round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio from employee emp, department dept, location l, city c where emp.department_id = dept.department_id(+) and dept.location_id = l.location_id(+) and l.city_id = c.city_id(+) group by grouping sets (c.city_name, dept.department_name);

從結果集中可以看出grouping sets聚合了城市組合，部門組合兩種情況，沒有進行組合分組合計。輸出結果展示如下：

好了，本次分享就到這里了，感謝大家的關注。后期精彩敬請期待！

——? ? ?END? ? ?——

總結

以上是生活随笔為你收集整理的groupby函数_干货分享|达梦数据库常用集函数与分析函数（下）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。