OceanBase SUM

?SUM? 函數(shù)返回參數(shù)中指定列的和。此函數(shù)將可以隱式轉(zhuǎn)換為數(shù)值數(shù)據(jù)類型的任何數(shù)值數(shù)據(jù)類型或任何非數(shù)值數(shù)據(jù)類型作為參數(shù)。 函數(shù)返回與參數(shù)的數(shù)值數(shù)據(jù)類型相同的數(shù)據(jù)類型。

語(yǔ)法

SUM([ DISTINCT | UNQIUE | ALL ] expr) [ OVER (analytic_clause) ]

作為分析函數(shù)使用時(shí)，您需要使用窗口函數(shù)的完整語(yǔ)法，它對(duì)一組行的集合進(jìn)行計(jì)算并返回多個(gè)值。作為聚合函數(shù)使用時(shí)，該函數(shù)對(duì)一組行的集合進(jìn)行聚合計(jì)算，結(jié)果只能返回一個(gè)值，此時(shí)不需要加 ?OVER? 關(guān)鍵字。

參數(shù)

注意 ：
如果您指定了 ?DISTINCT? 或 ?UNIQUE? 關(guān)鍵字，則 ?analytic_clause? 中不允許出現(xiàn) ?order_by_clause? 和 ?windowing_clause?。

返回類型

返回與 ?expr? 相同數(shù)據(jù)類型的值。

示例

分析函數(shù)示例

創(chuàng)建表 employees，并向里面插入數(shù)據(jù)，執(zhí)行以下語(yǔ)句：

CREATE TABLE employees(manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(300, 'Wei',  '2019-09-11',23600);     
INSERT INTO employees VALUES(200, 'Red', '2019-11-05', 23800);
INSERT INTO employees VALUES(100, 'Part',  '2018-10-01',24000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',23500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 23000);
INSERT INTO employees VALUES(200, 'Part',  '2018-06-11',24500);    
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
COMMIT;

計(jì)算工資總額，執(zhí)行以下語(yǔ)句：

SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id 
ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum
FROM employees ORDER BY manager_id, last_name, salary, l_csum;

查詢結(jié)果如下：

+------------+-----------+--------+--------+
| MANAGER_ID | LAST_NAME | SALARY | L_CSUM |
+------------+-----------+--------+--------+
|        100 | De Haan   |  11000 |  12400 |
|        100 | Errazuriz |   1400 |   1400 |
|        100 | Hartstein |  14000 |  26400 |
|        100 | Part      |  24000 |  50400 |
|        200 | Bell      |  23000 |  23000 |
|        200 | Part      |  24500 |  94800 |
|        200 | Red       |  23800 |  70300 |
|        200 | Ross      |  23500 |  46500 |
|        300 | Wei       |  23600 |  23600 |
+------------+-----------+--------+--------+

聚合函數(shù)示例

計(jì)算工資總額，執(zhí)行以下語(yǔ)句：

SELECT SUM(salary) FROM employees;

查詢結(jié)果如下：

+-------------+
| SUM(SALARY) |
+-------------+
|      168800 |
+-------------+