Lindorm彙總函式的用法和樣本 - Lindorm

Lindorm提供了一系列彙總函式，例如統計函數COUNT，求和函數SUM等，以滿足您對資料分析的多樣化需求。

引擎與版本

本文適用的引擎和版本如下：

寬表引擎。無版本要求。
時序引擎。版本為3.4.7及以上。
說明
如何查看或升級時序引擎版本，請參見時序引擎版本說明和升級小版本。

函數列表

Lindorm支援的彙總函式如下表所示。

函數	說明
AVG	返回指定列的平均值。
COUNT	返回指定條件的非空值的個數。
MAX	返回指定列的最大值。
MIN	返回指定列的最小值。
SUM	返回指定列的總和。
HEAD	返回指定列（或運算式）的第一個非NULL值。
GROUP_CONCAT	將投影列（一列或者多列）按照分組列（`GROUP BY`後的列）進行分組。對於每一組，將投影列按照排序列（一列或者多列）進行排序之後，拼接成一個長字串，返回該拼接的字串。

本文提供的樣本全部基於以下表結構和資料：

-- 建立樣本表sensor
CREATE TABLE sensor(
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

-- 批量寫入資料
UPSERT INTO sensor(device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6), ('F07A1261','south-cn','2021-04-22 15:33:00',18.1), ('F07A1261','south-cn','2021-04-22 15:33:10',19.7);

AVG

返回指定列的平均值。

文法

AVG(column_name)

參數說明

參數	描述
column_name	列名。僅支援數實值型別。

樣本

計算所有裝置的溫度的平均值。

SELECT AVG(temperature) AS temperature FROM sensor;

返回結果：

+--------------------+
| temperature        |
+--------------------+
| 16.133333333333333 |
+--------------------+

COUNT

返回指定條件的非空值的個數。

文法

COUNT(*|column_name)

參數說明

參數	描述
column_name	列名。

樣本

統計所有裝置的溫度的值個數。

SELECT COUNT(temperature) AS temperature FROM sensor;

返回結果：

+-------------+
| temperature |
+-------------+
| 3           |
+-------------+

統計sensor表中device_id為F07A1260的行數。

SELECT COUNT(*) FROM sensor WHERE device_id = 'F07A1260';

返回結果：

+--------+
| EXPR$0 |
+--------+
| 1      |
+--------+

統計sensor表的行數。

SELECT COUNT(*) FROM sensor;

返回結果：

+--------+
| EXPR$0 |
+--------+
| 3      |
+--------+

MAX

返回指定列的最大值。

文法

MAX(column_name)

參數說明

參數	描述
column_name	列名。

樣本

查詢所有裝置的溫度的最大值。

SELECT MAX(temperature) FROM sensor;

返回結果：

+--------+
| EXPR$0 |
+--------+
| 19.7   |
+--------+

MIN

返回指定列的最小值。

文法

MIN(column_name)

參數說明

參數	描述
column_name	列名。

樣本

查詢所有裝置的溫度的最小值。

SELECT MIN(temperature) FROM sensor;

返回結果：

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

SUM

返回指定列的總和。

文法

SUM(column_name)

參數說明

參數	描述
column_name	列名。僅支援數實值型別。

樣本

查詢所有裝置的溫度之和。

SELECT SUM(temperature) FROM sensor;

返回結果：

+--------+
| EXPR$0 |
+--------+
| 48.4   |
+--------+

HEAD

返回指定列（或者運算式）expr1的第一個非NULL值，且支援根據expr2的值進行排序。

重要

要求寬表引擎為2.7.9及以上版本，且Lindorm SQL為2.8.8.0及以上版本。您可以在控制台查看寬表引擎版本和Lindorm SQL版本，並升級小版本至2.7.9及以上版本。

文法

HEAD (expr1 [ ORDER BY expr2 [ DESC ] ] )

參數說明

參數

描述

expr1

待輸出的運算式。

運算式的類型支援所有的整型（INTEGER、BIGINT、SMALLINT、TINYINT）、浮點型（FLOAT、DOUBLE、DECIMAL）、布爾型（BOOLEAN）、字串（VARCHAR、CHAR）、TIMESTAMP、JSON、Geometry。

expr2

指定排序的方法。預設時，按照第一主鍵列排序。

重要

expr2不支援與其他彙總函式嵌套使用，例如不支援HEAD(c1 ORDER BY COUNT(c2))。

樣本

查詢temperature的最小值和最大值。

SELECT HEAD(temperature ORDER BY temperature), HEAD(temperature ORDER BY temperature DESC) FROM sensor;

返回結果：

+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
| 10.6   | 19.7   |
+--------+--------+

查詢LENGTH(device_id)最小值對應的temperature。

SELECT HEAD(temperature ORDER BY LENGTH(device_id)) FROM sensor;

返回結果：

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

GROUP_CONCAT

將投影列（一列或者多列）按照分組列（GROUP BY後的列）進行分組。對於每一組，將投影列按照排序列（一列或者多列）進行排序之後，拼接成一個長字串，返回該拼接的字串。

重要

文法

GROUP_CONCAT ( [ DISTINCT ] expr (, expr ... )*
             [ ORDER BY col_name [ DESC ] (, col_name [ DESC ])* ]
             [ SEPARATOR str_val ] )

重要

DISTINCT與ORDER BY關鍵字不能同時使用。

參數說明

參數	描述
expr	投影列。
col_name	排序列。
str_val	拼接投影列時的分隔字元。

樣本

假設樣本表t1的結構及表中資料如下：

-- 建立樣本表t1
CREATE TABLE t1 (p1 INT, p2 VARCHAR, c1 INT, c2 VARCHAR, c3 DOUBLE, PRIMARY KEY(p1, p2));

-- 插入資料
UPSERT INTO t1(p1, p2, c1, c2, c3) VALUES 
(1, 'a', 10, 'java', 10.23),
(1, 'b', 10, 'c/c++', 5.76),
(1, 'c', 18, 'python', 8.21),
(2, 'a', 40, 'sql', 3.14),
(2, 'b', 100, 'c#', 4.96),
(2, 'c', 66, 'matlab', 7.01);

樣本一：將c1根據p1分組。

SELECT p1, GROUP_CONCAT(c1) FROM t1 GROUP BY p1;

返回結果：

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,100,66 |
+----+-----------+

樣本二：將c1根據p1分組，且移除重複值。

SELECT p1, GROUP_CONCAT(DISTINCT c1) FROM t1 GROUP BY p1;

返回結果：

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 18,10     |
| 2  | 100,40,66 |
+----+-----------+

樣本三：將c1根據p1分組，且按照自身升序。

SELECT p1, GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY p1;

返回結果：

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,66,100 |
+----+-----------+

樣本四：將c1根據p1分組，且按照c3降序。

SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC) FROM t1 GROUP BY p1;

返回結果：

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,18,10  |
| 2  | 66,100,40 |
+----+-----------+

樣本五：將c1根據p1分組，且按照c3降序，並將結果用豎線|拼接成字串。

SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC SEPARATOR '|') FROM t1 GROUP BY p1;

返回結果：

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10|18|10  |
| 2  | 66|100|40 |
+----+-----------+