今天主要介紹達夢資料庫的
常用集函式和分析函式
及各個函式的使用場景，希望大家在sql的編寫上能做到遊刃有餘。
本章的測試環境：
作業系統： 中標麒麟6 64位
資料庫版本：達夢8。1
達夢資料庫客戶端：DM管理工具
集函式
為了方便使用者的使用，增強查詢能力，達夢SQL 語言提供了多種內部集函式。
集函式又稱庫函式，當根據某一限制條件從表中匯出一組行集時，使用集函式可對該行集作統計操作。
集函式可分為 8 類：
1． COUNT（*）；
2． 相異集函式 AVG|MAX|MIN|SUM|COUNT（DISTINCT）；
3． 完全集函式 AVG|MAX|MIN| COUNT|SUM（［ALL］）；
4．方差集函式 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、 STDDEV；
5． 協方差函式 COVAR_POP、COVAR_SAMP、CORR；
6． 首行函式 FIRST_VALUE
7． 求區間範圍內最大值集函式 AREA_MAX；
8．FIRST/LAST 集函式 AVG|MAX|MIN| COUNT|SUM（［ALL］ ） KEEP （DENSE_RANK FIRST|LAST ORDER BY 子句）；
9．字串集函式 LISTAGG/LISTAGG2。
這裡介紹最常用集函式AVG|MAX|MIN| COUNT|SUM和LISTAGG/LISTAGG2。
AVG|MAX|MIN|COUNT|SUM
MAX：求最大值集函式；
MIN：求最小值集函式；
AVG：求平均值集函式；
SUM：總和集函式；
COUNT：求總個數集函式。
以查詢DMHR樣例資料庫中某公司每個部門的總人數，部門最高、最低、平均薪資水平，薪資總和為例，來展示各函式的查詢用途。
查詢sql樣例如下：
select dept。department_name， count（*） person_num， max（emp。salary） max_sal， min（emp。salary） min_sal， avg（emp。salary） avg_sal， sum（emp。salary） sum_sal from employee emp， department dept where emp。department_id = dept。department_id（+） group by dept。department_name；
輸出結果展示如下（數值列依次為本部門員工數，最大薪資數，最小薪資數，平均薪資數，薪資總和）：
字串函式LISTAGG/LISTAGG2
LISTAGG/LISTAGG2（exp1， exp2）集函式先根據 sql 語句中的 group by 分組（如果沒有指定分組則所有結果集為一組），然後在組內按照 WITHIN GROUP 中的ORDER BY進行排序，最後將表示式exp1用表示式exp2串接起來。
LISTAGG2 跟LISTAGG的功能是一樣的，區別就是LISTAGG2返回的是clob型別，LISTAGG 返回的是 VARCHAR 型別。
LISTAGG 的用法：（［，］） WITHIN GROUP（）
LISTAGG2 的用法：（［，］） WITHIN GROUP（）
以獲取某公司各區域部門的員工名單為例，各區域各部門一條記錄，sql樣例參考如下：
select c。city_name，dept。department_name， listagg（emp。employee_name，‘，’） within group（order by emp。employee_id）as employees，count（*） empnumfrom employee emp， department dept，location l， city cwhere 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， dept。department_name；
輸出結果展示如下（依次為區域名稱、部門名稱、部門員工名單，部門員工總數）：
分析函式
分析函式主要用於計算基於組的某種聚合值。
達夢資料庫分析函式為使用者分析資料提供了一種更加簡單高效的處理方式。如果不使用分析函式，則必須使用連線查詢、子查詢或者檢視，甚至複雜的儲存過程實現。引入分析函式後，只需要簡單的 SQL 語句，並且執行效率方面也有大幅提高。
與集函式的主要區別是，分析函式對於每組返回多行，而集函式對於每個分組只返回一行。多行形成的組稱為視窗，視窗決定了執行當前行的計算範圍，視窗的大小可以由組中定義的行數或者範圍值滑動。
分析函式可分為 11 類：
1． COUNT（*）；
2．完全分析函式 AVG|MAX|MIN| COUNT|SUM（［ALL］），這 5 個分析函式的引數和作為集函式時的引數一致；
3． 方差函式 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、 STDDEV；
4． 協方差函式 COVAR_POP、COVAR_SAMP、CORR；
5．首尾函式 FIRST_VALUE、LAST_VALUE；
6． 相鄰函式 LAG 和 LEAD；
7．分組函式 NTILE；
8．排序函式 RANK、DENSE_RANK、ROW_NUMBER；
9． 百分比函式 PERCENT_RANK、CUME_DIST、RATIO_TO_REPORT、 PERCENTILE_CONT、NTH_VALUE；
10． 字串函式 LISTAGG；
11． 指定行函式 NTH_VALUE。
分析函式語法如下：
分析子句
partition by 項
order by 項
這裡重點講解常用的幾個聚合分析函式。
AVG|MAX|MIN|COUNT|SUM
平均值|最大值|最小值|總個數|求總和也是最常用的分析函式。
① 以查詢DMHR樣例資料庫中某公司部門人員薪資，部門最高薪資，區域最高薪資，公司最高薪資為例，來展示聚合分析函式的查詢用途。
查詢sql樣例如下：
select c。city_name，dept。department_name deptname，emp。employee_name empname，emp。salary，max（emp。salary） over （partition by c。city_name， dept。department_name） deptmaxsal， max（emp。salary） over （partition by c。city_name） citymaxsal， max（emp。salary） over （） maxsalfrom employees emp， department dept，location l， city cwhere emp。department_id = dept。department_id（+）and dept。location_id = l。location_id（+）and l。city_id = c。city_id（+）；
輸出結果展示如下（數值項依次為員工個人薪資、部門最高薪資、區域最高薪資、公司最高薪資）：
② 以查詢DMHR樣例資料庫中某公司區域部門人員總數，部門人員總數，區域人員總數，總司總員工人數為例，來展示聚合分析函式的查詢用途。
查詢sql樣例如下：
select distinct c。city_name， dept。department_name， count（*） over （partition by c。city_name，dept。department_name） citydept_empnum， count（*） over （partition by dept。department_name） dept_empnum， count（*） over （partition by c。city_name） city_empnum， count（*） over （） totalempnum from employee emp， department dept，location l， city cwhere emp。department_id = dept。department_id（+） and dept。location_id = l。location_id（+） and l。city_id = c。city_id（+）；
輸出結果展示如下（數值項依次為各區域部門員工數、各部門員工數、各區域員工數、公司員工總數）：
字串函式LISTAGG
LISTAGG用於做字串之間的連線，即可以做集函式，也可以做分析函式。LISTAGG2不支援分析函式。
比如查詢公司員工資訊，並同步獲取相同工種的員工名單，
sql樣例參考如下：
select c。city_name，dept。department_name deptname，emp。employee_name empname， j。job_title，listagg（emp。employee_name，‘，’） within group （order by emp。employee_id）over（partition by j。job_title） as job_employeefrom employee emp， department dept，location l， city c， job jwhere emp。department_id = dept。department_id（+）and dept。location_id = l。location_id（+）and l。city_id = c。city_id（+）and emp。job_id = j。job_id；
輸出結果展示如下（最後一列為該員工相同工種的員工名單）：
排序函式RANK,DENSE_RANK,ROW_NUMBER
此三個函式用於對資料排序，生成排行榜的場景，為每條記錄產生一個從1開始至n的自然數，n的值可能小於等於記錄的總數。這3個函式的唯一區別在於當碰到相同資料時的排名策略。
ROW_NUMBER：當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。
DENSE_RANK： 當碰到相同資料時，此時所有相同資料的排名都是一樣的。
RANK：當碰到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。
① 以查詢DMHR樣例資料庫中公司每個員工薪水在公司排行情況為例，
sql參考如下：
select emp。employee_name，dept。department_name，emp。salary，rank（）over（order by salary desc） rank，dense_rank（）over（order by salary desc） dense_rank， row_number（）over（orde rby salary desc） rownumberfrom employee emp， department deptwhere emp。department_id = dept。department_id（+）；
從輸出結果可以看出此三個函式的區別，輸出結果展示如下：
② 以查詢DMHR樣例資料庫中公司每個員工薪水及薪水在部門和公司排行情況為例，sql樣例參考如下：
select dept。department_name，emp。employee_name，emp。salary，dense_rank（）over（partition by dept。department_name order by salary desc） dept_rank，dense_rank（）over（order by salary desc） total_rankfrom employees emp， department deptwhere emp。department_id = dept。department_id（+）；
從輸出結果中，我們可以看到每個員工薪資在自己部門和總公司的排行情況，輸出結果展示如下（數值項依次為員工個人薪資、所在部門排名、所在公司排名）：
好了，本次常用集函式和分析函式就介紹到這了，大家有沒有掌握呢。下次為大家繼續介紹佔比函式RATIO_TO_REPORT、相鄰函式 LAG 和 LEAD等分析函式的使用，敬請期待！
