上次主要介紹達(dá)夢(mèng)數(shù)據(jù)庫(kù)的常用集函數(shù)和分析函數(shù)，這次在上次的基礎(chǔ)上繼續(xù)介紹相鄰函數(shù) LAG 和 LEAD，占比函數(shù)RATIO_TO_REPORT等分析函數(shù)，并補(bǔ)充介紹分組函數(shù)GROUP BY、ROLLUP、CUBE的用法以及區(qū)別。

本章的測(cè)試環(huán)境：

操作系統(tǒng): 中標(biāo)麒麟6? 64位

數(shù)據(jù)庫(kù)版本：達(dá)夢(mèng)8.1

達(dá)夢(mèng)數(shù)據(jù)庫(kù)客戶(hù)端：DM管理工具

分析函數(shù)

本次重點(diǎn)講解相鄰函數(shù) LAG 和 LEAD，占比函數(shù)RATIO_TO_REPORT兩個(gè)分析函數(shù)。

● 相鄰函數(shù)LAG和LEAD?●

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

語(yǔ)法：

LAG(EXP_STR,OFFSET,DEFVAL)OVER()LEAD(EXP_STR,OFFSET,DEFVAL)OVER()EXP_STR：要取的列OFFSET：取偏移后的第幾行數(shù)據(jù)DEFVAL：沒(méi)有符合條件的默認(rèn)值

? ? ? ①比如獲取某公司各員工薪資情況，并同時(shí)展示同部門(mén)比該員工高1級(jí)的員工薪資，同部門(mén)比該員工低1級(jí)的員工薪資，總公司比該員工高1級(jí)的員工薪資情況，總公司比該員工低1級(jí)的薪資情況，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(+);輸出結(jié)果展示如下(數(shù)值列依次為該部門(mén)員工薪資，同部門(mén)比該員工高1級(jí)的員工薪資，同部門(mén)比該員工低1級(jí)的員工薪資，總公司內(nèi)比該員工高1級(jí)的員工薪資情況，總公司內(nèi)比該員工低1級(jí)的薪資情況)：

相鄰函數(shù)也常用于公司年終財(cái)務(wù)報(bào)表計(jì)算同比和環(huán)比的場(chǎng)景。

? ? ? ②比如獲取銷(xiāo)售的業(yè)績(jī)情況，同比分析相同月份上年和下年的情況，環(huán)比分析上月和下月的銷(xiāo)售情況，sql樣例參考如下：

select to_char(trunc(logtime, 'mm'),'yyyy-mm-dd') "月份", to_char(sum(price),'fm999990.00') "當(dāng)月收入", -- 環(huán)比分析，與上個(gè)月份進(jìn)行比較 to_char(lag(sum(price), 1) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "環(huán)比上月", -- 環(huán)比分析，與下個(gè)月份進(jìn)行比較 to_char(lead(sum(price), 1) over(order by trunc(logtime, 'mm')),'fm999990.00') as "環(huán)比下月", -- 同比分析，與上個(gè)年度相同月份進(jìn)行比較 to_char(lag(sum(price), 12) over(order by trunc(logtime, 'mm')), 'fm999990.00') as "同比上年", -- 同比分析，與下個(gè)年度相同月份進(jìn)行比較 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;輸出結(jié)果展示如下(數(shù)值列依次為當(dāng)月收入，環(huán)比上月收入，環(huán)比下月收入，同比上年收入，同比下年收入)：

● 占比函數(shù)RATIO_TO_REPORT?●

占比函數(shù)常用于財(cái)務(wù)中計(jì)算收支占比。用于取某個(gè)值占總和的百分比。

語(yǔ)法：

RATIO_TO_REPORT(EXP_STR)OVER()RATIO_TO_REPORT() 括號(hào)中表達(dá)式EXP_STR就是分子，OVER() 括號(hào)中就是分母，分母缺省就是整個(gè)占比。

①比如獲取某公司每個(gè)城市員工人數(shù)分布和薪資待遇分布情況，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;

從輸出結(jié)果中可以看出各個(gè)區(qū)域公司人員占用百分比，各區(qū)域薪資占用百分比；如下結(jié)果集可以作為報(bào)表餅圖的原型。輸出結(jié)果展示如下：

RATIO_TO_REPORT 可以結(jié)合partition by 使用。

②比如獲取公司每個(gè)員工薪資及員工薪資在所在部門(mén)的薪資占比，每個(gè)部門(mén)總薪資及部門(mén)薪資在公司的占比，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))

輸出結(jié)果展示如下(數(shù)值列依次員工個(gè)人薪資/部門(mén)總薪資，分組值，公司薪資合計(jì)/部門(mén)薪資合計(jì)，薪資占比)：

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

分組函數(shù)GROUP BY子句?

GROUP BY 子句是 SELECT 語(yǔ)句的可選項(xiàng)部分。

它定義了分組表。

GROUP BY 子句語(yǔ)法如下：

GROUP BY 子句

ROLLUP 項(xiàng)

CUBE 項(xiàng)

GROUPING SETS 項(xiàng)

GROUP 項(xiàng) ( )

HAVING 子句

這里介紹ROLLUP、CUBE、GROUPING SETS三項(xiàng)的用法和差異。

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

要弄明白rollup，cube和grouping sets，就要知道group by的使用場(chǎng)景， group by 為對(duì)列進(jìn)行分組，只展現(xiàn)分組統(tǒng)計(jì)的值，而rollup 為分層次展現(xiàn)，cube為展現(xiàn)列中所有層次，grouping sets只展現(xiàn)列中單一層次。三者都是group by子句的擴(kuò)展。

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

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

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

例如：group by rollup(a, b, c) 首先會(huì)對(duì)(a, b, c)進(jìn)行g(shù)roup by，然后對(duì)(a, b)進(jìn)行g(shù)roup by，然后是(a)進(jìn)行g(shù)roup by，最后對(duì)全表進(jìn)行g(shù)roup by操作。

group by cube(a, b, c)，首先會(huì)對(duì)(a, b, c)進(jìn)行g(shù)roup by，然后依次是(a, b)，(a, c)，(a)，(b, c)，(b)，(c)，最后對(duì)全表進(jìn)行g(shù)roup by操作。

grouping sets (a, b, c)，首先會(huì)對(duì)(a)進(jìn)行g(shù)roup by，然后依次是(b)，(c)，不對(duì)全表進(jìn)行g(shù)roup by操作。

具體區(qū)別可以查看如下樣例。

●?ROLLUP?●

比如獲取公司各城市每個(gè)部門(mén)員工人數(shù)分布和薪資分布情況，使用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);從結(jié)果集中可以看出group by rollup聚合了城市和部門(mén)組合，城市組合，所有組合三種情況。輸出結(jié)果展示如下：

● 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);

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

● 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);

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

好了，本次分享就到這里了，感謝大家的關(guān)注。后期精彩敬請(qǐng)期待！

——? ? ?END? ? ?——

總結(jié)

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

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。