AnalyticDB MySQL彙總函式用法詳解 - AnalyticDB

彙總函式用於對一組資料進行匯總計算。本文介紹AnalyticDB for MySQL彙總函式的用法與樣本。

AnalyticDB for MySQL支援如下彙總函式：

ARBITRARY：隨機返回一組資料中的任意一個值。
AVG：該函數用於計算平均值。
BIT_AND：返回參數所有位按位AND後的結果。
BIT_OR：返回參數所有位按位OR後的結果。
BIT_XOR：返回參數所有位按位異或後的結果。
COUNT：該函數用於計算記錄數。
MAX：該函數用於計算最大值。
MIN：該函數用於計算最小值。
STD、STDDEV或STDDEV_POP：返回數值的總體標準差。
STDDEV_SAMP：返回一組數值（整數、小數或浮點）的樣本標準差。
SUM：該函數用於計算匯總值。
VARIANCE（非標準SQL函數）：返回一組數值（整數、小數或浮點）的總體方差。
VAR_POP（標準SQL函數）：返回一組數值（整數、小數或浮點）的總體方差。
VAR_SAMP：返回一組數值（整數、小數或浮點）的樣本方差。
GROUP_CONCAT：該函數用於將GROUP BY返回結果中屬於同一個分組的值串連起來，返回一個字串結果。

說明

除GROUP_CONCAT函數外，本文中的其他彙總函式均以testtable表為例，建表語句如下：

CREATE TABLE testtable(a INT) DISTRIBUTED BY HASH(a);

已使用如下語句往testtable表中插入測試資料：

INSERT INTO testtable VALUES (1),(2),(3);

ARBITRARY

arbitrary(x)

命令說明：隨機返回一組資料中的任意一個值。
輸入實值型別：支援輸入任意類型的參數。
傳回值類型：與該函數的輸入實值型別保持一致。

樣本：

SELECT arbitrary(a) FROM testtable;

返回結果如下：

+--------------+
| arbitrary(a) |
+--------------+
|            2 |
+--------------+

AVG

avg(x)

命令說明：該函數用於計算平均值。
輸入實值型別：BIGINT、DOUBLE或FLOAT。
傳回值類型：DOUBLE。

樣本：

SELECT avg(a) FROM testtable;

返回結果如下：

+--------+
| avg(a) |
+--------+
|    2.0 |
+--------+

BIT_AND

bit_and(x)

命令說明：返回參數所有位按位AND後的結果。
輸入實值型別：BIGINT、DOUBLE或FLOAT。
傳回值類型：BIGINT。

樣本：

SELECT bit_and(a) FROM testtable;

返回結果如下：

+------------+
| bit_and(a) |
+------------+
|          0 |
+------------+

BIT_OR

bit_or(x)

命令說明：返回參數所有位按位OR後的結果。
輸入實值型別：BIGINT、DOUBLE或FLOAT。
傳回值類型：BIGINT。

樣本：

SELECT bit_or(a) FROM testtable;

返回結果如下：

+-----------+
| bit_or(a) |
+-----------+
|         3 |
+-----------+

BIT_XOR

bit_xor(x)

命令說明：返回參數所有位按位異或後的結果。
輸入實值型別：BIGINT、DOUBLE或FLOAT。
傳回值類型：BIGINT。

樣本：

SELECT bit_xor(a) FROM testtable;

返回結果如下：

+------------+
| bit_xor(a) |
+------------+
|          0 |
+------------+

COUNT

count([distinct|all] x)

命令說明：該函數用於計算記錄數。
說明
distinct、all指明在計數時是否去除重複記錄，預設all，即返回全部記錄。如果指定distinct，返回結果只計算唯一值數量。
輸入實值型別：數值、字串類型或BOOLEAN類型。
傳回值類型：BIGINT。

樣本：

計算testtable中值是唯一的記錄數，語句如下：

SELECT count(distinct a) FROM testtable;

返回結果如下：

+-------------------+
| count(distinct a) |
+-------------------+
|                 3 |
+-------------------+

計算testtable中所有的記錄數，語句如下：

SELECT count(all a) FROM testtable;

返回結果如下：

+--------------+
| count(all a) |
+--------------+
|            3 |
+--------------+

MAX

max(x)

命令說明：該函數用於計算最大值。
輸入實值型別：該函數支援輸入任意類型的參數，但是BOOLEAN類型的資料不允許參與運算。
說明
當列中的值為NULL時，該行不參與計算。
傳回值類型：與該函數的輸入實值型別保持一致。

樣本：

SELECT max(a) FROM testtable;

返回結果如下：

+--------+
| max(a) |
+--------+
|      3 |
+--------+

MIN

min(value x)

命令說明：該函數用於計算最小值。
輸入實值型別：該函數支援輸入任意類型的參數，但是BOOLEAN類型的資料不允許參與運算。
說明
當列中的值為NULL時，該行不參與計算。
傳回值類型：與該函數的輸入實值型別保持一致。

樣本：

SELECT min(a) FROM testtable;

返回結果如下：

+--------+
| min(a) |
+--------+
|      1 |
+--------+

STD、STDDEV或STDDEV_POP

std(x)
stddev(x)
stddev_pop(x)

命令說明：返回數值的總體標準差。
輸入實值型別：BIGINT或DOUBLE。
傳回值類型：DOUBLE。

樣本：

樣本一：

SELECT std(a) FROM testtable;

返回結果如下：

+-------------------+
| std(a)            |
+-------------------+
| 0.816496580927726 |
+-------------------+

樣本二：

SELECT stddev_pop(a) FROM testtable;

返回結果如下：

+-------------------+
| stddev_pop(a)     |
+-------------------+
| 0.816496580927726 |
+-------------------+

STDDEV_SAMP

stddev_samp(x)

命令說明：返回一組數值（整數、小數或浮點）的樣本標準差。
輸入實值型別：BIGINT或DOUBLE。
傳回值類型：DOUBLE。

樣本：

SELECT stddev_samp(a) FROM testtable;

返回結果如下：

+----------------+
| stddev_samp(a) |
+----------------+
|            1.0 |
+----------------+

SUM

sum(x)

命令說明：該函數用於計算匯總值。
輸入實值型別：BIGINT、DOUBLE或FLOAT。
傳回值類型：BIGINT。

樣本：

SELECT sum(a) FROM testtable;

返回結果如下：

+--------+
| sum(a) |
+--------+
|      6 |
+--------+

VARIANCE

variance(x)

命令說明：返回一組數值（整數、小數或浮點）的總體標準方差。
說明
- VARIANCE()會忽略值為NULL的行。因此若一組數值全為NULL，VARIANCE()會直接返回NULL。
- VARIANCE()函數作為標準SQL的延伸，您也可以使用標準SQL函數VAR_POP()來代替。
輸入實值型別：BIGINT或DOUBLE。
傳回值類型：DOUBLE。

樣本：

SELECT variance(a)  FROM testtable;
+----------------------------+
|         variance(a)        |
+----------------------------+
|    0.6666666666666666      |

VAR_POP

var_pop(x)

命令說明：返回一組數值x（整數、小數或浮點）的總體標準方差。
說明
- VAR_POP()會忽略值為NULL的行。因此若一組數值全為NULL，VAR_POP()會直接返回NULL。
- 也可以使用VARIANCE()函數，具有相同的意義，但VARIANCE()不是標準的SQL函數。
輸入實值型別：BIGINT或DOUBLE。
傳回值類型：DOUBLE。

樣本：

SELECT var_pop(a) FROM testtable;

返回結果如下：

+--------------------+
| var_pop(a)         |
+--------------------+
| 0.6666666666666666 |
+--------------------+

VAR_SAMP

var_samp(x)

命令說明：返回一組數值（整數、小數或浮點）的樣本方差。
輸入實值型別：BIGINT或DOUBLE。
傳回值類型：DOUBLE。

樣本：

SELECT var_samp(a)  FROM testtable;

返回結果如下：

+-------------+
| var_samp(a) |
+-------------+
|         1.0 |
+-------------+

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] col_name
             [ORDER BY col_name [ASC | DESC]]
             [SEPARATOR str_val])

參數	是否必填	說明
`DISTINCT`	選填	指定需要去重的列。
`ORDER BY`		指定需要在分組內部進行排序的列，支援如下排序方式： `ASC`：升序。 `DESC`：降序。若未指定排序方式，預設按照升序排序。
`SEPARATOR`		指定分組內部各值間的分隔字元。若未指定分隔字元，預設使用英文逗號（,）分隔。

命令說明：該函數用於將GROUP BY返回結果中屬於同一個分組的值串連起來，返回一個字串結果。
說明
僅當需要使用GROUP_CONCAT函數進行串連的列中所有取值均為NULL時，才會輸出NULL。
輸入實值型別：字串。
傳回值類型：字串。

樣本

本樣本以person表為例介紹如何使用GROUP_CONCAT函數，person表建立語句如下：

CREATE TABLE person(id INT,name VARCHAR,age INT ) DISTRIBUTED BY HASH(id);

使用如下語句往表中插入資料：

INSERT INTO person VALUES (1,'mary',13),(2,'eva',14),(2,'adam',13),(3,'eva',13),(3,null,13),(3,null,null),(4,null,13),(4,null,null);

現需要根據id列進行分組，並通過GROUP_CONCAT函數將ID相同的name列展示出來，展示結果時需要對name列去重，並按照name列進行降序排序，多個name列間用#分隔，語句如下：

SELECT
  id,
  GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#')
FROM person 
  GROUP BY id;

返回結果如下：

+------+--------------------------------------------------------------+
| id   | GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR '#') |
+------+--------------------------------------------------------------+
|    2 | eva#adam                                                     |
|    1 | mary                                                         |
|    4 | NULL                                                         |
|    3 | eva                                                          |
+------+--------------------------------------------------------------+